SQL Query to Create View
-
Sorry if I put this in the wrong category. This is my first visit.
I have the following SQL I created but I am missing something. I am combining 5 table to 1 view. In the table addrline it might contain 1, 2 or more lines for one person with the same column name "text" having different info within it. Example one row might read "Apt 2" and the another row show "123 Street" for the same person. My problem is that the sql works if they have a addrline = 1 or 1 & 2 or 1 & 4 or 2 & 3 but it does not work if they have only a addrline = 2 or =3 or =4 and so on.
Here is my sql:
CREATE OR REPLACE VIEW ADDRESS ( ACCTNBR,
PERSNBR, SALUCD, FIRSTNAME, LASTNAME,
ADDRNBR, ADD1, ADD2, CITY,
STATE, ZIP, ZIPSUF, ADDRUSECD
 
AS Select acct.acctnbr, pers.persnbr, pers.salucd, pers.firstname, pers.lastname, persaddruse.addrnbr, a.text, null text2,addr.cityname, addr.statecd, addr.zipcd, addr.zipsuf, persaddruse.addrusecd
from acct, pers, persaddruse, addr, addrline a, addrline b
where acct.CURRACCTSTATCD not in ('CLS', 'CO')
and acct.taxrptforpersnbr = pers.persnbr
and pers.persnbr = persaddruse.persnbr
and (persaddruse.addrnbr = addr.addrnbr and persaddruse.addrusecd = 'PRI')
and persaddruse.addrnbr = a.addrnbr
and a.linenbr = (Select Min(a.linenbr) from addrline
where a.linenbr < b.linenbr)
and a.addrnbr not in (select a.addrnbr from addrline b
where b.addrnbr = a.addrnbr
and linenbr != 1)
union
Select acct.acctnbr, pers.persnbr, pers.salucd, pers.firstname, pers.lastname, persaddruse.addrnbr, a.text, b.text,
addr.cityname, addr.statecd, addr.zipcd, addr.zipsuf, persaddruse.addrusecd
from acct, pers, persaddruse, addr, addrline a, addrline b
where acct.CURRACCTSTATCD not in ('CLS', 'CO')
and (persaddruse.addrnbr = addr.addrnbr and persaddruse.addrusecd = 'PRI')
and acct.taxrptforpersnbr = pers.persnbr
and pers.persnbr = persaddruse.persnbr
and persaddruse.addrnbr = a.addrnbr
and a.addrnbr = b.addrnbr(+)
and a.linenbr = (Select Min(a.linenbr) from addrline
where a.linenbr < b.linenbr)
and b.linenbr = (Select Max(b.linenbr) from addrline
where b.linenbr > a.linenbr)
order by 7,6,3
Any suggestions would be appreciated.
-
Hello,
it would be much easier to help you if you state what is the desired result, and in which way your SQL "does not work" in cases where the line no. 1 is missing.
I'm so used to the JOIN syntax that it is rather difficult for me to check your code, but what about the condition "and linenbr != 1"? Would it help to change this to "and linenbr <> min(b.linenbr)" ... or something similar, depending on how precisely it works (which I somehow can't make out)? It's just a suspect for me, because it refers to the problematic value of 1.
I tried to bring this query to normal JOIN syntax, but I'm a bit confused, since I don't understand what it should do and what is the meaning of each column. Looks like the tables store 1 or more addresses for each person, every address consisting of 1 or several lines... is that so? Well, but what should the view display?
-
Yikes...that's a scary piece of code. I shouldn't try to help (because you didn't post the DDL...tsk tsk).
Where are you joining to "addrline b"...in the subquery or in the main join? You have the table "addrline" referenced as "b" twice, and you have a reference to the same table in one of your subqueries, but with no alias. Definitely wrong....you're accessing "addrline" FOUR times, once as "a", twice as "b", and a fourth time without an alias.
Suggestion...take it apart and re-assemble it line by line. Make sure you know what the expected results are for every step of the way, and compare that to the actual results.
Signature is NULL
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply