Forum Discussion
Query that doesn't work
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
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 :).
5 Replies
- AnonymousNot applicable
Are you sure you posted this question in the right forum?
This looks like SQL and not PowerQuery to me ...
- danextianSuper User
Care to post a screenshot?
- DouweMeerImpactful Individual
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-msftCommunity Support
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.