February 8, 2009 at 12:13 pm
I have the following code that pulls out records where I have manually defined a filter with a HAVING...IN statement.
select a_abpm40.PAT_NR, a_pat.pat_id, a_pat.op_datum, count(a_abpm40.ABPM) as CountsBelow40, count(a_abpm40.ABPM)/6.001 as TimeBelow40, po_perf, po_timer6, ((count(a_abpm40.abpm)/6.001)/po_perf)*100 as PercentTimeBelow40
from a_abpm40 inner join a_pat on a_abpm40.pat_nr=a_pat.pat_nr
where po_timer6 = 0 and po_perf > 5
group by a_abpm40.PAT_NR, a_pat.pat_id, a_pat.op_datum, po_perf, po_timer6
HAVING a_pat.pat_id IN (8839140,8805947,8968218)
order by 1
Rather than me manually enter the values in the HAVING...IN line I'd like to use data in a csv file as the real life list will be more than three in this test.
I have found a way to read a csv file thus:
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=C:\;','select * from numbers.csv')
What I'm struggling to do is put these two pices of code together so I can use a csv file. Separately the work fine, together I falter. I've tried using a variable and assign the select...csv code to that variable but I'm having no joy, I guess that's the newbie in me.
I'd appreciate any pointers on how to make it work or where to look for help.
Thanks, Ian.
February 8, 2009 at 12:31 pm
You should be able to do something like:
select a_abpm40.PAT_NR, a_pat.pat_id, a_pat.op_datum, count(a_abpm40.ABPM) as CountsBelow40, count(a_abpm40.ABPM)/6.001 as TimeBelow40, po_perf, po_timer6, ((count(a_abpm40.abpm)/6.001)/po_perf)*100 as PercentTimeBelow40
from a_abpm40 inner join a_pat on a_abpm40.pat_nr=a_pat.pat_nr
where po_timer6 = 0 and po_perf > 5
group by a_abpm40.PAT_NR, a_pat.pat_id, a_pat.op_datum, po_perf, po_timer6
HAVING a_pat.pat_id IN
(select colno from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=C:\;','select * from numbers.csv') )
order by 1
Alternately, you can use dynamic sql and create your command with the values from your text file added in at the right spot.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
February 8, 2009 at 12:57 pm
Wow, easy when you know how. My mistake was using '*' in the SELECT rather specifying the column name in the csv file, I'm learning all the time.
Thanks so much for your rapid reply. Ian
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply