trying to use query builder

[theboy69]

trying to create a query in query builder and wondering if anyone knows anything about setting the table relationship. i am intersted the the part about "A not found in B" but the data doesn't seem to follow this relationship.

 

[Beilstwh]

Click on the line connecting the joined fields, then click the data menu item, and then the "set table relationships" item. Select what type of relationship you want and then click OK.

Bill
Oracle DBA/Developer
New York State, USA

 

[theboy69]

that didn't quite work. i have the relationship set but it doesn't give me the results that i need. i.e. our database consists of all properties in the county. every property has an "account" in every tax year. i'm trying get results for "accounts" that exist in tax year 2006 but not in tax year 2007. my gut tells me that i need a "where exists.." type statement.

 

[Beilstwh]

on the left side of the query screen you will see an empty rectangle. Click in it, type TAX_YEAR=2006 and hit return. If the name of the column is not TAX_YEAR, use the correct column name. The left side corresponds to your where and having clauses.

Bill
Oracle DBA/Developer
New York State, USA

 

[theboy69]

all that i get is 1000's of records of the same account which does exist in 2006 & 2007. my query (under show SQL) reads as

SELECT ALL IAS4.PARDAT.PARID, IAS4.PARDAT.TAXYR, PARDAT_A1.TAXYR
FROM IAS4.PARDAT, IAS4.PARDAT PARDAT_A1
WHERE (IAS4.PARDAT.TAXYR=2006
AND PARDAT_A1.TAXYR=2007
AND IAS4.PARDAT.CUR='Y')
AND (IAS4.PARDAT.PARID=PARDAT_A1.PARID)

i tried choosing the "A not found in B" as well as the other two options and i get 1196 rows. in 2007 i have 1196 accounts and in 2006 i have 1223 so it is obviously just grabbing my 2007 stuff, but the different ones.
let me just end with this, if it's not apparant enough i am attempting to self teach myself how to use this software and have no background in it.
thanks for the help

 

[Turkbear]

Try using the Minus operator

SELECT ALL IAS4.PARDAT.PARID
FROM IAS4.PARDAT
WHERE (IAS4.PARDAT.TAXYR=2006
 AND IAS4.PARDAT.CUR='Y')

MINUS

SELECT ALL IAS4.PARDAT.PARID
FROM IAS4.PARDAT
WHERE (IAS4.PARDAT.TAXYR=2007
 AND IAS4.PARDAT.CUR='Y')

I don't know if that tool can do that , however..







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Beilstwh]

You misunderstand what an outer join does, it means return the record even if it isn't in the joined table. Your query should look like this:

SELECT ALL a.PARID, a.TAXYR
FROM IAS4.PARDAT a
WHERE a.TAXYR=2006
AND a.CUR='Y'
and not exists
(select null
from ias4.pardat b
where a.PARID=b.PARID
and b.TAXYR=2007
AND b.CUR='Y')


or like this.

SELECT ALL IAS4.PARDAT.PARID, IAS4.PARDAT.TAXYR
FROM IAS4.PARDAT, IAS4.PARDAT PARDAT_A1
WHERE (IAS4.PARDAT.TAXYR=2006
AND PARDAT_A1.TAXYR=2007
AND IAS4.PARDAT.CUR='Y')
AND (IAS4.PARDAT.PARID=PARDAT_A1.PARID(+))
and PARDAT_A1.TAXYR is null;

Either one will work. When you outer join and the related record doesn't exist, then all the row information is returned as null. So by checking to see if the returned tax year from pardat_a1 is null, it indicates that the partner is in 2006, but NOT 2007. Notice the "(+)" in the where clause. This indicates an outer join.

Bill
Oracle DBA/Developer
New York State, USA