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.

Does this answer your question? [Replacing a string using SQL Server Replace function - string has more than 4000 characters - what to do?](https://stackoverflow.com/questions/5245721/replacing-a-string-using-sql-server-replace-function-string-has-more-than-4000) — June7, Dec 18 '20 at 20:13
Documentation https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.1/com.ibm.nz.sqltk.doc/r_sqlext_replace.html — June7, Dec 18 '20 at 20:17
To include the query as a quoted text string passed via the shell command line, you need to observe proper shell escape syntax for any embedded quotes that match the surrounding quotes (and the backslashes, assuming backslash is your escape character). — Fred, Dec 18 '20 at 20:27

score 0 · Answer 1 · answered Dec 19 '20 at 16:24

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

I need to extract data as text file in a remote SFTP location. I'm using nzsql to do that. How can I integrate your solution into my process? I don't have access to any other platforms. This is organization data, so I'm not allowed to extract data into local system. — naga satish, Dec 19 '20 at 16:33
Can you get SSH access to the Netezza host? Alternatively: is your client a Linux system? FIFO file and piping is your friend :) — Lars G Olsen, Dec 19 '20 at 20:30
Furthermore: the ‘not allowed to extract to local’ makes little sense with the example code you posted.... the file cdr_mrdw_dim_messaging_ota.txt seems pretty local to me :) — Lars G Olsen, Dec 19 '20 at 22:02

netezza: Unable to identify a function

1 Answers1