|
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--';
|
