Simple Crosstab function and not getting correct results

Toad Data Point
1

The script is simple:-

SELECT DISTINCT T2.PROVPARENTID AS PARID,T2.PROVID,
SUM (DECODE (T1.SERVICECATID, ‘HIT’, 1, NULL)) AS HIT,
SUM (DECODE (T1.SERVICECATID, ‘HME’, 1, NULL)) AS HME,
SUM (DECODE (T1.SERVICECATID, ‘O/P’, 1, NULL)) AS “O/P”,
SUM (DECODE (T1.SERVICECATID, ‘THH’, 1, NULL)) AS THH
FROM TBLPROVIDERSVCCATEGORY T1, TBLPROV T2
WHERE T1.PROVID = T2.PROVID
GROUP BY T2.PROVPARENTID,T2.PROVID,
ORDER BY T2.PROVPARENTID, T2.PROVID;
results:-

PARID
PROVID
HIT
HME
O/P
THH
1
1

1
1
518
4

1
519
1

1
521
2

1
522
2

My question is why results for HIT shown values 4, 2,2 instead of 1 as i requested as part od script?
And I did confirm that there is only unique records, so what am i doing wrong
Please advise thank you
Juman

2

The sql/grid says that for PROVID 518 there were a total of 4 rows that the value was ‘HIT’. Build a query to check this value. Your decodes say that if it finds a specific value (‘HIT’) give it the number 1. And the SUM adds all the times that if finds this occurrence. I can’t say the grid is wrong without knowing the data.

The best thing to do is query to data to validate your grid.

Debbie

3

Yes i actually i did validated data and there were only record for each, but now you made think is to make this value instead of 1 a zero because my whole purpose is to get a crosstab and with that the accumulation value will stay zero if i can have have an x instead of a number it will be great i will try to figure out how to convert the zero to x

thank you for your help