OraFAQ Forum: SQL & PL/SQL » Lost

Home » SQL & PL/SQL » SQL & PL/SQL » Lost - is it possible (8.04, XP)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Lost - is it possible [message #400511]

Tue, 28 April 2009 09:34

rbarcome
Messages: 2
Registered: April 2009

Junior Member

I have reviewed many, many a topic and books with no real answer.
What I am trying to do, I hoping is possiblem is query tow different tables and then take those results and query the first table for a different value.
I am trying to subtract two tables to locate the differences, and then query the first table (servinfo) with those resluts to get the install dates.
what I would like to do is:
SELECT ADDRESS, SERV_INSTALDATE
FROM SERVINFO
WHERE ADDRESS IN
(
SELECT ADDRSTREET, ADDRNO
FROM SERVINFO
WHERE SERV_ACTIVE='Y' AND SUBSTR(CITY,0,2)='SO'
MINUS
SELECT STREETNAME, STREETNUMBER
FROM SERVTEST
WHERE JURISDICTION='SO')

so far i can only get my deired resluts, but how or what method should i try to obtain the final query?

any helpfull dirction is all i seek

Report message to a moderator

Re: Lost - is it possible [message #400514 is a reply to message #400511]

Tue, 28 April 2009 09:38

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SELECT address, 
       serv_instaldate 
FROM   servinfo 
WHERE  address IN (SELECT addrstreet, 
                          addrno 
                   FROM   servinfo 
                   WHERE  serv_active = 'Y' 
                          AND Substr(city,0,2) = 'SO' 
                   MINUS 
                   SELECT streetname, 
                          streetnumber 
                   FROM   servtest 
                   WHERE  jurisdiction = 'SO')

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.

Report message to a moderator

Re: Lost - is it possible [message #400528 is a reply to message #400511]

Tue, 28 April 2009 10:24

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

so far i can only get my deired resluts, but how or what method should i try to obtain the final query?

Why is this not your final query?

Regards
Michel

Report message to a moderator

Re: Lost - is it possible [message #400529 is a reply to message #400511]

Tue, 28 April 2009 10:27

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>(8.04, XP)
Really?
V8.0 is ancient, unsupported, & buggy.
XP did not exist when V8 was released.

Report message to a moderator

Re: Lost - is it possible [message #400534 is a reply to message #400528]

Tue, 28 April 2009 10:30

rbarcome
Messages: 2
Registered: April 2009

Junior Member

sorry, for that misleading line

what I mneant was that I can get only half of my desired results.
but, what I did/can do, was to create a table with the result values from the first half of my problem, then concatinate those to query the first table

like this
create table servmerge6_08
as
SELECT ADDRSTREET, ADDRNO
FROM SERVINFO
WHERE SERV_ACTIVE='Y' AND SUBSTR(CITY,0,2)='SO'
MINUS
SELECT STREETNAME, STREETNUMBER
FROM SERVTEST
WHERE JURISDICTION='SO'

then I tried the subtraction on it
as in this
select addrno, addrstreet from servmerge6_08
minus
select address, serv_instaldate
from servinfo
where address in
(select servmerge6_08.addrno||servmerge6_08.addrstreet
from servmerge6_08)

I am closer to my final, but now i have a dfference of 14 rows.
I am still wondering if there is a way to do it in one statement.
or maybe I am still way off.
thanks to any of you that replied and or even looked at it.

Report message to a moderator

Re: Lost - is it possible [message #400536 is a reply to message #400511]

Tue, 28 April 2009 10:39

cookiemonster
Messages: 13960
Registered: September 2008
Location: Rainy Manchester

Senior Member

Unless one of servinfo.serv_instaldate and servmerge6_08.addrstreet is wildly missnamed that second query is logically equivalent to:

select addrno, addrstreet from servmerge6_08

Minus only removes rows that exist in both selects and I really don't see how those two columns could correspond.

You probably could do this in 1 sql statement but your requirement is less than clear.

If you post a test case (create table statements and insert statements) as has previously been suggested it'll probably become a lot clearer what you're trying achieve.

Also can you please use code tags (as I have above) - details are in the orafaq forum guide if you're not sure how.

Report message to a moderator

Previous Topic:	Wroking with DENSE_RANK function
Next Topic:	SQL : Update table based on the number of child attached

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 05 13:01:54 CST 2024