# DB Query Reader Error

**URL:** <https://forum.knime.com/t/db-query-reader-error/38532>\
**Category:** KNIME Analytics Platform\
**Created:** [January 3, 2022, 9:18am UTC](https://forum.knime.com/t/db-query-reader-error/38532 "2022-01-03T09:18:00Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![AdrianaFerro](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/a/a88e57/32.png) [@AdrianaFerro](https://forum.knime.com/u/AdrianaFerro)\
**Post date:** [January 3, 2022, 9:18am UTC](https://forum.knime.com/t/db-query-reader-error/38532/1 "2022-01-03T09:18:00Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![mlauber71](https://forum-cdn.knime.com/user_avatar/forum.knime.com/mlauber71/32/73590_2.png) [@mlauber71](https://forum.knime.com/u/mlauber71)\
**Post date:** [January 3, 2022, 10:18am UTC](https://forum.knime.com/t/db-query-reader-error/38532/2 "2022-01-03T10:18:18Z")

</div>

@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](https://forum-cdn.knime.com/uploads/default/original/3X/8/c/8cc36deb4bf48bf203c90719fbeb77ffda659dd2.png)

---

<div class="post-metadata">

**Author:** ![AdrianaFerro](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/a/a88e57/32.png) [@AdrianaFerro](https://forum.knime.com/u/AdrianaFerro)\
**Post date:** [January 3, 2022, 10:33am UTC](https://forum.knime.com/t/db-query-reader-error/38532/3 "2022-01-03T10:33:08Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![mlauber71](https://forum-cdn.knime.com/user_avatar/forum.knime.com/mlauber71/32/73590_2.png) [@mlauber71](https://forum.knime.com/u/mlauber71)\
**Post date:** [January 3, 2022, 10:50am UTC](https://forum.knime.com/t/db-query-reader-error/38532/4 "2022-01-03T10:50:43Z")

</div>

> [@AdrianaFerro](#):
>
> ,  
> 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”

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.

---

<div class="post-metadata">

**Author:** ![AdrianaFerro](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/a/a88e57/32.png) [@AdrianaFerro](https://forum.knime.com/u/AdrianaFerro)\
**Post date:** [January 3, 2022, 11:35am UTC](https://forum.knime.com/t/db-query-reader-error/38532/5 "2022-01-03T11:35:34Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![AdrianaFerro](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/a/a88e57/32.png) [@AdrianaFerro](https://forum.knime.com/u/AdrianaFerro)\
**Post date:** [January 3, 2022, 1:11pm UTC](https://forum.knime.com/t/db-query-reader-error/38532/6 "2022-01-03T13:11:15Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Daniel\_Weikert](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/d/e47c2d/32.png) [@Daniel\_Weikert](https://forum.knime.com/u/Daniel_Weikert)\
**Post date:** [January 3, 2022, 4:51pm UTC](https://forum.knime.com/t/db-query-reader-error/38532/7 "2022-01-03T16:51:07Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![system](https://forum-cdn.knime.com/uploads/default/original/1X/ab3ccf34482a0329361734a18199390177204f15.png) [@system](https://forum.knime.com/u/system)\
**Post date:** [July 5, 2022, 4:52am UTC](https://forum.knime.com/t/db-query-reader-error/38532/8 "2022-07-05T04:52:02Z")

</div>

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