Oracle SQL complexity

Anonymous — Sat, 16 Nov 2024 01:26:58 GMT

Hello,

In talend ETL studio I have several connexions to an Oracle Database. It works correctly.

For exemple :

"SELECT PINT.PN, SUBSTR(PID.LOCATION, 1,3) as LOCATION2, SUM(PID.QTY_AVAILABLE) as QUANTITY

FROM PRODUCTION.PN_INTERCHANGEABLE PINT LEFT JOIN PRODUCTION.PN_INVENTORY_DETAIL PID ON PINT.PN_INTERCHANGEABLE = PID.PN where PID.QTY_AVAILABLE > 0

GROUP BY PINT.PN, SUBSTR(PID.LOCATION, 1,3) ORDER BY PINT.PN, SUBSTR(PID.LOCATION, 1,3)"

result is a file

but when I try to integrate such SQL request into a more complex SQL structure

"WITH INTER1 AS ( SELECT PINT.PN, SUBSTR(PID.LOCATION, 1,3) as LOCATION2, SUM(PID.QTY_AVAILABLE) as QUANTITY

FROM PRODUCTION.PN_INTERCHANGEABLE PINT LEFT JOIN PRODUCTION.PN_INVENTORY_DETAIL PID ON PINT.PN_INTERCHANGEABLE = PID.PN where PID.QTY_AVAILABLE > 0

GROUP BY PINT.PN, SUBSTR(PID.LOCATION, 1,3) ORDER BY PINT.PN, SUBSTR(PID.LOCATION, 1,3) )

Select * from INTER1 "

I get

Same SQL request, same number of lines, Same columns, but the Quantity one (BigDecimal) always contain 0.

I checked several things ... but I can't find the solution.

Any idea ?

regards

Damien

Re: Oracle SQL complexity

Anonymous — Thu, 17 Sep 2020 08:39:25 GMT

SOLVED.

One of my collegues asked me to suppress the ORDER BY clause ... and it works !

Anonymous — Mon, 21 Sep 2020 06:26:01 GMT

Hello,

Thanks for letting us know this issue is fixed by yourself and sharing your solution with us.

Best regards

Sabrina