Translating SQL query to VBA

Question

I have got SQL query running successfully in Oracle, the code is-

Select Sam.SAM_ID, sum( case when Aud.AUDIT_COMPLETION between (next_day(trunc(sysdate, 'iw'), 'Friday') -  14) and 
(next_day(trunc(sysdate, 'iw'), 'Friday') - 7) then 1 else 0 end ) as "Major Defects - 1 week",
 sum( case when Aud.AUDIT_COMPLETION between (next_day(trunc(sysdate, 'iw'), 'Friday') -  28) and (next_day(trunc(sysdate, 'iw'), 'Friday') - 7) then 1 else 0 end ) as "Major Defect - 4 week", count(Aud.AUDIT_COMPLETION)
From CMS.CMS_SAM_ALL_DATA Sam left join CMS.WATSON_AUDIT_INSPECTION_DT1_VW Aud ON Sam.SAM_ID = Aud.SAM_ID
Where Aud.DEFECT_SEVERITY = 'Major' AND 
Aud.AUD_RESULT = 'Defect' And 
NOT (Aud.AUDIT_OUTCOME = 'SPFR Withdrawn' and 
Aud.AUDIT_OUTCOME = 'Defect/ Observation Cancelled' and 
Aud.AUDIT_OUTCOME = 'Rejected by MIMA' and 
Aud.AUD_RESULT = 'Fixed' and 
Aud.AUDIT_OUTCOME = 'SPFR response accepted') and 
Aud.AUDIT_COMPLETION IS NOT NULL
Group by Sam.SAM_ID;

Now I tried running the above code in macro(VBA) but not able to run macro successfully, Macro code -

StrSQL = StrSQL & "Select Sam.SAM_ID,"
 StrSQL = StrSQL & "sum(case when Aud.AUDIT_COMPLETION between (next_day(trunc(sysdate, 'iw'), 'Friday') - 14) and (next_day(trunc(sysdate, 'iw'), 'Friday') - 7) then 1 else 0 end )as ""Major Defects - 1 week"","
    StrSQL = StrSQL & "sum(case when Aud.AUDIT_COMPLETION between (next_day(trunc(sysdate, 'iw'), 'Friday') - 28) and (next_day(trunc(sysdate, 'iw'), 'Friday') - 7) then 1 else 0 end )as ""Major Defect - 4 week"","
    StrSQL = StrSQL & "count(Aud.AUDIT_COMPLETION)as ""Total Open Defects""
    StrSQL = StrSQL & "From CMS.CMS_SAM_ALL_DATA Sam left join CMS.WATSON_AUDIT_INSPECTION_DT1_VW Aud ON Sam.SAM_ID = Aud.SAM_ID"
    StrSQL = StrSQL & "Where Aud.DEFECT_SEVERITY = 'Major' AND Aud.AUD_RESULT = 'Defect' And NOT (Aud.AUDIT_OUTCOME = 'SPFR Withdrawn' and Aud.AUDIT_OUTCOME = 'Defect/ Observation Cancelled' and Aud.AUDIT_OUTCOME = 'Rejected by MIMA' and Aud.AUD_RESULT = 'Fixed' and Aud.AUDIT_OUTCOME = 'SPFR response accepted') and Aud.AUDIT_COMPLETION IS NOT NULL, Aud.AUDIT_COMPLETION IS NOT NULL, "
    StrSQL = StrSQL & "Group by Sam.SAM_ID;"

When I run above code I get an error as -

FROM Keyword not found where expected

There's no translation required other than to make a string containing your SQL. It's unclear what your code sample is showing. "not able to run macro successfully" is not a really useful description of what happened when you ran your VBA. Did you get an error? — Tim Williams
– Tim Williams, Commented Jun 12, 2018 at 5:51
By enclosing it in quotes. Or paste it into a worksheet cell as-is, and have your code read it from there. stackoverflow.com/questions/3671308/… — Tim Williams
– Tim Williams, Commented Jun 12, 2018 at 5:55
See the link I posted for how to format your string so it's more readable. — Tim Williams
– Tim Williams, Commented Jun 12, 2018 at 6:10

Tim Williams · Accepted Answer · 2018-06-12 06:16:18Z

2

Use line continuation (_) and double up your quotes to escape them:

strSQL = "select a, b as ""My Field"" from " & _
          "tableZ where a = 'blah' and " & _
          "b ='blah' "

answered Jun 12, 2018 at 6:16

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Lokhi Over a year ago

I tried the same format as mentioned above, still getting an error as "FROM Keyword not found where expected".

Tim Williams Over a year ago

It would be useful to update your question with the exact VBA you tried.

Lokhi Over a year ago

I have updated now. One more thing, when I try using &_ after each line it throws up an error as "Invalid Character".

Gadziu Over a year ago

Is not good to use & _ because this type of line connection has limit of connections...

Tim Williams Over a year ago

@Gadziu - like many other things in programming "good" is not an absolute. If you have too many lines to use & _ then do not use it, but if you have that many lines maybe you'd be better off storing your SQL in a worksheet cell where it's easier to maintain.

|

Gadziu · Accepted Answer · 2018-06-12 07:06:29Z

0

You don't have space beetween from count(Aud.AUDIT_COMPLETION)From CMS.CMS_SAM_ALL_DATA Sam left

but take Tim Wiliams advice and split it to new lines. You can also do it like this:

Dim strSQL As String

strSQL = strSQL & "SELECT "
strSQL = strSQL & "1 "
strSQL = strSQL & "FROM dual "

edited Jun 12, 2018 at 7:06

answered Jun 12, 2018 at 6:49

Gadziu

6978 silver badges22 bronze badges

1 Comment

Pᴇʜ Over a year ago

This will produce SELECT1FROM dual watch out your spaces!

Collectives™ on Stack Overflow

Translating SQL query to VBA

2 Answers 2

6 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Linked

Related