Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1134
  • Last Modified:

CASE Inside of SELECT

I know next to nothing about running queries on an AS400, but I can't for the life of me get this to run - it's adapted from a SQL Server query.

Any help would be much appreciated:

SELECT DISTINCT prprty, CASE WHEN prpRty IN (SELECT prprty FROM wlhjlfi WHERE deck = W4) THEN Yes ELSE No AS DkExists
0
ARACK04
Asked:
ARACK04
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi ARACK04,

The query doesn't select from anything.  (The subquery does, but not the outer query.)

But the format of the CASE portion looks reasonable.



Good Luck,
Kent
0
 
Daniel WilsonCommented:
Your main SELECT statement needs a FROM clause
0
 
3abqariCommented:
Are you getting an error?
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
ARACK04Author Commented:
Sorry, here's the good query:

DISPLAY 'SELECT DISTINCT prprty, CASE WHEN prpRty IN (SELECT prprty FROM wlhjlfi
 WHERE   deck = "W4") THEN "Yes" ELSE "No"  END AS DkExists  FROM pyavpf'      

Error Msg:

Cause . . . . . :   The selection item type 3 in item number 2 for selection  
  type 3 is not a valid selection item type.  Selection type 1 indicates      
  record selection, selection type 2 indicates group by selection, selection  
  type 3 indicates case selection, and selection type 4 indicates join        
  selection.                                                                  
0
 
Kent OlsenData Warehouse Architect / DBACommented:

Use single quotes around the constants.  

Single quotes are used around strings, double quotes are used around identifiers.


Kent
0
 
Kent OlsenData Warehouse Architect / DBACommented:
By the way, this query may be a lot faster if you do an outer join of the two tables, and filter on the results.  I'm assuming that prprty is the primary key (join key) for these tables.


SELECT DISTINCT
  t0.prprty,
  case when t1.prprty is NULL then 'No' else 'Yes' as DkExists
FROM pvyapf t0
LEFT OUTER JOIN mlhjlfi t1
  ON t0.prprty = t1.prprty
WHERE t1.deck = 'W4';


Good Luck,
Kent
0
 
ARACK04Author Commented:
That's a really good idea - trying it now.

In any event, do you know what's wrong with this:

Comparison operator IN not valid.                                

SELECT DISTINCT prprty, CASE WHEN prpRty IN (SELECT prprty        
FROM wlhjlfi                                                      
 WHERE   deck = 'W4') THEN 'Yes' ELSE 'No'  END AS DkExists  FROM
pyavpf                                                            
0
 
ARACK04Author Commented:
You were sooo close:

 t0.prprty,
  case when t1.prprty is NULL then 'No' else 'Yes' as DkExists
FROM pvyapf t0
LEFT OUTER JOIN
   ( SELECT * FROM mlhjlfi   WHERE t1.deck = 'W4' ) t1
  ON t0.prprty = t1.prprty;

With your way it filtered out too soon, and you only had yes's.

Thank you though - I should have figured out the left join on my own - I'm new to DB2, not not SQL in general!!!
0
 
ARACK04Author Commented:
Anyone searching this solution - see post 2 down for needed modification to make it work.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Ahha.  I was thinking that the data was somewhat different.  (Obviously, I wasn't really thinking.)

But, Great catch!  :)  

By the way, if these are large tables you'll want a slightly different SQL yet.  By definition, the derived table *t1* can not be indexed so if it contains tens or hundreds of thousands of rows, a lengthy full table scan could make this a slow query.

On the other hand, by moving the filter so that the join is on indexed items, the query could be lightening fast, even with a large number of rows.

SELECT DISTINCT
 t0.prprty,
  case when t1.prprty is NULL or t1. deck <> 'W4' then 'No' else 'Yes' as DkExists
FROM pvyapf t0
LEFT OUTER JOIN mlhjlfi t1
  ON t0.prprty = t1.prprty;

It's slightly less straight-forward, but allows the join to proceed on the keyed columns and filters in a single pass.


Good Luck,
Kent
0
 
ARACK04Author Commented:
Good point, but unfortunately this breaks down because of the nature of the data.  A single property can have rows for both 'W4' and something else at the same time.  We're essentially testing for whether or not a row with W4 exists for a given property, so the left join was perfect (even though slightly less performant).

Thanks again!
0
 
Kent OlsenData Warehouse Architect / DBACommented:
I think that that is covered.

The solution with the derived table as *t1* selects only rows with deck='W4' and joins them.  If no row is selected for the right half of the join, the answer is 'No', otherwise 'Yes'.

The last query should do the same.  If a join is not done, or a join is done and the row is not for deck='W4', the answer is 'No', otherwise 'Yes'.

Then both queries filter with DISTINCT.  You should get the same rows returned with either query.

Or did I miss something?
Kent
0
 
ARACK04Author Commented:
With the second query, assume you had, in t1:

prprty     deck
123        W4
123        E9

Correct me if I'm wrong, but when you join, you'll get two rows, one with a "Yes", and another with a "No", which DISTINCT will not filter.  If prprty were unique in this table, it would be golden.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Man, but I'm thick today.

You're looking for prprty values with ANY 'Yes' result.  I've taken it a step too far.

Or to paraphrase Mel Brooks, 'I've gone to plaid'!!!!


  :)
0

Featured Post

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now