Solved

Oracle SQL Case

Posted on 2013-11-04
14
383 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 38

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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

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 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 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 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 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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
Suggested Courses

630 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