1. Registered User
Join Date
Jun 2003
Posts
34

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

2. Registered User
Join Date
Jul 2001
Location
Germany
Posts
189

## Result

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

3. Registered User
Join Date
Jun 2003
Posts
34

## HI

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

4. Registered User
Join Date
Jul 2001
Location
Germany
Posts
189

## That gives exacly what you have ask for

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

5. Registered User
Join Date
Jun 2003
Posts
34
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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•