| View previous topic :: View next topic |
| Author |
Message |
cappertan
Beginner
Joined: 20 May 2003
Posts: 15
Topics: 10
|
 Posted: Thu Jan 31, 2008 12:30 pm Post subject: Position and substring query Problem\Question |
 |
|
Hi:
The following query below works but I am stuck as how to get the final results, if possible. If I have found 'LEFT VENTRICLE' then I want to get the percentage '40-45%'. I need to have the percentage as a column by itself. Percentage could be '40% (individual number).
I am confused. 
I have checked other sources but manuals to not give many example of combination examples. I am using this on Teradata DBMS but I would think the SQL would work on DB2 as well.
Thanks
Capp
| Code: |
SELECT line,
SUBSTRING (an.note_text from position('LEFT VENTRICLE' in an.note_text) for 14) as "LV",
SUBSTRING (an.note_text from position('Estimated ejection fraction is ' in an.note_text) for 37) as "EEF"
from ambulatory_notes an
where an.note_csn_id = 'XXXXX'
and (LV like '%LEFT VENTRICLE%' or EEF like '%Estimated ejection fraction is %')
order by line
|
VARCHAR Data Text 1 line=254 varchar
1 LEFT VENTRICLE: Normal in size. Diastolic internal dimension is 5.2 cm.
2 Estimated ejection fraction is 40-45%. Age
3 appropriate mitral inflow pattern, tissue doppler suggests borderline
4 elevated left atrial pressures.
_________________
Capp |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Thu Jan 31, 2008 1:07 pm Post subject: |
|
|
I am assuming that your output from the query is as follows
| Code: |
line LV Eff
===== ============= =============
linedata xxxx LEFT VENTRICLE
linedata yyyy Estimated ejection fraction
|
Do you want something like this ? when you found left ventricle then you want a percentage next it?
| Code: |
line LV % Eff %
===== ============= === ============= ===
linedata xxxx LEFT VENTRICLE 40 0
linedata yyyy 0 Estimated ejection fraction 58
|
If that is not what you wanted then show as sample like I showed
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
cappertan
Beginner
Joined: 20 May 2003
Posts: 15
Topics: 10
|
| Posted: Thu Jan 31, 2008 2:52 pm Post subject: |
|
|
Kolusu:
Yes. Your output sample is correct. That is what I want.
Thanks for quick reponse to my post.
Capp
_________________
Capp |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Thu Jan 31, 2008 3:45 pm Post subject: |
|
|
cappertan,
Try this
| Code: |
SELECT line,
SUBSTRING (an.note_text from position('LEFT VENTRICLE' in an.note_text) for 14) as "LV",
CASE SUBSTRING (an.note_text from position('LEFT VENTRICLE' in an.note_text) for 14)
WHEN 'LEFT VENTRICLE' THEN CHAR('40%')
ELSE CHAR('00%') END AS LV_PERCENT
SUBSTRING (an.note_text from position('Estimated ejection fraction is ' in an.note_text) for 37) as "EEF"
CASE SUBSTRING (an.note_text from position('LEFT VENTRICLE' in an.note_text) for 14)
WHEN 'Estimated ejection fraction is ' THEN CHAR('58%')
ELSE CHAR('00%') END AS LV_PERCENT
from ambulatory_notes an
where an.note_csn_id = 'XXXXX'
and (LV like '%LEFT VENTRICLE%' or
EEF like '%Estimated ejection fraction is %')
order by line
; |
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
