Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Hi All,
I'm attempting to join tables on multiple columns using SQL. For some reason, it doesn't like when attempt to join table d on multiple columns (fine if just one). What is the correct code I should be using?
SELECT ART_HIER_LEV3_NAME BU, SITE_NAME, NATURAL_DATE_DTE, ARTICLE_ID, site_id, DAY_OF_THE_WEEK, PRICE_TYPE, SUM(STK_POSTN_SNPSHT) STKPOSTNSNPSHTSUM, SUM(BACKSTG_QTY) BACKSTGQTYSUM, BEST_SELLER_IND, BACKSTGONLY_NR_IND FROM dm_vw.fact_backstg_scan A JOIN dm_vw.dim_article B ON A.merchandising_id = B.merchandising_id JOIN dm_vw.dim_calendar C ON A.calendar_id = C.calendar_id JOIN dm_vw.dim_site d ON A.geography_id = d.geography_id LEFT JOIN ( SELECT STORE_ONHAND_AVL, INTRANSIT_STORE_AVL, RDC_NDC_ONHAND_AVL, INTRANSIT_RDC_NDC_AVL, HC_FSV_ONHAND_AVL FROM dm_vw.FACT_ARTICLE_SITE_AVAILABILITY) d ON A.geography_id = d.geography_id AND a.calendar_id = d.calendar_id AND a.merchandising_id = d.merchandising_id WHERE (stk_postn_snpsht<>0 OR backstg_qty<>0) AND C.NATURAL_DATE_DTE>=(current DATE)-7 days AND (ART_HIER_LEV5_CODE ='G5-T01'OR ART_HIER_LEV5_CODE = 'G5-T02'OR ART_HIER_LEV5_CODE = 'G5-T06'OR ART_HIER_LEV5_CODE = 'G5-T27'OR ART_HIER_LEV5_CODE = 'G5-T38'OR ART_HIER_LEV5_CODE = 'G5-T41'OR ART_HIER_LEV5_CODE = 'G5-T42'OR ART_HIER_LEV5_CODE = 'G5-T43'OR ART_HIER_LEV5_CODE = 'G5-T49'OR ART_HIER_LEV5_CODE = 'G5-T57'OR ART_HIER_LEV5_CODE = 'G5-T59'OR ART_HIER_LEV5_CODE = 'G5-T33'OR ART_HIER_LEV5_CODE = 'G5-T37'OR ART_HIER_LEV5_CODE = 'G5-T51'OR ART_HIER_LEV5_CODE = 'G5-T52'OR ART_HIER_LEV5_CODE = 'G5-T60'OR ART_HIER_LEV5_CODE = 'G5-T61'OR ART_HIER_LEV5_CODE = 'G5-T81'OR ART_HIER_LEV5_CODE = 'G5-T03'OR ART_HIER_LEV5_CODE = 'G5-T07'OR ART_HIER_LEV5_CODE = 'G5-T08'OR ART_HIER_LEV5_CODE = 'G5-T09'OR ART_HIER_LEV5_CODE = 'G5-T10'OR ART_HIER_LEV5_CODE = 'G5-T11'OR ART_HIER_LEV5_CODE = 'G5-T12'OR ART_HIER_LEV5_CODE = 'G5-T14'OR ART_HIER_LEV5_CODE = 'G5-T15'OR ART_HIER_LEV5_CODE = 'G5-T16'OR ART_HIER_LEV5_CODE = 'G5-T17'OR ART_HIER_LEV5_CODE = 'G5-T19'OR ART_HIER_LEV5_CODE = 'G5-T25'OR ART_HIER_LEV5_CODE = 'G5-T28'OR ART_HIER_LEV5_CODE = 'G5-T30'OR ART_HIER_LEV5_CODE = 'G5-T75'OR ART_HIER_LEV5_CODE = 'G5-T64'OR ART_HIER_LEV5_CODE = 'G5-T71'OR ART_HIER_LEV5_CODE = 'G5-T72'OR ART_HIER_LEV5_CODE = 'G5-T74'OR ART_HIER_LEV5_CODE = 'G5-T76'OR ART_HIER_LEV5_CODE = 'G5-T77'OR ART_HIER_LEV5_CODE = 'G5-T78'OR ART_HIER_LEV5_CODE = 'G5-T86'OR ART_HIER_LEV5_CODE = 'G5-T87'OR ART_HIER_LEV5_CODE = 'G5-T88'OR ART_HIER_LEV5_CODE = 'G5-T92'OR ART_HIER_LEV5_CODE = 'G5-T35'OR ART_HIER_LEV5_CODE = 'G5-T36'OR ART_HIER_LEV5_CODE = 'G5-T47'OR ART_HIER_LEV5_CODE = 'G5-T80'OR ART_HIER_LEV5_CODE = 'G5-T84'OR ART_HIER_LEV5_CODE = 'G5-T18') GROUP BY NATURAL_DATE_DTE, ARTICLE_ID, site_id, DAY_OF_THE_WEEK, PRICE_TYPE, BEST_SELLER_IND, SITE_NAME, ART_HIER_LEV3_NAME, BACKSTGONLY_NR_IND
Solved! Go to Solution.
@darylmc ,
A dedicated forum might be more appropriate.
https://www.sqlbi.com/topics/from-sql-to-dax/
@darylmc As @v-chuncz-msft mentioned it is not the right community for this. As this is for Power BI stuff.
Anyway, as I can see in your query that you are trying to join the fields from d (an alias table) which are not present in the SELECT clause. Even though that table might have those fields but you are selection is restricted to the subset of fields and the join performing outside this will know or can access those fields in SELECT statement alone. So you need to have geography_id,calendar_id and merchandising_id in your select statement of d.
Hope this clarifies your issue.
Proud to be a PBI Community Champion
@darylmc As @v-chuncz-msft mentioned it is not the right community for this. As this is for Power BI stuff.
Anyway, as I can see in your query that you are trying to join the fields from d (an alias table) which are not present in the SELECT clause. Even though that table might have those fields but you are selection is restricted to the subset of fields and the join performing outside this will know or can access those fields in SELECT statement alone. So you need to have geography_id,calendar_id and merchandising_id in your select statement of d.
Hope this clarifies your issue.
Proud to be a PBI Community Champion
@darylmc ,
A dedicated forum might be more appropriate.
https://www.sqlbi.com/topics/from-sql-to-dax/
User | Count |
---|---|
82 | |
78 | |
67 | |
46 | |
45 |
User | Count |
---|---|
105 | |
44 | |
39 | |
39 | |
39 |