Hi I have a problem. Can anyone help me write this SQL ?
I have a table as :

Col Type pri_flag net_flag pct_flag
D 0 0 0
M 1 0 0
M 0 1 1
M 1 1 0
M 1 0 1

Now I want a output like:
0 - D (Everytime its 'D')
1 - M
1 - pri flag
1 - M
1 - net_flag
0 - pct_flag
1 - M
1 - pri_flag
1 - net_flag
1 - M
1 - pri_flag
0 - pct_flag

The logic is:
i) If we fing Col Type = D in a record put 0
ii) If we get Col Type = M then put 1 for M for that record
iii) If we get a pri_flag=1 along with M we put 1
iv) If we get a net_flag=1 along with M we put 1
v) If we get a pct_flag=1 along with M we put 0

Hope I could explain it.
Thanks

Hello,

use this

SELECT m.*,
DECODE(type, 'D', 0, 'M', DECODE(pct_flag, 1, 0, 1)) AS result
FROM mysql m

(mysql is the table i have used)


Hope that helps ?

Manfred Peter
Alligator Company Software GmbH
http://www.alligatorsql.com

I don't think I'll get the desired result by that SQL. Please see the result that I have given.

Hello,

these are the rules you have defined:

The logic is:
i) If we fing Col Type = D in a record put 0
ii) If we get Col Type = M then put 1 for M for that record
iii) If we get a pri_flag=1 along with M we put 1
iv) If we get a net_flag=1 along with M we put 1
v) If we get a pct_flag=1 along with M we put 0

the SQL statement

SELECT m.*,
DECODE(type, 'D', 0, 'M', DECODE(pct_flag, 1, 0, 1)) AS result
FROM mysql m

1) the statement print a 0 when type is D ....
2) the statement print a 1 when type is M ... but you have also defined in
5 that when pct_flag = 1 then print 0 ... the statement does ...
3) pri_flag = 1 and type = M the print a 1 ... this is waste, cause you want to print always a 1 when type = M
(same in 4)

So, where is the mistake ?

Best regards
Manfred Peter
Alligator Company Software GmbH
http://www.alligatorsql.com

According to the output I have shown I should get something like :

output
-----
0
1
1
1
1
0
1
1
1
1
1
0

Analyse each row. You'll see that :
Row 1: Type = D so the first record in the output is 0
Row 2: Type = M and pri_flag = 1 so we get two consequitive 1's
Row 3: Type = M , pri_flag = 1 and pct_flag =1 so we get 1 for M, another 1 for pri_flag and a 0 for pct_flag as it's set to 1.
so on and so forth.

If you see the output closely you have an output of 12 records and NOT
5. which your sql will generate.

Thanks