Archives of the TeradataForum

Message Posted: Fri, 08 Oct 2010 @ 16:17:28 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

You can use bteq to dynamically write and run a bteq script that will put the output into multiple files. Off the top of my head and guaranteed not to work as written, something like

     .export file=runme.btq

     select
     case
     when day_of_calendar = 1 then '.export file=' || UnitSernum ||
     cast(eventdate as char(19)) || '.txt'
     when day_of_calendar = 2 then
     'Select *
     From Data as d, Fault as f
     Where d.UnitSernum =  ' || UnitSernum  || '
     and f.eventdate = ' || EventDate || '   [!!! definitely have to play w/
     this to get it right]
     And f.UnitSernum = d.UnitSernum
     And d.timestamp <= f.eventdate
     And d.timestamp >= (f.eventdate - 6 months); '
     when day_of_calendar = 3 then '.export reset'
     end (title '')
     from fault
     inner join sys_calendar.calendar
     on day_of_calendar < 4
     order by UnitSernum, EventDate, day_of_calendar;

     .export reset
     .run file=runme.btq

Basically, you write a query that outputs the text of the query you want to run, and use sys_calendar.day_of_calendar to sandwich it between bteq commands that send the output to a new file each time.

I'm next to useless with bteq, so there's probably some line length settings that you'll have to mess with to make sure your final files aren't truncated, and you'll have to mess around with casting/formatting the timestamp in the part that builds the query, but the overall approach should work.

Dave C

	<Prev	Next>		<<First	<Prev	Next>	Last>>