Home » Other » Client Tools » condition in SQL (merged)
condition in SQL (merged) [message #245083] Fri, 15 June 2007 02:24 Go to next message
kogilaah_k
Messages: 20
Registered: May 2007
Location: Malaysia
Junior Member
hi there

I am not able to call out two conditions at one time using sql.
Below is the coding which i have build but then , i am not sure if i am leaving out something.

SET ECHO OFF
SET TERMOUT ON
SET VERIFY OFF
SET FEEDBACK OFF
SET HEADING ON

SET PAGESIZE &&1
SET LINESIZE &&2

COL ITEMTYPE         FORMAT a2  HEADING 'IT'
COL SPART            FORMAT a2  HEADING 'PS'
COL ITEMNO           FORMAT a11 HEADING 'ITEM NO'
COL DESCP            FORMAT a15 HEADING 'DESC'
COL SUBINV           FORMAT a11 HEADING 'SUP_SUBINV'
COL LOCATOR          FORMAT a9  HEADING 'S_LOCATOR'
COL ITEMSTAT         FORMAT a9  HEADING 'I_STATUS'

TTITLE   LEFT        'SAMPLE' -
         RIGHT       'DATE :       '"&&3" skip 1 -
         RIGHT       'PAGE :       'sql.pno skip 1 -
         CENTER      'ITEM INFORMATION BY ITEM TYPE' skip 3 -

REPFOOTER SKIP 2 CENTER '********** END OF REPORT **********'

SELECT MTL.ITEM_TYPE ITEMTYPE,
       MTL.ATTRIBUTE3 SPART,
       MTL.SEGMENT1 ITEMNO,
       MTL.DESCRIPTION DESCP,
       MTL.WIP_SUPPLY_SUBINVENTORY SUPINV,
       MTL.WIP_SUPPLY_LOCATOR_ID LOCATOR,
       MTL.INVENTORY_ITEM_STATUS_CODE ITEMSTAT
FROM   MTL_SYSTEM_ITEMS_B MTL,
       MTL_ITEM_LOCATIONS MIL
WHERE  MTL.WIP_SUPPLY_LOCATOR_ID = MIL.INVENTORY_LOCATION_ID
AND    MTL.ITEM_TYPE = '&&4'
AND    MTL.ATTRIBUTE3 = '&&5'
ORDER BY MTL.ITEM_TYPE, MTL.ATTRIBUTE3,MTL.SEGMENT1
;


I HAVE 4 PARAMETERS : PAGESIZE,LINESIZE, ITEM_TYPE, ATTRIBUTE3

THE CONDITION IS :
ATTRIBUTE3 HAS 4 TYPES OF DATA WHICH IS 'NULL','TP','ES','PS','VP'

I WILL NEED TO CALL UPON THE CONDITION TO RETRIEVE DATA FOR THE NULL VALUED FIELDS

SELECT MSI.ATTRIBUTE3
FROM MTL_SYSTEM_ITEMS_B MSI
WHERE MSI.ATTRIBUTE3 IS NULL


SECONDLY, I WILL NEED TO CALL UPON THE CONDTION TO RETRIEVE DATA FOR THE ('TP','ES','PS','VP') FIELDS

SELECT MSI.ATTRIBUTE3
FROM MTL_SYSTEM_ITEMS_B MSI
WHERE MSI.ATTRIBUTE3 IN ('TP','ES','PS','VP')


HOW CAN I APPLY THIS CONDTIONS IN MY SQL PROGRAM. SHOULD I USE CURSORS? I DO UNDERSTAND THE LOGIC BUT HOW DO I APPLY IT?

WHEN I RUN THE PROGRAM ON SQLPLUS I WILL BE PROMPT WITH THE
PARAMETERS "PAGESIZE, LINESIZE,ITEM_TYPE, ATTRIBUTE3"
AND WHEN I LEAVE 'ATTRIBUTE3' BLANK THEN CONDTION1 SHOULD APPLY, OR IF I ENTER ONE OF THIS ('TP','ES','PS','VP') THEN IT SHOULD RETRIEVE RELATED DATA'S.

PLEASE ASSIST ME


[Updated on: Mon, 18 June 2007 02:44] by Moderator

Report message to a moderator

Re: condition in SQL [message #245109 is a reply to message #245083] Fri, 15 June 2007 03:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not clear.
Post an example.
AND KEEP OFF YOUR FINGER FROM SHIFT key.

Regards
Michel
Re: condition in SQL [message #245112 is a reply to message #245083] Fri, 15 June 2007 03:43 Go to previous messageGo to next message
kogilaah_k
Messages: 20
Registered: May 2007
Location: Malaysia
Junior Member
FIRST CONDITION EXAMPLE (OUTPUT)
[U]INVENTORY_ITEM_ID[/U] [U]SEGMENT1[/U] [U]ATTRIBUTE3[/U]
        23	           0E5HC030T	       
        18	           0E4H0079T	       
         8	           0E3L002BT	       
        15	           0E3RB1R5T	       
        14	           0E3RA751T	       
        16	           0E3RB4R3T	       
        17	           0E334060T	       






SECOND CONDITION EXAMPLE (OUTPUT)
[U]INVENTORY_ITEM_ID[/U] [U]SEGMENT1[/U] [U]ATTRIBUTE3[/U]
        23	           0E5HC030T	       ES
        18	           0E4H0079T	       ES
         8	           0E3L002BT	       TP
        15	           0E3RB1R5T	       PS
        14	           0E3RA751T	       PS
        16	           0E3RB4R3T	       TP
        17	           0E334060T	       VP

Re: condition in SQL [message #245119 is a reply to message #245112] Fri, 15 June 2007 03:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I can't read upper case sentences.

Regards
Michel
Re: condition in SQL [message #245121 is a reply to message #245119] Fri, 15 June 2007 04:07 Go to previous messageGo to next message
kogilaah_k
Messages: 20
Registered: May 2007
Location: Malaysia
Junior Member
Apologies!!

Anyway I am running out of time, if you have any hints please share it with me.

Re: condition in SQL [message #245125 is a reply to message #245083] Fri, 15 June 2007 04:28 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Try this:

col formatted_input new_value formatted_input

accept input prompt 'Input: '

select decode('&input', null, ' is null', 'in '||'('''||replace('&input', ',',''',''')||''')') formatted_input
from dual
/

select * from dual where dummy &formatted_input
/

For null, just press return at the "input" prompt; for the list, just enter x,y,z with no brackets or quotes.
Re: condition in SQL [message #245129 is a reply to message #245083] Fri, 15 June 2007 04:37 Go to previous messageGo to next message
kogilaah_k
Messages: 20
Registered: May 2007
Location: Malaysia
Junior Member
thank you very much..i will try that and see ....


have a great weekend..bye
Sql solution [message #245552 is a reply to message #245083] Mon, 18 June 2007 02:14 Go to previous messageGo to next message
kogilaah_k
Messages: 20
Registered: May 2007
Location: Malaysia
Junior Member
Hi there experts,

I have a probelm with my sql program, Last week i posted my query but i could not get a a clear solution for my program..
Below is what i posted previously.

hi there

I am not able to call out two conditions at one time using sql.
Below is the coding which i have build but then , i am not sure if i am leaving out something.

SET ECHO OFF
SET TERMOUT ON
SET VERIFY OFF
SET FEEDBACK OFF
SET HEADING ON

SET PAGESIZE &&1
SET LINESIZE &&2

COL ITEMTYPE         FORMAT a2  HEADING 'IT'
COL SPART            FORMAT a2  HEADING 'PS'
COL ITEMNO           FORMAT a11 HEADING 'ITEM NO'
COL DESCP            FORMAT a15 HEADING 'DESC'
COL SUBINV           FORMAT a11 HEADING 'SUP_SUBINV'
COL LOCATOR          FORMAT a9  HEADING 'S_LOCATOR'
COL ITEMSTAT         FORMAT a9  HEADING 'I_STATUS'

TTITLE   LEFT        'SAMPLE' -
         RIGHT       'DATE :       '"&&3" skip 1 -
         RIGHT       'PAGE :       'sql.pno skip 1 -
         CENTER      'ITEM INFORMATION BY ITEM TYPE' skip 3 -

REPFOOTER SKIP 2 CENTER '********** END OF REPORT **********'

SELECT MTL.ITEM_TYPE ITEMTYPE,
       MTL.ATTRIBUTE3 SPART,
       MTL.SEGMENT1 ITEMNO,
       MTL.DESCRIPTION DESCP,
       MTL.WIP_SUPPLY_SUBINVENTORY SUPINV,
       MTL.WIP_SUPPLY_LOCATOR_ID LOCATOR,
       MTL.INVENTORY_ITEM_STATUS_CODE ITEMSTAT
FROM   MTL_SYSTEM_ITEMS_B MTL,
       MTL_ITEM_LOCATIONS MIL
WHERE  MTL.WIP_SUPPLY_LOCATOR_ID = MIL.INVENTORY_LOCATION_ID
AND    MTL.ITEM_TYPE = '&&4'
AND    MTL.ATTRIBUTE3 = '&&5'
ORDER BY MTL.ITEM_TYPE, MTL.ATTRIBUTE3,MTL.SEGMENT1
;



I have 4 parameters : PAGESIZE,LINESIZE, ITEM_TYPE, ATTRIBUTE3

The condition is :
ATTRIBUTE3 has 5 types of date which is 'NULL','TP','ES','PS','VP'

I will need to call upon thye condition to retrieve data for the null valued fields

SELECT MSI.ATTRIBUTE3
FROM MTL_SYSTEM_ITEMS_B MSI
WHERE MSI.ATTRIBUTE3 IS NULL


OUTPUT
[U]INVENTORY_ITEM_ID[/U] [U]SEGMENT1[/U] [U]ATTRIBUTE3[/U]
        23	           0E5HC030T	       
        18	           0E4H0079T	       
         8	           0E3L002BT	       
        15	           0E3RB1R5T	       
        14	           0E3RA751T	       
        16	           0E3RB4R3T	       
        17	           0E334060T	       




Secondly, I will need to call upon the condition to retrieve date for the ('TP','ES','PS','VP') fields


SELECT MSI.ATTRIBUTE3
FROM MTL_SYSTEM_ITEMS_B MSI
WHERE MSI.ATTRIBUTE3 IN ('TP','ES','PS','VP')


OUTPUT

U]INVENTORY_ITEM_ID[/U] [U]SEGMENT1[/U] [U]ATTRIBUTE3[/U]
        23	           0E5HC030T	       ES
        18	           0E4H0079T	       ES
         8	           0E3L002BT	       TP
        15	           0E3RB1R5T	       PS
        14	           0E3RA751T	       PS
        16	           0E3RB4R3T	       TP
        17	           0E334060T	       VP




How can I apply this conditions in my sql program. Should I use cursors? I do understand the logic but how can i apply it
When I run the program on sqlplus, i will be prompted with the parameters "PAGESIZE, LINESIZE,ITEM_TYPE, ATTRIBUTE3"

And when I leave 'ATTRIBUTE3' blank then condition1 should apply, or if I enter one of this ('TP','ES','PS','VP') then it should retrieve the related data.

The solution given to me wasnt really clear to me. could someone help me out.

thank you
Re: condition in SQL (merged) [message #246168 is a reply to message #245083] Wed, 20 June 2007 03:03 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
For your NULL case you should check your SQL*Plus variable for NULL.

For including the IN list you should use the method described in How can I do a variable "in list" on AskTom. Just demonstrating below:
SQL> create or replace type myTableType as table of varchar2(4000);
  2  /

Type created.

SQL> create or replace function in_list( p_str in varchar2 ) return myTableType
  2  as
  3      l_str   long default p_str || ',';
  4      l_n        number;
  5      l_data    myTableType := myTabletype();
  6  begin
  7      loop
  8          l_n := instr( l_str, ',' );
  9          exit when (nvl(l_n,0) = 0);
 10          l_data.extend;
 11          l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
 12          l_str := substr( l_str, l_n+1 );
 13      end loop;
 14      return l_data;
 15  end;
 16  /

Function created.

SQL> with test as (select level x, to_char(level*100) y from dual connect by level < 10
  2      union all select null x, null y from dual)
  3  select x, y from test
  4  /

         X Y
---------- ----------------------------------------
         1 100
         2 200
         3 300
         4 400
         5 500
         6 600
         7 700
         8 800
         9 900


10 rows selected.

SQL> set verify off
SQL> accept p_cod char default "" prompt "Enter code [NULL]: "
Enter code [NULL]: 
SQL> with test as (select level x, to_char(level*100) y from dual connect by level < 10
  2      union all select null x, null y from dual)
  3  select x, y from test
  4  where (( '&p_cod.' is null and y is null )
  5      or ( '&p_cod.' is not null and y in ( select column_value from table ( in_list( '&p_cod.' )) )))
  6  /

         X Y
---------- ----------------------------------------


SQL> accept p_cod char default "" prompt "Enter code [NULL]: "
Enter code [NULL]: 100, 200, 500
SQL> with test as (select level x, to_char(level*100) y from dual connect by level < 10
  2      union all select null x, null y from dual)
  3  select x, y from test
  4  where (( '&p_cod.' is null and y is null )
  5      or ( '&p_cod.' is not null and y in ( select column_value from table ( in_list( '&p_cod.' )) )))
  6  /

         X Y
---------- ----------------------------------------
         1 100
         2 200
         5 500

SQL> drop function in_list;

Function dropped.

SQL> drop type myTableType;

Type dropped.

SQL> 
Re: condition in SQL (merged) [message #246689 is a reply to message #246168] Thu, 21 June 2007 11:30 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Another simple approach is to implement IF THEN ELSE logic similar to this. All you do is check the value of ATTRIBUTE3 - if null then run script1 else if one of your values then script2 else script3 where script3 just prints an error.

From around 1990...
Q:     How to start a SQL*Plus script when at least one row is found            
       from a table ? If no rows are found, then nothing should be              
       started.                                                                 
                                                                                
A:     Use a script like the following:                                         
       spool tmp.sql                                                            
       Select decode(greatest(count(*),0),'0','start dummy','start script')     
       from tablex where colx = &parameter;                                     
       spool off                                                                
       @tmp


http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1934751139108

Previous Topic: Difference between EXIT and DISCONN
Next Topic: Report in SQLPLUS
Goto Forum:
  


Current Time: Thu Mar 28 11:39:29 CDT 2024