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)BUDGET AMT, gl00201.accatnum "BUDCATNUM",gl00201.ACTIND X "BUDGETIN
D",gl00201.periodid [BudgetPeriodid],gl00201.B UDGETID [Budgetid],
ISNULL(GL10110.PERDBLNC,0. 00) CURRENTAMT,GL10110.YEAR1 CURRENTYR,gl10110.accatnum "CURRENTCATNUM",gl10110.AC TINDX "ACTAMTIND", GL10110.PERIODID ACTPERID
FROM
(( GL00102 LEFT OUTER JOIN GL00100 ON GL00102.ACCATNUM = GL00100.ACCATNUM
LEFT OUTER JOIN GL00201 ON GL00201.ACTINDX=gl00100.AC TINDX and GL00102.ACCATNUM=GL00201.A CCATNUM
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.ac tindx and gl00201.PERIODID=gl10110.P ERIODID)
WHERE
gl00102.accatnum between 1 and 30
order by gl00102.accatnum,GL00100.A CTINDX
SELECT distinct GL00100.actdescr "ACCTDESC100", GL00100.actINDX "ACTINDX100",
GL00102.ACCATDSC "ACCDES102", gl00102.ACCATNUM "ACCAT102",
ISNULL(GL00201.BUDGETAMT,0
D",gl00201.periodid [BudgetPeriodid],gl00201.B
ISNULL(GL10110.PERDBLNC,0.
FROM
(( GL00102 LEFT OUTER JOIN GL00100 ON GL00102.ACCATNUM = GL00100.ACCATNUM
LEFT OUTER JOIN GL00201 ON GL00201.ACTINDX=gl00100.AC
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.ac
WHERE
gl00102.accatnum between 1 and 30
order by gl00102.accatnum,GL00100.A
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.AC TINDX and GL00102.ACCATNUM=GL00201.A CCATNUM
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.ac tindx and gl00201.PERIODID=gl10110.P ERIODID) and gl00102.accatnum between 1 and 30
order by gl00102.accatnum,GL00100.A CTINDX
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.AC
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.ac
order by gl00102.accatnum,GL00100.A
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.AC TINDX and GL00102.ACCATNUM=GL00201.A CCATNUM
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.actind x and gl00201.PERIODID=Foo.PERIO DID)
order by gl00102.accatnum,Foo.ACTIN DX
If that doesn't work, please post some sample data.
Try:
...
(( GL00102 LEFT OUTER JOIN GL00100 ON GL00102.ACCATNUM = GL00100.ACCATNUM
LEFT OUTER JOIN GL00201 ON GL00201.ACTINDX=gl00100.AC
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.actind
order by gl00102.accatnum,Foo.ACTIN
If that doesn't work, please post some sample data.
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.