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 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: DML
Next Topic: Re: DML
Goto Forum:
  


Current Time: Thu Oct 22 04:52:04 CDT 2020