Tuesday, April 7, 2009

SQL transformation

SQL transformation
- To run ANSI SQL queries in the pipelines

Type : Active/Passive based on the Mode

Two modes :-

1) Script Mode
- run scripts that are located externally. you specify script name to the transformation with
- It outputs one row for each input row
Ports
ScriptName Input
ScriptResult Output - Returns PASSED or FAILED
ScriptError Output - Returns error

2) Query Mode
Allows to run queries that you define in the editor. You can pass strings or parameters to the query (dynamic query). You can output multiple rows when the query has a select statement.
Static
Dynamic :
Full Query - put ~Query_Port~ to pass the full query to the transformation
you can pass more than 1 query statment. If the query has SELECT, then you have to configure the output ports

Partial Query -
you can create a port and then write the sql and then use ~Table_Port~ to substitue the value

Connection Type : Specify a connection object or pass the database connection to the transformation at run time.

Number of ports should match with the number of columns in the SELECT
Default port :SQLErrorPort, Numaffectedports

4 comments:

  1. Do you have a screen shoot or a step by step note on how to use the SQL Transformation - Query Mode?

    ReplyDelete
  2. what are the transformation is support to retrive duplicate records

    what are the transformation is support to retrive rejected records

    which tranformations is support to write expressions.

    ReplyDelete
  3. My Query is like this :
    DELETE FROM CAAMD_GA_T.PATIENT_HASHCUR; INSERT INTO CAAMD_GA_T.PATIENT_HASHCUR SELECT PAT_ID, HASHROW(ADD_LINE_1,ADD_LINE_2,CITY,STATE_C,ZIP,PAT_MRN_ID,PAT_NAME,PAT_FIRST_NAME,PAT_LAST_NAME,PAT_MIDDLE_NAME,PAT_STATUS_C,PRIM_FC,BIRTH_DATE,SSN,CUR_PCP_PROV_ID,SEX) FROM HCCLGAM.PATIENT;
    and I get an error in the PAT_ID saying cannot resolve pat_id, specify table or view..

    Since this is not a select statement rather an insert, could you please help me find me out issue.. Thanks very much.

    ReplyDelete
  4. above I am calling a dynamic sql for the avlive query..

    ReplyDelete