?
Solved

Complicated CASE statement... is this possible?

Posted on 2006-10-30
6
Medium Priority
?
267 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
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 2000 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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

800 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