Solved

Oracle SQL Case

Posted on 2013-11-04
14
382 Views
Last Modified: 2013-11-04
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
0
Comment
Question by:garethtnash
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
14 Comments
 
LVL 13

Accepted Solution

by:
Alexander Eßer [Alex140181] earned 250 total points
ID: 39621263
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
 

Author Comment

by:garethtnash
ID: 39621269
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
 
LVL 37

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 250 total points
ID: 39621275
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
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!

 

Author Comment

by:garethtnash
ID: 39621295
Doesn't appear to like subqueries

driving in the dark!
0
 

Author Comment

by:garethtnash
ID: 39621314
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
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39621352
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
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 39621374
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
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39621386
Expecting specific results without knowing the where and/or group by clause seems to me a little bit naive ;-)
0
 

Author Comment

by:garethtnash
ID: 39621435
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
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 39621438
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
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39621447
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
 

Author Comment

by:garethtnash
ID: 39621513
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
 

Author Closing Comment

by:garethtnash
ID: 39621999
Thanks for your help!
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup

738 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