USING REQUEST MODIFIER

Translation specification for the USING REQUEST MODIFIER query.

As per Teradata's documentation, the USING REQUEST MODIFIER defines one or more variable parameter names to be used in the subsequent SELECT, INSERT, UPDATE, or DELETE statements to import or export data.

The syntax for this statement is as follows:

USING ( <using_spec> [,...] ) SQL_request

<using_spec> ::= using_variable_name data_type [ data_type_attribute [...] ]
  [ AS { DEFERRED [BY NAME] | LOCATOR } ]

As stated in Teradata's documentation, the USING REQUEST MODIFIER needs to be preceded by an .IMPORT statement for it to load the data into the defined parameters.

Thus, the transformation for this statement follows these steps:

1. Call the import_file() function from the SnowConvert Helpers. This loads the data into a temporary file.

2. Call the using() function from the SnowConvert Helpers to create a dictionary with the loaded data.

3. For each query, run the exec() function from the SnowConvert Helpers and pass the previously defined dictionary. This will use Snowflake Python Connector data binding capabilities.

With this input data:

A,B,C

Teradata (MultiLoad)

Query

.IMPORT DATA FILE = inputData.dat;
USING var_1 (CHARACTER), var_2 (CHARACTER), var_3 (CHARACTER)
INSERT INTO testtabu (c1) VALUES (:var_1)
;INSERT INTO testtabu (c1) VALUES (:var_2) 
;INSERT INTO testtabu (c1) VALUES (:var_3) 
;UPDATE testtabu 
   SET c2 = 'X'
   WHERE c1 = :var_1 
;UPDATE testtabu 
   SET c2 = 'Y' 
   WHERE c1 = :var_2 
;UPDATE testtabu 
   SET c2 = 'Z'
   WHERE c1 = :var_3;

Result

ROW	C1	C2
1	A	X
2	B	Y
3	C	Z

Snowflake (Python)

Query

#*** Generated code is based on the SnowConvert Python Helpers version 2.0.6 ***
 
import os
import sys
import snowconvert.helpers
from snowconvert.helpers import Export
from snowconvert.helpers import exec
from snowconvert.helpers import BeginLoading
con = None
def main():
  snowconvert.helpers.configure_log()
  con = snowconvert.helpers.log_on()
  #** MSC-ERROR - MSCEWI1047 - THE STATEMENT WAS CONVERTED BUT ITS FUNCTIONALITY IS NOT IMPLEMENTED YET **
  Export.record_mode(True)
  snowconvert.helpers.import_file(fr"inputData.dat")
  using = snowconvert.helpers.using("var_1", "VARCHAR(20)", "var_2", "CHARACTER", "var_3", "CHARACTER")
  exec("""
    INSERT INTO testtabu (c1)
    VALUES (SUBSTRING(:var_1, 2))
    """, using = using)
  exec("""
    INSERT INTO testtabu (c1)
    VALUES (:var_2)
    """, using = using)
  exec("""
    INSERT INTO testtabu (c1)
    VALUES (:var_3)
    """, using = using)
  exec("""
    UPDATE testtabu
      SET
        c2 = 'X'
      WHERE
        c1 = :var_1
    """, using = using)
  exec("""
    UPDATE testtabu
      SET
        c2 = 'Y'
      WHERE
        c1 = :var_2
    """, using = using)
  exec("""
    UPDATE testtabu
      SET
        c2 = 'Z'
      WHERE
        c1 = :var_3
    """, using = using)
  snowconvert.helpers.quit_application()

if __name__ == "__main__":
  main()

Result

ROW	C1	C2
1	A	X
2	B	Y
3	C	Z

Known Issues

1. .REPEAT command is not yet supported

The .REPEAT command is not yet supported. This means that the USING REQUEST MODIFIER will only use the data loaded from the first row of the input file. Thus, the queries will only run once.

This issue should be fixed when the .REPEAT command receives proper transformation support.

If you have any additional questions regarding this documentation, you can email us at [email protected].