Welcome to the Toad for Data Analysts Community
Advanced Search - Help - Search Toad World
Welcome, Guest
Login Login / Register
Help
NEW? Get Plugged In

Forums » Join Discussions About... » Toad for Data Analysts

Thread: Simple Crosstab function and not getting correct results

This question is answered. Helpful answers available: 2. Answered answers available: 1.

Reply to this Thread Reply to this Thread Search Forum Search Forum Back to Thread List Back to Thread List Point your RSS reader here for a feed of the latest messages in all forums

Permlink Replies: 2 - Pages: 1 - Last Post: Nov 4, 2009 10:33 AM by: juman.shawi_625
juman.shawi_625

Posts: 15
Registered: 10/2/09
Simple Crosstab function and not getting correct results
Posted: Nov 3, 2009 1:21 PM 		 
  Click to reply to this thread Reply

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:-
<table border="0" cellspacing="0" cellpadding="0" width="230" style="width: 173pt; border-collapse: collapse"> <tbody> <tr height="17" style="height: 12.75pt"> <td class="xl22" width="47" height="17" style="width: 35pt; height: 12.75pt; background-color: transparent; border: windowtext 0.5pt solid">PARID</td> <td class="xl22" width="57" style="border-right: windowtext 0.5pt solid; border-top: windowtext 0.5pt solid; border-left: windowtext; width: 43pt; border-bottom: windowtext 0.5pt solid; background-color: transparent">PROVID</td> <td class="xl22" width="28" style="border-right: windowtext 0.5pt solid; border-top: windowtext 0.5pt solid; border-left: windowtext; width: 21pt; border-bottom: windowtext 0.5pt solid; background-color: transparent">HIT</td> <td class="xl22" width="35" style="border-right: windowtext 0.5pt solid; border-top: windowtext 0.5pt solid; border-left: windowtext; width: 26pt; border-bottom: windowtext 0.5pt solid; background-color: transparent">HME</td> <td class="xl22" width="30" style="border-right: windowtext 0.5pt solid; border-top: windowtext 0.5pt solid; border-left: windowtext; width: 23pt; border-bottom: windowtext 0.5pt solid; background-color: transparent">O/P</td> <td class="xl22" width="33" style="border-right: windowtext 0.5pt solid; border-top: windowtext 0.5pt solid; border-left: windowtext; width: 25pt; border-bottom: windowtext 0.5pt solid; background-color: transparent">THH</td> </tr> <tr height="17" style="height: 12.75pt"> <td class="xl23" height="17" align="right" style="border-right: windowtext 0.5pt solid; border-top: windowtext; border-left: windowtext 0.5pt solid; border-bottom: windowtext 0.5pt solid; height: 12.75pt; background-color: transparent">1</td> <td class="xl23" align="right" style="border-right: windowtext 0.5pt solid; border-top: windowtext; border-left: windowtext; border-bottom: windowtext 0.5pt solid; background-color: transparent">1</td> <td class="xl23" style="border-right: windowtext 0.5pt solid; border-top: windowtext; border-left: windowtext; border-bottom: windowtext 0.5pt solid; background-color: transparent"> </td> <td class="xl23" style="border-right: windowtext 0.5pt solid; border-top: windowtext; border-left: windowtext; border-bottom: windowtext 0.5pt solid; background-color: transparent"> </td> <td class="xl23" style="border-right: windowtext 0.5pt solid; border-top: windowtext; border-left: windowtext; border-bottom: windowtext 0.5pt solid; background-color: transparent"> </td> <td class="xl23" align="right" style="border-right: windowtext 0.5pt solid; border-top: windowtext; border-left: windowtext; border-bottom: windowtext 0.5pt solid; background-color: transparent">1</td> </tr> <tr height="17" style="height: 12.75pt"> <td class="xl23" height="17" align="right" style="border-right: windowtext 0.5pt solid; border-top: windowtext; border-left: windowtext 0.5pt solid; border-bottom: windowtext 0.5pt solid; height: 12.75pt; background-color: transparent">1</td> <td class="xl23" align="right" style="border-right: windowtext 0.5pt solid; border-top: windowtext; border-left: windowtext; border-bottom: windowtext 0.5pt solid; background-color: transparent">518</td> <td class="xl23" align="right" style="border-right: windowtext 0.5pt solid; border-top: windowtext; border-left: windowtext; border-bottom: windowtext 0.5pt solid; background-color: transparent">4</td> <td class="xl23" style="border-right: windowtext 0.5pt solid; border-top: windowtext; border-left: windowtext; border-bottom: windowtext 0.5pt solid; background-color: transparent"> </td> <td class="xl23" style="border-right: windowtext 0.5pt solid; border-top: windowtext; border-left: windowtext; border-bottom: windowtext 0.5pt solid; background-color: transparent"> </td> <td class="xl23" style="border-right: windowtext 0.5pt solid; border-top: windowtext; border-left: windowtext; border-bottom: windowtext 0.5pt solid; background-color: transparent"> </td> </tr> <tr height="17" style="height: 12.75pt"> <td class="xl23" height="17" align="right" style="border-right: windowtext 0.5pt solid; border-top: windowtext; border-left: windowtext 0.5pt solid; border-bottom: windowtext 0.5pt solid; height: 12.75pt; background-color: transparent">1</td> <td class="xl23" align="right" style="border-right: windowtext 0.5pt solid; border-top: windowtext; border-left: windowtext; border-bottom: windowtext 0.5pt solid; background-color: transparent">519</td> <td class="xl23" align="right" style="border-right: windowtext 0.5pt solid; border-top: windowtext; border-left: windowtext; border-bottom: windowtext 0.5pt solid; background-color: transparent">1</td> <td class="xl23" style="border-right: windowtext 0.5pt solid; border-top: windowtext; border-left: windowtext; border-bottom: windowtext 0.5pt solid; background-color: transparent"> </td> <td class="xl23" style="border-right: windowtext 0.5pt solid; border-top: windowtext; border-left: windowtext; border-bottom: windowtext 0.5pt solid; background-color: transparent"> </td> <td class="xl23" style="border-right: windowtext 0.5pt solid; border-top: windowtext; border-left: windowtext; border-bottom: windowtext 0.5pt solid; background-color: transparent"> </td> </tr> <tr height="17" style="height: 12.75pt"> <td class="xl23" height="17" align="right" style="border-right: windowtext 0.5pt solid; border-top: windowtext; border-left: windowtext 0.5pt solid; border-bottom: windowtext 0.5pt solid; height: 12.75pt; background-color: transparent">1</td> <td class="xl23" align="right" style="border-right: windowtext 0.5pt solid; border-top: windowtext; border-left: windowtext; border-bottom: windowtext 0.5pt solid; background-color: transparent">521</td> <td class="xl23" align="right" style="border-right: windowtext 0.5pt solid; border-top: windowtext; border-left: windowtext; border-bottom: windowtext 0.5pt solid; background-color: transparent">2</td> <td class="xl23" style="border-right: windowtext 0.5pt solid; border-top: windowtext; border-left: windowtext; border-bottom: windowtext 0.5pt solid; background-color: transparent"> </td> <td class="xl23" style="border-right: windowtext 0.5pt solid; border-top: windowtext; border-left: windowtext; border-bottom: windowtext 0.5pt solid; background-color: transparent"> </td> <td class="xl23" style="border-right: windowtext 0.5pt solid; border-top: windowtext; border-left: windowtext; border-bottom: windowtext 0.5pt solid; background-color: transparent"> </td> </tr> <tr height="17" style="height: 12.75pt"> <td class="xl23" height="17" align="right" style="border-right: windowtext 0.5pt solid; border-top: windowtext; border-left: windowtext 0.5pt solid; border-bottom: windowtext 0.5pt solid; height: 12.75pt; background-color: transparent">1</td> <td class="xl23" align="right" style="border-right: windowtext 0.5pt solid; border-top: windowtext; border-left: windowtext; border-bottom: windowtext 0.5pt solid; background-color: transparent">522</td> <td class="xl23" align="right" style="border-right: windowtext 0.5pt solid; border-top: windowtext; border-left: windowtext; border-bottom: windowtext 0.5pt solid; background-color: transparent">2</td> <td class="xl23" style="border-right: windowtext 0.5pt solid; border-top: windowtext; border-left: windowtext; border-bottom: windowtext 0.5pt solid; background-color: transparent"> </td> <td class="xl23" style="border-right: windowtext 0.5pt solid; border-top: windowtext; border-left: windowtext; border-bottom: windowtext 0.5pt solid; background-color: transparent"> </td> <td class="xl23" style="border-right: windowtext 0.5pt solid; border-top: windowtext; border-left: windowtext; border-bottom: windowtext 0.5pt solid; background-color: transparent"> </td> </tr> </tbody></table>
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

Debbie Peabody

Posts: 286
Registered: 2/15/07
Re: Simple Crosstab function and not getting correct results
Posted: Nov 4, 2009 9:46 AM   in response to: juman.shawi_625 		 
  Click to reply to this thread Reply

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


juman.shawi_625

Posts: 15
Registered: 10/2/09
Re: Simple Crosstab function and not getting correct results
Posted: Nov 4, 2009 10:33 AM   in response to: Debbie Peabody 		 
  Click to reply to this thread Reply

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

Pages: 1
Back to Thread List Back to Thread List
Legend
Guru: 2001 + pts
Expert: 751 - 2000 pts
Enthusiast: 31 - 750 pts
Novice: 0 - 30 pts
Moderators
Helpful answer (5 pts)
Answered (10 pts)

Point your RSS reader here for a feed of the latest messages in all forums



© 2009 Quest Software, Inc  |  Legal Agreement  |  Privacy Policy  |  Terms of Use  |  Report a Problem with This Site
Powered By Jive Software