Concatenate ???!!! 1

[Dineedshelp]

Hi,

I have a stored procedure that was created for us, but is now causing me hell of a lot of problems!!!!

I need to change various fields which I have managed to do, but the last one involves a sub query and I need to concatenate 2 fields which is doesn't seem to like.

Is there anyway of concatenating two fields when you are using a select query within a select query?

PLEASE HELP!!!

 

[SQLBill]

Can you show us what you are trying to do? And what error are you getting?

-SQLBill

 

[Dineedshelp]

SELECT l.LocationReference ,
c.ClientUniqueReference,
l.WardReference,
l.ApexAccountCode,
l.InvoiceContactName as 'ContactNames' ,
l.LocationName as 'Location_Company_Name',
l.InvoiceName as 'LocationAddress1',
l.InvoiceAddress1 as 'LocationAddress2' ,
l.InvoiceAddress2 as 'LocationAddress3',
(l.invoiceaddress3 + ', ' + l.invoicecity) as 'locationaddress3' ,
l.LocationCounty ,
l.InvoicePostcode as 'LocationPostcode',
l.LocationTelephone ,
l.LocationFax ,
c.SettlementDiscount,
c.SettlementDays,
c.DueDateMethod,
c.DueDateDays,
l.ApexSalesCode,
l.paymentratecode as 'Location_Category',
c.VATnumber,
c.VATCode,
'Payment Due in ' + cast (c.duedatedays as varchar (12)) + ' Days' as 'Terms',
l.TempIT_Status ,
l.LocationBranch,
l.MatchTimesheets,
c.BreakPaid,
c.WorkDurationHours,
c.BreakLength,
l.LocationCategory as 'UserSortKey2'

FROM [Ibis Client].dbo.Client_Reporting c
INNER JOIN
(SELECT
lr.ClientUniqueReference,
lr.LocationReference ,
wr.WardReference,
lr.ApexAccountCode,
lr.InvoiceContactName ,
lr.LocationName ,
lr.InvoiceName,
lr.InvoiceAddress1 ,
lr.InvoiceAddress2,
(lr.invoiceaddress3 + ', ' + lr.invoicecity) as 'locationaddress3' ,
lr.InvoicePostcode,
lr.LocationTelephone , lr.LocationFax ,lr.ApexSalesCode,lr.paymentratecode ,
lr.TempIT_Status , lr.LocationBranch, lr.MatchTimesheets , lr.LocationCategory
FROM [Ibis Client].dbo.Location_Reporting lr
LEFT OUTER JOIN [Ibis Client].dbo.Ward_Reporting wr ON
lr.LocationReference = wr.LocationReference) l ON
c.ClientUniqueReference = l.ClientUniqueReference
GROUP BY l.LocationReference , c.ClientUniqueReference, l.WardReference,l.ApexAccountCode,
l.InvoiceContactName , l.LocationName , l.InvoiceName, l.InvoiceAddress1 ,
l.InvoiceAddress2, l.InvoicePostcode,
l.LocationTelephone , l.LocationFax , c.SettlementDiscount, c.SettlementDays,
c.DueDateMethod, c.DueDateDays, l.ApexSalesCode, l.paymentratecode , c.VATnumber, c.VATCode,l.TempIT_Status ,
l.LocationBranch, l.MatchTimesheets, c.BreakPaid, c.WorkDurationHours, c.BreakLength, l.LocationCategory



this is the error I keep getting

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'invoiceaddress3'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'invoicecity'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'LocationCounty'.

Please help!!! I can cope with so much but this is confusing me!!!!

 

[SQLBill]

Okay, looks like the problem is in this section:

INNER JOIN
(SELECT
lr.ClientUniqueReference,
lr.LocationReference ,
wr.WardReference,
lr.ApexAccountCode,
lr.InvoiceContactName ,
lr.LocationName ,
lr.InvoiceName,
lr.InvoiceAddress1 ,
lr.InvoiceAddress2,
(lr.invoiceaddress3 + ', ' + lr.invoicecity) as 'locationaddress3' ,
lr.InvoicePostcode,
lr.LocationTelephone , lr.LocationFax ,lr.ApexSalesCode,lr.paymentratecode ,
lr.TempIT_Status , lr.LocationBranch, lr.MatchTimesheets , lr.LocationCategory
FROM [Ibis Client].dbo.Location_Reporting lr
LEFT OUTER JOIN [Ibis Client].dbo.Ward_Reporting wr ON
lr.LocationReference = wr.LocationReference) l

1. Where is LocationCounty???
2. invoiceaddress3 and invoicecity are concatenated to locationaddress3. So invoiceaddress3 and invoicecity are never returned as l (the table alias). Instead you return locationaddress3 to the outer Select.

-SQLBill

 

[Dineedshelp]

Thanks, its sorted, have added the locationcounty as I got rid of it for some reason!!!!

Another lesson learnt for me!!!! Will get there some day!!!!