Solved

Need SQL query

Posted on 2011-02-18
26
873 Views
Last Modified: 2012-05-11
I have a table which contains the column COL1 like below

Table 1
-----------
COL1
A
B
C
D
E

I have an another table contains two column FROM_CODE and TO_CODE. The values of this column are same as table 1 values.

My requirement is, based on below conditions the value needs to be fetch in the query.

1) If FROM_CODE is equals to COL1 then query should return 0
2) If TO_CODE is equals to COL1 then query should return 1
3) if both FROM_CODE and TO_CODE is same as COL1 then don't display the data in the query.

Please let me know how is this done by SQL?

Thanks
0
Comment
  • 12
  • 8
  • 3
  • +2
26 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
what about this
select t1.COL1
  , DECODE(t1.COL1, t2.FROM_CODE, 0, 1 )
  from table1 t1
  left join table2 t2
   on t1.col1 in ( t2.FROM_CODE, t2.TO_CODE)
  and t2.FROM_CODE <> t2.TO_CODE
group by t1.col1

Open in new window

0
 

Author Comment

by:Suriyaraj_Sudalaiappan
Comment Utility
I am getting the below error

ORA-00979: not a GROUP BY expression
0
 
LVL 12

Expert Comment

by:HugoHiasl
Comment Utility
You can only select fields that are in the group by clause or that are aggregated with an aggregate function like MAX, MIN or AVG etc...

select t1.COL1
  , DECODE(t1.COL1, t2.FROM_CODE, 0, 1 )
  from table1 t1
  left join table2 t2
   on t1.col1 in ( t2.FROM_CODE, t2.TO_CODE)
  and t2.FROM_CODE <> t2.TO_CODE
group by t1.col1,
 DECODE(t1.COL1, t2.FROM_CODE, 0, 1 )
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Why do you need the group by anyway?

>>if both FROM_CODE and TO_CODE is same as COL1 then don't display the data in the query.

This piece confuses me.  Given the test below, what are your expected results?


drop table tab1 purge;
create table tab1(col1 char(1));

drop table tab2 purge;
create table tab2(from_code char(1), to_code char(1));

insert into tab1 values('A');
insert into tab1 values('B');
insert into tab1 values('C');
insert into tab1 values('D');
insert into tab1 values('E');

insert into tab2 values('A','Z');
insert into tab2 values('Z','B');
insert into tab2 values('C','D');
insert into tab2 values('Z','Z');
commit;

Open in new window

0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
Comment Utility
sorry...

the group by is needed to avoid that 2 or more records are returned, if COL1 would be in several rows in table2
select t1.COL1
  , MIN(DECODE(t1.COL1, t2.FROM_CODE, 0, 1 ))
  from table1 t1
  left join table2 t2
   on t1.col1 in ( t2.FROM_CODE, t2.TO_CODE)
  and t2.FROM_CODE <> t2.TO_CODE
group by t1.col1

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
which also means that you might WANT to have several rows returned, if there could be several matches...

in which case:
select t1.COL1
  , DECODE(t1.COL1, t2.FROM_CODE, 0, 1 )
  , t2.FROM_CODE
  , t2.TO_CODE
  from table1 t1
  left join table2 t2
   on t1.col1 in ( t2.FROM_CODE, t2.TO_CODE)
  and t2.FROM_CODE <> t2.TO_CODE

Open in new window

0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
To clarify your problem, please respond to slightwv's question and, to help me better understand the issue, please include values of ('E','E') in tab2.
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
Comment Utility
Yes as awking00 said, if we include E, E then that row should be display in the query result
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Please post the expected results from my test case and awking00's adding of 'E','E'.

0
 

Author Comment

by:Suriyaraj_Sudalaiappan
Comment Utility
sorry the row should not be display in the query result
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Again:  Please post the expected results.

We need to see that you want.
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
Comment Utility
insert into tab2 values('A','Z');       -- It should return
insert into tab2 values('Z','B');       -- It should return
insert into tab2 values('C','D');       -- It should not return because both are there in table 1
insert into tab2 values('Z','Z');       -- It should not return because both are not there in table 1
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>It should return

Return what?

What I'm looking for is the expected output from whatever SQL we come up with.

Something like:

COL1     MYRES
-------    ---------
A           1
B           0
D           0
etc...

C was not returned because of ???
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Suriyaraj_Sudalaiappan
Comment Utility
It should something like below

MYRES  FROM_CODE   TO_CODE  COL1
---------------------------------
0      A           Z        A
1      Z           B        B
0      C           Z        C
1      Z           D        D

Open in new window

0
 

Author Comment

by:Suriyaraj_Sudalaiappan
Comment Utility
-- C was not returned because of ???

Because C and D both are present in Table 1
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
Comment Utility
Hi below is my query.

Here subquery alias flv in from clause is nothing but Table 1 in the sample script and alias mtrl is nothing but Table 2

Please let me know what modification i should do??
SELECT 'MO-' || mtrh.request_number "id", mtrl.line_id line_id,
       NULL "description", msib.segment1 "itemid",
       mtrl.quantity "requiredquantity",
       TO_CHAR (SYSDATE, 'MM/DD/YY HH:MM:SS') "Creation date",
       ROW_NUMBER () OVER (PARTITION BY mtrh.request_number ORDER BY mtrh.request_number)
                                                              "positionindex",
       0 "type", NULL "processing", NULL "status", 0 "command",
       mtrl.from_subinventory_code,
       mtrl.TO_subinventory_code,
       flv.meaning
  FROM mtl_txn_request_headers mtrh,
       mtl_txn_request_lines mtrl,
       mtl_system_items_b msib,
      (SELECT flv.meaning
        FROM fnd_lookup_values flv
        WHERE flv.lookup_type = 'EMR VLM MO SOURCE SUBINV MMI'
         AND flv.LANGUAGE = USERENV ('LANG')
         AND flv.enabled_flag = 'Y'
         AND NVL (flv.end_date_active, SYSDATE) >= SYSDATE) flv       
 WHERE mtrh.header_id = mtrl.header_id
   AND mtrl.inventory_item_id = msib.inventory_item_id
   AND mtrl.organization_id = msib.organization_id
   AND mtrh.organization_id = 2034
   AND mtrh.header_status = 3
   AND mtrl.line_status IN (3, 7)
   AND flv.meaning IN (mtrl.from_subinventory_code, mtrl.to_subinventory_code)
   AND mtrl.from_subinventory_code <> mtrl.to_subinventory_code

Open in new window

0
 

Author Comment

by:Suriyaraj_Sudalaiappan
Comment Utility
And also need to display "type" column as 0 or 1 based on the satisfied condition
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
We're having a communication problem.

>>-- C was not returned because of ???

That was just an example of what I need as part of the expected results.  If something doesn't show up in the expected results, I need to know why it isn't supposed to show up.

In the expected output posted in http:#a34926859

Why doesn't the 'E' awking00 added in http:#a34925557 show up?  
What is the reason behind this?

>>Please let me know what modification i should do

Not sure.  Let's stick to the simple test case for now.  Once we get the desired output from that, we can work on integrating it into your SQL.
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
Comment Utility
That is my requirement. Actually both E, E scenario will not happen in my case. So i think we can leave that. We will consider If A and B scenario
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Using my test code above,  try the sql provided by angelIII's in http:#a34925502

Just remove the left join and change the table names to match.

change:
left join table2 t2

to:
join table2 t2
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
>>It should something like below<<
 MYRES  FROM_CODE   TO_CODE  COL1
---------------------------------
0      A           Z        A
1      Z           B        B
0      C           Z        C
1      Z           D        D
But there is no to_code of 'Z' with a from_code of 'C' and there is no from_code of 'Z' with a to_code of 'D'.
Shouldn't it look more like this?
 MYRES  FROM_CODE   TO_CODE  COL1
---------------------------------
0      A           Z        A
1      Z           B        B
0      C           D        C
1      C           D        D
0      E           E        E
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
Comment Utility
hi awking00,

This is what happening in my case. Whatever in second example.
MYRES  FROM_CODE   TO_CODE  COL1
---------------------------------
0      A           Z        A
1      Z           B        B
0      C           D        C   -- It should not return
1      C           D        D   -- It should not return
0      E           E        E   -- this scenario will never happen

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>0      C           D        C   -- It should not return
>>1      C           D        D   -- It should not return

Why not?

It exactly matches your requirements from the original question:
1) If FROM_CODE is equals to COL1 then query should return 0
2) If TO_CODE is equals to COL1 then query should return 1
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
Comment Utility
Yes that's correct. Needs to include one more condition

1) If FROM_CODE is equals to COL1 then query should return 0
2) If TO_CODE is equals to COL1 then query should return 1
3) if both FROM_CODE and TO_CODE is same as COL1 then don't display the data in the query.
4) And also if both FROM_CODE and TO_CODE values are exists in COL1 then the row should not return
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
Comment Utility
This is very ugly and can probably be cleaned up quite a bit by one of the Experts better at SQL than I am but this at least get's the results you are after.
drop table tab1 purge;
create table tab1(col1 char(1));

drop table tab2 purge;
create table tab2(from_code char(1), to_code char(1));

insert into tab1 values('A');
insert into tab1 values('B');
insert into tab1 values('C');
insert into tab1 values('D');
insert into tab1 values('E');

insert into tab2 values('A','Z');
insert into tab2 values('Z','B');
insert into tab2 values('C','D');
insert into tab2 values('Z','Z');
commit;

select
  DECODE(t1.COL1, t2.FROM_CODE, 0, 1 ) myres
  , t2.FROM_CODE
  , t2.TO_CODE
  , t1.COL1
  from tab1 t1
  join tab2 t2
   on t1.col1 in ( t2.FROM_CODE, t2.TO_CODE)
  and t2.FROM_CODE <> t2.TO_CODE
and t2.rowid in
(
select t2_row
from
(
select rowid t2_row from tab2 where from_code in (select col1 from tab1)
union all
select rowid from tab2 where to_code in (select col1 from tab1)
)
group by t2_row
having count(1) =1
)
/

Open in new window

0
 

Author Closing Comment

by:Suriyaraj_Sudalaiappan
Comment Utility
Nice one to understand
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now