Adding subject (long text field) to temp schema output of a query | Community
Skip to main content
davidh2892249
davidh2892249
Level 5
March 15, 2019
Solved

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

  • March 15, 2019
  • 5 replies
  • 6858 views

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

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by Jonathon_wodnicki

    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

    5 replies

    Jonathon_wodnicki
    Community Advisor
    Jonathon_wodnickiCommunity Advisor
    Community Advisor
    March 18, 2019

    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

      davidh2892249
      davidh2892249Author
      Level 5
      March 19, 2019

      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

      Jonathon_wodnicki
      Community Advisor
      Jonathon_wodnickiCommunity AdvisorAccepted solution
      Community Advisor
      March 19, 2019

      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

        davidh2892249
        davidh2892249Author
        Level 5
        March 28, 2019

        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

        Jonathon_wodnicki
        Community Advisor
        Jonathon_wodnickiCommunity Advisor
        Community Advisor
        April 1, 2019

        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.

          Terms & ConditionsAccessibility statement

          Loading footer...