Only one expression can be specified in the select

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Only one expression can be specified in the select

Author

Topic

ErinKlanderman
Starting Member

2 Posts

Posted - 2011-08-25 : 10:37:35

I am trying to write a subquery in my query that will provide me with additional information. I need to provide the BillTo address and ShipTo address for a customer on 1 report - the issue is that for each invoice a record is created for each address type.
Here is the data example:
C100177 47825 275th St Harrisburg 57032-5517 SD B
C100177 1794 250th St Callaway 54684-1794 WI S

So it would appear:
C100177 47825 275th St Harrisburg 57032-5517 SD 1794 250th St Callaway 54684-1794 WI
(obviously I have other fields, just shortened it up for display purposes

)

I need both of these addresses for this customer to appear as one line in the results set. This is the code I have put together. Running each query on its own works, but I can't figure how to combine them?

SELECT DISTINCT
T1.CardCode as CustomerNumber,
T1.CardName as CustomerName,
T1.CNtctPrsn as ContactPerson,
T1.Phone1 as Phone,
T1.GroupCode as CustomerType,
T2.Street as BillToAddress1,
T2.Block as BillToAddress2,
T2.City as BillToCity,
T2.State as BillToState,
T2.ZipCode as BillToZip,
T2.County as BilltoCounty,
T2.Country as BillToCountry,
count(distinct(T3.DocEntry)) as NumberOfInvoices,
sum(distinct(T4.GTotal)) as InvoiceTotal,
T2.AdresType,
(SELECT S2.Street as ShipToAddress1,
S2.Block as ShipToAddress2,
S2.City as ShipToCity,
S2.State as ShipToState,
S2.ZipCode as ShipToZip,
S2.County as ShiptoCounty,
S2.Country as ShipToCountry
FROM [ASAP].[dbo].[OCRD] as S1
INNER JOIN [ASAP].[dbo].[CRD1] as S2
ON S1.CardCode = S2.CardCode
WHERE S2.AdresType = 'S' )
FROM [ASAP].[dbo].[OCRD] as T1
INNER JOIN [ASAP].[dbo].[CRD1] as T2 ON T1.CardCode = T2.CardCode
INNER JOIN [ASAP].[dbo].[OINV] as T3 on T1.CardCode = T3.CardCode
INNER JOIN [ASAP].[dbo].[INV1] as T4 on T3.DocEntry = T4.DocEntry
WHERE T2.AdresType = 'B' and T1.CardCode = 'C100177'
AND T3.DocDate >= '2011-01-01 00:00:00.000'
AND T3.DocDate <= '2011-12-31 00:00:00.000'
GROUP BY
T1.CardCode,
T1.CNtctPrsn,
T1.CardName,
T1.Phone1,
T1.GroupCode,
T2.Street,
T2.Block,
T2.City,
T2.ZipCode,
T2.County,
T2.State,
T2.Country,
T2.AdresType

EK

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-25 : 12:35:53

[code]SELECT T1.CardCode as CustomerNumber,
T1.CardName as CustomerName,
MAX(CASE WHEN AdresType = 'B' THEN Street ELSE NULL END) AS BillToAddress1,
MAX(CASE WHEN AdresType = 'B' THEN Block ELSE NULL END) AS BillToAddress2,
....
MAX(CASE WHEN AdresType = 'S' THEN Street ELSE NULL END) AS ShipToAddress1,
MAX(CASE WHEN AdresType = 'S' THEN Block ELSE NULL END) AS ShipToAddress2,
FROM [ASAP].[dbo].[OCRD] as T1
INNER JOIN [ASAP].[dbo].[CRD1] as T2 ON T1.CardCode = T2.CardCode
INNER JOIN [ASAP].[dbo].[OINV] as T3 on T1.CardCode = T3.CardCode
GROUP BY T1.CardCode ,
T1.CardName
[/code]

include other common columns in GROUP BY
and also ship and billto inside similar MAX(CASE..)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ErinKlanderman
Starting Member

2 Posts

Posted - 2011-08-25 : 14:40:48

Thank you so MUCH!!!
That worked perfectly!

EK

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-25 : 23:53:16

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources