August 25, 2015 at 3:10 pm
serviceaellis (8/25/2015)
Anyone see why there's a syntax error on 'pm' from the function?
no idea really....but have you tried replacing your "pm" alias with actual table names?
shot in the dark.
good luck
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 25, 2015 at 3:13 pm
Looks like you're using pm as an alias twice; on the 2nd line of Split2 and at the end of Split3.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 25, 2015 at 6:06 pm
Alvin Ramard (8/25/2015)
Looks like you're using pm as an alias twice; on the 2nd line of Split2 and at the end of Split3.
Does that matter?
Strange thing is nothing has changed when it successfully ran last month in July.
So I have no idea why it would error now on 'pm' as a syntax error.
August 26, 2015 at 6:09 am
serviceaellis (8/25/2015)
Alvin Ramard (8/25/2015)
Looks like you're using pm as an alias twice; on the 2nd line of Split2 and at the end of Split3.Does that matter?
Strange thing is nothing has changed when it successfully ran last month in July.
So I have no idea why it would error now on 'pm' as a syntax error.
Yes it should matter. Calling 2 things by the same name? That rarely works.
Nothing changed? I find that hard to believe, if this used to work. If it worked before and now it doesn't, then something has changed.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 26, 2015 at 6:36 am
in addition to what Alvin has already said....your TSQL code IS different to the Excel function....(ignoring the &_ line continuation)
TSQL
FROM attribute.PersonMembership LEFT OUTER JOIN USFSA.dbo.SOP10100 AS invWork
Excel
"FROM attribute.PersonMembership pm " & _
"LEFT JOIN USFSA.dbo.SOP10100 invWork
whether this is relevant.....I dont know
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 26, 2015 at 7:52 am
J Livingston SQL (8/26/2015)
in addition to what Alvin has already said....your TSQL code IS different to the Excel function....(ignoring the &_ line continuation)TSQL
FROM attribute.PersonMembership LEFT OUTER JOIN USFSA.dbo.SOP10100 AS invWork
Excel
"FROM attribute.PersonMembership pm " & _
"LEFT JOIN USFSA.dbo.SOP10100 invWork
whether this is relevant.....I dont know
Removing that "pm" errors. Or at the end of that join where the other "pm" is located. Either or both still results in an error.
As to Alvin, from what I can tell there's a, I think it's called a subquery? from a derived table.
the originator derived attribute.PersonMembership then looks like a subquery from it.
the end-user says nothing changed, meaning I would think that they would have to have gone in and changed the Module and don't think they know how or would attempt to modify anything after, if they did, clicking on Debug.
The SQL works in SSMS. Issue is the SQL in Excel not working. That's why I say does it matter because it runs successfully in SSMS.
This is why it's curious.
August 26, 2015 at 9:29 am
serviceaellis (8/26/2015)
J Livingston SQL (8/26/2015)
in addition to what Alvin has already said....your TSQL code IS different to the Excel function....(ignoring the &_ line continuation)TSQL
FROM attribute.PersonMembership LEFT OUTER JOIN USFSA.dbo.SOP10100 AS invWork
Excel
"FROM attribute.PersonMembership pm " & _
"LEFT JOIN USFSA.dbo.SOP10100 invWork
whether this is relevant.....I dont know
Removing that "pm" errors. Or at the end of that join where the other "pm" is located. Either or both still results in an error.
As to Alvin, from what I can tell there's a, I think it's called a subquery? from a derived table.
the originator derived attribute.PersonMembership then looks like a subquery from it.
the end-user says nothing changed, meaning I would think that they would have to have gone in and changed the Module and don't think they know how or would attempt to modify anything after, if they did, clicking on Debug.
The SQL works in SSMS. Issue is the SQL in Excel not working. That's why I say does it matter because it runs successfully in SSMS.
This is why it's curious.
The Excel query is being stored in a variable. Get the value of that variable and run it in SSMS. It's possible the error is in building the query text.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 26, 2015 at 9:55 am
What's proper when giving an Alias here?
Split2 = "FROM ( SELECT PersonId, ISNULL( OrganizationName, 'Individual' ) HomeClub, MembershipTypeId, InvoiceNumber, EndDate " & _
"FROM attribute.PersonMembership pm " & _
"LEFT JOIN USFSA.dbo.SOP10100 invWork ON InvoiceNumber = invWork.SOPNUMBE " & _
"LEFT JOIN USFSA.dbo.SOP30200 invHist ON InvoiceNumber = invHist.SOPNUMBE " & _
"JOIN lookup.MemberTypes mt ON mt.Id = PersonMembership.MembershipTypeId AND MemberGroup = 'Regular Member' " & _
"LEFT JOIN entity.Organization org ON org.Id = PersonMembership.OrganizationId " & _
"WHERE "
Split3 = " PersonMembership.CreatedDate > DATEADD( day, -120, GETDATE() ) AND " & _
" ( ( SUBSTRING( invWork.BACHNUMB, 1, 8 ) >= '" + StartingBatchDate + "' AND SUBSTRING( invWork.BACHNUMB, 1, 8 ) <= '" + EndingBatchDate + "' ) " & _
"OR ( SUBSTRING( invHist.BACHNUMB, 1, 8 ) >= '" + StartingBatchDate + "' AND SUBSTRING( invHist.BACHNUMB, 1, 8 ) <= '" + EndingBatchDate + "' ) ) ) pm "
Split4 = "JOIN entity.Person person ON person.Id = pm.PersonId " & _
If it's set here at the start on Split2 FROM attribute.PersonMembership pm
Would all other instances with fields from that table should be referenced with the Alias 'pm'?
August 26, 2015 at 11:07 am
Tried variations of the 'pm' and attribute.PersonMembership and still failing.
NOTE: When the 'pm' at the end of Split3 is removed, the error is on JOIN. All else the error is on 'pm'
August 26, 2015 at 12:05 pm
I also copied the SQL that works in SSMS and used in the Properties Definition for the Connection and split it in the Excel Module:
Sub Button1_Click()
Dim StartingBatchDate As String, EndingBatchDate As String, Split1 As String, Split2 As String, Split3 As String, Split4 As String, AdditionalMemberNumbers As String
StartingBatchDate = Range("B2")
EndingBatchDate = Range("B3")
With ActiveWorkbook.Connections("RegularMemberships").OLEDBConnection
Split1 = "SELECT DISTINCT person.MembershipNumber, " & _
"person.FirstName + ' ' + person.LastName AS NAME, person.FirstName, person.RegionId AS REGION " & _
"addr.StreetOne, addr.StreetTwo, ISNULL(unit.Description, '') + ' ' + addr.SubUnit AS 'Unit', addr.City + ', ' + addrState.Code + ' ' + addr.PostalCode AS 'CityStateZip' " & _
"addrState.Code AS 'State', lookup.Country.Description AS 'Country', CONVERT(char(10), pm.EndDate, 101) AS EndDate, addr.PostalCode, pm.HomeClub " & _
Split2 = "FROM (SELECT attribute.PersonMembership.PersonId, ISNULL(org.OrganizationName, N'Individual') AS HomeClub, attribute.PersonMembership.MembershipTypeId, attribute.PersonMembership.InvoiceNumber, attribute.PersonMembership.EndDate " & _
"FROM attribute.PersonMembership " & _
"LEFT OUTER JOIN USFSA.dbo.SOP10100 AS invWork ON attribute.PersonMembership.InvoiceNumber = invWork.SOPNUMBE " & _
"LEFT OUTER JOIN USFSA.dbo.SOP30200 AS invHist ON attribute.PersonMembership.InvoiceNumber = invHist.SOPNUMBE " & _
"INNER JOIN lookup.MemberTypes AS mt ON mt.Id = attribute.PersonMembership.MembershipTypeId AND mt.MemberGroup = 'Regular Member' " & _
"LEFT OUTER JOIN entity.Organization AS org ON org.Id = attribute.PersonMembership.OrganizationId " & _
"WHERE "
Split3 = "(attribute.PersonMembership.CreatedDate > DATEADD(day, - 120, GETDATE())) AND " & _
"(SUBSTRING(invWork.BACHNUMB, 1, 8) >= '20150601' AND SUBSTRING(invWork.BACHNUMB, 1, 8) <= '20150710' OR " & _
" SUBSTRING(invHist.BACHNUMB, 1, 8) >= '20150601' AND SUBSTRING(invHist.BACHNUMB, 1, 8) <= '20150710')) AS pm " & _
"INNER JOIN "
Split4 = "entity.Person AS person ON person.Id = pm.PersonId LEFT OUTER JOIN" & _
"lookup.TitlePrefix ON person.PrefixId = lookup.TitlePrefix.Id AND lookup.TitlePrefix.Id > 0 LEFT OUTER JOIN" & _
"lookup.TitleSuffix ON person.SuffixId = lookup.TitleSuffix.Id AND lookup.TitleSuffix.Id > 0 INNER JOIN " & _
"attribute.Address AS addr ON person.PrimaryAddressId = addr.Id LEFT OUTER JOIN " & _
"lookup.AddressSubUnit AS unit ON unit.Id = addr.SubUnitTypeId AND addr.SubUnitTypeId <> 0 LEFT OUTER JOIN " & _
"lookup.State AS addrState ON addr.StateId = addrState.Id LEFT OUTER JOIN " & _
"lookup.Country ON addr.CountryId = lookup.Country.Id AND addr.CountryId > 0 AND addr.CountryId <> 42" & _
"ORDER BY addr.PostalCode"
.CommandText = Split1 + Split2 + Split3 + Split4
End With
ActiveWorkbook.Connections("RegularMemberships").Refresh
End Sub
The error is now on 'attribute'
August 26, 2015 at 12:27 pm
just a guess, but cound the query might be truncating at exactly 2000 characters? it looks pretty darn close, maybe there's an odbc limitation somewhere?
can you switch to calling a stored procedure isntead?
Lowell
August 26, 2015 at 12:41 pm
Lowell (8/26/2015)
just a guess, but cound the query might be truncating at exactly 2000 characters? it looks pretty darn close, maybe there's an odbc limitation somewhere?can you switch to calling a stored procedure instead?
That would be odd that it would fail now when it worked in July, just a few weeks ago.
And far as they know, nothing changed.
It comes down to entering the date range and clicking on Refresh really.
Since I've verified that the SQL in the Connection Property Definition works fine without the Excel button module.
Hence the original question about setting the code to prompt for user input instead.
So I did create another worksheet and left out the button and using the built-in user input criteria option within the Microsoft Query Editor in Excel.
Hopefully they are ok with that. There were some oddities in the original code anyway.
1. Using CreatedDate vs. PaymentDate, which the user noted that's what it should be.
2. The link to another SQL db files on BATCHNUM, which is a text field and extrapolating the 1st 8 characters for the date. Instead of using the DOCDATE which, after some verifying, matches the 'date' in the BATCHNUM text field as well as the PaymentDate.
Since they are saying CreatedDate is wrong anyway, I just removed the link to the other SQL db (which also makes for faster updating) and eliminating the Module, I think it's ... cleaner overall. Hope they think so as well.
In the meantime though, if anyone finds the cause and knows how to fix it, great for everyone in the future.
August 27, 2015 at 9:52 am
Finally figured it out.
Changed from this
"INNER JOIN lookup.MemberTypes AS mt ON mt.Id = attribute.PersonMembership.MembershipTypeId AND mt.MemberGroup = 'Regular Member' " & _
"LEFT OUTER JOIN entity.Organization AS org ON org.Id = attribute.PersonMembership.OrganizationId " & _
to this:
"INNER JOIN lookup.MemberTypes AS mt ON mt.Id = pm.MembershipTypeId AND mt.MemberGroup = 'Regular Member' " & _
"LEFT OUTER JOIN entity.Organization AS org ON org.Id = pm.OrganizationId " & _
Oddly the other placements didn't error that wasn't using 'pm'
I also removed the CreatedDate line. They said it's incorrect anyway to use that field.
August 27, 2015 at 9:18 pm
Heh... stop it. Stop writing T-SQL in Excel macros. Write a stored procedure or the appropriate view in SQL Server, dedicate 2 well marked cells in Excel as the parameters that you'll pass to the stored procedure, and setup a button to call the stored procedure using "external data".
--Jeff Moden
Change is inevitable... Change for the better is not.
August 27, 2015 at 9:23 pm
lol!
I wouldn't have, though I don't know enough to do that anyway.
That's what I did. I created a View.
Then using the built-in parameter in Excel via MS Query for the date range prompt, viola, much better anyway.
Having to use both the SQL and the macro module and editing them seems cumbersome anyway.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply