Solved

Complicated CASE statement... is this possible?

Posted on 2006-10-30
6
262 Views
Last Modified: 2008-02-01
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
Comment
Question by:SOMCEE
6 Comments
 
LVL 11

Expert Comment

by:regbes
ID: 17836371
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
 
LVL 10

Expert Comment

by:AaronAbend
ID: 17836423
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
 
LVL 42

Expert Comment

by:dqmq
ID: 17836522
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 42

Expert Comment

by:dqmq
ID: 17836547
Drat, just noticed typo. Should have used a comma, not a period on the FROM clause
0
 

Author Comment

by:SOMCEE
ID: 17836720
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
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 17838360
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

863 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

24 Experts available now in Live!

Get 1:1 Help Now