Link to home
Start Free TrialLog in
Avatar of AFRINEND
AFRINEND

asked on

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
Avatar of monosodiumg
monosodiumg

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.



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

Avatar of AFRINEND

ASKER

That solution does'nt work
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.


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.
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.
ASKER CERTIFIED SOLUTION
Avatar of monosodiumg
monosodiumg

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial