 |
| SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
|
June 10th, 2004, 10:26 AM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
table variables and udfs
I am trying to create a table variable for doing some record processing.
Code:
-- Create temporary table for CityStateZIP
declare @tblcsz table
(
kCityStateZIP int
,sCSZ varchar(255) NULL
,kCountry varchar(10)
,kAddress int
,iFillLoc smallint
)
insert @tblcsz
select
a.f1 as kCityStateZIP
, a.f2 as sCSZ
, a.f3 as kCountry
, a.f4 as kAddress
, a.f5 as iFillLoc
from
(
select
tcsz.PK_CityStateZIP as f1
, NullIf(LTrim(RTrim(IsNull(NullIf(LTrim(RTrim(tcsz.CityName)) +
', ', ', '), '') + IsNull(NullIf(LTrim(RTrim(tcsz.StateAbbr)), ''), '') +
' ' + IsNull(NullIf(LTrim(RTrim(tcsz.FK_ZIP5)), ''), ''))), '')) as f2
, tcsz.FK_Country as f3
, ta.PK_Address as f4
-- The following line fails with error - Line 90: Incorrect syntax near '.'.
, dbo.f_FindAllData(ta.AddrLine1, ta.AddrLine2, ta.AddrLine3, ta.AddrLine4) as f5
from dbo.t_CityStateZIP tcsz
left join t_Address ta on tcsz.PK_CityStateZIP = ta.FK_CityStateZIP
where tcsz.FK_Country NOT IN ('USA', 'ALASK', 'HAWAI')
and tcsz.StateAbbr not in(select pk_state from t_state
) a
-- the following line fails with error - Line 95: Incorrect syntax near 'a'.
having sCSZ is not null
order by tcsz.PK_CityStateZIP
)
can anyone make any suggestions
Thank you in advance!
Rand
__________________
Rand
|
|
June 10th, 2004, 11:14 AM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
|
|
Hello,
Could that error be coming with your function?
Brian
|
|
June 10th, 2004, 11:38 AM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
The function works fine independently or with a temporary table. It accepts four fields and returns an integer [0 - 15] indicating which fields (if any) have data.
If I replace the function with an integer (-1), I get the same error.
Rand
Rand
|
|
June 11th, 2004, 03:13 AM
|
|
Authorized User
|
|
Join Date: Jun 2003
Posts: 95
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
I may be wrong, but I thing the HAVING clause needs to stay together with a GROUP BY clause.
Also, I would check the following code for possible parenthesis error, think there is one more ) than (
Code:
NullIf(LTrim(RTrim(IsNull(NullIf(LTrim(RTrim(tcsz.CityName)) +
', ', ', '), '') + IsNull(NullIf(LTrim(RTrim(tcsz.StateAbbr)), ''), '') +
' ' + IsNull(NullIf(LTrim(RTrim(tcsz.FK_ZIP5)), ''), ''))), '')) as f2
|
|
|
