Left outer join does not work in IDT

[Former Member]

Hi,

I have a universe from 5 DSOs in IDT.When i join these 5 DSOs with left outer join, it works like inner join.Always filtering the data, when i refresh report.I use the sql parameters as in the image;

Also here is the query that want to run;

SELECT

  Table__32."0TXTMD",

  Table__1."ZFREETXT1",

  Table__9."0TXTMD"

FROM

  "ZHRHAP07"."PUBLIC"."T0HAP_AR_EMP"  Table__9 LEFT OUTER JOIN "ZHRHAP07"."PUBLIC"."IZHRHAP07"  Table__2 ON (Table__9."0HAP_AR_EMP"=Table__2."0HAP_AR_EMP")

   LEFT OUTER JOIN "ZHRHAP07"."PUBLIC"."IZHRHAP07"  Table__1 ON (Table__1."0HAP_AR_ID"=Table__2."0HAP_AR_ID"  AND  Table__1."ZHRHEDEF"=Table__2."ZHRHEDEF")

   LEFT OUTER JOIN "ZHRHAP06"."PUBLIC"."IZHRHAP06"  Table__31 ON (Table__31."ZHRHEDEF"=Table__1."ZHRHEDEF" )

   LEFT OUTER JOIN "ZHRHAP06"."PUBLIC"."TZHRHEDEF6"  Table__32 ON (Table__32."ZHRHEDEF6"=Table__31."ZHRHEDEF6")

WHERE

  (

   Table__1."0CALYEAR"  IN  @dpvalue('A', DP0.DO3)

   AND

   Table__9."REFERENCE_DATE"  =  @Prompt(Key Date)

   AND

   Table__2."REFERENCE_DATE"  =  @Prompt(Key Date)

   AND

   Table__1."REFERENCE_DATE"  =  @Prompt(Key Date)

   AND

   Table__31."REFERENCE_DATE"  =  @Prompt(Key Date)

   AND

   Table__32."REFERENCE_DATE"  =  @Prompt(Key Date)

  )

I can see left outer join in the script of query.But its working like inner join.Please help me about this.

Regards,

Utku

[former_member4998]

Hi..

SELECT

Table__32."0TXTMD",

Table__1."ZFREETXT1",

Table__9."0TXTMD"

FROM

"ZHRHAP07"."PUBLIC"."T0HAP_AR_EMP" Table__9

   LEFT OUTER JOIN "ZHRHAP07"."PUBLIC"."IZHRHAP07"  Table__2 ON (Table__9."0HAP_AR_EMP"=Table__2."0HAP_AR_EMP")

Get all Common Records (Table 9 and Table 2) and all the records from Table 9

LEFT OUTER JOIN "ZHRHAP07"."PUBLIC"."IZHRHAP07" Table__1 ON (Table__1."0HAP_AR_ID"=Table__2."0HAP_AR_ID" AND  Table__1."ZHRHEDEF"=Table__2."ZHRHEDEF")

Get all Common Records (Table 1 and Table 2 – two restrictions) and all the records from Table 2

LEFT OUTER JOIN "ZHRHAP06"."PUBLIC"."IZHRHAP06"  Table__31 ON (Table__31."ZHRHEDEF"=Table__1."ZHRHEDEF" )

Get all Common Records (Table 31 and Table 1) and all the records from Table 1

LEFT OUTER JOIN "ZHRHAP06"."PUBLIC"."TZHRHEDEF6"  Table__32 ON (Table__32."ZHRHEDEF6"=Table__31."ZHRHEDEF6")

Get all Common Records (Table 32 and Table 31) and all the records from Table 31

WHERE

(

Table__1."0CALYEAR"  IN  @dpvalue('A', DP0.DO3)

AND

Table__9."REFERENCE_DATE"  =  @Prompt(Key Date)

AND

Table__2."REFERENCE_DATE"  =  @Prompt(Key Date)

AND

Table__1."REFERENCE_DATE"  =  @Prompt(Key Date)

AND

Table__31."REFERENCE_DATE"  =  @Prompt(Key Date)

AND

Table__32."REFERENCE_DATE"  =  @Prompt(Key Date)

)

Check from one by one join getting the correct data according to the requirements. Instead of joining all the tables once.

And also, is there any specific reason creating universe on top of DSO’s …coz we have other better below options

creating universe on 5 DSO,

• You can join all the DSO’s using infoset and build universe on top of that
• or Create report on top of infoset, and then build universe on top of report.

[Former Member]

Hi,

Thank you for your respond.I dont have rights for create infoset.Thats why i have to do this on universe level.I also tried to run all table before but its still working like inner join.

Regards,

Utku

[former_member4998]

Hi..

If you do not have access to create infoset request your BW team to create infoset and then build universe on top of that, it will improve the performance.

I am suggesting run the first join and check getting correct results

SELECT

Table__32."0TXTMD",

Table__1."ZFREETXT1",

Table__9."0TXTMD"

FROM

"ZHRHAP07"."PUBLIC"."T0HAP_AR_EMP" Table__9

   LEFT OUTER JOIN "ZHRHAP07"."PUBLIC"."IZHRHAP07"  Table__2 ON (Table__9."0HAP_AR_EMP"=Table__2."0HAP_AR_EMP")

Get all Common Records (Table 9 and Table 2) and all the records from Table 9

Then again join one more table and check the results.

SELECT

Table__32."0TXTMD",

Table__1."ZFREETXT1",

Table__9."0TXTMD"

FROM

"ZHRHAP07"."PUBLIC"."T0HAP_AR_EMP" Table__9

   LEFT OUTER JOIN "ZHRHAP07"."PUBLIC"."IZHRHAP07"  Table__2 ON (Table__9."0HAP_AR_EMP"=Table__2."0HAP_AR_EMP")

Get all Common Records (Table 9 and Table 2) and all the records from Table 9

LEFT OUTER JOIN "ZHRHAP07"."PUBLIC"."IZHRHAP07" Table__1 ON (Table__1."0HAP_AR_ID"=Table__2."0HAP_AR_ID" AND  Table__1."ZHRHEDEF"=Table__2."ZHRHEDEF")

Get all Common Records (Table 1 and Table 2 – two restrictions) and all the records from Table 2

Like trial and error methods

[Former Member]

Hi,

I also tried to run one by one, but its same again.Now i will try infoset.

Regards,

Utku

[former_member4998]

Hi..

once crating infoset, You can also request to build Bex Q on top of that to include all the calculations, restrictions and all the business logic's. All the best

[Former Member]

Hi,

My system is BW 7.0, thats why i think, IDT does not see my infoset with DSOs .It only see infosets from sq02.But in sq02 i cant build infoset from DSOs.I also cant build bex q, because ill use drill option.So i have to use IDT for joining.I have to fix it

Thanks,

Regards,

Utku

[former_member4998]

Hi Utku,

Is your problem had solve or not?

[Former Member]

Hi,

I couldnt make it with BO.I have solved my problem by using ABAP on BW.

Utku

[former_member4998]

Hi..

It’s good pushing all the logic's into DB level.

Request to please mark the blog helpful & answered, so it’s easy to find SDN expertes unanswered q’s and provide answers.