Adding subject (long text field) to temp schema output of a query

[#1]

Hi all,

I'm trying to build a worktable/temp schema from a workflow query (targeting dimension = nms:delivery) where I include the subject line of each delivery to the temp schema/work table.

I've reviewed the nms:delivery schema and located the xpath of the subject field as mailParameters/subject (and can see the data type is CDATA).

So in the output columns of my query activity (as well as others), i have added the following expression.
[mailParameters/subject]

This produces the following error:

03/15/2019 9:36:46 AM query PGS-220000 PostgreSQL error: ERROR:  function unescapexmlvalue(character varying) does not exist LINE 1: ...) SELECT  DISTINCT  D0.iDeliveryId, D0.tsContact, UnescapeXm...                                                              ^ HINT:  No function

03/15/2019 9:36:46 AM query matches the given name and argument types. You might need to add explicit type casts.

03/15/2019 9:36:46 AM query WDB-200001 SQL statement 'INSERT INTO wkf41455034_8_1 (iDeliveryId,tsContact,mSubject,iId) SELECT  DISTINCT  D0.iDeliveryId, D0.tsContact, UnescapeXmlValue((XPath(E'/delivery/mailParameters/subject', D0.mData::XML))[1]::varchar), D0.iDeliveryId FROM NmsD

03/15/2019 9:36:46 AM query elivery D0 WHERE ((D0.iState = 95) AND (D0.tsContact >= TIMESTAMP WITH TIME ZONE '2019-03-01 00:00:00.000Z')) AND ((D0.iDeliveryId > 0 OR D0.iDeliveryId < 0) AND ((D0.iDeleteStatus = 0)))' could not be executed.

Can anyone advise how long text fields can be added to temp schemas/worktables?

Thanks

David

[#2 · Accepted solution]

Hi,

Maybe it's your build, this was on an 8981 server. No reference to UnescapeXmlValue in the neolane dir either.

Just stub it?

create or replace function UnescapeXmlValue(t text) returns text as $$
begin
  return t;
end;

Thanks,

-Jon

[#3]

That was the stub, just outputs its input. Run it in a sql script to add it to the db- after that your code should work fine, assuming you didn't need to unescape any xml values. NB code is untested, it was written directly in comment box here.

[#4]

Hi,

The workflow is error'ing on missing unescapexmlvalue() function. I just tried same on a postgres instance and it worked fine, query activity didn't add that function call though:

SELECT  DISTINCT  (XPath(E'/delivery/mailParameters/subject', D0.mData::XML))[1]::varchar

Thanks,

-Jon

[#5]

Hi Jon,

Apologies, again you'd have to forgive me, What does "stub it" mean 🙂 ...

Where would you implement the code you've provided? In a workflow? What would be the learning or output?

Could you share a step by step example?

Would really appreciate the help.

Thanks

David

[#6]

Hi Jon,

Thanks for looking into it and coming back to me.

I saw that error in the log, but forgive me I'm not a SQL developer.

So i know we use a postgres db.

In the underlying SQL select that the query activity generates, it's trying to use a function that isn't native?

And you created a like for like example your side and it ran ok? (but didn't add the function in the underlying SQL select?)

If I've understood this all correctly, any ideas on how to remedy or why our instances are behaving differently?

Thanks, appreciate the support.

Dave