Using subquery in a join statement in ADO SQL Excel VBA

[pcanino]

Hi everyone - if anyone can figure out what is wrong with my sqlstatement here because I'm really out of ideas on how to resolve this, i have scoured the net for days already.

Here's the query:
SELECT Sup.[SAP ID] FROM [Supply$] AS <sup>LEFT JOIN [Demand$] AS [Dem] ON (Sup.[Career Level] IN (SELECT [Range Level 1] FROM [Matrix$] AS [M] WHERE M.[Range Level] = Dem.[Career Level]))

I'm executing this in Excel and I get this error:
"[Microsoft][ODBC Excel Driver] Syntax error in query expression 'Sup.[Career Level] IN (SELECT [Range Level 1] FROM [Matrix$] AS [M] WHERE M.[Range Level] = Dem.[Career Level]'."</sup>

 

[Kyle123]

I'm not entirely sure what you're query is supposed to do, but I think you want

SELECT Sup.[sap id] 
FROM   [supply$] AS  
       LEFT JOIN [demand$] AS [Dem] 
              ON sup.[career level] = Dem.[career level] 
       LEFT JOIN [matrix$] AS [M] 
              ON M.[range level] = dem.[career level] 
WHERE  ( dem.[career level] IS NULL 
          OR M.[range_level] IS NOT NULL ) 

 

[pcanino]

I'm not entirely sure what you're query is supposed to do, but I think you want

SELECT Sup.[sap id] 
FROM   [supply$] AS  
       LEFT JOIN [demand$] AS [Dem] 
              ON sup.[career level] = Dem.[career level] 
       LEFT JOIN [matrix$] AS [M] 
              ON M.[range level] = dem.[career level] 
WHERE  ( dem.[career level] IS NULL 
          OR M.[range_level] IS NOT NULL ) 

^{Thanks for the reply, I tried to run your given query but I got this error:
"[Microsoft][ODBC Excel Driver] Syntax error (missing operator) in query expression '(sup.[career level] = Dem.[career level]) LEFT JOIN [matrix$] AS [M] ON (M.[range level] = dem.[career level]'."}

 

[Kyle123]

That error message isn't the sql from the post?

Are you doing this in VBA? If so, post the code please

 

[pcanino]

That error message is from the sql that you posted. I just managed to put some parenthesis just to try if it works, but then it's the same. Yes, I'm running this on Excel VBA. The whole code is very big, that sql code is just the gist of what I'm trying to do, so I just made that out to simplify what I'm having trouble with.

 

[Kyle123]

So please post the code that builds the sql string

 

[pcanino]

Here it is then:

'I just want to put the fields in a separate variables so it's easy to find
supplyFields = "[SAP ID], [Personnel Name], Sup.[Career Level], [Capability], [Skill]," & _
"[Final Proficiency], [Skill Type], [Current IG], [PM]"
demandFields = "[Onsite Country], [RRD ID], [Ready to Hard Lock], [Account Group], [Client]," & _
"[Project], Dem.[Start Date], Dem.[End Date], [Primary Skill], Dem.[Career Level]"

'Computations for Visa Aging
lastRepatriationField = " ,IIF([SAP ID] IS NOT NULL AND [Onsite Country] = 'USA', DateDiff('m',[Last Repatriation Date], Now()), '') AS [Last Repatriation]"
monthsAgingField = " ,IIF([SAP ID] IS NOT NULL AND [Onsite Country] = 'USA', DateDiff('m', Sup.[Visa Start Date], Now()), '') AS [Months Aging]"
visaAgingField = " ,IIF([SAP ID] IS NOT NULL, IIF([Onsite Country] = 'USA'" & _
" ,IIF([Visa Type] = 'H-1B'" & _
" OR (([Visa Type] = 'L-1B' OR [Visa Type] = 'L-1A')" & _
" AND DateDiff('m', Sup.[Visa Start Date], Now()) >= 6" & _
" AND DateDiff('m',[Last Repatriation Date], Now()) >= 6), 'Yes', 'No')" & _
", 'Not Applicable; non-US demand'), '') AS [Visa Aging],"

'Set Supply Criteria in the Join Clause
If supplyMatchLvl = "Exact" Then
supplyCriteria = supplyCriteria & " AND Sup.[Career Level] = Dem.[Career Level]"
Else
supplyCriteria = supplyCriteria & " AND Sup.[Career Level] IN (SELECT [Range Level 1] FROM [Matrix$] AS [M] WHERE M.[Range Level] = Dem.[Career Level])"
End If

'Change Request - they want that if the skill type is SECONDARY then it would have to look for both primary and secondary _
and if primary then it should be primary only.
If supplySkillType = "SECONDARY SKILL ASSESSMENT" Then
supplyCriteria = supplyCriteria & " AND Sup.[Skill Type] IN ('PRIMARY SKILL ASSESSMENT', 'SECONDARY SKILL ASSESSMENT')"
Else
supplyCriteria = supplyCriteria & " AND Sup.[Skill Type] = '" & supplySkillType & "'"
End If


'Visa Ready
If supplyVisaReady <> "ALL" Then
supplyCriteria = supplyCriteria & " AND Sup.[Visa Re-usable Y/N] = '" & supplyVisaReady & "'"
End If

'Are you interested to be assigned onshore under the Global Careers Program (GCP)?
'Supply Preferred Country
If demandCountry <> "ALL" Then
If demandCountry <> "USA" Then
supplyCriteria = supplyCriteria & " AND Sup.[Interested onshore] = 'Yes'"
supplyCriteria = supplyCriteria & " AND Sup.[Sustainable IJP Project Tenure] = 'Yes'"
End If
supplyCriteria = supplyCriteria & " AND Sup.[" & demandCountry & "] = 'Y'"
End If

'Visa Aging
If supplyVisaAging <> "ALL" Then
supplyCriteria = supplyCriteria & " AND (IIF([Onsite Country] = 'USA'" & _
" ,IIF([Visa Type] = 'H-1B'" & _
" OR (([Visa Type] = 'L-1B' OR [Visa Type] = 'L-1A')" & _
" AND DateDiff('m', Sup.[Visa Start Date], Now()) >= 6" & _
" AND DateDiff('m',[Last Repatriation Date], Now()) >= 6), 'Yes', 'No')" & _
", 'Not Applicable; non-US demand')) = '" & supplyVisaAging & "'"
End If

'SET DEMAND CRITERIA
'Demand Primary Skill
If demandPrimarySkill <> "ALL" Then
demandCriteria = demandCriteria & " AND Dem.[Primary Skill] = '" & demandPrimarySkill & "'"
End If

'Demand IG
If demandIG <> "ALL" Then
If demandIG = "CMT" Then
demandCriteria = demandCriteria & " AND Dem.[Account Group] IN ('CMT', 'Communications, Media & Technology')"
Else
demandCriteria = demandCriteria & " AND Dem.[Account Group] = '" & demandIG & "'"
End If
End If

'Demand Country
If demandCountry <> "ALL" Then
demandCriteria = demandCriteria & " AND Dem.[Onsite Country] = '" & demandCountry & "'"
End If

'Demand RHL
If demandRHL = "Yes" Then
demandCriteria = demandCriteria & " AND Dem.[Ready to Hard Lock] IN ('Yes', 'Y')"
ElseIf demandRHL = "No" Then
demandCriteria = demandCriteria & " AND Dem.[Ready to Hard Lock] IN ('No', 'N')"
End If

'Demand Level
If demandCareerLvl <> "ALL" Then
demandCriteria = demandCriteria & " AND Dem.[Career Level] = '" & demandCareerLvl & "'"
End If

'Contatenate the fields and the criterias
'Criterias that are common, I just added it directly in the sqlStatement
sqlStatement = "SELECT " & supplyFields & lastRepatriationField & monthsAgingField & visaAgingField & demandFields & _
" FROM [Demand$] AS [Dem] LEFT JOIN [Supply$] AS <sup>" & _
" ON (Sup.[Skill] = Dem.[Primary Skill]" & supplyCriteria & _
" AND Sup.[Project Tenure] = '>=12 months'" & _
" AND IIF(Sup.[GCP Status] IS NULL, '', Sup.[GCP Status]) = ''" & _
" AND IIF(Sup.[PIP Status] IS NULL, '', Sup.[PIP Status]) = ''" & _
" AND IIF(Sup.[Rating - Eligibility] IS NULL, '', Sup.[Rating - Eligibility]) IN ('Yes', '')" & _
" AND Sup.[Accenture Tenure] = 'Yes')" & _
" WHERE 1=1" & demandCriteria & " ORDER BY [SAP ID] DESC"

I don't have a problem if it does not pass in this code: supplyCriteria = supplyCriteria & " AND Sup.[Career Level] IN (SELECT [Range Level 1] FROM [Matrix$] AS [M] WHERE M.[Range Level] = Dem.[Career Level])"</sup>

 

[Kyle123]

After:

sqlStatement = "SELECT " & supplyFields & lastRepatriationField & monthsAgingField & visaAgingField & demandFields & _
" FROM [Demand$] AS [Dem] LEFT JOIN [Supply$] AS " & _
" ON (Sup.[Skill] = Dem.[Primary Skill]" & supplyCriteria & _
" AND Sup.[Project Tenure] = '>=12 months'" & _
" AND IIF(Sup.[GCP Status] IS NULL, '', Sup.[GCP Status]) = ''" & _
" AND IIF(Sup.[PIP Status] IS NULL, '', Sup.[PIP Status]) = ''" & _
" AND IIF(Sup.[Rating - Eligibility] IS NULL, '', Sup.[Rating - Eligibility]) IN ('Yes', '')" & _
" AND Sup.[Accenture Tenure] = 'Yes')" & _
" WHERE 1=1" & demandCriteria & " ORDER BY [SAP ID] DESC"

Put a

Debug.Print sqlStatement

And post the output here

 

[pcanino]

Here it is:
SELECT [SAP ID], [Personnel Name], Sup.[Career Level], [Capability], [Skill],[Final Proficiency], [Skill Type], [Current IG], [PM] ,IIF([SAP ID] IS NOT NULL AND [Onsite Country] = 'USA', DateDiff('m',[Last Repatriation Date], Now()), '') AS [Last Repatriation] ,IIF([SAP ID] IS NOT NULL AND [Onsite Country] = 'USA', DateDiff('m', Sup.[Visa Start Date], Now()), '') AS [Months Aging] ,IIF([SAP ID] IS NOT NULL, IIF([Onsite Country] = 'USA' ,IIF([Visa Type] = 'H-1B' OR (([Visa Type] = 'L-1B' OR [Visa Type] = 'L-1A') AND DateDiff('m', Sup.[Visa Start Date], Now()) >= 6 AND DateDiff('m',[Last Repatriation Date], Now()) >= 6), 'Yes', 'No'), 'Not Applicable; non-US demand'), '') AS [Visa Aging],[Onsite Country], [RRD ID], [Ready to Hard Lock], [Account Group], [Client],[Project], Dem.[Start Date], Dem.[End Date], [Primary Skill], Dem.[Career Level] FROM [Demand$] AS [Dem] LEFT JOIN [Supply$] AS ^{ON (Sup.[Skill] = Dem.[Primary Skill] AND Sup.[Career Level] IN (SELECT [Range Level 1] FROM [Matrix$] AS [M] WHERE M.
[Range Level] = Dem.[Career Level]) AND Sup.[Skill Type] = 'PRIMARY SKILL ASSESSMENT' AND Sup.[Project Tenure] = '>=12 months' AND IIF(Sup.[GCP Status] IS NULL, '', Sup.[GCP Status]) = '' AND IIF(Sup.[PIP Status] IS NULL, '', Sup.[PIP Status]) = '' AND IIF(Sup.[Rating - Eligibility] IS NULL, '', Sup.[Rating - Eligibility]) IN ('Yes', '') AND Sup.[Accenture Tenure] = 'Yes') WHERE 1=1 AND Dem.[Career Level] = '09-Team Lead' ORDER BY [SAP ID] DESC}

 

[Kyle123]

Does just the sql I gave you work? Excluding all the other criteria?

Some things in your SQL don't appear to make sense to me, what exactly are you expecting the Red section to do?

       LEFT JOIN [supply$] AS [Sup] 
              ON ( Sup.[skill] = Dem.[primary skill] AND Sup.[career level] IN (SELECT [range level 1] FROM   [matrix$] AS [M] WHERE  M.[range level] = Dem.[career level]) 
                   AND Sup.[skill type] = 'PRIMARY SKILL ASSESSMENT' 
                   AND Sup.[project tenure] = '>=12 months' 
[COLOR="#FF0000"]                   AND Iif(Sup.[gcp status] IS NULL, '', Sup.[gcp status]) = '' 
                   AND Iif(Sup.[pip status] IS NULL, '', Sup.[pip status]) = ''
                   AND Iif(Sup.[rating - eligibility] IS NULL, '', Sup.[rating - eligibility]) IN ( 'Yes', '' )  [/COLOR]
                   AND Sup.[accenture tenure] = 'Yes' )