Issue with clause column extraction

[Selvi21]

While trying to extract columns from the below query. I'm not getting the columns with proper alias value. The query is joining two with clause.

Query: WITH SECTION AS (SELECT VALUE FROM PAR) , DOCUMENT AS (SELECT L.AS_DT FROM EMP L) , FIN AS (SELECT ALS.VALUE,APS.QTY FROM SECTION ALS FULL JOIN DOCUMENT APS); Result: [VALUE, EMP.AS_DT , ALS.VALUE, APS.QTY ]

I would like to know the reason for these two column values ALS.VALUE and APS.QTY.

I'm expecting [PAR.VALUE , EMP.QTY] or else [SECTION.VALUE, DOCUMENT.QTY].

[collerek]

You have only 3 with queries (so kind of sub queries), where is the final query used?

[Selvi21]

@collerek Corrected complete query is below,

WITH SECTION AS (SELECT VALUE FROM PAR) , DOCUMENT AS (SELECT L.AS_DT FROM EMP L) , FIN AS (SELECT ALS.VALUE,APS.QTY FROM SECTION ALS FULL JOIN DOCUMENT APS ON (ALS.PD_TYPE = APS.PD_TYPE)) SELECT *, CASE WHEN PD_TYPE = 'P CUS' THEN 'PCUS' END AS PD_TYPE FROM FIN;

[Selvi21]

Hi @macbre ,Do you need any additional details?

[collerek]

OK, so the current output is: ['VALUE', 'EMP.AS_DT', 'ALS.VALUE', 'APS.QTY', 'ALS.PD_TYPE', 'APS.PD_TYPE', '*', 'PD_TYPE']. That's because the with queries are not considered as sub-queries so as of now they are not resolved to each other.

I would say this is an enhancement but definitely a desired one.

As of now, you can modify the query into a chain of sub-queries in order to resolve them. We will try to introduce this feature but we cannot commit to any deadlines.

Of course, we always welcome PRs :)