Post reply

Including or Excluding columns based on the value of another column

  • March 31, 2009 at 9:28 am

    #71407

    We have a very simple DTS script that does a transformation and exports records to an ASCII file (SQL 2000).

    We have to change the DTS transformation to account for the following:

    A column in the table being exported will contain a Y or N value. Based on that value, I must INCLUDE or EXCLUDE certain other columns from that table in the same transformation. All columns are in 1 single table.

    So I will have a column called "SendSpecialData" - set to Y or N (or NULL)

    The special columns are called "ColumnS1" and "ColumnS2"

    If SendSpecialData is set to N, my transformation should not include the data in ColumnS1 or ColumnS2 - the data in these columns for records set to N will just contain NULL or blank values.

    If SendSpecialData is set to Y, the data for ColumnS1 and ColumnS2, for that record, is exported into the ASCII file.

    I know this can be done, but I cannot determine the exact syntax. This is running in a DTS transformation. Many examples I have come across base the condition on the value of a column in a 2nd table, not on column in the same table. The data in the actual column "SendSpecialData" is not being exported. It is only to be used as a conditional selector for the other columns.

    Any assistance would be appreciated.

    Thanks

    --

    Scot

  • May 7, 2009 at 8:23 am

    #990374

    Scot,

    I have a question about what you are asking. Are you doing this filtering of the columns for multiple rows?

    In other words, the column that has a Y or N, will determine if the other columns are being imported. Are there multiple rows and you are wanting to do this row by row?

    If so, then in DTS, you would want to use the multi-phase data pump. You can read records and code for each row as they are imported.

    For more information, see this URL:

    http://www.databasejournal.com/features/mssql/article.php/10894_3300201_2/SQL-Server-2000-DTS-Part-8---DTS-Designer-Tasks---Transform-Data-Task.htm

    Another way, again if you want to go row by row:

    1) Create two tables (could be template tables)

    2) In each table create an identity field of 1,1 to increate the value by one

    3) Load all the table in the first table.

    4) Create a while loop with a counter to traverse the first table

    5) If the column is Y, then copy that record and insert that into the second table

    If you are reading the column with a Y or N once and it will determin the other columns to import,

    you could also use dynamic SQL with an "IF statement.

    Declare @sql as varchar(1000)

    Select @sql = ('select columna ')

    Continue to build your SQL statement based on the columns that you want to add (columnb, columnc, etc.)

    Hope that helps.

    Tony

    Things will work out.  Get back up, change some parameters and recode.

Viewing 2 posts - 1 through 1 (of 1 total)

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