Getting Sql error on Data source

[ThierryC]

Image 006.png
i'm running a (quite complex) query that i have prepared on Dbeaver, and runs there without ant issues..
also tried it with interactive sql - and no problems


when asking the preview in the datasource i get the following error as in picture


The sql i'm executing is the following

WITH WHAF (wcsite, wnartk, wnklnt, wDlevr, wqafrp) as
(SELECT 'TLT', afnartk, afnklnt,
last_day(afdlevr),
sum(afqafrp)
FROM codat/cohafpf
WHERE afdlevr>='2020-01-01'
GROUP BY afnartk, afnklnt, Last_day(afdlevr)
UNION
SELECT 'BUD', afnartk, afnklnt,
last_day(afdlevr),
sum(afqafrp)
FROM codathu/cohafpf
WHERE afdlevr>='2020-01-01'
GROUP BY afnartk, afnklnt, Last_day(afdlevr)),
WBTG (w2csite, w2ntool, w2qvcys ) as
(SELECT 'TLT', vwntool, max(vwqvcys)
FROM codat/COBTGV1
GROUP BY vwntool
UNION
SELECT 'BUD', vwntool, max(vwqvcys)
FROM codathu/COBTGV1
GROUP BY vwntool
)
SELECT wcsite,
cxbrcm CONCAT ' ' CONCAT Substr(BUDG.ptxpraw, 1, 30) as Budgetkl,
wnklnt, cxcunm,
wDlevr,
wnartk, CASE WHEN arxarkl IS NULL THEN arvlab ELSE arxarkl END AS Description,
CASE WHEN arxaprt IS NULL THEN 'N/A' ELSE arxaprt END AS pARTNBR,
CASE WHEN arntool IS NULL THEN 0 else arntool END AS TOOL,
CASE WHEN tlxtool IS NULL THEN 'N/A' ELSE tlxtool END TOOLTEXT,
wqafrp,
coud04rsit('TLT', wnklnt, wnartk, wdlevr, date('0001-01-01'), date('0001-01-01'), 0, 0) AS Price,
CASE WHEN w2qvcys IS NULL THEN 0 ELSE w2qvcys END AS MAx_CycleTime
FROM whaf LEFT OUTER JOIN codat/cobarpf ON
arnartk = wnartk
LEFT OUTER JOIN pudat/OBABAR ON
bedrab=1 AND arnrab = char(wnartk)
LEFT OUTER JOIN codat/cxx ON
cxcuno = wnklnt
LEFT OUTER JOIN cobtlpf ON
TLNTOOL= arntool
Left outer join codat/cotptpf BUDG on
Budg.ptcparm = 'BUDGETKL' and Budg.ptcprsl = digits(cxbrcm)
LEFT OUTER JOIN WBTG ON
w2csite='TLT' AND w2ntool=arntool
WHERE wcsite='TLT'
UNION
SELECT wcsite,
cxbrcm CONCAT ' ' CONCAT Substr(BUDG.ptxpraw, 1, 30) as Budgetkl,
wnklnt, cxcunm,
wDlevr,
wnartk, CASE WHEN arxarkl IS NULL THEN arvlab ELSE arxarkl END AS Description,
CASE WHEN arxaprt IS NULL THEN 'N/A' ELSE arxaprt END AS pARTNBR,
CASE WHEN arntool IS NULL THEN 0 else arntool END AS TOOL,
CASE WHEN tlxtool IS NULL THEN 'N/A' ELSE tlxtool END TOOLTEXT,
wqafrp,
coud04rsit('BUD', wnklnt, wnartk, wdlevr, date('0001-01-01'), date('0001-01-01'), 0, 0) AS Price,
CASE WHEN w2qvcys IS NULL THEN 0 ELSE w2qvcys END AS MAx_CycleTime
FROM whaf LEFT OUTER JOIN codathu/cobarpf ON
arnartk = wnartk
LEFT OUTER JOIN pudat.OBABAR ON
bedrab=1 AND arnrab = char(wnartk)
LEFT OUTER JOIN codathu.cxx ON
cxcuno = wnklnt
LEFT OUTER JOIN cobtlpf ON
TLNTOOL= arntool
Left outer join codathu/cotptpf BUDG on
Budg.ptcparm = 'BUDGETKL' and Budg.ptcprsl = digits(cxbrcm)
LEFT OUTER JOIN WBTG ON
w2csite='BUD' AND w2ntool=arntool
WHERE wcsite='BUD'
ORDER BY 1, 2, 3, 5, 4 

[Johnny Major]

The NAB SQL data source does not allow compounds/unions in the main statement, so you need to move them within the WITH clause.

Below is an example; however, we are unable to test this since we do not have access to your tables, etc.

WITH whaf(wcsite, wnartk, wnklnt, wdlevr, wqafrp) AS (
  (
    SELECT
      'TLT',
      afnartk,
      afnklnt,
      last_day(afdlevr),
      sum(afqafrp)
    FROM
      codat/cohafpf
    WHERE
      (afdlevr >= '2020-01-01')
    GROUP BY
      afnartk,
      afnklnt,
      last_day(afdlevr)
  )
  UNION
  SELECT
    'BUD',
    afnartk,
    afnklnt,
    last_day(afdlevr),
    sum(afqafrp)
  FROM
    codathu/cohafpf
  WHERE
    (afdlevr >= '2020-01-01')
  GROUP BY
    afnartk,
    afnklnt,
    last_day(afdlevr)
),
wbtg(w2csite, w2ntool, w2qvcys) AS (
  (
    SELECT
      'TLT',
      vwntool,
      max(vwqvcys)
    FROM
      codat/cobtgv1
    GROUP BY
      vwntool
  )
  UNION
  SELECT
    'BUD',
    vwntool,
    max(vwqvcys)
  FROM
    codathu/cobtgv1
  GROUP BY
    vwntool
),
tempt AS (
  (
    SELECT
      wcsite,
      cxbrcm concat ' ' concat substr(budg.ptxpraw, 1, 30) AS budgetkl,
      wnklnt,
      cxcunm,
      wdlevr,
      wnartk,
    CASE
        WHEN arxarkl is NULL
          THEN arvlab
        ELSE arxarkl
  END AS description,
CASE
    WHEN arxaprt is NULL
      THEN 'N/A'
    ELSE arxaprt
END AS partnbr,
CASE
    WHEN arntool is NULL
      THEN 0
    ELSE arntool
END AS tool,
CASE
    WHEN tlxtool is NULL
      THEN 'N/A'
    ELSE tlxtool
END AS tooltext,
wqafrp,
coud04rsit(
  'TLT',
  wnklnt,
  wnartk,
  wdlevr,
  date('0001-01-01'),
  date('0001-01-01'),
  0,
  0
) AS price,
CASE
    WHEN w2qvcys is NULL
      THEN 0
    ELSE w2qvcys
END AS max_cycletime
FROM
  whaf
  LEFT OUTER JOIN codat/cobarpf ON arnartk = wnartk
  LEFT OUTER JOIN pudat/obabar ON bedrab = 1 and arnrab = char(wnartk)
  LEFT OUTER JOIN codat/cxx ON cxcuno = wnklnt
  LEFT OUTER JOIN cobtlpf ON tlntool = arntool
  LEFT OUTER JOIN codat/cotptpf AS budg ON budg.ptcparm = 'BUDGETKL' and budg.ptcprsl = digits(cxbrcm)
  LEFT OUTER JOIN wbtg ON w2csite = 'TLT' and w2ntool = arntool
WHERE
  (wcsite = 'TLT')
)
UNION
SELECT
  wcsite,
  cxbrcm concat ' ' concat substr(budg.ptxpraw, 1, 30) AS budgetkl,
  wnklnt,
  cxcunm,
  wdlevr,
  wnartk,
CASE
    WHEN arxarkl is NULL
      THEN arvlab
    ELSE arxarkl
END AS description,
CASE
    WHEN arxaprt is NULL
      THEN 'N/A'
    ELSE arxaprt
END AS partnbr,
CASE
    WHEN arntool is NULL
      THEN 0
    ELSE arntool
END AS tool,
CASE
    WHEN tlxtool is NULL
      THEN 'N/A'
    ELSE tlxtool
END AS tooltext,
wqafrp,
coud04rsit(
  'BUD',
  wnklnt,
  wnartk,
  wdlevr,
  date('0001-01-01'),
  date('0001-01-01'),
  0,
  0
) AS price,
CASE
    WHEN w2qvcys is NULL
      THEN 0
    ELSE w2qvcys
END AS max_cycletime
FROM
  whaf
  LEFT OUTER JOIN codathu/cobarpf ON arnartk = wnartk
  LEFT OUTER JOIN pudat.obabar ON bedrab = 1 and arnrab = char(wnartk)
  LEFT OUTER JOIN codathu.cxx ON cxcuno = wnklnt
  LEFT OUTER JOIN cobtlpf ON tlntool = arntool
  LEFT OUTER JOIN codathu/cotptpf AS budg ON budg.ptcparm = 'BUDGETKL' and budg.ptcprsl = digits(cxbrcm)
  LEFT OUTER JOIN wbtg ON w2csite = 'BUD' and w2ntool = arntool
WHERE
  (wcsite = 'BUD')
ORDER BY
  1,
  2,
  3,
  5,
  4
)
SELECT
  wcsite,
  budgetkl,
  wnklnt,
  cxcunm,
  wdlevr,
  wnartk,
  description,
  partnbr,
  tool,
  tooltext,
  wqafrp,
  price,
  max_cycletime
FROM
  tempt