topic Re: Qlikview - SQL replace statement error in QlikView

Qlikview - SQL replace statement error

loke2014 — Mon, 07 Dec 2015 15:35:15 GMT

Hi,

In SQL 2012, I'm using the replace query as shown below with no problem. However, when I used in the script editor, it returned an error in Qlikview.

select

t.tpc_num,

replace(t.tpc_subject, ',',' '),

t.create_time,

t.closed_time,

s.status_name,

m.mpt_num,

c.item_type

FROM tpc t WITH(NOLOCK);

Please assist.

Thanks.

Re: Qlikview - SQL replace statement error

m_woolf — Mon, 07 Dec 2015 18:43:37 GMT

Try loading the t.tpc_Subject field as is and then do the replace in a preceding load.

Re: Qlikview - SQL replace statement error

Kushal_Chawda — Mon, 07 Dec 2015 18:55:33 GMT

may be like this

select

t.tpc_num,

replace(t.tpc_subject, ',',' ') as tpc_subject,

t.create_time,

t.closed_time,

s.status_name,

m.mpt_num,

c.item_type

FROM tpc t WITH(NOLOCK);

Re: Qlikview - SQL replace statement error

maxgro — Mon, 07 Dec 2015 19:18:04 GMT

Are you sure the problem is in replace function?

I just tried with a replace as your and I didn't get any error.

Also I've seen you have some other table alias in your code (bold) s, m, c but the table are missing.

select

t.tpc_num,

replace(t.tpc_subject, ',',' '),

t.create_time,

t.closed_time,

s.status_name,

m.mpt_num,

c.item_type

FROM tpc t WITH(NOLOCK);

Re: Qlikview - SQL replace statement error

Peter_Cammaert — Mon, 07 Dec 2015 20:04:32 GMT

It's always great to get hold of the actual error message text.

BTW the QlikView scripting engine puts everything between (SQL) SELECT and the semicolon as-is in a packet buffer and sends it unparsed to the Relational Database. It would be weird for QlikView instead of the RDBMS parser to return an error for bad SQL syntax...

Re: Qlikview - SQL replace statement error

loke2014 — Tue, 08 Dec 2015 01:52:47 GMT

Hi,

Thanks for all the feedback. Kush141087 gave me the correct working solution.