DB Query Reader Error

KNIME Analytics Platform
1

Hi everyone,

I’m using the DB Query Reader to connect with amazon redshift.

And I have the following query:

Select b.*, c.“releasedate”,
DENSE_RANK() OVER(PARTITION BY b.originboardid ORDER BY TO_DATE(SUBSTRING(b.sprintenddate,0,9), ‘DD/Mon/YY’) DESC) AS “rank_sprint”,
DENSE_RANK() OVER(PARTITION BY b.originboardid ORDER BY TO_DATE(c.releasedate, ‘YYYY-MM-DD’) DESC) AS “rank_release”,
RANK() OVER (ORDER BY b.issueid, b.sprintid DESC) as “rank_issue”,
MAX(
IF (b.issueorigin=‘completed’) AND (b.changeto=‘In Progress’) and (b.changefield=‘status’)
THEN
max(TO_DATE((SUBSTRING(b.changecreation,0,10))) OVER(b.issueid,b.sprintid);
END IF;
) OVER (b.issueid,b.sprintid) as “lastinprogress”
from digitalplatforms.issues_braze b

Left join jira.releases c
On b.version_id=c.versionid

and its outputing the following error:

[Amazon] (500335) One query is expected: SELECT * FROM (Select b.*, c.“releasedate”,
DENSE_RANK() OVER(PARTITION BY b.originboardid ORDER BY TO_DATE(SUBSTRING(b.sprintenddate,0,9), ‘DD/Mon/YY’) DESC) AS “rank_sprint”,
DENSE_RANK() OVER(PARTITION BY b.originboardid ORDER BY TO_DATE(c.releasedate, ‘YYYY-MM-DD’) DESC) AS “rank_release”,
RANK() OVER (ORDER BY b.issueid, b.sprintid DESC) as “rank_issue”,
MAX(
IF (b.issueorigin=‘completed’) AND (b.changeto=‘In Progress’) and (b.changefield=‘status’)
THEN
max(TO_DATE(b.changecreation,‘YYYY-MM-DD’)) OVER(b.issueid,b.sprintid);
END IF;
) OVER (b.issueid,b.sprintid) as “lastinprogress”
from digitalplatforms.issues_braze b

Left join jira.releases c
On b.version_id=c.versionid

) AS tempTable_5545117599441710190 LIMIT 10;

Also if I query just:

Select b.*, c.“releasedate”,
DENSE_RANK() OVER(PARTITION BY b.originboardid ORDER BY TO_DATE(SUBSTRING(b.sprintenddate,0,9), ‘DD/Mon/YY’) DESC) AS “rank_sprint”,
DENSE_RANK() OVER(PARTITION BY b.originboardid ORDER BY TO_DATE(c.releasedate, ‘YYYY-MM-DD’) DESC) AS “rank_release”,
RANK() OVER (ORDER BY b.issueid, b.sprintid DESC) as “rank_issue”
from digitalplatforms.issues_braze b

Left join jira.releases c
On b.version_id=c.versionid

it works.

Can someone help?

Thank you

2

@AdrianaFerro from what I see you will have to check the brackets you use and if they are correct and also the syntax and setting of the IF … statement.

image
image789×106 7.67 KB

3

Thank you @mlauber71. Just corrected a bit my syntax:

Select b.*, c.“releasedate”,
DENSE_RANK() OVER(PARTITION BY b.originboardid ORDER BY TO_DATE(SUBSTRING(b.sprintenddate,0,9), ‘DD/Mon/YY’) DESC) AS “rank_sprint”,
DENSE_RANK() OVER(PARTITION BY b.originboardid ORDER BY TO_DATE(c.releasedate, ‘YYYY-MM-DD’) DESC) AS “rank_release”,
RANK() OVER (ORDER BY b.issueid, b.sprintid DESC) as “rank_issue”,
MAX(IF (b.issueorigin=‘completed’) AND (b.changeto=‘In Progress’) and (b.changefield=‘status’)
max(TO_DATE(SUBSTRING(b.changecreation,0,10))) OVER(b.issueid,b.sprintid)
) OVER (b.issueid,b.sprintid) as “lastinprogress”
from digitalplatforms.issues_braze b

Left join jira.releases c
On b.version_id=c.versionid

and the error now is: Amazon Invalid operation: syntax error at or near “max”

so something is wrong with my max.

4

This syntax still does not look good. Maybe you check that again and test it separately if you get a result that would make sense. Some databases do not support the IF … construction.

5

@mlauber71 I changed the if to a CASE condition:

Select b.*, c.“releasedate”,
DENSE_RANK() OVER(PARTITION BY b.originboardid ORDER BY TO_DATE(SUBSTRING(b.sprintenddate,0,9), ‘DD/Mon/YY’) DESC) AS “rank_sprint”,
DENSE_RANK() OVER(PARTITION BY b.originboardid ORDER BY TO_DATE(c.releasedate, ‘YYYY-MM-DD’) DESC) AS “rank_release”,
RANK() OVER (ORDER BY b.issueid, b.sprintid DESC) as “rank_issue”,
MAX
(CASE
WHEN(b.issueorigin=‘completed’) AND (b.changeto=‘In Progress’) and (b.changefield=‘status’) THEN max(TO_DATE(SUBSTRING(b.changecreation,0,10),‘YYYY-MM-DD’)) OVER(b.issueid,b.sprintid)
END) OVER (b.issueid,b.sprintid) as “lastinprogress”
from digitalplatforms.issues_braze b

Left join jira.releases c
On b.version_id=c.versionid

And now Amazon (500310) Invalid operation: syntax error at or near “b”
Position: 536;

and if I just perform:

Select b.*,
MAX
(CASE
WHEN(b.issueorigin=‘completed’) AND (b.changeto=‘In Progress’) and (b.changefield=‘status’) THEN max(TO_DATE(SUBSTRING(b.changecreation,0,10),‘YYYY-MM-DD’)) OVER(b.issueid,b.sprintid)
END) OVER (b.issueid,b.sprintid) as “lastinprogress”
from digitalplatforms.issues_braze b

Left join jira.releases c
On b.version_id=c.versionid

I get the error: Amazon(500310) Invalid operation: syntax error at or near “b”
Position: 201;

6

@mlauber71 Do you have any alternative with KNIME nodes that I can use to perform this?

7

KNIME has a Rank Node which can do a lot of grouped rankings but not sure whether you really want to do the transformation in KNIME or rather upfront in the Database via your query
br