Need SQL query

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
Suriyaraj_SudalaiappanAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Suriyaraj_SudalaiappanAuthor Commented:
I am getting the below error

ORA-00979: not a GROUP BY expression
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
HugoHiaslCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
awking00Commented:
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
 
Suriyaraj_SudalaiappanAuthor Commented:
Yes as awking00 said, if we include E, E then that row should be display in the query result
0
 
slightwv (䄆 Netminder) Commented:
Please post the expected results from my test case and awking00's adding of 'E','E'.

0
 
Suriyaraj_SudalaiappanAuthor Commented:
sorry the row should not be display in the query result
0
 
slightwv (䄆 Netminder) Commented:
Again:  Please post the expected results.

We need to see that you want.
0
 
Suriyaraj_SudalaiappanAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
Suriyaraj_SudalaiappanAuthor Commented:
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
 
Suriyaraj_SudalaiappanAuthor Commented:
-- C was not returned because of ???

Because C and D both are present in Table 1
0
 
Suriyaraj_SudalaiappanAuthor Commented:
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
 
Suriyaraj_SudalaiappanAuthor Commented:
And also need to display "type" column as 0 or 1 based on the satisfied condition
0
 
slightwv (䄆 Netminder) Commented:
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
 
Suriyaraj_SudalaiappanAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
awking00Commented:
>>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
 
Suriyaraj_SudalaiappanAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
Suriyaraj_SudalaiappanAuthor Commented:
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
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
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
 
Suriyaraj_SudalaiappanAuthor Commented:
Nice one to understand
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.