Home » SQL & PL/SQL » SQL & PL/SQL » Help in query
Help in query [message #240683] Fri, 25 May 2007 09:03 Go to next message
basant_u
Messages: 3
Registered: May 2007
Location: India
Junior Member
Could you please anyone help in following query.

I have a table called PO_HEADERS and a column in the table is PO_NUMBER. I have set of PO numbers like

'1010000185'
'1010000193'
'1010000206'
'1010000207'
'1010000208'
'1010000209'
'1010000210'
'1010000212'
'1010000213'
'10100002167' etc.

Now my query is, I want to write one single query, which will show the PO numbers from the above set, which doesn't exist in the table.

Thanks !

Re: Help in query [message #240690 is a reply to message #240683] Fri, 25 May 2007 09:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'll assume that the last po header number was a mistype.

This should do the job:
create table temp_po_header (po_header  number);

insert into temp_po_header values (1010000185);
insert into temp_po_header values (1010000193);
insert into temp_po_header values (1010000206);
insert into temp_po_header values (1010000207);
insert into temp_po_header values (1010000208);
insert into temp_po_header values (1010000209);
insert into temp_po_header values (1010000210);
insert into temp_po_header values (1010000212);
insert into temp_po_header values (1010000213);
insert into temp_po_header values (1010000216);


select level + min_po
from   (
select min(po_header) min_po
      ,max(po_header) max_po
from  temp_po_header)
connect by level <= (max_po-min_po) 
minus
select po_header
from   temp_po_header;

LEVEL+MIN_PO
------------
  1010000186
  1010000187
  1010000188
  1010000189
  1010000190
  1010000191
  1010000192
  1010000194
  1010000195
  1010000196
  1010000197
  1010000198
  1010000199
  1010000200
  1010000201
  1010000202
  1010000203
  1010000204
  1010000205
  1010000211
  1010000214
  1010000215
Re: Help in query [message #240704 is a reply to message #240690] Fri, 25 May 2007 09:51 Go to previous messageGo to next message
basant_u
Messages: 3
Registered: May 2007
Location: India
Junior Member
Thanks for your response.
I know that that option which you have explained.
I need a single query withing creating any dummy table.
Could it be possible ?
Re: Help in query [message #240709 is a reply to message #240683] Fri, 25 May 2007 09:58 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
The only way I could think of would be:

(select '1010000185' from dual
union all
select '1010000193' from dual
union all
...
select '1010000215' from dual)
minus
select po_number from po_header

Although it is beyond me why anyone would want to work like that for the sake of not creating a temporary table.
Re: Help in query [message #240716 is a reply to message #240704] Fri, 25 May 2007 10:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm afraid you've misunderstood my solution at a fairly fundamental level. The table I created is there because I don't have a copy of your PO_HEADERS table.

Just use your table PO_HEADERS where I've used temp_po_header
Re: Help in query [message #240720 is a reply to message #240716] Fri, 25 May 2007 10:10 Go to previous messageGo to next message
basant_u
Messages: 3
Registered: May 2007
Location: India
Junior Member
I have not misunderstood your solution.
I am working in a read only schema and I dont have any erite to create a temporory table and insert data into it. that why I need a single statement to get it done.

Thanks !
Re: Help in query [message #240764 is a reply to message #240720] Fri, 25 May 2007 12:24 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
What temporary table are you talking about? JRow created a table for himself so he can "simulate" your environment. Do you not understand that?
How about next time providing that for us so you won't be confused by an excellent detailed answer.
And without providing any helpful info other than what the data looks like, we assume that your data is of NUMBER datatype, which that solution will work for.

[Updated on: Fri, 25 May 2007 12:27]

Report message to a moderator

Previous Topic: about database review
Next Topic: UTL_FILE writing performance
Goto Forum:
  


Current Time: Sat Dec 03 03:44:10 CST 2016

Total time taken to generate the page: 0.09069 seconds