Using .HANG command with DROP statement in the same line is not working

Database
Enthusiast

Using .HANG command with DROP statement in the same line is not working

Hi Ulrich/Dieter,

I am using the below query to create DROP statement and HANG statement after every 25 DROP statements.

SELECT DISTINCT 'DROP TABLE DB.' || TABLENAME || ' ;'   

|| CASE WHEN ROW_NUMBER() OVER (ORDER BY TABLENAME) MOD 25 = 0

THEN '.HANG 10;' ELSE ' ' END                     

FROM DBC.TABLES     

Issues:

1) The file is getting special chars at first two bytes before every DROP Statement.  Since, the DROP Statement was not working I added a SORT Statement to remove first two bytes. Is there any other way to do this?

2) HANG statement is coming after every 25 statements but in the same line and its not getting executed because in the same line DROP and HANG is not working ( I tried with separate lines and it worked.). How to add this HANG command in New Line? I tried "THEN '0D0A'XCF|| '.HANG 10;'" but this also adding two special chars before .HANG. How to remove that?

Please advice.

Thanks,

Terankit                                  

3 REPLIES
Enthusiast

Re: Using .HANG command with DROP statement in the same line is not working

I have tried ".RECORDMODE OFF

.FOLDLINE ALL " also but then I am getting my query written in the file as well with the page numbers.

Terankit

Enthusiast

Re: Using .HANG command with DROP statement in the same line is not working

Hi dnoeth,

please help as it is not working at the end time of my request.thanks in advance.

Terankit

Senior Apprentice

Re: Using .HANG command with DROP statement in the same line is not working

Those options should work:

.export data file = xxx

.recordmode off

.foldline all

And split the string in two columns:

SELECT

   'DROP TABLE DB.' || TABLENAME || ' ;',   

   CASE WHEN ROW_NUMBER() OVER (ORDER BY TABLENAME) MOD 25 = 0

   THEN '.HANG 10;' ELSE ' ' END                     

FROM DBC.TABLES

Dieter