Multiple Join Query for a PowerPivot Table

Question

Post reply

Multiple Join Query for a PowerPivot Table

Jerid421

Right there with Babe

Points: 794
More actions
January 23, 2015 at 1:21 pm

#296930

I am a newbie to this forum... I don't write SQL very often but I do understand it.
I have a query that just won't return the values that I need. It is an import query that populates the dim_SES table in my PowerPivot data model. This query is run against a Data Mart in SQL Server:
SELECT [dbo].[dim_ses].* FROM [dbo].[dim_ses]
It returns 499,585 records, which is every SES (payment) ever made.
I tried to join this table to (2) other tables, to filter the results down to a particular Business Analyst:
SELECT
dim_ses.ses_cd
,dim_ses.ses_line_cd
,dim_ses.ses_line_text
,dim_ses.ses_change_date
,dim_ses.ses_approved
,dim_ses.ses_created_date
,dim_ses.ses_short_text
,dim_ses.ses_external_number
,dim_ses.ses_fully_paid
,dim_ses.ses_paid_pct
,dim_ses.ses_gr_amount
,dim_ses.ses_ir_amount
,dim_ses.ses_header_amount
,dim_ses.ses_id
FROM
dim_ses
INNER JOIN fact_ses
ON dim_ses.ses_id = fact_ses.ses_id
LEFT OUTER JOIN v_dim_wotask_ES
ON fact_ses.wotask_id = v_dim_wotask_ES.wotask_id
WHERE
v_dim_wotask_ES.budget_analyst_text = N'ES-EPM-Peurrung, Pam'
But it returns absolutely zero records! Are my joins wrong? I can provide this files for anyone that can help 😀

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

What do you get for the following query?

SELECT *

FROM v_dim_wotask_ES

WHERE v_dim_wotask_ES.budget_analyst_text = N'ES-EPM-Peurrung, Pam'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jerid421 Right there with Babe Points: 794 More actions · Answer 2

I actually pass this query to the same Datamart to populate a different table in my PowerPivot model:

SELECT

v_dim_wotask_ES.wotask_id

,v_dim_wotask_ES.work_order_cd

,v_dim_wotask_ES.task_cd

,v_dim_wotask_ES.work_order_desc

,v_dim_wotask_ES.task_desc

,v_dim_wotask_ES.wo_status_cd

,v_dim_wotask_ES.requested_date

,v_dim_wotask_ES.task_status_cd

,v_dim_wotask_ES.plant

,v_dim_wotask_ES.profit_center_cd

,v_dim_wotask_ES.cost_center_cd

,v_dim_wotask_ES.COORDER

,v_dim_wotask_ES.cost_type_cd

,v_dim_wotask_ES.bnft_par

,v_dim_wotask_ES.project_id

,v_dim_wotask_ES.powerplant_work_order_code

,v_dim_wotask_ES.business_analyst_text

,v_dim_wotask_ES.capital_groupings_text

,v_dim_wotask_ES.cost_recovery_capital_programs_text

FROM

v_dim_wotask_ES

WHERE

v_dim_wotask_ES.business_analyst_text = N'ES-EPM-Peurrung, Pam'

It returns 1,645 rows.

PowerDAX SSC Journeyman Points: 79 More actions · Answer 3

you are referencing two different columns:

WHERE

v_dim_wotask_ES.budget_analyst_text = N'ES-EPM-Peurrung, Pam'

WHERE

v_dim_wotask_ES.business_analyst_text = N'ES-EPM-Peurrung, Pam'

PowerDAX.com

Jerid421 Right there with Babe Points: 794 More actions · Answer 4

Nice catch! Wow, sometimes a fresh set of eyes is everything! Sorry to bother you for a silly typo... but THANKS!!!

PowerDAX SSC Journeyman Points: 79 More actions · Answer 5

PowerDAX

SSC Journeyman

Points: 79

January 27, 2015 at 12:40 pm

#1773479

no problem...good luck!

PowerDAX.com