Retrieve records from in house data.

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

General SQL Server Forums

New to SQL Server Programming

Retrieve records from in house data.

Author

Topic

rvan
Starting Member

28 Posts

Posted - 2007-01-15 : 14:43:22

Hi, All

I'm only have permition to query table data and with local sql server installed. Also, capable link server from local machine to live sql server (Blue). Getting error type when ran a query that commons: Column name or number of supplied values does not match table definition. Others, ambiguous column name " ". Here are the information below before i ran the query.

In house data:-Table fields contain (address,city,state,zip,zip4,fips)
-All data type fields are character
-No Primary Key

Local machine table:
-Fipscodes (table) contain data was provided by customer that I inserted into my machine
and column fields (state,zip,fips) made Zip as Primary Key.

So, I wants to able run a query to retrieve data from live server by using some kind of join table with table(FipsCodes)locate in my local machine.

Query Statement:
SELECT h.lname,h.fname,h.street,h.city,h.state,h.zip,h.zip4,h.carroute,h.gender,
h.keycode,h.purprice,h.mortamt,h.phone,h.lender,h.recorddate,h.fips,h.pubmonth,
h.pubday,h.pubyr,h.trantype,h.condocode,h.transdate,h.ratetype,h.loantype,h.birth,
h.heritage,h.estcurrval,h.estcurreq,h.dpbc,n.state,n.fips

FROM blue5.Homeowner.dbo.homeowners AS h INNER JOIN FipsCodes AS n
ON h.FIPS = n.FIPS

WHERE state ='AL' AND fips='001' AND pubDate >= '12/1/2006' AND pubDate <='1/8/2007'

Error occur:
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'State'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'FIPS'.

, please help me solve this issue and thank you very much everyone.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-15 : 14:59:35

You haven't provided the entire sql statement. The error indicates you are running an INSERT statement, yet you didn't include that in your post.

quote:

In house data:-Table fields contain (address,city,state,zip,zip4,fips)
-All data type fields are character
-No Primary Key

No primary key!!! Oh my!

Tara Kizer

rvan
Starting Member

28 Posts

Posted - 2007-01-15 : 18:02:59

quote:
Originally posted by tkizer

You haven't provided the entire sql statement. The error indicates you are running an INSERT statement, yet you didn't include that in your post.

quote:

In house data:-Table fields contain (address,city,state,zip,zip4,fips)
-All data type fields are character
-No Primary Key

No primary key!!! Oh my!

Tara Kizer

==================================================================
Hi,Tara

I can't do any modify on live data remember that but only local machine server was able let me create my own table call "FipsCodes" with Primary Key for column Zip. Kind of notice the problem because I don't have Primary Key in original data.

I don't want to type multiple fips code in my WHERE clause statement. Here below 2 tables structure:

FipsCode Table: in local machine
CREATE TABLE [dbo].[FipsCodes] (
[State] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Zip] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FIPS] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
=================================================
NewHomeowners table: live server
CREATE TABLE [dbo].[HomeOwners] (
[Lname] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fname] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Street] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Zip] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Zip4] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CarRoute] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Gender] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Keycode] [char] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PurPrice] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MortAmt] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Lender] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecordDate] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FIPS] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PubMonth] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PubDay] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PubYr] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TranType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ConDoCode] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TransDate] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RateType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LoanType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Birth] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Heritage] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EstCurrVal] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EstCurrEq] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DPBC] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[append_date] [smalldatetime] NULL ,
[Latitude] [float] NULL ,
[Longitude] [float] NULL ,
[pubDate] [smalldatetime] NULL ,
[RecNo] [int] NOT NULL ,
[mdAddress] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mdCity] [varchar] (28) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mdState] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mdZip] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mdPlus4] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mddpb] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mdstatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

--------------------
Thank you
ryan,

RV

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-15 : 18:09:30

I see you modified your original post so that the error is different. The error that you have posted now means you need to tell the query which table to use for State and Fips columns as they exist in more than one table. So put a table alias before them.

WHERE AliasName.Column...

Tara Kizer

rvan
Starting Member

28 Posts

Posted - 2007-01-15 : 19:15:58

quote:
Originally posted by tkizer

I see you modified your original post so that the error is different. The error that you have posted now means you need to tell the query which table to use for State and Fips columns as they exist in more than one table. So put a table alias before them.

WHERE AliasName.Column...

Tara Kizer

In house data table live server: NewHomeOwners
My local machine table: FipsCodes <<<<---for example, customer provide this table. What would you do to it?>>>>>

thank you
ryan,

RV

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-15 : 19:18:40

I don't understand what you are asking. You just need to use the table alias on the columns you are getting errors on since they appear in more than one table.

Tara Kizer

rvan
Starting Member

28 Posts

Posted - 2007-01-16 : 13:09:38

quote:
Originally posted by tkizer

I don't understand what you are asking. You just need to use the table alias on the columns you are getting errors on since they appear in more than one table.

Tara Kizer

Good Morning!
Tara,

Questions: Why would I have to create "alias name on table" for WHERE clause? Can you look at my Where clause statement and tell what wrong with it?

thank you
ryan,

RV

X002548
Not Just a Number

15586 Posts

Posted - 2007-01-16 : 13:12:13

She did. If a column belongs with the same name in more than 1 table, you need to have an alias.

Do you know what an alias is?

And no, it's not a tv show.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

rvan
Starting Member

28 Posts

Posted - 2007-01-17 : 19:08:19

quote:
Originally posted by tkizer

I don't understand what you are asking. You just need to use the table alias on the columns you are getting errors on since they appear in more than one table.

Tara Kizer

Hi, Tara

First of all, I wants to said, thank you for helping me solve the problem. This will save alot my time by query without menually enter the data.

Thank You against!

RV

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources