netezza: Unable to identify a function

Question

I have a requirement to extract data from Netezza DB into a text file. In this process I need to add additional \ before \ or " that exists in data. when I try to use the below mentioned sql query in it is working.

select guest_key, cast(replace_nvarchar(replace_nvarchar(guest_last_nm,'\','\\'),'"','\"') as nchar(100)) as guest_last_nm from admin.cdr_mrdw_dim_messaging_ota limit 100;

but when I use the same query in shell script it says function does not exists.

nzsql -d $NZ_DATABASE -F $'\t' -A -c "select guest_key, cast(replace_nvarchar(replace_nvarchar(guest_last_nm,'\','\\'),'"','\"') as nchar(100)) as guest_last_nm from admin.cdr_mrdw_dim_messaging_ota limit 100;" > cdr_mrdw_dim_messaging_ota.txt

Error: Function 'replace(varchar, unknown)' does not exist. Unable to identify a function that satisfies the given argument types. You may need to add explicit typecasts```

tried translate function too. Same error has been shown. What is this error and how to resolve this??

Thanks.

Answer 1

You dont have to do something quite that complicated: there is a build in functionality to create a csv file right on your PC... an example:

create external table 'C:\\temp\\SomeTable.csv'
                    USING
                                (           REMOTESOURCE 'ODBC'
                                            ENCODING 'internal'
                                            DELIMITER '\t'
                                            EscapeChar '\'
                                            NullValue '*'
                                --          CtrlChars TRUE
                                --          LFinString TRUE
                                --          CRinString TRUE
                                            LogDir 'C:\\temp\\')
as
select *
from 
SomeTable;

I have commented out a few options you may or may not need. Encoding=internal seems to indicate Unicode and works nicely for me :)

You can read more here: https://www.ibm.com/support/knowledgecenter/SSULQD_7.2.1/com.ibm.nz.load.doc/c_load_external_tables.html