Announcement

Collapse
No announcement yet.

Problem Nitroquery when using SQL statement with UDF

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Problem Nitroquery when using SQL statement with UDF

    I have a valid sql statement that is running perfectly well in interactive Sql. and in the Sql Script (based on JDBC)

    when trying this statement in Nitro query.. i get the message that the datasource is valid, and i don't get any error, but in the preview nothing is displayed.
    I assume that the problem lies with the usage of user defined function. (when i leave those out, it works)

    Is there any changes this functionality will be added as well?..
    thx

    Thierry


    SELECT
    substr(reknde, 1, 5) AS levnr,
    dlcdde AS act_cod, omscde, adrsde, postde, gemtde, lndkde, copgm.puud03r(reknde, 'F') AS contact,
    idate(crdtde, '*YMD') AS crea_dat, idate(lwdtde, '*YMD') AS chg_dat,
    dlcdlv AS act_cd2,
    lvvwlv AS lever_vw, dsbllv AS kompass, mtkdde AS munt, bkonde AS betalingskond, bterde AS betalingsterm, blcdde AS betalingsvanaf,
    rrn(obderd) AS rrn1, rrn(v1lvhd) AS rrn2
    FROM
    pudat/obderd LEFT OUTER JOIN pudat/v1lvhd ON bedrde = bedrlv
    and kdklde = kdkllv and reknde = reknlv
    WHERE
    bedrde = 1 and dlcdde not in ('S', 'R', 'T') and ( kscdlv between '10 ' and '159999' or kscdlv between '30 ' and '339999' )
    ORDER BY levnr


  • #2
    Theirry,

    I see that rrn's are in the result list so this was parsed and regenerated from Nitro Query App Builder. Did you try taking that exact statement and running it in interactive SQL/SQL Script? Also, just to to make sure I understand if you remove the "contact" from the results it works? "copgm.puud03r(reknde, 'F') AS contact"

    Code:
    SELECT
      substr(reknde, 1, 5) AS levnr,
      dlcdde AS act_cod,
      omscde,
      adrsde,
      postde,
      gemtde,
      lndkde,
      copgm.puud03r(reknde, 'F') AS contact,
      idate(crdtde, '*YMD') AS crea_dat,
      idate(lwdtde, '*YMD') AS chg_dat,
      dlcdlv AS act_cd2,
      lvvwlv AS lever_vw,
      dsbllv AS kompass,
      mtkdde AS munt,
      bkonde AS betalingskond,
      bterde AS betalingsterm,
      blcdde AS betalingsvanaf,
      rrn(obderd) AS rrn1,
      rrn(v1lvhd) AS rrn2
    FROM
      pudat/obderd
      LEFT OUTER JOIN pudat/v1lvhd ON bedrde = bedrlv
      and kdklde = kdkllv
      and reknde = reknlv
    WHERE
      bedrde = 1
      and dlcdde not in ('S', 'R', 'T')
      and (
        kscdlv between '10    '
        and '159999'
        or kscdlv between '30    '
        and '339999'
      )
    ORDER BY
      levnr
    Thanks

    Comment


    • #3
      Just wanted to place an update on this. Nitro Query App Builder handles SQL user defined functions, just was a library list issue that was easily fixed by adding the required libraries to the environment.

      Comment

      Working...
      X