Solved

Need SQL query

Posted on 2011-02-18
26
883 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 8
  • 3
  • +2
26 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34925096
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
ID: 34925182
I am getting the below error

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

Expert Comment

by:HugoHiasl
ID: 34925204
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34925420
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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 34925492
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34925502
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 32

Expert Comment

by:awking00
ID: 34925557
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
ID: 34926131
Yes as awking00 said, if we include E, E then that row should be display in the query result
0
 
LVL 77

Expert Comment

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

0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 34926197
sorry the row should not be display in the query result
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34926242
Again:  Please post the expected results.

We need to see that you want.
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 34926568
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34926729
>>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
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 34926859
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
ID: 34927224
-- C was not returned because of ???

Because C and D both are present in Table 1
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 34927266
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
ID: 34927283
And also need to display "type" column as 0 or 1 based on the satisfied condition
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34927430
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
ID: 34927611
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34927733
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 32

Expert Comment

by:awking00
ID: 34928171
>>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
ID: 34931732
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34934490
>>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
ID: 34937844
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 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 34939343
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
ID: 35108293
Nice one to understand
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

635 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