Oracle SQL Case

Hey All,

I would appreciate a little help with a query I've got.

I've only got access to the line of code, I can't provide more detail as to the table structure etc

COUNT(DISTINCT CASE WHEN "Bid Actions"."Bid Action Code" = '0002' THEN "Bid Actions"."Bid Action Code" ELSE null END)

Open in new window


So what I'm trying to do is count all rows returned where Bid Action Code" = '0002' so for the particular query if there were a total of 12 rows then the returned result would be 12..

Any suggestions?

Many thanks
garethtnashAsked:
Who is Participating?
 
Alexander Eßer [Alex140181]Connect With a Mentor Software DeveloperCommented:
Not much information :-(

Why not just do a
select count(*) from ... where "Bid Actions"."Bid Action Code" = '0002' group by ...

Open in new window


btw: what DB?! Oracle, SQL Server or MySQL ?!?
0
 
garethtnashAuthor Commented:
Hi, Sorry its Oracle. I don't have access to the where clause, this all needs to be within the select, and I'm not starting the select?

Hence  not much info..

Sorry very confusing!
0
 
Geert GConnect With a Mentor Oracle dbaCommented:
lol, it's like driving a car without being aloud to turn the steering wheel

why those restrictions ?
who imposes them ?

use a subselect in the select clause
select a, b, (select count(*) from yourtable where yourcondition) yourcount from ...

Open in new window

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
garethtnashAuthor Commented:
Doesn't appear to like subqueries

driving in the dark!
0
 
garethtnashAuthor Commented:
Hey Guys,

out of curiosity is the syntax almost correct?

COUNT(DISTINCT CASE WHEN "Bid Actions"."Bid Action Code" = '0002' THEN "Bid Actions"."Bid Action Code" ELSE null END)

Open in new window


It seems to make sense, but the results are out..

Thanks
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
That tiny code snippet looks "ok" to me, but who the hell creates tables with spaces in tablename AND column names?!? OMFG :-(

driving in the dark!
TRYING to drive in the dark WITHOUT WEELS or something like that...
0
 
Geert GOracle dbaCommented:
the results will be grouped, possibly that's why you're values are off
i hope you know the grouping condition ... :)

if not then it's driving without wheels, steering wheel and without a windshield too ...
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
Expecting specific results without knowing the where and/or group by clause seems to me a little bit naive ;-)
0
 
garethtnashAuthor Commented:
I think I managed to find the solution --

COUNT(DISTINCT CASE WHEN "Bid Actions"."Bid Action Code" = '0002' THEN "Bid Actions"."Bid Action Code" ELSE null END)

Find * rows where Bid Action Code = '0002'
From that list count all distinct values in Bid Action Code  (if true there will only ever be 1 record the first one)
if no values where Bid Action Code = '0002' return 0

hence either 1 or 0 is returned..

I needed to do the Count Distinct on a unique column !

I think that is correct?
0
 
Geert GOracle dbaCommented:
if you don't have control over the where clause
or no control whatsoever

why not ask for a read-only user on that schema to the dba ?
much simpler to verify your results like that
> you would be able to give table info, column info, ...etc ...etc
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
why not ask for a read-only user on that schema to the dba ?
much simpler to verify your results like that
> you would be able to give table info, column info, ...etc ...etc
good point ;-)
0
 
garethtnashAuthor Commented:
I agree, totally, but it's a legacy system :( I think what I needed was a column in Bid Actions with unique values....

Thank you
0
 
garethtnashAuthor Commented:
Thanks for your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.