SELECT DISTINCT 'DROP TABLE DB1.' || TABLENAME || ' ;
' !! case when row_number() over (order by tablename) mod 10 = 0 then '.hang 50;' else '' end
FROM dbc.tables WHERE tablekind = 'T' AND databasename = 'DB1'
AND TRIM(tablename) LIKE any
AND CAST(CreateTimeStamp AS DATE) < add_months(date - extract(day from date) + 1, -1)
Can you please explain a bit? I am not aware of this.Thanks.
hm I mixed the 10 and 50...
check also dieters posts in
if you have trouble with the newline in the export...
Use the query provided by Ulrich. It will give you the desired result.
Thanks a lot Ulrich and Waq. That query gave me the desired output perfectly. Thanks again.
I have used this query to add ".HANG10" after every 25 statements. But, when I executed this query using BTEQ it is not processing ".HANG10" command with the DROP statement. I think we can not use DROP statement and .HANG in single line..because when I manually moved the .HANG10 command in the new line it went fine.
Can you please let me know how to do this because the above query will add .HANG 10 immediately after the DROP Command.