OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » UNION clause

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

UNION clause [message #202061]

Wed, 08 November 2006 01:15

monasingh
Messages: 229
Registered: May 2006
Location: Mumbai

Senior Member

Hi Frank,
You have helped me in a similar script.
Below is the table creation script with some inserts.

create table si_cancel_workorder(
anchor_ord varchar2(15)
,slct varchar2(10)
,wo_nbr varchar2(10),rel_wo_nbr varchar2(10),wostatus varchar2(10),
opstatus varchar2(10),loc varchar2(10),Item varchar2(10),qty number(7)
)

insert into si_cancel_workorder values('1417561','PRIMARY','END','1417511','45','30','122','1351566',150);
insert into si_cancel_workorder values('1417578','PRIMARY','1417573','1418823','85','20','115','1428667-P',4667);
insert into si_cancel_workorder values('1417578','RELATED','1418898','1418899','45','30','115','1428667',67);
insert into si_cancel_workorder values('1417578','RELATED','1418898','1417586','85','20','115','1428667-P',135);
insert into si_cancel_workorder values('1417638','PRIMARY','END','1417638','45','30','122','1386377',288);
insert into si_cancel_workorder values('1417647','PRIMARY','1417555','1417347','68','5','122','1408643',295);
insert into si_cancel_workorder values('1417547','PRIMARY','1417556','1417261','45','30','122','1351566',150);

SQL> column ordercolumn noprint
SQL> column idcolumn    noprint
SQL>
SQL> select 1                     as ordercolumn
    ,      anchor_ord||','||slct as datacolumn
    ,      anchor_ord||'#'||slct as idcolumn
    from   si_cancel_workorder
    group  by 1
    ,      anchor_ord
    ,      slct
    union
    select 2
   ,      'Dependent Work Order,Related Work Order,Wostatus,OpStatus,Location,
Item,Qty'
   ,      anchor_ord||'#'||slct
   from   si_cancel_workorder
   group  by 1
   ,      2
   ,      anchor_ord
   ,      slct
   union
   select 3
   ,      wo_nbr||','||rel_wo_nbr||','||wostatus||','||opstatus||','||loc||','
||Item||','||qty
   ,      anchor_ord||'#'||slct
   from   si_cancel_workorder
   order  by 3, 1, 2

The above code is giving data on three select statements ordered.
I need a 4th select statement which should be displayed as first row for each bunch of data that is as per Achor_order.

4 Anchor Order, SLCT
1 1417547,PRIMARY                                                             
2 Dependent Work Order,Related Work Order,Wostatus,OpStatus,Location,         
  Item,Qty

3 1417556,1417261,45,30,122,1351566,150                            

4 Anchor Order, SLCT
1 1417561,PRIMARY                                                             
2 Dependent Work Order,Related Work Order,Wostatus,OpStatus,Location,         
  Item,Qty

3 END,1417511,45,30,122,1351566,150

The value on second row "1417547 and PRIMARY" are values for the columns "Anchor Order" and "SLCT" which needs to be displayed above them.

I tried adding 1 more select statement as in the below code but its giving the error
ERROR at line 35:
ORA-01785: ORDER BY item must be the number of a SELECT-list expression

 select 1                     as ordercolumn
     ,      anchor_ord||','||slct as datacolumn
     ,      anchor_ord||'#'||slct as idcolumn
     from   si_cancel_workorder
     group  by 1
     ,      anchor_ord
     ,      slct
     union
 select 4
 ,    'Anchor Order, SLCT'
 ,    anchor_ord||'#'||slct
 from   si_cancel_workorder
     group by 4
    ,1
    ,2
    ,anchor_ord
    ,      slct
    union
     select 2
    ,      'Dependent Work Order,Related Work Order,Wostatus,OpStatus,Location,
 Item,Qty'
    ,      anchor_ord||'#'||slct
    from   si_cancel_workorder
    group  by 4
     ,     1
    ,      2
    ,      anchor_ord
    ,      slct
    union
    select 3
    ,      wo_nbr||','||rel_wo_nbr||','||wostatus||','||opstatus||','||loc||','
 ||Item||','||qty
    ,      anchor_ord||'#'||slct
    from   si_cancel_workorder
    order  by 4,3, 1, 2

Thanks,
Mona

[Updated on: Wed, 08 November 2006 01:16]

Report message to a moderator

Re: UNION clause [message #202064 is a reply to message #202061]

Wed, 08 November 2006 01:22

Maaher
Messages: 7065
Registered: December 2001

Senior Member

you problem lies in this line:

order  by 4,3, 1, 2

You tell Oracle to sort by the fourth column of your UNION SELECT first, then to sort by the third, the first and the second. But you have only 3 columns you select. What do you want to order by?

MHE

Report message to a moderator

Re: UNION clause [message #202067 is a reply to message #202064]

Wed, 08 November 2006 01:31

monasingh
Messages: 229
Registered: May 2006
Location: Mumbai

Senior Member

Hi Maaher,
I am displaying three columns in total and there are 4 select statement in union.
I am not getting a way to display the select statement which displays the heading
"4 Anchor Order, SLCT" for each anchor order.
I am not getting the way to do this. Please suggest me how can I put one more selection so that the order by clause as well as the output works.

Thanks,
Mona

Report message to a moderator

Re: UNION clause [message #202069 is a reply to message #202067]

Wed, 08 November 2006 01:42

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Here's my suggestion: the "order column" is a good idea. The idea is that you give it a fix value in each part of your UNION. The select you want to be displayed first you give the numeric literal 1, then you give the select you want to display next the numeric literal 2 and so forth. Now you can order by your order column.

SQL> SELECT 5 order_column
  2       , 'Fifth' x
  3  FROM    dual
  4  UNION ALL
  5  SELECT 2 order_column
  6       , 'Second' x
  7  FROM   dual
  8  UNION ALL
  9  SELECT 4 order_column
 10       , 'Fourth' x
 11  FROM   dual
 12  UNION ALL
 13  SELECT 1 order_column
 14       , 'First' x
 15  FROM   dual
 16  UNION ALL
 17  SELECT 3 order_column
 18       , 'Third' x
 19  FROM dual
 20  ORDER BY order_column
 21  /

ORDER_COLUMN X
------------ ------
           1 First
           2 Second
           3 Third
           4 Fourth
           5 Fifth

MHE

Report message to a moderator

Re: UNION clause [message #202071 is a reply to message #202069]

Wed, 08 November 2006 01:50

Frank
Messages: 7901
Registered: March 2000

Senior Member

Mona, reread the other threads and examples I gave you. Try to _understand_ what's going on. Maaher explained it very well in the above post.
You really have to know what the idea is before you can extend on the examples.

Report message to a moderator

Re: UNION clause [message #202272 is a reply to message #202071]

Wed, 08 November 2006 22:16

monasingh
Messages: 229
Registered: May 2006
Location: Mumbai

Senior Member

Thanks Frank and Maaher for your valuable advice.
I tried and I was able to get the required result.
Now I really understand what is going on and how to work with UNION Smile

select 1 as ordercolumn
, 'anchor order, SLCT ' as datacolumn
, anchor_ord||'#'||slct as idcolumn
from si_cancel_workorder
group by 1
, anchor_ord
, slct
union
select 2 
, anchor_ord||','||slct 
, anchor_ord||'#'||slct 
from si_cancel_workorder
group by 1
, anchor_ord
, slct
union
select 3
, 'Dependent Work Order,Related Work Order,Wostatus,OpStatus,Location,
Item,Qty'
, anchor_ord||'#'||slct
from si_cancel_workorder
group by 1
, 2
, anchor_ord
, slct
union
select 4
, wo_nbr||','||rel_wo_nbr||','||wostatus||','||opstatus||','||loc||','
||Item||','||qty
, anchor_ord||'#'||slct
from si_cancel_workorder
order by 3, 1, 2

Thanks,
Mona

Report message to a moderator

Previous Topic:	Stored Procedure: Prompt
Next Topic:	cannot insert data

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 08:38:06 CST 2024