?
Solved

Oracle SQL Case

Posted on 2013-11-04
14
Medium Priority
?
384 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 14

Accepted Solution

by:
Alexander Eßer [Alex140181] earned 1000 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 38

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 1000 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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 14

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 38

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 14

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 38

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 14

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

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.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup

719 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