Solved

CASE  Inside of SELECT

Posted on 2008-10-13
14
1,124 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
14 Comments
 
LVL 45

Expert Comment

by:Kdo
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
 

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:Kdo
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:
Kdo 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

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

18 Experts available now in Live!

Get 1:1 Help Now