Script Help ideas

[ivandrago]

Hi,
I have the below query in sql server 2005, how would I create a qvd file for this as not sure how to handle inline views?

select
tablea.appointmentNo,
tablea.son,
tablea.PersonId,
case when app.callno is null then 0 else 1 end as LastCall,
cus.queue

from
tablea

left outer join (select son, max(appointmentNo) as lastAppointmentNo from tableb group by son) app
on tablea.son = app.son
and tablea.appointmentNo = app.lastAppointmentNo

left outer join (select son, queue, FixFromOpen from tablec union select son, queue,FixFromOpen from tabled
where not exists (select * from TableD z where z.callNo = tablec.CallNo)) cus
on tablea.son = cus.son

where
tablea.status='Live'

Thanks

[ivandrago]

Any experts out there? That can tell me if this can be done or not?

[#3]

Hi Ivan,

Perhaps I'm misunderstanding, but if you have the SQL query why don't you do one of these:

1) define it as a view in SQL and pull data from the view

2) inside your LOAD statement, just SQL SELECT the whole query you've just posted here

either of these will load the data from the query you've provided.

[ivandrago]

Hi, I want to avoid creating a view in sql server, how would you do 2) can you provide a starting example?

Thanks

[#5 · Accepted solution]

Sure. My Semi-coloning might not be totally accurate. I'm writing this on the fly.

[My Table Name]:
LOAD
*;
SQL SELECT
tablea.appointmentNo,
 tablea.son,
 tablea.PersonId,
 case when app.callno is null then 0 else 1 end as LastCall,
 cus.queue

from
 tablea

 left outer join (select son, max(appointmentNo) as lastAppointmentNo from tableb group by son) app
 on tablea.son = app.son
 and tablea.appointmentNo = app.lastAppointmentNo

 left outer join (select son, queue, FixFromOpen from tablec union select son, queue,FixFromOpen from tabled
 where not exists (select * from TableD z where z.callNo = tablec.CallNo)) cus
 on tablea.son = cus.son


where
 tablea.status='Live';

[ivandrago]

Thanks Fry!