on 07-28-2015 7:05 PM
I have a datawindow, that's pulling some data from a sql server database.
But... on the exported file, one of the columns needs to be a counter of the row.
It's harder to produce a row # in sqlserver... And... doing a saveas only
saves the data that was produced from the Query, and not the computed fields in the datawindow
Does anyone have any ideas?
What makes this worse, is I have to do a Union of 2 queries.
Select *
from XXXX
where audit = 'F300000'
Union
Select *
from YYYY
where audit = 'F300000'
I think I need to do something like this
Select row_number() over (), *
(select *
from XXXX
union
Select *
from YYYY) test
where test.audit = 'F30000'
Is there any easier way to do this? can computed fields be saved to the excel file?
Hi Scott;
1) Use the SS "Row_Number" method.
2) use a dummy computed column in the SQL. Then on the RetrieveEnd event of the DC/DS, use a SetItemNumber () with DW GetRow() within a loop to populate the SQL column.
Either one of those should produce a nice export.
HTH
Regards ... Chris
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Yet another, albeit longer-winded, approach would be to use a stored procedure. You could define your result set as a temp table, and declare the row number column as Identity(1,1)... Or, if you're doing multiple inserts, you can use an integer variable to Update the rows...
DECLARE @li_ROW int
SET @li_ROW = 0
-- initial insert
SELECT ROW_NUM,
ALL_OTHER_DATA
INTO #TEMP_TABLE
FROM YOUR_DATA_SOURCE
-- handle other inserts...
-- number everything
UPDATE #TEMP_TABLE
SET ROW_NUM = @li_ROW,
@li_ROW = @li_ROW + 1
-- result set
SELECT ROW_NUM,
ROW_1,
ROW_2,
ETC
FROM #TEMP_TABLE
-- free
DROP TABLE #TEMP_TABLE
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You are not saying what format you need to save to, but the dw.SaveAsFormattedText() and dw.SaveAsAscii () function will save computed fields.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
If you want a sequential row numbering in the exported file, I suggest doing the following:
1. Use a dummy column in the SQL. e.g. row_number.
2. Create a compute field, e.g. compute_1, and just assign a value of 1.
3. Upon exporting the data, use the following code:
dw.modify('compute_1.expression="getrow()")
dw.object.row_number.primary = dw.object.compute_1.primary
dw.modify('compute_1.expression="1")
The reasons that I will do the line 1 and 3 of the code is to optimize performance with compute field recalculation. The reason that I will insert the code 'upon' exporting the data is to avoid the numbers to become out of order when you sort or filter the data.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.