Home » SQL & PL/SQL » SQL & PL/SQL » Count Distinct Not Working in Case Select Oracle SQL
Count Distinct Not Working in Case Select Oracle SQL [message #664309] Tue, 11 July 2017 14:02 Go to next message
phaenggi
Messages: 5
Registered: July 2017
Junior Member
I have a PL/SQL question in which the code fails to count distinct ID's.  It does count them, but does not do so distinctly. Anybody have any idea as to why?

Thank you!

 SELECT
"RESERVATION_STAT_DAILY"."RESORT" AS "RESORT",
"RESERVATION_STAT_DAILY"."BUSINESS_DATE" AS "BUSINESS_DATE",
 to_char("RESERVATION_STAT_DAILY"."BUSINESS_DATE",'MON-yyyy') AS "MONTHYEAR",
 Extract(day from "RESERVATION_STAT_DAILY"."BUSINESS_DATE") AS "DAY",  
 Extract(month from "RESERVATION_STAT_DAILY"."BUSINESS_DATE") AS "MONTH",
 Extract(year from "RESERVATION_STAT_DAILY"."BUSINESS_DATE") AS "YEAR",
 "RESERVATION_STAT_DAILY"."SOURCE_CODE" AS "SOURCE_CODE",
 "RESERVATION_STAT_DAILY"."MARKET_CODE" AS "MARKET_CODE",
 "RESERVATION_STAT_DAILY"."RATE_CODE" AS "RATE_CODE",
 "RESERVATION_STAT_DAILY"."RESV_NAME_ID" AS "RESV_NAME_ID",
     (CASE WHEN "RESERVATION_STAT_DAILY"."SOURCE_CODE" = 'GDS' 
          AND "RESERVATION_STAT_DAILY"."RATE_CODE" NOT IN ('BKIT', 'EXPEDIA')
          AND "RESERVATION_STAT_DAILY"."MARKET_CODE" NOT IN ('GOVG', 'ENT')
     THEN 'GDS'
     ELSE 'Other'
END) AS "BizUnit",
COUNT(DISTINCT CASE WHEN "RESERVATION_STAT_DAILY"."SOURCE_CODE" = 'GDS' 
          AND "RESERVATION_STAT_DAILY"."RATE_CODE" NOT IN ('BKIT', 'EXPEDIA')
          AND "RESERVATION_STAT_DAILY"."MARKET_CODE" NOT IN ('GOVG', 'ENT')
     THEN "RESERVATION_STAT_DAILY"."RESV_NAME_ID" 
ELSE NULL   
END) AS "COST",

(SUM("RESERVATION_STAT_DAILY"."BUSINESS_DATE" - "RESERVATION_STAT_DAILY"."BUSINESS_DATE_CREATED")/(COUNT      ("RESERVATION_STAT_DAILY"."BUSINESS_DATE_CREATED"))) AS "DIFF",
SUM(NVL("RESERVATION_STAT_DAILY"."NIGHTS",0)) AS "NIGHTS",
SUM(NVL("RESERVATION_STAT_DAILY"."ROOM_REVENUE",0)) AS "ROOM_REVENUE"
FROM "OPERA"."RESERVATION_STAT_DAILY" "RESERVATION_STAT_DAILY"
Where RESORT in     ('558339','558341','4856','558340','602836','HCA','HZSD', 'TAC') and
BUSINESS_DATE < SYSDATE AND EXTRACT(year FROM "RESERVATION_STAT_DAILY"."BUSINESS_DATE_CREATED") >=2016
GROUP BY
"RESERVATION_STAT_DAILY"."RESORT",
"RESERVATION_STAT_DAILY"."BUSINESS_DATE",
  to_char("RESERVATION_STAT_DAILY"."BUSINESS_DATE",'MON-yyyy'),
  Extract(day from "RESERVATION_STAT_DAILY"."BUSINESS_DATE"),  
 Extract(month from "RESERVATION_STAT_DAILY"."BUSINESS_DATE"),
 Extract(year from "RESERVATION_STAT_DAILY"."BUSINESS_DATE"),
  "RESERVATION_STAT_DAILY"."SOURCE_CODE",
  "RESERVATION_STAT_DAILY"."MARKET_CODE",
  "RESERVATION_STAT_DAILY"."RATE_CODE", 
  "RESERVATION_STAT_DAILY"."RESV_NAME_ID",

  (  CASE 
WHEN (("RESERVATION_STAT_DAILY"."SOURCE_CODE" = 'GDS') AND ("RESERVATION_STAT_DAILY"."RATE_CODE" != 'BKIT' OR "RESERVATION_STAT_DAILY"."RATE_CODE" != 'EXPEDIA'
)) THEN 'GDS'
ELSE 'Other'
END )
Re: Count Distinct Not Working in Case Select Oracle SQL [message #664311 is a reply to message #664309] Tue, 11 July 2017 14:24 Go to previous messageGo to next message
Michel Cadot
Messages: 65252
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You surely did something wrong but what do you expect from us and this mess.
Format your SQL, if you don't know how to do it, learn it using SQL Formatter.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: Count Distinct Not Working in Case Select Oracle SQL [message #664331 is a reply to message #664311] Wed, 12 July 2017 03:27 Go to previous messageGo to next message
cookiemonster
Messages: 12957
Registered: September 2008
Location: Rainy Manchester
Senior Member
Count distinct is too basic to make mistakes with so either:
a) you're looking at an oracle bug - in which case you need to contact oracle support
b) it is doing what it's supposed to do and there's a flaw in whatever method you are using to check the result.

b is much, much more likely.
So how are you determining that it's wrong?
Re: Count Distinct Not Working in Case Select Oracle SQL [message #664332 is a reply to message #664331] Wed, 12 July 2017 03:42 Go to previous messageGo to next message
cookiemonster
Messages: 12957
Registered: September 2008
Location: Rainy Manchester
Senior Member
You appear to have found a solution before you posted here:
https://community.oracle.com/thread/4062222
Re: Count Distinct Not Working in Case Select Oracle SQL [message #664346 is a reply to message #664332] Wed, 12 July 2017 07:48 Go to previous messageGo to next message
Bill B
Messages: 1701
Registered: December 2004
Senior Member
I formatted your code for you

  SELECT Rsd.Resort AS Resort,
         Rsd.Business_date AS Business_date,
         TO_CHAR (Rsd.Business_date, 'MON-yyyy') AS Monthyear,
         EXTRACT (DAY FROM Rsd.Business_date) AS Day,
         EXTRACT (MONTH FROM Rsd.Business_date) AS Month,
         EXTRACT (YEAR FROM Rsd.Business_date) AS Year,
         Rsd.Source_code AS Source_code,
         Rsd.Market_code AS Market_code,
         Rsd.Rate_code AS Rate_code,
         Rsd.Resv_name_id AS Resv_name_id,
         (CASE
              WHEN     Rsd.Source_code = 'GDS'
                   AND Rsd.Rate_code NOT IN ('BKIT', 'EXPEDIA')
                   AND Rsd.Market_code NOT IN ('GOVG', 'ENT')
              THEN
                  'GDS'
              ELSE
                  'Other'
          END)
             AS Bizunit,
         COUNT (
             DISTINCT CASE
                          WHEN     Rsd.Source_code = 'GDS'
                               AND Rsd.Rate_code NOT IN ('BKIT', 'EXPEDIA')
                               AND Rsd.Market_code NOT IN ('GOVG', 'ENT')
                          THEN
                              Rsd.Resv_name_id
                          ELSE
                              NULL
                      END)
             AS Cost,
         (  SUM (Rsd.Business_date - Rsd.Business_date_created)
          / (COUNT (Rsd.Business_date_created)))
             AS Diff,
         SUM (NVL (Rsd.Nights, 0)) AS Nights,
         SUM (NVL (Rsd.Room_revenue, 0)) AS Room_revenue
    FROM Opera.Reservation_stat_daily Rsd
   WHERE     Resort IN ('558339',
                        '558341',
                        '4856',
                        '558340',
                        '602836',
                        'HCA',
                        'HZSD',
                        'TAC')
         AND Business_date < SYSDATE
         AND EXTRACT (YEAR FROM Rsd.Business_date_created) >= 2016
GROUP BY Rsd.Resort,
         Rsd.Business_date,
         TO_CHAR (Rsd.Business_date, 'MON-yyyy'),
         EXTRACT (DAY FROM Rsd.Business_date),
         EXTRACT (MONTH FROM Rsd.Business_date),
         EXTRACT (YEAR FROM Rsd.Business_date),
         Rsd.Source_code,
         Rsd.Market_code,
         Rsd.Rate_code,
         Rsd.Resv_name_id,
         (CASE
              WHEN (    (Rsd.Source_code = 'GDS')
                    AND (   Rsd.Rate_code != 'BKIT'
                         OR Rsd.Rate_code != 'EXPEDIA'))
              THEN
                  'GDS'
              ELSE
                  'Other'
          END)
Re: Count Distinct Not Working in Case Select Oracle SQL [message #664348 is a reply to message #664309] Wed, 12 July 2017 08:02 Go to previous messageGo to next message
joy_division
Messages: 4802
Registered: February 2005
Location: East Coast USA
Senior Member
Why the pointless use of double quotes around everything?
Re: Count Distinct Not Working in Case Select Oracle SQL [message #664353 is a reply to message #664331] Wed, 12 July 2017 09:12 Go to previous messageGo to next message
phaenggi
Messages: 5
Registered: July 2017
Junior Member
cookiemonster wrote on Wed, 12 July 2017 03:27
Count distinct is too basic to make mistakes with so either:
a) you're looking at an oracle bug - in which case you need to contact oracle support
b) it is doing what it's supposed to do and there's a flaw in whatever method you are using to check the result.

b is much, much more likely.
So how are you determining that it's wrong?

Cookiemonster you were absolutely correct! I spoke to my CIO about this and there was a bug, which we were able to fix yesterday!! Nothing was wrong with the code to begin with, besides it was wrote poorly by whomever did it before me. I have the problem fixed now. Thanks for your assistance on this and potential workarounds! Smile
Re: Count Distinct Not Working in Case Select Oracle SQL [message #664355 is a reply to message #664311] Wed, 12 July 2017 09:14 Go to previous messageGo to next message
phaenggi
Messages: 5
Registered: July 2017
Junior Member
Michel Cadot wrote on Tue, 11 July 2017 14:24

You surely did something wrong but what do you expect from us and this mess.
Format your SQL, if you don't know how to do it, learn it using SQL Formatter.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Hey Mike, this was my first post on this board. Relax..............coming across like that does no one any favors!! It's definitely not supportive in any way.
Re: Count Distinct Not Working in Case Select Oracle SQL [message #664357 is a reply to message #664355] Wed, 12 July 2017 09:37 Go to previous messageGo to next message
Bill B
Messages: 1701
Registered: December 2004
Senior Member
That's great that you found the problem, one of the things we do on this forum is to show the fixes so that people who are looking at similar problems have a hint of what to do.
Re: Count Distinct Not Working in Case Select Oracle SQL [message #664358 is a reply to message #664348] Wed, 12 July 2017 09:38 Go to previous messageGo to next message
phaenggi
Messages: 5
Registered: July 2017
Junior Member
joy_division wrote on Wed, 12 July 2017 08:02
Why the pointless use of double quotes around everything?

Haha, I have no idea! I did not write this code, was the systems analyst before me! This code is over a 100 pages long if you paste it into word. It's ridiculous.
Re: Count Distinct Not Working in Case Select Oracle SQL [message #664359 is a reply to message #664357] Wed, 12 July 2017 09:39 Go to previous messageGo to next message
phaenggi
Messages: 5
Registered: July 2017
Junior Member
Bill B wrote on Wed, 12 July 2017 09:37
That's great that you found the problem, one of the things we do on this forum is to show the fixes so that people who are looking at similar problems have a hint of what to do.
Yup, that's fantastic Bill, thanks for your support for this issue.
Re: Count Distinct Not Working in Case Select Oracle SQL [message #664366 is a reply to message #664359] Wed, 12 July 2017 15:05 Go to previous message
Bill B
Messages: 1701
Registered: December 2004
Senior Member
LOL. I'm sorry, what I meant is that you should share what was wrong with your script so that someone attempting the same thing has your fix to look at. Sorry that I wasn't clearer.
Previous Topic: Merging of Interval Partitions
Next Topic: Insert statement with bind variable and Create statement
Goto Forum:
  


Current Time: Wed Dec 13 03:33:45 CST 2017

Total time taken to generate the page: 0.27265 seconds