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
Tuesday, April 7, 2009
Subscribe to:
Post Comments (Atom)
Do you have a screen shoot or a step by step note on how to use the SQL Transformation - Query Mode?
ReplyDeletewhat are the transformation is support to retrive duplicate records
ReplyDeletewhat are the transformation is support to retrive rejected records
which tranformations is support to write expressions.
My Query is like this :
ReplyDeleteDELETE 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.
above I am calling a dynamic sql for the avlive query..
ReplyDelete