Subquery / Derived table field error?

Transact-SQL
1

Trying to figure out why it's erroring on pm.OrganizationName' or at least that's what it seems like is the issue?

Here's the subquery, using a derived table "pm".
When I remove it, it works but having it it errors.

Anyone know what can be fixed so that the field can remain in the formula?

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. 'OrganizationName' FROM (SELECT attribute.PersonMembership.PersonId, ISNULL(org.OrganizationName, N'Individual') AS 'OrganizationName', attribute.PersonMembership.MembershipTypeId, attribute.PersonMembership.InvoiceNumber, attribute.PersonMembership.EndDate FROM attribute.PersonMembership LEFT OUTER JOIN

2

I removed the ' ' around OrganizationName and that seems to have fixed it from SSMS,
however in Excel the function is erroring on 'pm'? Excel is the reporting tool for this.

Incorrect syntax near 'pm'

Assuming it's at "FROM attribute.PersonMembership pm " & _?

[code]
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 'Unit', addr.City + ', ' + addrState.Code + ' ' + addr.PostalCode 'CityStateZip', addrState.Code 'State', Country.Description 'Country', " & _
"pm.OrganizationName 'HomeClub', CONVERT( char(10), pm.EndDate, 101 ) EndDate, addr.PostalCode "
Split2 = "FROM ( SELECT PersonId, ISNULL( OrganizationName, 'Individual' ) OrganizationName, 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 " & _
"LEFT JOIN lookup.TitlePrefix ON person.PrefixId = TitlePrefix.Id AND TitlePrefix.Id > 0 " & _
"LEFT JOIN lookup.TitleSuffix ON person.SuffixId = TitleSuffix.Id AND TitleSuffix.Id > 0 " & _
"JOIN attribute.Address addr ON person.PrimaryAddressId = addr.Id " & _
"LEFT JOIN lookup.AddressSubunit unit ON unit.id = Addr.SubunitTypeId AND addr.SubunitTypeId <> 0 " & _
"LEFT JOIN lookup.State addrState ON addr.StateId = addrState.Id " & _
"LEFT JOIN lookup.Country ON addr.CountryId = 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[/code]

3

Incorrect syntax near 'pm'

that's and Excel error message?

4

Yes, I am getting that part in the Excel (.xlsm) file

Run-time error 1004

Incorrect syntax near 'pm'

5

after

.CommandText = Split1 + Split2 + Split3 + Split4

Can you add a new line

Console.WriteLine(.CommandText)

and post the results?

6

like this?

.CommandText = Split1 + Split2 + Split3 + Split4 Console.WriteLine (.CommandText)

I get a
Run-time error 424
Object required

7

Yes, that's correct. Not sure why that fails. Is this a VB program or VBA?

8

Thinking VBA?

It's a marco in Excel using that in the module

What I'm also confused about is that the original line

                         pm. 'OrganizationName' AS 'HomeClub'
FROM            (SELECT        attribute.PersonMembership.PersonId, ISNULL(org.OrganizationName, N'Individual') AS 'OrganizationName',

errors yet when I replace 'OrganizationName' with OrganizationName (without the ' ') it doesn't error in SQL query.

And the 'pm' error in the Excel module I don't understand.
Don't know if it's related but at least i don't get the error in the SQL query but since the report is ran in Excel with that module and erroring on the 'pm' is confusing.

When I remove the first part of that (uncheck Output or simply removing the field it work in SQL but not when the field needs to be displayed. It's odd.
pm. 'OrganizationName' AS 'HomeClub'

9

OK --

pm. 'OrganizationName' AS 'HomeClub'

is not valid SQL

pm. OrganizationName AS 'HomeClub'

is valid SQL

10

Further, with the 1st part removed vs. changing the 'OrganizationName' without the ' ' where the Output works, it produces 18 more records.

Right, that's what makes it work in SQL query. But still an issue in Excel and for some reason producing different number of records?

So when I change the original, that's erroring in SQL to

                         pm.OrganizationName AS HomeClub
FROM            (SELECT        attribute.PersonMembership.PersonId, ISNULL(org.OrganizationName, N'Individual') AS OrganizationName,

it no longer errors but I get 18 more records as the result having it Output.
Though at this point main issue here is Outputting this field and making it work in Excel.

Can't figure out the issue in Excel module still. Why would it error on 'pm' in the Module?

11

Note: this

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 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 (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 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

works when I replaced in Excel.
Aside from the Button1_Click / Module not working.

What could be edited or perhaps re-written with the above SQL in the Excel Module?

12

No idea (this is not an Excel forum). However could you post the entire query just before your macro sends it to SQL for execution?

13

see above. the SQL is working just not the Excel Module

and here's the Excel Module

[code]
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 'Unit', addr.City + ', ' + addrState.Code + ' ' + addr.PostalCode 'CityStateZip', addrState.Code 'State', Country.Description 'Country', " & _
"pm.HomeClub, CONVERT( char(10), pm.EndDate, 101 ) EndDate, addr.PostalCode "
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 " & _
"LEFT JOIN lookup.TitlePrefix ON person.PrefixId = TitlePrefix.Id AND TitlePrefix.Id > 0 " & _
"LEFT JOIN lookup.TitleSuffix ON person.SuffixId = TitleSuffix.Id AND TitleSuffix.Id > 0 " & _
"JOIN attribute.Address addr ON person.PrimaryAddressId = addr.Id " & _
"LEFT JOIN lookup.AddressSubunit unit ON unit.id = Addr.SubunitTypeId AND addr.SubunitTypeId <> 0 " & _
"LEFT JOIN lookup.State addrState ON addr.StateId = addrState.Id " & _
"LEFT JOIN lookup.Country ON addr.CountryId = 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[/code]

14

Yup. Don't want to see the Excel Module. I want to see the SQL that is actually sent to SQL Server. That is what does .CommandText contain just before End With

15

This is the SQL:

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 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 (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 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

How about this ...
How do I change this for user input in the date range portion?

WHERE (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

16

I may not be seeing it, but that doesn't look any different to the larger block of code immediately above?

Personally I'm never happy mixing AND and OR without parenthesis - i.e. in this snippet of there WHERE clause:

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'
)

I would add parenthesis as follows:

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'
	)

I thought SQL was deprecating the use of single quotes around alias names? We use square brackets for all Alias Names now:

	xxx AS [MyAliasName]

Couple of points in case or interest:

attribute.PersonMembership.CreatedDate > DATEADD(day, - 120, GETDATE()))

compared CreatedDate against 120 days ago to-the-millisecond with current time. If CreatedDate is a DATETIME, rather than just DATE, datatype then you might prefer to compare against a date "rounded" to midnight so that things exactly 120 days ago are not only included for part of the day.

If CreatedDate is a DATE datatype its probably fine (either in, or out) although worth checking whether a CreatedDate exactly 120 days ago behaves (i.e. is either Included or EXcluded) as you need it to be.

SUBSTRING(invWork.BACHNUMB, 1, 8)

always worries me as a means of storing a date. What's to stop you getting, for example, 30-Feb in there? SQL is much happier, and can validate data much better, if date columns are stored as DATE or DATETIME datatypes :smile:

LEFT OUTER JOIN lookup.TitlePrefix
		 ON person.PrefixId = lookup.TitlePrefix.Id
		AND lookup.TitlePrefix.Id > 0

the "lookup.TitlePrefix.Id > 0" part is a bit wacky!

person.FirstName + ' ' + person.LastName AS NAME

Are both columns always present? i.e. if ONE of them could be NULL then that will cause the composite [name] result to be NULL. If that is an issue you could combine them with IsNull() instead:

IsNull(person.FirstName + ' ', '') + IsNull(person.LastName, '') AS NAME

or, if you would like [Name] to be NULL if both elements are blank / null then:

NullIf(
    RTrim(
            IsNull(person.FirstName + ' ', '') + IsNull(person.LastName, ''
        ),
    '') AS NAME