Skip to Content
0
Former Member
May 07, 2007 at 03:25 PM

Check This Code

12 Views

Post Author: Set_Shot_Dave

CA Forum: Data Connectivity and SQL

I've been tring to write SQL code in the command node of the Database Expert section of CR IX. Can someone look at this code and tell me whats wrong.

Create table #temp(Id int null,relationship_type varchar(25)null,major_key varchar(10)null,chapter varchar(10)null,fullname varchar(40)null,address_1 varchar (40)null,address_2 varchar (40)null,city varchar (25)null,state varchar(10) Null,zip varchar(10)null)

insert into #temp(Id ,relationship_type,major_key,chapter,fullname,address_1,address_2,city,state,zip)

Select DistinctName.Id,Relationship.RELATION_TYPE,Name.MAJOR_KEY,Name.CHAPTER,Name.FULL_NAME,Name_Address.ADDRESS_1,Name_Address.ADDRESS_2,Name_Address.CITY,Name_Address.STATE_PROVINCE,Name_Address.ZIP

FROM (NAC_USA.dbo.Name Name INNER JOIN NAC_USA.dbo.Relationship Relationship ON Name.ID=Relationship.ID) INNER JOIN NAC_USA.dbo.Name_Address Name_Address ON Name.MAIL_ADDRESS_NUM=Name_Address.ADDRESS_NUM

WHERE NOT (Name.MAJOR_KEY='AD' OR Name.MAJOR_KEY='US') AND Relationship.RELATION_TYPE='R'

update #temp set Id = Name.ID, fullname = Name.FULL_NAME, address_1= Name_Address.ADDRESS_1, address_2 = Name_Address.ADDRESS_2 , city = Name_Address.CITY, state= Name_Address.STATE_PROVINCE, zip = Name_Address.ZIP

FROM NAC_USA.dbo.Name Name NAC_USA.dbo.Relationship Relationship NAC_USA.dbo.Name_Address Name_Address

WHERE Relationship.RELATION_TYPE='ALTERNATE' and Name.MAJOR_KEY = NAME.MAJOR_KEYand Name.CHAPTER = Name.CHAPTER

I get invaild code on line 52.

Basically I'm tring to update a mailing list.