DB Query Reader Error

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

@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.

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.

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.

@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;

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

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

1 Like

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.