Solved

Oracle SQL Case

Posted on 2013-11-04
14
372 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
  • 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 36

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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 36

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 36

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle 12c 10 100
Need help with Oracle sql and I am using Aqua studio. 8 62
automatic email alert 1 21
sql query 9 22
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

762 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now