?
Solved

null value in DECODE gives wrong values count in Crystal

Posted on 2006-05-20
5
Medium Priority
?
799 Views
Last Modified: 2008-01-09
Hi,
I create view in Oracle and here is short part of it:

SELECT  
     MAX(DATA.name) AS Name,
     SUM(DECODE(DATA.Type_M1, NULL, 0, 1)) AS Type_M1
FROM DATA ....

I use this view in Crystal 9 for count Type_M1 for each Name.
I got a vert strange problem - if I run my query through database I get Type_M1 = 47
but if check Type_M1 through Crystal it becomes equal = 49.

How can I get right number(47)in Crystal? And why I get wrong number(because when I run the query from Crystal in Oracle it gives me right result again - 47!)

I greatly appreciate any help,

thank you very much

Lana

0
Comment
Question by:lana123
  • 2
4 Comments
 
LVL 6

Expert Comment

by:hans_vd
ID: 16724291
Try this:

SELECT  
     MAX(DATA.name) AS Name,
     SUM(DECODE(nvl(DATA.Type_M1, '###'), '###', 0, 1)) AS Type_M1
FROM DATA
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16725911
or try:

SELECT  
     MAX(DATA.name) AS Name,
     SUM(nvl2(DATA.Type_M1, 1,0)) AS Type_M1
FROM DATA
0
 
LVL 19

Accepted Solution

by:
actonwang earned 1400 total points
ID: 16725915
Actually, you just need to do this:

SELECT  
     MAX(DATA.name) AS Name,
     COUNT(DATA.Type_M1) AS Type_M1
FROM DATA


NOTE: count will not nul l values.

Acton
0
 

Expert Comment

by:AnnieMod
ID: 20239952
Force accepted

AnnieMod
Cleanup Admin
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses
Course of the Month13 days, 18 hours left to enroll

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question