Home » SQL & PL/SQL » SQL & PL/SQL » Nested SELECT??? and MINUS/NEST - stumped!!!! (merged 2 threads)
Nested SELECT??? and MINUS/NEST - stumped!!!! (merged 2 threads) [message #230397] Wed, 11 April 2007 15:51 Go to next message
hdogg
Messages: 93
Registered: March 2007
Member
Have I have two tables:

FORECAST_ALL and FORECAST_PART

they both have the Field JOBCODE

----
FORECAST_ALL has ALL of the JOBCODES.
FORECAST_PART has PART of the JOBCODES.
-----

I want a query of the codes that do not have matches:

I want to select JOBCODE.FORECAST_ALL where JOBCODE.FORECAST_ALL does not equal JOBCODE.FORECAST_PART

I know I'm close but I don't have the exact syntax down...

What do I do??

-Hyrum
Re: Nested SELECT??? [message #230399 is a reply to message #230397] Wed, 11 April 2007 15:56 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Try something like this:

SELECT jobcode FROM forcast_all 
MINUS
SELECT jobcode FROM forcast_part;
Re: Nested SELECT??? [message #230400 is a reply to message #230399] Wed, 11 April 2007 16:04 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
Thanks! Worked like a charm Smile

Cheers-

Hyrum
Re: Nested SELECT??? [message #230401 is a reply to message #230397] Wed, 11 April 2007 16:24 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
So...

Query A
*********
SELECT DISTINCT JOBCODE FROM FORECAST_DATA MINUS SELECT DISTINCT JOBCODE FROM FORECAST_INPUT
**********
Worked perfectly....


Query B
****************
The following WORKING query selects the latest PERIOD.FORECAST_DATA (date) that has data in it WHERE JOBCODE = $jobcode

SELECT PERIOD FROM (select PERIOD from FORECAST_DATA order by to_date(period,'mm/dd/rr') DESC) WHERE ROWNUM<=1 AND JOBCODE LIKE '%$jobcode2%'") ;
****************

I would like to be able retrieve DISTINCT JOBCODE and PERIOD.FORECAST_DATA by using QUERY A to limit the results.

-Hyrum
MINUS/ NEST - stumped!!!! [message #230402 is a reply to message #230397] Wed, 11 April 2007 16:44 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
So...

I have two tables:

FORECAST_ALL and FORECAST_PART

they both have the Field JOBCODE

----
FORECAST_ALL has ALL of the JOBCODES.
FORECAST_PART has PART of the JOBCODES.
-----

Query A
*********
SELECT DISTINCT JOBCODE FROM FORECAST_DATA MINUS SELECT DISTINCT JOBCODE FROM FORECAST_INPUT
**********
Worked perfectly....


Query B

The following WORKING query selects the latest PERIOD.FORECAST_DATA (date) that has data in it WHERE JOBCODE = $jobcode
****************
SELECT PERIOD FROM (select PERIOD from FORECAST_DATA order by to_date(period,'mm/dd/rr') DESC) WHERE ROWNUM<=1 AND JOBCODE LIKE '%$jobcode2%'") ;
****************

I would like to be able retrieve DISTINCT JOBCODE and PERIOD.FORECAST_DATA by using QUERY A to limit the results.

-Hyrum
Re: MINUS/ NEST - stumped!!!! [message #230454 is a reply to message #230402] Thu, 12 April 2007 01:18 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
u can

SELECT PERIOD FROM (select PERIOD from FORECAST_DATA order by to_date(period,'mm/dd/rr') DESC) WHERE ROWNUM=1 AND JOBCODE LIKE '%$');

thanks
Srinivas
Re: MINUS/ NEST - stumped!!!! [message #230456 is a reply to message #230454] Thu, 12 April 2007 01:26 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
please send job code date and what u need?

thanks
srinivas
Re: MINUS/ NEST - stumped!!!! [message #230459 is a reply to message #230454] Thu, 12 April 2007 01:27 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
sorry jobcode data

thanks
srinivas
Re: MINUS/ NEST - stumped!!!! [message #230461 is a reply to message #230459] Thu, 12 April 2007 01:32 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
It is very nice your trying to help someone.
but please don't use IM SPEAK !!!
it is very hard to read.

Hope you understand.

Regards
Taj
Re: MINUS/ NEST - stumped!!!! [message #230465 is a reply to message #230461] Thu, 12 April 2007 01:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
And please, pavuluri, use the Edit option instead of replying three times in a row.

[Updated on: Thu, 12 April 2007 01:35]

Report message to a moderator

Re: MINUS/ NEST - stumped!!!! [message #230469 is a reply to message #230465] Thu, 12 April 2007 01:37 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
Thanks Frank
Re: MINUS/ NEST - stumped!!!! [message #230658 is a reply to message #230469] Thu, 12 April 2007 09:14 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
That query is half the solution..... both tables table1 and table2 contain job codes, table 1 contains all jobcodes, and table2 contains part of the jobcodes, i want to do a MINUS and subtract out table2's jobcodes, and while doing that selecting the jobcode.TABLE1 , DATE.TABLE1 field.

Hence Table1 contains the columns: JOBCODE, DATE
Table2 contatins the columns: JOBCODE, DATE

The working minus statement I use is "SELECT DISTINCT JOBCODE FROM TABLE1 MINUS SELECT DISTINCT JOBCODE FROM TABLE2 "*

*This statement takes all the jobcodes in Table2 that are in TABLE1 out of the query.

The query has been trimmed.....

But I want to make it more specific and have it display DATE.Table1 along with the results...

So would something like this work?

("SELECT DATE.TABLE1 FROM(SELECT DISTINCT JOBCODE FROM TABLE1 MINUS SELECT DISTINCT JOBCODE FROM TABLE2 ORDER BY DATE DESC))"

Re: Nested SELECT??? and MINUS/NEST - stumped!!!! (merged 2 threads) [message #230962 is a reply to message #230397] Fri, 13 April 2007 10:23 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Please do NOT use distinct, it slows down the query. A minus union will return a distinct list anyway. Also do NOT use a reserved word (date) for a column name. Try the following

select b.jobcode, b.date_col
from 
(SELECT JOBCODE FROM TABLE1 MINUS SELECT JOBCODE FROM TABLE2) x,
table1 b
where x.jobcode = b.jobcode
order by b.date_col desc;

[Updated on: Fri, 13 April 2007 10:24]

Report message to a moderator

Re: Nested SELECT??? and MINUS/NEST - stumped!!!! (merged 2 threads) [message #230996 is a reply to message #230962] Fri, 13 April 2007 11:54 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
Thanks a bunch...

Quick question-

What does the "x" signifiy?

-Hyrum
Re: Nested SELECT??? and MINUS/NEST - stumped!!!! (merged 2 threads) [message #230998 is a reply to message #230397] Fri, 13 April 2007 11:57 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
It is just an alias for the inline select. To oracle it looks like a standard view or table and you have to be able to reference it in the where section of the query.
Previous Topic: compare two (merged)
Next Topic: Count of error messages
Goto Forum:
  


Current Time: Mon Dec 05 08:49:32 CST 2016

Total time taken to generate the page: 0.12196 seconds