Broken charts

[rsnell]

I just installed the latest version of 6.4. I have an app that displays a number of column charts. The underlying data is retrieved after setting a vvin_virtual Parameter to a complex date calculation (date98 >= date(trim(char(year(current_date) - CASE WHEN month(current_date) = 12 THEN 0 ELSE 1 END ) ) || '-' || right( '0' || trim(char(mod(month(current_date), 12) + 1)), 2 ) || '-01' ) AND date98 <= current_date). The app worked fine at 6.3 but returns no data at 6.4.

[sean.lanktree]

Returns empty data or the sql statement is invalid? I would be curious to see the difference between the sql statement in 6.3 and 6.4. This can be done via the NAB Developer Tools if you check "Return SQL".

[rsnell]

I looked at the SQL. There's a CTE that has the vvin_virtual Parameter. It looks like the default value isn't being overridden. Here's the CTE:

WITH dates_selected AS ( SELECT ndat98, date98, year(date98) AS year98, month(date98) AS month98, left(monthname(date98), 3) AS month_desc FROM ddc98 WHERE 1 > 1 ),

The 1>1 is the default value set for the vvin_virtual parameter. Here's the data sources:

WITH dates_selected AS (
SELECT
ndat98,
date98,
year(date98) AS year98,
month(date98) AS month98,
left(monthname(date98), 3) AS month_desc
FROM
ddc98
WHERE
vvin_virtual(
'fixed12months',
'1>1',
'char',
300,
NULL,
'false'
)
),?

[rsnell]

Were you able to reproduce the problem?

[Johnny Major]

Were you able to grab the SQL statement that was run (at least on 6.4) from the NAB developer tools so I can see the actual statement that is being run? Thanks. If we can get the full SQL from the data source and the SQL that was run, that would be helpful.

[rsnell]

Here's the full SQL:

WITH dates_selected AS (
  SELECT
    ndat98,
    date98,
    year(date98) AS year98,
    month(date98) AS month98,
    left(monthname(date98), 3) AS month_desc
  FROM
    ddc98
  WHERE
    vvin_virtual(
      'fixed12months',
      '1>1',
      'char',
      300,
      NULL,
      'false'
    )
),
production_records AS (
  (
    SELECT
      cono83,
      wono83,
      shft83,
    CASE
        WHEN bnum83 = 1
          THEN 1
        ELSE 0
  END AS color_change,
frst83,
tran83,
secq83,
rbmd83,
0 AS rebale,
0 AS rework,
0 AS tow,
year98,
month98,
month_desc,
frst83 + secq83 + tran83 + rbmd83 AS total83,
secq83 + rbmd83 + tran83 AS waste83
FROM
  dwh83
  JOIN inp15l88 ON cono15 = cono83 AND psar15 = line83
  JOIN dates_selected ON ndat98 = shdt83
WHERE
  (
    cono83 = vvin_virtual('gCompany', 'xx', 'char', 2, NULL, 'true')
  )
UNION ALL
SELECT
  cono94,
  wono94,
  shft94,
  0 AS color_change,
  0,
  0,
  0,
  0,
CASE
    WHEN item94 like 'TRAN%'
      THEN wght94
    ELSE 0
END AS rebale,
CASE
    WHEN item94 not like 'TRAN%'
      THEN wght94
    ELSE 0
END AS rework,
0 AS tow,
year98,
month98,
month_desc,
CASE
    WHEN item94 like 'TRAN%'
      THEN 0 - wght94
    ELSE 0
END AS total83,
CASE
    WHEN item94 like 'TRAN%'
      THEN 0 - wght94
    ELSE 0
END AS waste83
FROM
  dwh94
  JOIN inp15l88 ON cono15 = cono94 AND psar15 = line94
  JOIN dates_selected ON ndat98 = shdt94
WHERE
  (
    cono94 = vvin_virtual('gCompany', 'xx', 'char', 2, NULL, 'true')
  )
UNION ALL
SELECT
  cono84,
  wono84,
  shft84,
  0 AS color_change,
  0,
  0,
  0,
  0,
  0 AS rebale,
  0 AS rework,
  wght84 AS tow,
  year98,
  month98,
  month_desc,
  wght84 AS total83,
  wght84 AS waste83
FROM
  dwh84
  JOIN inp15l88 ON cono15 = cono84 AND psar15 = line84
  JOIN dates_selected ON ndat98 = shdt84
WHERE
  (
    cono84 = vvin_virtual('gCompany', 'xx', 'char', 2, NULL, 'true')
  )
)
)
SELECT
  year98,
  month98,
  month_desc,
  shft83,
  SUM(frst83) AS s_frst83,
  SUM(tran83) AS s_tran83,
  SUM(secq83) AS s_secq83,
  SUM(rbmd83) AS s_rbmd83,
  SUM(rebale) AS s_rebale,
  SUM(rework) AS s_rework,
  SUM(tow) AS s_tow,
  SUM(color_change) AS s_color_change,
  dec(
    100 * decfloat(SUM(waste83)) / nullif(decfloat(SUM(total83)), 0),
    7,
    2
  ) AS waste_pc,
  dec(
    100 * decfloat(SUM(tran83)) / nullif(decfloat(SUM(total83)), 0),
    7,
    2
  ) AS tran_pc,
  dec(
    100 * decfloat(SUM(secq83)) / nullif(decfloat(SUM(total83)), 0),
    7,
    2
  ) AS secq_pc,
  dec(
    100 * decfloat(SUM(tow)) / nullif(decfloat(SUM(total83)), 0),
    7,
    2
  ) AS tow_pc,
  dec(
    decfloat(SUM(tran83)) / nullif(decfloat(SUM(color_change)), 0),
    7,
    0
  ) AS lbs_per_change,
  dec(
    decfloat(SUM(frst83)) / nullif(decfloat(SUM(color_change)), 0),
    7,
    0
  ) AS avg_batch,
  dec(
    100 * decfloat(SUM(rework)) / nullif(decfloat(SUM(total83)), 0),
    7,
    2
  ) AS rework_pc
FROM
  production_records
GROUP BY
  year98,
  month98,
  month_desc,
  shft83
ORDER BY
  shft83,
  year98,
  month98

[Johnny Major]

Just wanted to respond in case others come into this thread. This is resolved and appeared to be a timeout issue because of the time it was taking to run the data sources’ SQL statement.