Forum Discussion

DouweMeer's avatar
DouweMeer
Impactful Individual
5 years ago
Solved

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

 

  • DouweMeer's avatar
    DouweMeer
    5 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable

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

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

    • DouweMeer's avatar
      DouweMeer
      Impactful 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-msft's avatar
        v-xuding-msft
        Community 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.