Link table and view by CAST() field

Question

Post reply

Link table and view by CAST() field

awsiemieniec

Valued Member

Points: 74
More actions
May 24, 2011 at 2:11 pm

#238080

I'm working with two DBs (DB1, DB2) within SQL Server Management Studio (SQL 2008 R2)
In DB1 I have a table, db1.ERate (contains employee payroll info). The fields I use are id (FK, varchar(10), not null) and rate (decimal(14,6), null)
In DB2 I have a view that has a table called empMain that I am linking DB1.ERate to. In table empMain of DB2 the linking field is called employeeid (nvarchar(50), not null).
Both fields are populated with the same data, but of different data types (varchar and nvarchar) If I add a field in db1.ERate of datatype varchar and populate it with data, the link between the table and the other table works fine and the data is displayed. If the link between the two tables is mismatched between varchar and nvarchar the query doesn't display any results.
So, I created a simple view that only has the DB1.ERate.id and DB1.ERate.rate in it and I CAST(id AS nvarchar(50)) to force it to type nvarchar. I rerun the main query (view) but change out the DB1.ERate table with the view I just created (that has the CAST()) and still no data is displayed.
So the question is, how do I make the data in DB1.ERate.id of type 'whatever' so that it correctly links to the data in DB2.empMain.employeeid of datatype nvarchar? I can't modify any table structure because the database is sync'd across the internet to a payroll vendor and if any table structure is different on "our end", payroll doesn't work.
Suggestions?
Thanks!
AWS

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Leju Geevarghese SSC Eights! Points: 982 More actions · Answer 1

Can you please post the table structure with sample data.

Also the code that is linking the 2 tables

---------------------------------------------------------------------------------------
It begins by taking the first step.

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 2

Yes, please post DDL, DML to create sample data and example of expected results:

http://qa.sqlservercentral.com/articles/Best+Practices/61537/[/url]

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

awsiemieniec Valued Member Points: 74 More actions · Answer 3

Here is db1.ERate table:

***********************************

CREATE TABLE [dbo].[ERate](

[co] [varchar](10) NOT NULL,

[id] [varchar](10) NOT NULL,

[rateCode] [varchar](10) NOT NULL,

[salary] [decimal](14, 2) NULL,

[rate] [decimal](14, 6) NULL,

[ratePer] [varchar](10) NULL,

... ETC. Rest of fields not necessary for example

CONSTRAINT [PK_ERate] PRIMARY KEY NONCLUSTERED

(

[guidfield] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]

) ON [PRIMARY]

***********************************

Here is DB2.empMain table:

CREATE TABLE [dbo].[empMain](

[employee_id] [int] IDENTITY(1,1) NOT NULL,

[company_id] [int] NOT NULL,

[employeeid] [nvarchar](50) NOT NULL,

[unused2] [nvarchar](50) NOT NULL,

... ETC. Other fields removed

CONSTRAINT [PK_empMain] PRIMARY KEY NONCLUSTERED

(

[employee_id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

***********************************

Here is the view I am using that is replacing DB1.ERate:

SELECT rate, CAST(id AS nvarchar(50)) AS employeeid

FROM dbo.ERate

***********************************

Here is the main view that I am building my data source from that is not yielding any results (this is being run from within DB2):

SELECT dbo.Job.JobNumber AS JobId, dbo.empMain.firstname, dbo.empMain.lastname, dbo.timeCard.total_hr * DB1.dbo.sscERate.rate AS Hrs,

DATEADD(DAY, 7 - DATEPART(WEEKDAY, dbo.timeCard.timecard_dt), dbo.timeCard.timecard_dt) AS WeekEnding

FROM dbo.empMain INNER JOIN

dbo.timeCard ON dbo.empMain.employee_id = dbo.timeCard.employee_id INNER JOIN

dbo.Job ON dbo.timeCard.job_id = dbo.Job.job_id INNER JOIN

DB1.dbo.sscERate ON dbo.empMain.employeeid = DB1.dbo.sscERate.employeeid

ORDER BY WeekEnding DESC, JobId

***********************************

When I added a field to DB1.ERate and made it nvarchar(50), everything worked fine. Little did I know payroll was unable to process because the sync of data between here and the payroll vendor was failing. So I had to remove the new field and come up with an alternative that did not include a table structure alteration.

Thanks for your assistance.

Leju Geevarghese SSC Eights! Points: 982 More actions · Answer 4

Why even create a view, do a join between the tables and do a Cast(fieldname, int). See example below

SELECT dbo.Job.JobNumber AS JobId, dbo.empMain.firstname, dbo.empMain.lastname, dbo.timeCard.total_hr * DB1.dbo.sscERate.rate AS Hrs,

DATEADD(DAY, 7 - DATEPART(WEEKDAY, dbo.timeCard.timecard_dt), dbo.timeCard.timecard_dt) AS WeekEnding

FROM dbo.empMain

INNER JOIN dbo.timeCard

ON dbo.empMain.employee_id = dbo.timeCard.employee_id

INNER JOIN dbo.Job

ON dbo.timeCard.job_id = dbo.Job.job_id

INNER JOIN DB1.dbo.ERate

ON dbo.empMain.employeeid = Cast(DB1.dbo.ERate.id, int)

ORDER BY WeekEnding DESC, JobId

---------------------------------------------------------------------------------------
It begins by taking the first step.

awsiemieniec Valued Member Points: 74 More actions · Answer 5

Thanks, that does it. There was a "TEST" user in the empMain table that when I did that before I would get a conversion error converting "TEST" to int data type. I changed the ID from "TEST" to some number then applied your linking suggestion and now we're good.

Thanks for the quick info!