Home » RDBMS Server » Server Administration » inserting an "if... then" statement into SQL
| inserting an "if... then" statement into SQL [message #370929] |
Wed, 08 March 2000 13:42  |
Chris
Messages: 128
Registered: November 1998
|
Senior Member |
|
|
Could someone tell me how I can insert a variable and/or if...then statement into SQL.
Any help would be appreciated.
Chris
This is what I have. Essentially what I want to do is create another field which displays static values based on the value of the E.DESCRIPTION field.
SELECT
A.ACCT_NO , A.FIRST_NAME , A.LAST_NAME , A.MAIN_PHONE_NO , E.DESCRIPTION, NEW_ROW_VALUE
FROM
TBADMIN.ACCT_C A,
TBADMIN.ITEM_INSTANCE_C C,
TBADMIN.WITEM_DEAL_CHARGE E
WHERE
(C.ACCT_NO = A.ACCT_NO)
AND (E.ITEM_NO = C.ITEM_NO)
AND (E.DEAL_NO = C.DEAL_NO)
(the if...then statement might look something like this)
if E.DESCRIPTION = 'X-VALUE' then NEW_ROW_VALUE = '$200'
|
|
|
|
| Re: inserting an "if... then" statement into SQL [message #370931 is a reply to message #370929] |
Wed, 08 March 2000 15:39  |
Suresh
Messages: 189
Registered: December 1998
|
Senior Member |
|
|
Hi,
Use decode function to achieve your end result..
Decode is works just like if..else structure
SELECT
A.ACCT_NO , A.FIRST_NAME , A.LAST_NAME , A.MAIN_PHONE_NO , E.DESCRIPTION,
DECODE( NEW_ROW_VALUE,'X-VALUE','$200','Y-VALUE','$100','$300') NEW_ROW_ALIAS
FROM
TBADMIN.ACCT_C A,
TBADMIN.ITEM_INSTANCE_C C,
TBADMIN.WITEM_DEAL_CHARGE E
WHERE
(C.ACCT_NO = A.ACCT_NO)
AND (E.ITEM_NO = C.ITEM_NO)
AND (E.DEAL_NO = C.DEAL_NO)
DECODE( NEW_ROW_VALUE,'X-VALUE','$200','Y-VALUE','$100','$300')
above decode statement is equivalant to following if..else statement
IF new_row_value='X-VALUE' THEN
new_row_alias := '$200';
ELSIF new_row_value='Y-VALUE' THEN
new_row_alias := '$100';
ELSE
new_row_alias := '$300';
END IF;
hope this helps
Suresh
|
|
|
|
| Re: inserting an "if... then" statement into SQL [message #370933 is a reply to message #370929] |
Wed, 08 March 2000 17:01 |
Cindy
Messages: 88
Registered: November 1999
|
Member |
|
|
Try this...
SELECT
A.ACCT_NO , A.FIRST_NAME , A.LAST_NAME ,
A.MAIN_PHONE_NO , E.DESCRIPTION,
decode(e.description,'x-value','$200','y-value','$300','$400')
FROM
TBADMIN.ACCT_C A,
TBADMIN.ITEM_INSTANCE_C C,
TBADMIN.WITEM_DEAL_CHARGE E
WHERE
(C.ACCT_NO = A.ACCT_NO)
AND (E.ITEM_NO = C.ITEM_NO)
AND (E.DEAL_NO = C.DEAL_NO);
|
|
|
|
| Re: inserting an "if... then" statement into SQL [message #370934 is a reply to message #370929] |
Thu, 09 March 2000 00:42 |
Atavur Rahaman S.A
Messages: 23
Registered: January 2000
|
Junior Member |
|
|
Hello,
Good Day!
Well..The simple way to do that is use DECODE function with appropriate parameters. There is an aliter to do the same but it seems to be ugly but it worth's....
Use the SET operator...i.e UNION ALL to achieve this........
>> Task:-- if E.DESCRIPTION = 'X-VALUE' then NEW_ROW_VALUE = '$200'
SQL> SELECT A.ACCT_NO , A.FIRST_NAME , A.LAST_NAME , A.MAIN_PHONE_NO ,
E.DESCRIPTION, '$200' FROM TBADMIN.ACCT_C A,
TBADMIN.ITEM_INSTANCE_C C,
TBADMIN.WITEM_DEAL_CHARGE E
WHERE (C.ACCT_NO = A.ACCT_NO)
AND (E.ITEM_NO = C.ITEM_NO)
AND (E.DEAL_NO = C.DEAL_NO) AND E.DESCRIPTION = 'X-VALUE'
UNION ALL
SELECT A.ACCT_NO , A.FIRST_NAME , A.LAST_NAME , A.MAIN_PHONE_NO ,
E.DESCRIPTION, '$300' FROM TBADMIN.ACCT_C A,
TBADMIN.ITEM_INSTANCE_C C,
TBADMIN.WITEM_DEAL_CHARGE E
WHERE (C.ACCT_NO = A.ACCT_NO)
AND (E.ITEM_NO = C.ITEM_NO)
AND (E.DEAL_NO = C.DEAL_NO) AND E.DESCRIPTION = 'Y-VALUE'
Hope this will help you...
Regards
Atavur Rahaman S.A
|
|
|
|
Goto Forum:
Current Time: Sat Jan 18 02:19:27 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
