| if then else [message #19473] |
Thu, 21 March 2002 00:42  |
Anita
Messages: 21
Registered: March 2000
|
Junior Member |
|
|
I have a list of products which I want to divide into three groups according to their balances, namely 0-499, 500-1000 and greater then 1000. So basically I have to create a new field to group by, however with the If Then Else Statement I am getting an error.
Select Product, Count(Ref_No), Sum(Balance)
From Products_Table
Group by Product, Balance
If Balance <= 500 Then "Less than LM500"
Else If Balance between (500,1000) Then "500 TO 1000"
Else If Balance > 1000 Then "Greater Than LM1000"
End
Can anybody help please.
|
|
|
|
|
|
|
|
| Re: if then else [message #19485 is a reply to message #19473] |
Thu, 21 March 2002 07:22  |
Jon
Messages: 483
Registered: May 2001
|
Senior Member |
|
|
|
Your SQL statement ends after "Group by Product, Balance" so that is why you are getting the error. If-then-else is a PL/SQL construct. Check out Analytic Functions in the SQL manual. I'm sure they would prove useful. You possibly could use the CASE construct to help you, but I think the analytic functions would be better.
|
|
|
|
| Re: if then else [message #19515 is a reply to message #19473] |
Fri, 22 March 2002 15:09 |
Sudhakar Atmakuru
Messages: 58
Registered: May 2001
|
Member |
|
|
Try this out.
Select Product, Count(Ref_No), Sum(Balance)
From Products_Table
Group by Product,
decode(sign(Balance-500),-1,'Less than LM500',
decode(sign(Balance-1001),-1,'500 to 1000',1,'Greater than LM1000'));
Note: 'if then else' is allowed in PL/SQL, but not in interactive SQL, as Jon said. So you can use DECODE() function for the same purpose in your above SQL. DECODE() is a function, which checks and returns a conditional value, like DECODE(condition,true,true-value,false,false-value,...);
SIGN() is another function checks the positive, negative or zero value of the given.
Good luck :)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
