UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> SQL Statement Not Working In Powerquery, Any Versions    
 
   
justair07
post Oct 3 2018, 03:22 PM
Post#1



Posts: 759
Joined: 22-August 13



Can someone tell me why this SQL statement works fine in SQL Managment but not PowerQuery?

CODE
select * from fRequest r (nolock)
left outer join fRequestEvent re (nolock) on r.RequestUid = re.RequestUid
order by re.RequestUid, re.EventDateTime


Thank you,

- Justin

--------------------
----------------------------------------------------------------------------------------------
Justin

“If at first you don't succeed, try, try again. Then quit. No use being a d*** fool about it.” - W.C. Fields
Go to the top of the page
 
justair07
post Oct 3 2018, 03:32 PM
Post#2



Posts: 759
Joined: 22-August 13



Ok, I just realized that Excel Power Query does not recognize a relationship established between two tables in SQL. I was hoping to pull in the two table and create an outer left join.

Any tips?

--------------------
----------------------------------------------------------------------------------------------
Justin

“If at first you don't succeed, try, try again. Then quit. No use being a d*** fool about it.” - W.C. Fields
Go to the top of the page
 
MadPiet
post Oct 3 2018, 06:31 PM
Post#3



Posts: 2,506
Joined: 27-February 09



I thought if you paste the T-SQL into the query window after you establish the connection to the database, it just passes the query down the line, SQL Server executes it and sends the results back. I do it all the time and it works fine.

I did it once in PowerBI and once in Excel 2016, and they both worked. If you have SSMS available, what happens if you try to run your code there?

It might be easier/safer to build a stored procedure in SQL Server and then grant users the right to execute it, and then they can do their own analysis...
Go to the top of the page
 
justair07
post Oct 4 2018, 04:32 AM
Post#4



Posts: 759
Joined: 22-August 13



I figured out the problem. The qry runs fine in SSMS. The issue was the relationships not being noticed by Excel. Trying to find the fix.

Thank you.

--------------------
----------------------------------------------------------------------------------------------
Justin

“If at first you don't succeed, try, try again. Then quit. No use being a d*** fool about it.” - W.C. Fields
Go to the top of the page
 
MadPiet
post Oct 8 2018, 02:42 PM
Post#5



Posts: 2,506
Joined: 27-February 09



Not sure this will help, but I'll throw it out there anyway...

In my experience, if you have relationships established/defined in SQL Server or Access, and then use PowerQuery (in PowerBI or Excel), the relationships should be picked up/inherited in Access/Excel.

Otherwise, you'll have to create the relationships yourself.
Go to the top of the page
 
justair07
post Oct 9 2018, 10:58 AM
Post#6



Posts: 759
Joined: 22-August 13



Hi,

Thank you for the feedback. unfortunately in neither Access nor Excel were the relationships recognized.

frown.gif

--------------------
----------------------------------------------------------------------------------------------
Justin

“If at first you don't succeed, try, try again. Then quit. No use being a d*** fool about it.” - W.C. Fields
Go to the top of the page
 
MadPiet
post Yesterday, 11:59 PM
Post#7



Posts: 2,506
Joined: 27-February 09



pssst!

Do you know what NOLOCK does? Are you okay with dirty reads?
Go to the top of the page
 
cheekybuddha
post Today, 02:20 AM
Post#8


UtterAccess VIP
Posts: 10,451
Joined: 6-December 03
From: Telegraph Hill


Did you try it without the nolock?

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th October 2018 - 08:57 PM