Isolated table Error

[former_member402770]

Hi,

Could you please help me to root cause the problem of the error in we bi report as iam experiencing this for the first. Looks like me its a database error

I have two data provider with one as delhi class and other as mumbai class belong to same universe but no joins at the design as it is isolated table.

I want to merge prompts and webi common dimension for my reqd of the report .

Thanks,

Dinya

[amitrathi239 · Accepted solution]

Hi,

have you done some customization in the webi query SQL? reverify if you have by mistake added some objects from delhi class to mumbai query or vice-versa?

or first try to run individual query in webi and see if it's working or not.

Share your webi report SQL.

Amit

[former_member402770 · Accepted solution]

Hi Amit,

I am getting an different error now, iam trying to reproduce the same database error, but also not sure on this below too,..

[amitrathi239 · Accepted solution]

Hi,

This is generic error.close the Rich client and try again.Also in the Fiscal/Year prompt use Equal To instead of Inlist operator and try.

or try to restart the adaptive server which holding the Data Federation service in CMC and try.

Amit

[former_member402770 · Accepted solution]

Hi Amit,

This is generic error.close the Rich client and try again.Also in the Fiscal/Year prompt use Equal To instead of Inlist operator and try.

    tried this, but ended up in same promptDPCommandsEx error:

Investigated, there are five folder class, below are the ones i tested:

Delhi Conn Class is working

Mumbai not working:

at  webi report level, objects i dragged are Customer Order No, Tot1 in to an separate report delhi and   mumbai separetly..

Mumbai Conn alone not working on running the query:

SQL IN WEBI :

WITH

"DT_Mumbai" AS NATIVE ( "Test CONNECTION",

'SELECT year(Cust.FiscalMonth) AS Year,month(Cust.FiscalMonth) AS Month, Cust.FiscalMonth, ProjHistory.tot1, Cust.C_Total, Cust.CustomerOrderNo, tableEntities.HFMCode, substring(tableInstances.Code,1,3) AS CodeCode,Cust.BKDate, Cust.OrderType, ProjDataSources.DataSourceName FROM (((Cust INNER JOIN ProjHistory ON (Cust.OrdNo = ProjHistory.OrdNo) AND (Cust.Instance = ProjHistory.Instance))) ) INNER JOIN tableJobs ON Cust.Ord = tableJobs.Ord INNER JOIN tableInstances on (Cust.Instance = tableInstances.Instance) INNER JOIN tableEntities on (tableEntities.Code = tableInstances.Code) INNER JOIN ProjDataSources on (ProjDataSources.Code = tableInstances.Code) WHERE ((ProjHistory.Action=''Booked'') AND ((Cust.Show)<>0));',

"Year" INTEGER, "Month" INTEGER, "FiscalMonth" TIMESTAMP, "Action" VARCHAR (50), "OrdNo" VARCHAR (30), "Ord" VARCHAR (30), "Description" VARCHAR (100), "LOB" VARCHAR (3), "BusUnit" VARCHAR (3), "tot1" DECIMAL (19, 4), "C_Total" DECIMAL (19, 4), "CustomerOrderNo" VARCHAR (30), "JobType" VARCHAR (3), "HFMCode" VARCHAR (100), "CodeCode" VARCHAR (3), "BKDate" TIMESTAMP, "OrderType" VARCHAR (30), "DataSourceName" VARCHAR (100)

)

SELECT

  Table__14."Ord",

  sum(Table__14."tot1")

FROM

  "DT_Mumbai"  Table__14

WHERE

  concat(( ifelse(length(convert(Table__14."Month",Varchar))=1,

concat('00',convert(Table__14."Month",Varchar)),

concat('0',convert(Table__14."Month",Varchar))) ),( concat('.',convert(Table__14."Year",varchar)) ))  =  @Prompt('Fiscal Year/Period (Selection Options)','A','Test Connection\Dt Mumbai\Fiscal Year/Period (Selection Options)',Mono,Free,Persistent,,User:0)

GROUP BY

  1

[amitrathi239 · Accepted solution]

Check all objects from mumbai class are parsing or not.might be issue with the derived table SQL.

[former_member402770 · Accepted solution]

Hi Amit and Experts,

The sql i shared as substring in it not sure derived table itself an best practise, May be you or experts can tune this sql and revert me..I know you are not good in sql like me but still someone can shred light on this beacuse i might have to change the universe design if not using the tables.

Surprisingly there are five derived tables with same sql, it is working fine for other three but failing for two, cross-checked, parsed but no clue. running long time and throwing above post screenshot errror.

Thanks,

Dinya

[amitrathi239 · Accepted solution]

Hi,

have you tried to run the same sql on the data base?

Edit the derived table and select option database specific and try.

also share the derived table SQL.

Amit

[former_member402770 · Accepted solution]

Hi Amit,

  have you tried to run the same sql on the data base? Yes it runs only for lessthan 1 minute.

  It is Database specific connection only and derived sql as below:

Derived Table SQL:

SELECT year(Cust.FiscalMonth) AS Year,month(Cust.FiscalMonth) AS Month, Cust.FiscalMonth, ProjHistory.tot1, Cust.C_Total, Cust.CustomerOrderNo, tableEntities.HFMCode, substring(tableInstances.Code,1,3) AS CodeCode,Cust.BKDate, Cust.OrderType, ProjDataSources.DataSourceName FROM (((Cust INNER JOIN ProjHistory ON (Cust.OrdNo = ProjHistory.OrdNo) AND (Cust.Instance = ProjHistory.Instance))) ) INNER JOIN tableJobs ON Cust.Ord = tableJobs.Ord INNER JOIN tableInstances on (Cust.Instance = tableInstances.Instance) INNER JOIN tableEntities on (tableEntities.Code = tableInstances.Code) INNER JOIN ProjDataSources on (ProjDataSources.Code = tableInstances.Code) WHERE ((ProjHistory.Action=''Booked'') AND ((Cust.Show)<>0));

Pls. note that the sql for webi is in above post..Appreciate your quick help on webi running for long time and throwing the above post prompt internal error...

Thanks,

Dinya.

[amitrathi239 · Accepted solution]

Hi,

Remove this "AND ((Cust.Show)<>0)" part from the derived table SQL and try.

If report will run then later you can add this condition at webi query level.

Amit

[former_member402770 · Accepted solution]

Hi Amit,

  It worked now the error gone as you suggested.. Removed this "AND ((Cust.Show)<>0)" part from the derived table SQL.

The webi report in rich client runs for some long time just for one object and one query prompt filter and retrived the output now.

Amazing this is what expected what is the background of the issue why this needs to be removed

I also added back this condition at webi query level, the report works charm..iam really happy.

What is your thought on improve the running time still better should i try check with removing WHERE ((ProjHistory.Action=''Booked'') AND ((Cust.Show)<>0)) and apply this condition at webi level or iam good with removing  "AND ((Cust.Show)<>0)"

Thanks,

Dinya

[amitrathi239 · Accepted solution]

Hi,

not sure about the exact cause.Sometimes back i saw one thread where issue was with non equi joins.Similar to your condition where excluding 0.

One more thing you can try to instead of putting webi query filter (Cust.Show)<>0,apply this filter at webi report/table level and exclude the zeros from there.

I don't think so issue with ((ProjHistory.Action=''Booked'')  condition.but again you can try this.removing from derived table put the condition at webi query level filter.Also again put the condition at webi report/table level and compare.

See if there is a difference.

Amit

[former_member402770 · Accepted solution]

Hi Amit,

Good it worked this issue was around for an while

I close this thread now..

(Cust.Show)<>0,apply this filter at webi report/table level and exclude the zeros. Pls post you have screenshot  for this.

Thanks,

Dinya

[amitrathi239 · Accepted solution]

Hi,

Follow these steps.

Right click on the table->Filter->Add filter.

2) click on add filter and select the object

3) select the operator and add the value & click ok/apply.

Amit

[former_member402770 · Accepted solution]

Hi Amit,

  Thanks got it. Really appreciate your quick help..Wonder this operator applied at measure level instead dimension. Anyway your screenshot does help me and usefull for others too...I will apply the same.

Thanks,

Dinya

[amitrathi239 · Accepted solution]

Hi,

You can apply the filter at dimension or measure objects/variables.

difference is about the LOV's display to select the value in the filter condition.

If filter is based on the dimension object you will get the LOV's panel to select the values or manually enter the values.

If filter is based on the Measure then you will not get the lov's list to select the values.

Amit

[former_member402770 · Accepted solution]

Hi Amit,

Could you please help on this issue again i removed <> 0 condition but given same error dialong with index as 9..

Derived Table SQL:

SELECT year(Cust.FiscalMonth) AS Year,month(Cust.FiscalMonth) AS Month, Cust.FiscalMonth, ProjHistory.tot1, Cust.C_Total, Cust.CustomerOrderNo, tableEntities.HFMCode,substring(tableInstances.Code,1,3) AS CodeCode,Cust.BKDate, Cust.OrderType, ProjDataSources.DataSourceName FROM (((Cust INNER JOIN ProjHistory ON (Cust.OrdNo = ProjHistory.OrdNo) AND (Cust.Instance = ProjHistory.Instance))) ) INNER JOIN tableJobs ON Cust.Ord = tableJobs.Ord INNER JOIN tableInstances on (Cust.Instance = tableInstances.Instance) INNER JOIN tableEntities on (tableEntities.Code = tableInstances.Code) INNER JOIN ProjDataSources on (ProjDataSources.Code = tableInstances.Code) WHERE ((ProjHistory.Action=''Booked''));

Webi Error Again:

Thanks,

Dinya

[amitrathi239 · Accepted solution]

Hi,

can you try after removing this condition from derived table.

WHERE ((ProjHistory.Action=''Booked''));

If it will then later add this condition in webi level.

Amit

[former_member402770 · Accepted solution]

Amit,

  Still not working same issue.

Derived sql:

SELECT year(Cust.FiscalMonth) AS Year,month(Cust.FiscalMonth) AS Month, Cust.FiscalMonth, ProjHistory.tot1, Cust.C_Total, Cust.CustomerOrderNo, tableEntities.HFMCode, tableInstances.Code AS CodeCode,Cust.BKDate, Cust.OrderType, ProjDataSources.DataSourceName FROM (((Cust INNER JOIN ProjHistory ON (Cust.OrdNo = ProjHistory.OrdNo) AND (Cust.Instance = ProjHistory.Instance))) ) INNER JOIN tableJobs ON Cust.Ord = tableJobs.Ord INNER JOIN tableInstances on (Cust.Instance = tableInstances.Instance) INNER JOIN tableEntities on (tableEntities.Code = tableInstances.Code) INNER JOIN ProjDataSources on (ProjDataSources.Code = tableInstances.Code);

Webi SQL:

WITH

"DT_Mumbai" AS NATIVE ( "Test CONNECTION",

'SELECT year(Cust.FiscalMonth) AS Year,month(Cust.FiscalMonth) AS Month, Cust.FiscalMonth, ProjHistory.tot1, Cust.C_Total, Cust.CustomerOrderNo, tableEntities.HFMCode, tableInstances.Code AS CodeCode,Cust.BKDate, Cust.OrderType, ProjDataSources.DataSourceName FROM (((Cust INNER JOIN ProjHistory ON (Cust.OrdNo = ProjHistory.OrdNo) AND (Cust.Instance = ProjHistory.Instance))) ) INNER JOIN tableJobs ON Cust.Ord = tableJobs.Ord INNER JOIN tableInstances on (Cust.Instance = tableInstances.Instance) INNER JOIN tableEntities on (tableEntities.Code = tableInstances.Code) INNER JOIN ProjDataSources on (ProjDataSources.Code = tableInstances.Code);',

"Year" INTEGER, "Month" INTEGER, "FiscalMonth" TIMESTAMP, "Action" VARCHAR (50), "OrdNo" VARCHAR (30), "Ord" VARCHAR (30), "Description" VARCHAR (100), "LOB" VARCHAR (3), "BusUnit" VARCHAR (3), "tot1" DECIMAL (19, 4), "C_Total" DECIMAL (19, 4), "CustomerOrderNo" VARCHAR (30), "JobType" VARCHAR (3), "HFMCode" VARCHAR (100), "CodeCode" VARCHAR (3), "BKDate" TIMESTAMP, "OrderType" VARCHAR (30), "DataSourceName" VARCHAR (100)

)

SELECT

  Table__14."Ord",

  sum(Table__14."tot1")

FROM

  "DT_Mumbai"  Table__14

WHERE

  concat(( ifelse(length(convert(Table__14."Month",Varchar))=1,

concat('00',convert(Table__14."Month",Varchar)),

concat('0',convert(Table__14."Month",Varchar))) ),( concat('.',convert(Table__14."Year",varchar)) ))  =  @Prompt('Fiscal Year/Period (Selection Options)','A','Test Connection\Dt Mumbai\Fiscal Year/Period (Selection Options)',Mono,Free,Persistent,,User:0) AND

GROUP BY

  1

Thanks,

Dinya

[amitrathi239 · Accepted solution]

Hi,

Why this coming then in webi report?

"Year" INTEGER, "Month" INTEGER, "FiscalMonth" TIMESTAMP, "Action" VARCHAR (50), "OrdNo" VARCHAR (30), "Ord" VARCHAR (30), "Description" VARCHAR (100), "LOB" VARCHAR (3), "BusUnit" VARCHAR (3), "tot1" DECIMAL (19, 4), "C_Total" DECIMAL (19, 4), "CustomerOrderNo" VARCHAR (30), "JobType" VARCHAR (3), "HFMCode" VARCHAR (100), "CodeCode" VARCHAR (3), "BKDate" TIMESTAMP, "OrderType" VARCHAR (30), "DataSourceName" VARCHAR (100)

)

SELECT

  Table__14."Ord",

  sum(Table__14."tot1")

FROM

  "DT_Mumbai"  Table__14

WHERE

  concat(( ifelse(length(convert(Table__14."Month",Varchar))=1,

concat('00',convert(Table__14."Month",Varchar)),

concat('0',convert(Table__14."Month",Varchar))) ),( concat('.',convert(Table__14."Year",varchar)) ))  =  @Prompt('Fiscal Year/Period (Selection Options)','A','Test Connection\Dt Mumbai\Fiscal Year/Period (Selection Options)',Mono,Free,Persistent,,User:0) AND

GROUP BY

  1