Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

CASE WHEN (FIELD1 = 1 AND FIELD2 = 2) THEN SELECT THE MAX OF THIS RECORD FROM THIS FIELD

Status
Not open for further replies.

rodrunner79

IS-IT--Management
Dec 27, 2005
144
US
Hi there, I've been trying to find out how to do this but I can't get the right results so I am resorting to tek-tips to help me out, as I've always had positive experience in these forums.

Just to preface the problem, here's my table of data:
REC_ID F_CD P_CD S_CD VERS_NO
A 1 5 4 1
A 2 3 4 3
A 2 1 2 2
B 1 5 4 1
B 2 3 4 2
C 1 3 4 1
D 1 3 4 1

What I want the query to return is all IDs where IF the F_CD > 1 and the P_CD = 3, then find the maximum VERS_NO and return that record only . So if you look at the first 3 records, it's the same ID but there's 3 versions, I only want to return 1 record out of the three because only 1 record have both F_CD greater than 1 and P_CD equals 3.

Here's what I got but it's returning all the records still.
SQL:
SELECT A.REC_ID, A.VERS_NO, A.F_CD, A.P_CD, A.S_CD
FROM Table1 A,
(SELECT CASE WHEN (F_CD > 1 AND P_CD = 3) THEN MAX(VERS_NO) ELSE 0 END AS MAX_VERS_NO, REC_ID, VERS_NO
 FROM Table1
 GROUP BY REC_ID, DOC_VERS_NO, F_CD, S_CD) B
 WHERE A.REC_ID = B.REC_ID AND
 A.VERS_NO = H2.DOC_CD AND
 H.REC_ID = H2.REC_ID AND
 H.VERS_NO = H2.VERS_NO

Can't means won't try.
 
Sorry, wrong query. Here's the right query:

SQL:
SELECT A.REC_ID, A.VERS_NO, A.F_CD, A.P_CD, A.S_CD
FROM Table1 A,
(SELECT CASE WHEN (F_CD > 1 AND P_CD = 3) THEN MAX(VERS_NO) ELSE 0 END AS MAX_VERS_NO, REC_ID, VERS_NO
 FROM Table1
 GROUP BY REC_ID, DOC_VERS_NO, F_CD, S_CD) B
 WHERE A.REC_ID = B.REC_ID AND
 A.VERS_NO = B.VERS_NO

Can't means won't try.
 
Hi imex,

First, thanks for your response. I tried your suggestion and the query returned only records where (t.F_CD > 1) and (t.P_CD = 3). Your query is only returning Row 2 and 5. That's not what I'm expecting but that's my fault for not properly articulating what I expect the results to return. I think what's missing is an ELSE (F_CD < 1).

Here's what I need it to do, if you look at the data, row 6 & 7 are both version 1 with a their own REC_ID. I need the query to return these records when F_CD < 1 and P_CD = 3 but also return only the rows with the highest maximum version where F_CD > 1 and P_CD = 3. Basically from a business operation, F_CD is my indicator whether the document record is new or has been modified. If F_CD is 1, it's new and if it's greater than 1, then it has been modified; therefore, I need to return the highest version for that ID only where F_CD does not equal 1. So given that, if I was to run the query, I would expect it to only return the following records:

REC_ID F_CD P_CD S_CD VERS_NO
A 2 3 4 3
B 2 3 4 2
C 1 3 4 1
D 1 3 4 1


Can't means won't try.
 
Why can't you edit these post.... Let me rephrase my last sentences:

Basically from a business operation standpoint, F_CD would be my indicator of whether the document record is new or has been modified. If F_CD is 1, it's new and if it's greater than 1, then it has been modified; therefore, I need to return the highest version ONLY for that ID where F_CD does not equal 1.

Can't means won't try.
 
Thank you! That did it.

If you don't mind, could you explain to me what this query is doing. I know it's a common table expression or temp table but never really used one. I just need to understand it.

Can't means won't try.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top