Full outer join giving only left outer join

I Have the following query getting some budgetdetails(gl00201) and the associated actual amounts of that specifc year and date from (gl00110),i have given a fuller outer join on the common keys between the gl00201 and gl00110 ,but still the output am getting is the left outer join,gl00102 is the table from where i am getting the accounts description. kinldy Let me know what is the problem with this query.

SELECT distinct     GL00100.actdescr "ACCTDESC100",  GL00100.actINDX "ACTINDX100",        
GL00102.ACCATDSC "ACCDES102", gl00102.ACCATNUM "ACCAT102",
ISNULL(GL00201.BUDGETAMT,0.00)BUDGETAMT,  gl00201.accatnum "BUDCATNUM",gl00201.ACTINDX "BUDGETIN
D",gl00201.periodid [BudgetPeriodid],gl00201.BUDGETID [Budgetid],
ISNULL(GL10110.PERDBLNC,0.00) CURRENTAMT,GL10110.YEAR1 CURRENTYR,gl10110.accatnum "CURRENTCATNUM",gl10110.ACTINDX "ACTAMTIND",  GL10110.PERIODID ACTPERID
FROM
    (( GL00102 LEFT OUTER JOIN  GL00100 ON GL00102.ACCATNUM = GL00100.ACCATNUM
     LEFT OUTER JOIN GL00201  ON GL00201.ACTINDX=gl00100.ACTINDX and GL00102.ACCATNUM=GL00201.ACCATNUM
     and budgetid ='budget3' and GL00201.periodid Between 0 and 3) FULL OUTER JOIN  GL10110 ON GL00102.ACCATNUM =
GL10110.ACCATNUM and gl10110.year1='2000'
and GL10110.periodid Between 0 and 3 and  gl00201.actindx=gl10110.actindx and gl00201.PERIODID=gl10110.PERIODID)
WHERE
    gl00102.accatnum between 1 and 30
    order by gl00102.accatnum,GL00100.ACTINDX
AFRINENDAsked:
Who is Participating?
 
monosodiumgConnect With a Mentor Commented:
Naz,
Got the tables.Thx.

You've got a Where clause on GL00102.ACCATNUM so all rows in the resultset will correspond to rows in that table and any joins from that table to other tables might as well be LEFT.Making them FULL makes no difference. It makes sense that if you should only be getting resutls for which you have account descriptions (GL00102) ; I'd assume there are no accounts anywhere for which you do not have a GL00102 record.

What is the relationship between ACTINDX and ACCATNUM?

I'm a bit worried about the fact the so many column reappear in all the tables, e.g. ACTNUMBR_1,_2,_3, PERIODID, DEX_ROW_ID. There may be nothing wrong with that but often when I see the same column over and over again it is becuase there is a normlisation issue.

BTW, the tbls you supplied have NO constraints. Can you supply the DDL for the tables?  Without it I can't even assume that ACCTINDX is unique for GL00100, which it seems to be on the edata sample.

It might be better if you explained in English what your query is supposed to achieve.
0
 
monosodiumgCommented:
The Where clause "gl00102.accatnum between 1 and 30" requires that the gl00102.accatnum  be non-null so it will eliminate any rows on the right hand-side (GL10110) that do not have a corresponding gl00102 row. Put that cluase into the join conditions.



0
 
monosodiumgCommented:
Naz,

In response to your out of band email (stick to the forum please):

...
FROM
    (( GL00102 LEFT OUTER JOIN  GL00100 ON GL00102.ACCATNUM = GL00100.ACCATNUM
     LEFT OUTER JOIN GL00201  ON GL00201.ACTINDX=gl00100.ACTINDX and GL00102.ACCATNUM=GL00201.ACCATNUM
     and budgetid ='budget3' and GL00201.periodid Between 0 and 3) FULL OUTER JOIN  GL10110 ON GL00102.ACCATNUM =
GL10110.ACCATNUM and gl10110.year1='2000'
and GL10110.periodid Between 0 and 3 and  gl00201.actindx=gl10110.actindx and gl00201.PERIODID=gl10110.PERIODID) and gl00102.accatnum between 1 and 30
    order by gl00102.accatnum,GL00100.ACTINDX

0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
AFRINENDAuthor Commented:
That solution does'nt work
0
 
monosodiumgCommented:
Can you elaborate?

Try:
...
    (( GL00102 LEFT OUTER JOIN  GL00100 ON GL00102.ACCATNUM = GL00100.ACCATNUM
     LEFT OUTER JOIN GL00201  ON GL00201.ACTINDX=gl00100.ACTINDX and GL00102.ACCATNUM=GL00201.ACCATNUM
     and budgetid ='budget3' and GL00201.periodid Between 0 and 3) FULL OUTER JOIN  (select * from GL10110 where and gl00102.accatnum between 1 and 30 ) Foo ON GL00102.ACCATNUM =
Foo.ACCATNUM and Foo.year1='2000'
and Foo.periodid Between 0 and 3 and  gl00201.actindx=Foo.actindx and gl00201.PERIODID=Foo.PERIODID)
    order by gl00102.accatnum,Foo.ACTINDX

If that doesn't work, please post some sample data.

0
 
AFRINENDAuthor Commented:

No, the above query doesn't work,i have a zip file with the required data and tables,how can i send that  attachement to you.i did not find any provision to attach files here.

This is quite urgent.
0
 
monosodiumgCommented:
Send me them by email (see my profile) or publish them on a web site or get a yahoo account on which you can publish files.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.