OraFAQ Forum: SQL & PL/SQL » Count Distinct Not Working in Case Select Oracle SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Count Distinct Not Working in Case Select Oracle SQL

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Count Distinct Not Working in Case Select Oracle SQL [message #664309]

Tue, 11 July 2017 14:02

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 )

Report message to a moderator

Re: Count Distinct Not Working in Case Select Oracle SQL [message #664311 is a reply to message #664309]

Tue, 11 July 2017 14:24

Michel Cadot
Messages: 68641
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.

Report message to a moderator

Re: Count Distinct Not Working in Case Select Oracle SQL [message #664331 is a reply to message #664311]

Wed, 12 July 2017 03:27

cookiemonster
Messages: 13920
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?

Report message to a moderator

Re: Count Distinct Not Working in Case Select Oracle SQL [message #664332 is a reply to message #664331]

Wed, 12 July 2017 03:42

cookiemonster
Messages: 13920
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

Report message to a moderator

Re: Count Distinct Not Working in Case Select Oracle SQL [message #664346 is a reply to message #664332]

Wed, 12 July 2017 07:48

Bill B
Messages: 1971
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)

Report message to a moderator

Re: Count Distinct Not Working in Case Select Oracle SQL [message #664348 is a reply to message #664309]

Wed, 12 July 2017 08:02

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Why the pointless use of double quotes around everything?

Report message to a moderator

Re: Count Distinct Not Working in Case Select Oracle SQL [message #664353 is a reply to message #664331]

Wed, 12 July 2017 09:12

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

Report message to a moderator

Re: Count Distinct Not Working in Case Select Oracle SQL [message #664355 is a reply to message #664311]

Wed, 12 July 2017 09:14

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.

Report message to a moderator

Re: Count Distinct Not Working in Case Select Oracle SQL [message #664357 is a reply to message #664355]

Wed, 12 July 2017 09:37

Bill B
Messages: 1971
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.

Report message to a moderator

Re: Count Distinct Not Working in Case Select Oracle SQL [message #664358 is a reply to message #664348]

Wed, 12 July 2017 09:38

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.

Report message to a moderator

Re: Count Distinct Not Working in Case Select Oracle SQL [message #664359 is a reply to message #664357]

Wed, 12 July 2017 09:39

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.

Report message to a moderator

Re: Count Distinct Not Working in Case Select Oracle SQL [message #664366 is a reply to message #664359]

Wed, 12 July 2017 15:05

Bill B
Messages: 1971
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.

Report message to a moderator

Previous Topic:	Merging of Interval Partitions
Next Topic:	Insert statement with bind variable and Create statement

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 22:52:00 CDT 2024