Solved

Complicated CASE statement... is this possible?

Posted on 2006-10-30
6
266 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
[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
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

726 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