Home » SQL & PL/SQL » SQL & PL/SQL » Oracle + 2 select querys + minus + order by + decode help... (Oracle )
 Oracle + 2 select querys + minus + order by + decode help... [message #442055] |
Thu, 04 February 2010 10:06  |
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  |
 |
BlackSwan
Messages: 26766
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 #442061 is a reply to message #442060] |
Thu, 04 February 2010 10:32 |
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 #442065 is a reply to message #442055] |
Thu, 04 February 2010 10:40 |
cookiemonster
Messages: 13961
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 #442078 is a reply to message #442070] |
Thu, 04 February 2010 11:09 |
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 #442093 is a reply to message #442090] |
Thu, 04 February 2010 11:47 |
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.
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Dec 09 19:36:13 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
.