Query that doesn't work

[DouweMeer]

It's probably just me... but my query isn't working. It tell me that 'G_EF.mximpl' is invalid.

Select
G.line_item_id
, G.price
, G_EF.mximpl
FROM
mn_price_master_li G
inner join
(
SELECT
max ( G_ED.date_impl ) as "mximpl"
, max ( G_ED.eff_start_date ) as "mxeff"
, G_ED.line_item_id as "Id"
FROM
mn_price_master_li G_ED
WHERE
G_ED.line_item_id = '6314925' OR G_ED.line_item_id = '908200'
GROUP BY
G_ED.line_item_id
) G_EF
ON
G.line_item_id = G_EF.id
and G.date_impl = G_EF.mximpl
and G.eff_start_date = G_EF.mxeff

But when I do the following, it does return a table with 3 columns as specified with the select statement in the from part...

SELECT

G_EF.*

FROM

(
SELECT
max ( G_ED.date_impl ) as "mximpl"
, max ( G_ED.eff_start_date ) as "mxeff"
, G_ED.line_item_id as "Id"
FROM
mn_price_master_li G_ED
WHERE
G_ED.line_item_id = '6314925' OR G_ED.line_item_id = '908200'
GROUP BY
G_ED.line_item_id
) G_EF

[danextian]

Care to post a screenshot?

[DouweMeer]

Small:

Small query with only G_EF

Result of small:

Result of small query with only G_EF

Attempt on longer one

Long:

Larger query merging G_EF with G.

[v-xuding-msft]

Hi @DouweMeer ,

Please run the SQL command in your database firstly to check if it will return the correct result. And please check if there is any special character in the G_EF.maxeff column.

[DouweMeer · Accepted solution]

So, I've found the problem.  

["mximpl"] is not matching with [G_EF.mximpl], it is supposed to be [G_EF."mximpl"] to make it work. 

Silly flaw :). 

[drorli]

Are you sure you posted this question in the right forum?

This looks like SQL and not PowerQuery to me ...