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

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

Complicated CASE statement... is this possible?

Hello,

  I don't know if there is any way to do this but I hope so....

In the CASE statement below on the first CASE statement where I am returning a different table column (CLLABS.TCOMMENT)... What I really need to return is the TCOMMENT field for the next two rows of data... In other words I don't need the TCOMMENT field for that row, I need it for the 2 following rows beneath it.  Is this possible, and if so how would it be done? Thanks!

SELECT LEFT(OBSERVE_DESC,40),  
CASE
WHEN LEFT(OBSERVE_DESC,15)= 'INTERPRETATION:' THEN CLLABS.TCOMMENT
WHEN LEFT(OBSERVE_VALUE,15)= 'NON-REACTIVE' THEN 'NR'
WHEN LEFT(OBSERVE_VALUE,8)= 'see note' THEN 'KWK Note'
WHEN LEFT(OBSERVE_VALUE,3)= 'DNR' THEN NULL
ELSE LEFT(OBSERVE_VALUE,25) END,
LTRIM(CAST(DATEIN AS CHAR(12)))  
FROM CLLABS WHERE COMPANY='MASH' AND SSNO='146223'

0
SOMCEE
Asked:
SOMCEE
1 Solution
 
regbesCommented:
Hi SOMCEE,

try something like a self join where you return 3 rows in one long row then use the case to get the cols you want
0
 
AaronAbendCommented:
When you say "next two rows" that is meaningless unless you have an order by statement. You could be getting a default sort based on a clustered index, but that's not a good way to work. Is there a column that lets you know which rows belong together? You need that to follow regbes suggestion, which should work.
0
 
dqmqCommented:
Yes, it's possible, and would look something like this:

...
THEN CLLABS_2.TCOMMENT + ' ' + CLLABS_3.TCOMMENT
...
FROM CLABS as CLABS,
          CLABS as CLABS_2.
          CLABS as CLABS_3
WHERE COMPANY='MASH' AND SSNO='146223'
AND

a set of conditions to tie the first, second, and third lines together. With the info given, I can only offer conjecture but it go something like:

AND CLABS_2.COMPANY = CLABS.COMPANY
AND CLABS_3.COMPANY = CLABS.COMPANY
AND CLABS_2.SSNO = CLABS.SSNO
AND CLABS_3.SSN = CLABS.SSN
AND CLABS_2.SomeSeqNumber = CLABS.SomeSeqNumber + 1
AND CLABS_3.SomeSeqNumber = CLABS.SomeSeqNumber + 2


Saying you want the "following two rows", is sort of undefined.  The trick is explaining precisely how to identify those two rows in an SQL sense.  


 
   
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
dqmqCommented:
Drat, just noticed typo. Should have used a comma, not a period on the FROM clause
0
 
SOMCEEAuthor Commented:
dqmg,

  Yes actually I forgot to mention that there are a couple of columns used to determine what rows go together... They are TSEQUENCE and TSUBSEQ, where in this case TSEQUENCE is alway 2 and then TSUBSEQ is 0, 1 and 2 respectively... so I believe your above example would work... Thanks!

0
 
dqmqCommented:
OK, then try this:

THEN CLLABS_2.TCOMMENT + ' ' + CLLABS_3.TCOMMENT
...
FROM CLlABS as ClLABS,
          CLlABS as ClLABS_2,
          CLlABS as CLlABS_3
WHERE CLLABS.COMPANY='MASH' AND CLLABS.SSNO='146223'
AND CLlABS_2.TSEQUENCE = TSEQUENCE
AND CLlABS_3.TSEQUENCE = TSEQUENCE
AND CLlABS.TSUBSEQ = 0      
AND CLlABS_2.TSUBSEQ = CLlABS.TSUBSEQ + 1
AND CLlABS_3.TSUBSEQ = CLlABS.TSUBSEQ + 2

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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