Home » Other » Client Tools » condition in SQL (merged)
| condition in SQL (merged) [message #245083] |
Fri, 15 June 2007 02:24  |
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 #245112 is a reply to message #245083] |
Fri, 15 June 2007 03:43  |
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 #245125 is a reply to message #245083] |
Fri, 15 June 2007 04:28 |
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.
|
|
|
|
|
|
| Sql solution [message #245552 is a reply to message #245083] |
Mon, 18 June 2007 02:14 |
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 |
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 |
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 = ¶meter;
spool off
@tmp
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1934751139108
|
|
|
|
Goto Forum:
Current Time: Sun Dec 22 20:20:21 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
