Oracle SQL complexity

[Anonymous]

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

[Anonymous]

SOLVED.

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

[Anonymous]

Hello,

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

Best regards

Sabrina