cancel
Showing results for 
Search instead for 
Did you mean: 

Saving Sql Server data saved in a datawindow

Former Member
0 Kudos

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?

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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

Answers (3)

Answers (3)

Former Member
0 Kudos

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

Former Member
0 Kudos

You are not saying what format you need to save to, but the dw.SaveAsFormattedText() and dw.SaveAsAscii () function will save computed fields.

Former Member
0 Kudos

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.