
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Join SQL Select with Excel
Hi,
I am trying to Left Join data from server and from Excel, but I get an error.
As you can see below there are several joins in the server and they are going well.
But when I add the join from the excel, I get this error :
"
The following error occurred:
Connector reply error: ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Incorrect syntax near 'labels'."
What is wrong in the code?
Thanks a lot!
Sandra
Fin_ACCOUNTS:
LIB CONNECT TO 'Sandra-Lavi (lavi_sandra)';
LOAD *;
SQL SELECT
a.ACCOUNT,
a.ACCNAME,
a.ACCDES,
a."SECTION",
a.ADJACCOUNT,
a.VATSECTION,
a.ACNGROUP,
a.RECDATE,
a.COMPANY,
a.ADJACCOUNT2,
s."ORD" as ORD_Sections,
s.SECNAME,
s.ACCTYPE,
s.SECTIONCODE,
AT.ACCTYPENAME,
AT."ORD" as ORD_AccType,
AT.BALTYPE,
AT.ACCTOTAL,
B.BALTYPECODE,
B.BALTYPEDES,
B.BALFLAG,
B.COEF,
B.EBALTYPEDES
FROM lavi.dbo.ACCOUNTS as a
Left Join lavi.dbo.SECTIONS as s on a."SECTION" = s."SECTION"
Left Join lavi.dbo.ACCTYPES as AT on AT.ACCTYPE = s.ACCTYPE
Left Join lavi.dbo.BALTYPES as B on B.BALTYPE = AT.BALTYPE
Left Join[lib://FinanceData (lavi_sandra)/PL\PL_Auxiliary.xlsx]
(ooxml, embedded labels, table is Accounts) as Ex
on Ex.AccountID = a.ACCNAME
;
- Tags:
- join
- left join()
- sql


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Load XLSX data independently of SQL. And if you don't need left join let the associative engine do it's work If you do - join using RESIDENT load.
Tomasz
.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Optionally:
LIB CONNECT TO 'Sandra-Lavi (lavi_sandra)';
Fin_ACCOUNTS:
LOAD *;
SQL SELECT
a.ACCOUNT,
a.ACCNAME,
a.ACCDES,
a."SECTION",
a.ADJACCOUNT,
a.VATSECTION,
a.ACNGROUP,
a.RECDATE,
a.COMPANY,
a.ADJACCOUNT2,
s."ORD" as ORD_Sections,
s.SECNAME,
s.ACCTYPE,
s.SECTIONCODE,
AT.ACCTYPENAME,
AT."ORD" as ORD_AccType,
AT.BALTYPE,
AT.ACCTOTAL,
B.BALTYPECODE,
B.BALTYPEDES,
B.BALFLAG,
B.COEF,
B.EBALTYPEDES
FROM lavi.dbo.ACCOUNTS as a
Left Join lavi.dbo.SECTIONS as s on a."SECTION" = s."SECTION"
Left Join lavi.dbo.ACCTYPES as AT on AT.ACCTYPE = s.ACCTYPE
Left Join lavi.dbo.BALTYPES as B on B.BALTYPE = AT.BALTYPE
;
Left Join
LOAD
AccountID as a.ACCNAME,
*
from [lib://FinanceData (lavi_sandra)/PL\PL_Auxiliary.xlsx]
(ooxml, embedded labels, table is Accounts);
Hope this helps.
Juraj

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
As you can see from Juraj example, there is a Load statement that you have to apply the join. Instead, you join in the connection string directly, which is not a valid Qlik statement. Actually, even the aliases you have after the XLSX connection attributes are not valid.
BR
Christos
