Home » SQL & PL/SQL » SQL & PL/SQL » Oracle + 2 select querys + minus + order by + decode help... (Oracle )
icon9.gif  Oracle + 2 select querys + minus + order by + decode help... [message #442055] Thu, 04 February 2010 10:06 Go to next message
nik0lla
Messages: 17
Registered: February 2010
Location: Canada
Junior Member
Hey guys

I'm trying to display items in the work_order_info table called wo_num. The items display in the format 1Txxxx/xx where xxxx is the item number and xx is the year.

I wanted to select only the wo_num's that are no longer being used, or have the signed_off = 'Y'. I need to use the minus to get rid of the wo_num's that have mutiple items with some signed off and some not.

Here is what I have

select distinct UPPER(wo_num) a, wo_num b from work_order_info
where location_id = 1 and signed_off = 'Y'
MINUS
select distinct UPPER(wo_num) a, wo_num b from work_order_info
where location_id = 1 and signed_off IS NULL
order by a
where decode( substr(wo_num,8,2),92,92,93,93,94,94,95,95,96,96,97,97,98,98,99,99, substr(wo_num,8,2)+100 ) desc, substr(wo_num,3,4) asc

I'm getting the error ORA-00933: SQL command not properly ended.


I tried replacing the "wo_num" in the decode statement with a, b, 1, 2. None of those work. I feel like I'm going something really dumb here and just need some guidance. I appreciate the help.
Re: Oracle + 2 select querys + minus + order by + decode help... [message #442056 is a reply to message #442055] Thu, 04 February 2010 10:09 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>where decode( substr(wo_num,8,2),92,92,93,93,94,94,95,95,96,96,97,97,98,98,99,99, substr(wo_num,8,2)+100 ) desc, substr(wo_num,3,4) asc

simply put; INVALID syntax
Re: Oracle + 2 select querys + minus + order by + decode help... [message #442060 is a reply to message #442055] Thu, 04 February 2010 10:30 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Or in other words, remove the where keyword from the order by.
Re: Oracle + 2 select querys + minus + order by + decode help... [message #442061 is a reply to message #442060] Thu, 04 February 2010 10:32 Go to previous messageGo to next message
nik0lla
Messages: 17
Registered: February 2010
Location: Canada
Junior Member
Crap I put in the wrong code hahahaha.

It should be:

select distinct UPPER(wo_num) a, wo_num b from work_order_info
where location_id = 1 and signed_off = 'Y'
MINUS
select distinct UPPER(wo_num) a, wo_num b from work_order_info
where location_id = 1 and signed_off IS NULL
order by decode( substr(wo_num,8,2),92,92,93,93,94,94,95,95,96,96,97,97,98,98,99,99, substr(wo_num,8,2)+100 ) desc, substr(wo_num,3,4) asc
Re: Oracle + 2 select querys + minus + order by + decode help... [message #442062 is a reply to message #442055] Thu, 04 February 2010 10:34 Go to previous messageGo to next message
nik0lla
Messages: 17
Registered: February 2010
Location: Canada
Junior Member
I was messing around in my frustration and put up the intentionally wrong one haha sorry.

The error code for this is: ORA-01785: ORDER BY item must be the number of a SELECT-list expression
Re: Oracle + 2 select querys + minus + order by + decode help... [message #442065 is a reply to message #442055] Thu, 04 February 2010 10:40 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Try this:
Select * 
from (select distinct UPPER(wo_num) a, wo_num b 
      from work_order_info
      where location_id = 1 and signed_off = 'Y'
      MINUS
      select distinct UPPER(wo_num) a, wo_num b 
      from work_order_info
      where location_id = 1 and signed_off IS NULL)
order by decode( substr(b,8,2),92,92,93,93,94,94,95,95,96,96,97,97,98,98,99,99, substr(wo_num,8,2)+100 ) desc, 
         substr(b,3,4) asc 


And do us a favour next time you post code - use code tags, like I've done - see the orafaq forum guide if you're not sure how.

EDIT: fixed indentation

[Updated on: Thu, 04 February 2010 10:41]

Report message to a moderator

Re: Oracle + 2 select querys + minus + order by + decode help... [message #442066 is a reply to message #442062] Thu, 04 February 2010 10:40 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

You are NOT a funny guy.
Re: Oracle + 2 select querys + minus + order by + decode help... [message #442067 is a reply to message #442065] Thu, 04 February 2010 10:44 Go to previous messageGo to next message
nik0lla
Messages: 17
Registered: February 2010
Location: Canada
Junior Member
Sorry I just joined, first time user, and I'm at work so I didn't have time to read the rules of conduct. Was just looking for some quick help. I'll follow the rules next time when I get a chance to read them Smile.

Thanks for the help too.
Re: Oracle + 2 select querys + minus + order by + decode help... [message #442068 is a reply to message #442065] Thu, 04 February 2010 10:47 Go to previous messageGo to next message
nik0lla
Messages: 17
Registered: February 2010
Location: Canada
Junior Member
Unfortunately I'm getting more errors
ORA-00904: "WO_NUM": invalid identifier

.___.
Re: Oracle + 2 select querys + minus + order by + decode help... [message #442070 is a reply to message #442055] Thu, 04 February 2010 10:59 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Find the one instance of wo_num in the order by that I forgot to change to b and change it.
Re: Oracle + 2 select querys + minus + order by + decode help... [message #442075 is a reply to message #442055] Thu, 04 February 2010 11:03 Go to previous messageGo to next message
nik0lla
Messages: 17
Registered: February 2010
Location: Canada
Junior Member
yay! It works!

Thank you so much! I'm still kind of new to the whole Oracle/SQL thing.

Again, thanks a bunch.
Re: Oracle + 2 select querys + minus + order by + decode help... [message #442077 is a reply to message #442055] Thu, 04 February 2010 11:07 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
You're welcome. To explain what I did - oracle for whatever reason doesn't like functions in order bys on queries containing minus/union/intersect. To get round that you just nest the query.
Re: Oracle + 2 select querys + minus + order by + decode help... [message #442078 is a reply to message #442070] Thu, 04 February 2010 11:09 Go to previous messageGo to next message
nik0lla
Messages: 17
Registered: February 2010
Location: Canada
Junior Member
/sigh

celebrated too soon. I'm starting think this is a fruitless venture.

I thought I knew what was wrong. Now I just don't. rawr.

Error: ORA-06550: line 1, column 64: PL/SQL: ORA-00947: not enough values ORA-06550: line 1, column 7: PL/SQL: SQL Statement ignored performing List of Values query: "Select * from (select distinct UPPER(wo_num) a, wo_num b from work_order_info where location_id = 1 and signed_off = 'Y' MINUS select distinct UPPER(wo_num) a, wo_num b from work_order_info where location_id = 1 and signed_off IS NULL) order by decode( substr(b,8,2),92,92,93,93,94,94,95,95,96,96,97,97,98,98,99,99, substr(b,8,2)+100 ) desc, substr(b,3,4) asc ".

[Updated on: Thu, 04 February 2010 11:20]

Report message to a moderator

Re: Oracle + 2 select querys + minus + order by + decode help... [message #442079 is a reply to message #442055] Thu, 04 February 2010 11:19 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Ok you need to give us more info here, like how the query is being called and what you're trying to do with the results.
Looks like an LOV query from oracle forms?
Re: Oracle + 2 select querys + minus + order by + decode help... [message #442080 is a reply to message #442055] Thu, 04 February 2010 11:22 Go to previous messageGo to next message
nik0lla
Messages: 17
Registered: February 2010
Location: Canada
Junior Member
Yeah it's a LOV. Just making a drop down menu (select list in Oracle) to display the work orders (aka wo_num) so they can choose which one they want and click a button which redirects them to the appropriate page.
Re: Oracle + 2 select querys + minus + order by + decode help... [message #442081 is a reply to message #442055] Thu, 04 February 2010 11:23 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
LOV's and drop down lists are not the same thing in oracle forms. So which is it?
Re: Oracle + 2 select querys + minus + order by + decode help... [message #442083 is a reply to message #442081] Thu, 04 February 2010 11:26 Go to previous messageGo to next message
nik0lla
Messages: 17
Registered: February 2010
Location: Canada
Junior Member
I went to the Items section of the page to edit. I added a "select list" and in the "list of values definition" is where I put the code.
Re: Oracle + 2 select querys + minus + order by + decode help... [message #442090 is a reply to message #442055] Thu, 04 February 2010 11:39 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
That's not oracle forms - oracle forms is the program Oracle Forms Builder, which creates .fmb and .fmx files.
So I'm note sure what tool you are using.
You need to tell us what format of data this select list is expecting.
Re: Oracle + 2 select querys + minus + order by + decode help... [message #442093 is a reply to message #442090] Thu, 04 February 2010 11:47 Go to previous messageGo to next message
nik0lla
Messages: 17
Registered: February 2010
Location: Canada
Junior Member
I'm using "Oracle Database: HTML DB".

This database was made before I got here so I'm not sure how a lot of things were done and why.

Theoretically this code should work, it's producing 2 columns of information just like it did before which worked.

Working code:
select distinct UPPER(wo_num) a, wo_num b 
from work_order_info
where location_id = 2 and signed_off = 'Y'
order by decode( substr(wo_num,8,2),92,92,93,93,94,94,95,95,96,96,97,97,98,98,99,99, substr(wo_num,8,2)+100 ) desc, substr(wo_num,3,4) asc


Code not working:
Select * 
from (select distinct UPPER(wo_num) a, wo_num b 
      from work_order_info
      where location_id = 1 and signed_off = 'Y'
      MINUS
      select distinct UPPER(wo_num) a, wo_num b 
      from work_order_info
      where location_id = 1 and signed_off IS NULL)
order by decode( substr(b,8,2),92,92,93,93,94,94,95,95,96,96,97,97,98,98,99,99, substr(b,8,2)+100 ) desc, 
         substr(b,3,4) asc 



I'm not entirely sure how to specify what it is expecting. I know what when I go to create an item, I choose "select list" as the type, "select list" as the control type, and place the code in the "list of values query" box.
Re: Oracle + 2 select querys + minus + order by + decode help... [message #442094 is a reply to message #442055] Thu, 04 February 2010 11:49 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
try replacing the * with a, b. Really shouldn't make any difference but I can't think of anything else.
Re: Oracle + 2 select querys + minus + order by + decode help... [message #442097 is a reply to message #442094] Thu, 04 February 2010 11:55 Go to previous message
nik0lla
Messages: 17
Registered: February 2010
Location: Canada
Junior Member
Holy cow, it worked.

I can't thank you enough for all your help today. You've saved my life. If you're ever in the Nova Scotia area, message me and I'll buy you a beer : )
Previous Topic: how to remove the special characters (All)
Next Topic: Table/User Information
Goto Forum:
  


Current Time: Fri Dec 09 06:12:53 CST 2016

Total time taken to generate the page: 1.32009 seconds