We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Full outer join giving only left outer join

AFRINEND
AFRINEND asked
on
Medium Priority
489 Views
Last Modified: 2008-02-01
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
Comment
Watch Question

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

Author

Commented:
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.

Author

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.
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.
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.