November 22, 2006 at 2:11 pm
Hi,
I have a requirement to create a view with data from a query. The issue I am stuck with is how to create a unique field. Some of the data rows are :-
01/11/06 RES1
Nick
November 22, 2006 at 2:26 pm
What do you need to do exactly? I have no (or too many) idea on how to help you!
November 22, 2006 at 2:36 pm
Hi,
The view is a mixture of data from multiple tables I need to have a ROWID number that is unqiue for each record.
So... For example
The row containing data for Machine1 would be say :-
DESC, ID, DESC, POS1, POS2
PRT1, 307,MACH1, 307, 624
I would need to add a unique ROWID for each column so the data would look like
ROWID,DESC, ID, DESC, POS1, POS2
1, PRT1, 307,MACH1, 307, 624
2, PRT1, 3087,MACH1, 308, 624
etc etc
I hope that makes sense!
Nick
November 22, 2006 at 2:40 pm
That makes sens. Why do you need to have a row id in the data? There are ways to do it but they all are pretty costly. Can the application generate the ID when presenting the data?
November 22, 2006 at 2:45 pm
Hi, Unfortunately not. The data source I am using does not have a unique column that can be used for each record, however the target database requires one.
Sadly I have been told that data must have a unique field so the cost does not matter.
Thanks
Nick
November 22, 2006 at 2:54 pm
Is this an export / import task or will the application will have to be able to update those records (using the view as datasource), cause that ain't gonna be possible?
November 22, 2006 at 3:03 pm
I am exporting the data from one database into a data warehouse, ideally I would like the sql script to create the unique ID's as the application will not touch the data
Regards,
Nick
November 22, 2006 at 3:12 pm
SELECT IDENTITY(int, 1,1), <list of columns>
INTO #TempTable
FROM <your query>
SELECT * FROM #TempTable
_____________
Code for TallyGenerator
November 22, 2006 at 3:16 pm
So would that be...
SELECT IDENTITY(int, 1,1), DESC, ID, DESC, POS1, POS2 INTO #TempTable
FROM <your query>
so How would I apply that to a view?
regards,
Nick
November 22, 2006 at 4:27 pm
You must do it in SP.
You should not allow application to run queries on tables or views.
_____________
Code for TallyGenerator
November 22, 2006 at 8:35 pm
Agreed, if you are to transform the data, then transform it permanently and let the users see the final data, not something autogenerated at each run.
November 23, 2006 at 12:41 am
The data is going into a table for presentation to an ETL process, I am merely creating a new view containing data from multiple tables. The target table requires a unique reference for each item starting at record 1 and incrementing as the view is populated with data from the other tables.
So as I understand it from the threads above this is not possible?
Nick
November 23, 2006 at 6:39 am
Yes create a temp table with identity(1,1), then insert the data to that table. That'll create the unique id starting at one. Also you can truncate that table and reseed the identity column to 1 so you can rerun that part of the code.
Using a view to do this is possible but exponentially more costly as you add more rows. I would strongly advise against this. Especially if you have a lot of rows to transform.
November 23, 2006 at 7:13 am
Hi,
Here is the code that I have to date for the view
SELECT
CAST('' AS VARCHAR(20)) AS 'cre_nm',
create_date AS 'cre_dt',
update_date AS 'lst_updt_dt',
CAST('FLAXBY' AS VARCHAR(20)) AS 'lst_updt_nm',
-- CONVERT(VARCHAR(10),CAST(WW_EDIT_DATE AS DATETIME),112) AS 'lst_updt_dt',
CAST('N' AS VARCHAR) AS 'del_flg',
CAST('QTMS' AS VARCHAR) AS 'src_syst_nm',
CAST(machine_mode_id AS INT) AS 'mchn_mode_id',
CAST(machine_mode_desc AS VARCHAR)AS 'mchn_mode_dscr',
CAST('0' AS INTEGER) AS 'lds_dlay_cd',
CAST('0' AS INTEGER) AS 'lds_oper_typ'
FROM MACHINE_MODE
Any help would be appreciated.
Nick
November 23, 2006 at 7:15 am
Hi,
Here is the code that I have to date for the view
SELECT
CAST('' AS VARCHAR(20)) AS 'cre_nm',
create_date AS 'cre_dt',
update_date AS 'lst_updt_dt',
CAST('FLAXBY' AS VARCHAR(20)) AS 'lst_updt_nm',
-- CONVERT(VARCHAR(10),CAST(WW_EDIT_DATE AS DATETIME),112) AS 'lst_updt_dt',
CAST('N' AS VARCHAR) AS 'del_flg',
CAST('QTMS' AS VARCHAR) AS 'src_syst_nm',
CAST(machine_mode_id AS INT) AS 'mchn_mode_id',
CAST(machine_mode_desc AS VARCHAR)AS 'mchn_mode_dscr',
CAST('0' AS INTEGER) AS 'lds_dlay_cd',
CAST('0' AS INTEGER) AS 'lds_oper_typ'
FROM MACHINE_MODE
Any help would be appreciated.
Nick
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply