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
Solved

Complicated CASE statement... is this possible?

Posted on 2006-10-30
6
264 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

861 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