Home » SQL & PL/SQL » SQL & PL/SQL » Lost - is it possible (8.04, XP)
Lost - is it possible [message #400511] Tue, 28 April 2009 09:34 Go to next message
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
Re: Lost - is it possible [message #400514 is a reply to message #400511] Tue, 28 April 2009 09:38 Go to previous messageGo to next message
BlackSwan
Messages: 25033
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.

Re: Lost - is it possible [message #400528 is a reply to message #400511] Tue, 28 April 2009 10:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Lost - is it possible [message #400529 is a reply to message #400511] Tue, 28 April 2009 10:27 Go to previous messageGo to next message
BlackSwan
Messages: 25033
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.
Re: Lost - is it possible [message #400534 is a reply to message #400528] Tue, 28 April 2009 10:30 Go to previous messageGo to next message
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.

Re: Lost - is it possible [message #400536 is a reply to message #400511] Tue, 28 April 2009 10:39 Go to previous message
cookiemonster
Messages: 12403
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.
Previous Topic: Wroking with DENSE_RANK function
Next Topic: SQL : Update table based on the number of child attached
Goto Forum:
  


Current Time: Sat Dec 03 13:54:40 CST 2016

Total time taken to generate the page: 0.25061 seconds