I am trying to generate sql script to be read later on another db. begin declare v_dncode varchar(20); declare v_select_stmt varchar(200); declare v_unload_stmt varchar(200); select distinct docnumcode into v_dncode from document ; set v_select_stmt = 'select * from document where docnumcode = ''' + v_dncode + ''' and docnr > 100;'; set v_unload_stmt = 'unload select ''' + v_select_stmt + ''' from dummy to ''c:\temp\sql_statement.sql'''; execute immediate string (v_unload_stmt); end The result which I get in sql_statement.sql is: 'select * from document where docnumcode = ''111'' and docnr > 100;' Question: how can I get the result as: select * from document where docnumcode = '111' and docnr > 100; i.e. removing the first and last quotes and also remove one of the double quotes around 111. whereas 111 is the result of: select distinct docnumcode into v_dncode from document ; and it is of type varchar(10); Thanks in advance |
Add the QUOTES OFF clause: http://dcx.sap.com/index.html#sqla170/en/html/817fd0fd6ce21014a58ff727fbb7b4e2.html*loio817fd0fd6ce21014a58ff727fbb7b4e2 Also, for what you are doing, you could try xp_write_file. http://dcx.sap.com/index.html#sqla170/en/html/8180f5b16ce21014934ea5a5d5c8efba.html*loio8180f5b16ce21014934ea5a5d5c8efba Thanks for the reply, the second comments looks very useful, and hope that it will work for version 10. Will test it and write back
(23 Jun '17, 11:43)
Baron
thank you very much. xp_write_file is exactly what I needed.
(23 Jun '17, 19:12)
Baron
could you please tell me if there is possibility to append text using xp_write_file?
(26 Jun '17, 03:48)
Baron
Replies hidden
1
xp_write_file isn't designed for that. But you could use xp_read_file to read the contents of that file into a variable, add the contents you want to append to it and write it back with xp_write_file.
(26 Jun '17, 04:24)
Reimer Pods
1
As Reimer says, xp_write_file doesn't support appending, see http://dcx.sap.com/index.html#sqla170/en/html/8180f5b16ce21014934ea5a5d5c8efba.html*loio8180f5b16ce21014934ea5a5d5c8efba UNLOAD might still work for you. You could use QUOTES OFF ESCAPES OFF APPEND ON. You might also need ROW DELIMITED BY '' since I don't recall if we add a trailing delimiter. If the data is binary, you should use HEXADECIMAL OFF too.
(26 Jun '17, 05:50)
John Smirnios
|
In addition to John's reply and sundry comments, here is one of my favorite templates, named "load_unload_raw_text.sql"... --------------------------------------------------------------------- -- LOAD UNLOAD cr-lf delimited text. BEGIN DROP TABLE raw_text; EXCEPTION WHEN OTHERS THEN END; /* CREATE LOCAL TEMPORARY TABLE raw_text ( line_number BIGINT NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY CLUSTERED, line_text LONG VARCHAR NOT NULL DEFAULT '' ) NOT TRANSACTIONAL; */ CREATE TABLE raw_text ( line_number BIGINT NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY CLUSTERED, line_text LONG VARCHAR NOT NULL DEFAULT '' ); LOAD TABLE raw_text ( line_text ) FROM 'c:/temp/xxx.txt' DEFAULTS ON DELIMITED BY '' ESCAPES OFF HEXADECIMAL OFF QUOTES OFF ROW DELIMITED BY '\x0d\x0a' -- preserve empty lines STRIP OFF; SELECT * FROM raw_text ORDER BY raw_text.line_number; UNLOAD SELECT line_text FROM raw_text ORDER BY line_number TO 'c:/temp/yyy.txt' DELIMITED BY '' ESCAPES OFF HEXADECIMAL OFF QUOTES OFF; -- similar use of OUTPUT... OUTPUT TO 'temp_generated_alter_autoincrement.txt' DELIMITED BY '' ESCAPES OFF HEXADECIMAL OFF QUOTE ''; --------------------------------------------------------------------- -- LOAD UNLOAD operations captured by DBTRAN BEGIN DROP TABLE raw_dbtran_text; EXCEPTION WHEN OTHERS THEN END; CREATE TABLE raw_dbtran_text ( operation_number BIGINT NOT NULL DEFAULT AUTOINCREMENT, operation_text LONG VARCHAR NOT NULL DEFAULT '', PRIMARY KEY ( operation_number ) ); LOAD TABLE raw_dbtran_text ( operation_text ) FROM 'C:/projects/ASA9_templates/run/dbtran/dbtran_ddd.sql' DEFAULTS ON DELIMITED BY '' ESCAPES OFF HEXADECIMAL OFF QUOTES OFF ROW DELIMITED BY '\x0d\x0a--' STRIP OFF; SELECT * FROM raw_dbtran_text ORDER BY operation_number; -- Note: UNLOAD writes an extra '\x0d\x0a--' to the end of the output file. -- Otherwise the output file is identical to the input file. UNLOAD SELECT operation_text FROM raw_dbtran_text ORDER BY operation_number TO 'C:/projects/ASA9_templates/run/dbtran/dbtran_ddd2.sql' DELIMITED BY '' ESCAPES OFF HEXADECIMAL OFF QUOTES OFF ROW DELIMITED BY '\x0d\x0a--'; |