Problem Nitroquery when using SQL statement with UDF

[ThierryC]

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

[Johnny Major]

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"

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

[Johnny Major]

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.