Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi,
I am using PowerBI with an ODBC data connection.
When I run my SQL
Select T1.ASSR_FEE, T1.CAU_DT, T1.Incident_Date, T1.Inv_amount, T1.Rep_Cost, T2.Co_Name + " " + T2.Surname as DriverName, T3.Co_Name + " " + T3.Surname as InspectorNamefrom cl_details as T1 Left Join tb_pers as T2 on T1.pers_no = T2.ref_id left join tb_pers as T3 on T1.insp_id = T3.ref_id
I get the error message........
Details: "ODBC: ERROR [42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'T1.pers_no = T2.ref_id
left join tb_pers as T3 on T1.insp_id = T3.ref_i'."
I have check the syntax online and it seem to be correct... any help appreciated.
Solved! Go to Solution.
Well, if it was me here what I would do :
Create a view for Driver with everything you need
Create a view for Inspector with everything you need
Import your 3 tables in PowerBI and then you can join them and do everything you want, the software is kinda powerful
Hi @Anonymous
You forgot the "FROM"
Quentin
Nope its got the form in it, for somereason the foreum cut it off.
Select
T1.ASSR_FEE,
T1.CAU_DT,
T1.Incident_Date,
T1.Inv_amount,
T1.Rep_Cost,
T2.Co_Name + " " + T2.Surname as DriverName,
T3.Co_Name + " " + T3.Surname as InspectorName
from
cl_details as T1
Left Join tb_pers as T2 on T1.pers_no = T2.ref_id
left join tb_pers as T3 on T1.insp_id = T3.ref_id
@Anonymous If you replace T1, T2 and T3 by your tablename, does it work ?
Else, try that :
Select
T1.ASSR_FEE,
T1.CAU_DT,
T1.Incident_Date,
T1.Inv_amount,
T1.Rep_Cost,
Concat(T2.Co_Name, ' ' ,T2.Surname) as DriverName,
Concat(T3.Co_Name, ' ', T3.Surname) as InspectorName
from
cl_details as T1
Left Join tb_pers as T2 on T1.pers_no = T2.ref_id
left join tb_pers as T3 on T1.insp_id = T3.ref_id
EDIT : You are doing a left join on the same table twice
That code didnt work.
Yes was told the best way was to create another instance of the table to do the different join.
Try that maybe ?
Select cl_details.ASSR_FEE, cl_details.CAU_DT, cl_details.Incident_Date, cl_details.Inv_amount, cl_details.Rep_Cost, tb_pers.Co_Name as DriverFirstName, tb_pers.Surname as DriverSurname from cl_details Left Join tb_pers as T2 on cl_details.pers_no = T2.ref_id left join tb_pers as T3 on cl_details.insp_id = T3.ref_id
Nope doesnt seem to like the alises, maybe have to do a select statement within the select
Well, if it was me here what I would do :
Create a view for Driver with everything you need
Create a view for Inspector with everything you need
Import your 3 tables in PowerBI and then you can join them and do everything you want, the software is kinda powerful
Yeah dont think there is going to be an easy way....
Seems to be the syntax - Managed to get it working with.
Select
T1.ASSR_FEE,
T1.CAU_DT,
T1.Incident_Date,
T1.Inv_amount,
T1.Rep_Cost,
T2.CO_Name & ' ' & T2.Surname as DriverName,
T3.CO_Name & ' ' & T3.Surname as InspectorName
FROM
((cl_details T1)
LEFT OUTER JOIN tb_pers T2 on T1.pers_no = T2.ref_id)
LEFT OUTER JOIN tb_pers T3 on T1.insp_id = T3.ref_id
It kind of worked
Select
cl_details.ASSR_FEE,
cl_details.CAU_DT,
cl_details.Incident_Date,
cl_details.Inv_amount,
cl_details.Rep_Cost,
tb_pers.Co_Name as DriverFirstName,
tb_pers.Surname as DriverSurname
from
cl_details
Left Join tb_pers on cl_details.pers_no = tb_pers.ref_id
Though if I am not using aliases how am I going to join the same table twice?
Hi QuentiN,
Good thinking will give that a try.
Thanks
@Anonymous I don't know if you saw the last line on my post because I've edited it 5 seconds before you replied
You're welcome
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
34 | |
17 | |
12 | |
11 | |
9 |
User | Count |
---|---|
45 | |
25 | |
16 | |
14 | |
14 |