Solved

CASE  Inside of SELECT

Posted on 2008-10-13
14
1,128 Views
Last Modified: 2012-05-05
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
Comment
Question by:ARACK04
[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
14 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 22705104
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
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22705186
Your main SELECT statement needs a FROM clause
0
 
LVL 3

Expert Comment

by:3abqari
ID: 22705195
Are you getting an error?
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

Author Comment

by:ARACK04
ID: 22705275
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
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 22705332

Use single quotes around the constants.  

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


Kent
0
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 500 total points
ID: 22705354
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
 

Author Comment

by:ARACK04
ID: 22705383
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
 

Author Comment

by:ARACK04
ID: 22705464
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
 

Author Closing Comment

by:ARACK04
ID: 31505623
Anyone searching this solution - see post 2 down for needed modification to make it work.
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 22705574
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
 

Author Comment

by:ARACK04
ID: 22705666
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
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 22705718
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
 

Author Comment

by:ARACK04
ID: 22705794
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
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 22706123
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

734 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