Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
61 | |
53 | |
27 | |
16 | |
10 |