March 6, 2009 at 11:05 am
I have a view across all similar databases but in one of the database which is considered as Old does not match with other databases, where it has less columns than other. How can i manage the view in the old database so that when a query is running against it, it shudnt fail. say when i do some join,union etc against that old view.
example:
vwOld - Select RevID,JoinDt,Budget,Available, RevCode from Revenue
vwNew - Select RevID,JoinDt,Budget,Available, RevCode from Revenue
From the above vwNew has all the column in Revenue table but vwOld does not have columns Available(bit) and RevCode(Char) but still my jobs which may do join or union against these views should not fail.What measures i need to take.
thanks
March 6, 2009 at 11:16 am
Hello
A little bit more information would help. 😉
What are the "jobs" doing?
If you want to relize a UNION you can do:
[font="Courier New"]
DECLARE @old TABLE (id INT, fld1 VARCHAR(100))
DECLARE @new TABLE (id INT, fld1 VARCHAR(100), fld2 VARCHAR(100))
INSERT INTO @old VALUES (1, 'Hello World')
INSERT INTO @new VALUES (2, 'New', 'World')
SELECT id, fld1, fld2
FROM @new
UNION ALL
SELECT id, fld1, CONVERT(VARCHAR(100), NULL)
FROM @old
[/font]
If you runnin dynamic SQL jobs use the INFORMATION_SCHEMA.COLUMNS.
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
March 6, 2009 at 11:40 am
can i do something like this.
vwNew - Select RevID,JoinDt,Budget,Y as Available,5 as RevCode from Revenue
March 6, 2009 at 11:45 am
Mike Levan (3/6/2009)
can i do something like this.vwNew - Select RevID,JoinDt,Budget,Y as Available,5 as RevCode from Revenue
Yes, why don't you just try? Just quote the Y with "'" as 'Y'
Select RevID,JoinDt,Budget,'Y' as Available,5 as RevCode from Revenue
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
March 6, 2009 at 11:51 am
Yes, why don't you just try? Just quote the Y with "'" as 'Y'
whats the difference ??
I am just worried if i do a join or union on that column will it kick any error if it has different datatypes.
March 6, 2009 at 11:59 am
Try the statement on one of the old tables which do not have the columns "Available" and "RevCode". The difference is the quoted Y.
Select RevID,JoinDt,Budget,'Y' as Available,5 as RevCode from Revenue
The UNION problem can be solved with my previous sample.
JOINs may be a more complicated problem. Can you explain the business case?
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
March 6, 2009 at 6:00 pm
Users may do different type of joins say for instance new view can be join with old view on a column which old view doesnt have , like new view may have empid but old view may not have but still they may need to join empid as there scripts are build in that way which can not be changed.
would like to know how i can handle so that their job wont fail.
March 6, 2009 at 7:45 pm
Maybe the most simple way would be to create views for all the tables you have to handle and let the users JOIN this view instead of the tables. So you can add the missing columns as pseudo columns within the views. So the user do not have to know which columns are really available. If your new tables get new columns again you can simply add them to the views of the old tables.
How to Post Data/Code to get the best Help How to Post Performance Problems
March 7, 2009 at 7:48 am
pseudo columns ???
can you explain that with real table structure, am not sure how that will work.
thanks
March 7, 2009 at 8:25 am
Well
[font="Courier New"]
IF (OBJECT_ID('old_table') IS NOT NULL)
DROP TABLE old_table
CREATE TABLE old_table (id INT, oldField VARCHAR(100))
IF (OBJECT_ID('new_table') IS NOT NULL)
DROP TABLE new_table
CREATE TABLE new_table (id INT, oldField VARCHAR(100), newField VARCHAR(100))
GO
INSERT INTO old_table VALUES (1, 'old in old')
INSERT INTO new_table VALUES (2, 'new in old', 'new in new')
GO
IF (OBJECT_ID('v_old') IS NOT NULL)
DROP VIEW v_old
GO
CREATE VIEW v_old
AS
SELECT id, oldField, CONVERT(VARCHAR(100), 'pseudo value') newField FROM old_table
GO
IF (OBJECT_ID('v_new') IS NOT NULL)
DROP VIEW v_new
GO
CREATE VIEW v_new
AS
SELECT id, oldField, newField FROM new_table
GO
SELECT * FROM v_old
UNION ALL
SELECT * FROM v_new
[/font]
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
March 7, 2009 at 2:24 pm
As you said can the 'pseudo value' be null? hoes does it " NULL" effect in joins or unions.
thanks
March 7, 2009 at 2:51 pm
As we know CONVERT and CAST has similar functionality but which one wud be best? and is there any diffrence between them.
Also how would i change a data type " money" for psedo value.
March 7, 2009 at 3:21 pm
Mike Levan (3/7/2009)
As you said can the 'pseudo value' be null? hoes does it " NULL" effect in joins or unions.thanks
Sure NULL values will not be included if you join this columns; except you define it as join criterion.
How to Post Data/Code to get the best Help How to Post Performance Problems
March 7, 2009 at 3:26 pm
Mike Levan (3/7/2009)
As we know CONVERT and CAST has similar functionality but which one wud be best? and is there any diffrence between them.
Basically they have the same job but the CONVERT function has more options (the style argument) as you can see in the BOL. So I only use the CONVERT because it does everything the CAST does with additional features.
Also how would i change a data type " money" for psedo value.
You can convert every value - as long as the value is valid.
Just:
-- For NULL value
SELECT CONVERT(NULL, MONEY)
-- OR for any other value
SELECT CONVERT(123.45, MONEY)
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
March 7, 2009 at 3:46 pm
Not my job to judge, but "Well Done!", Flo. 🙂
--Jeff Moden
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply