Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Need to Query based on dynamic array of input as query condition

  1. #1

    Default Need to Query based on dynamic array of input as query condition

    Hi Team,

    I have a table with huge volume of data (rows and columns). Now I have a set of records that i need to compare against that huge volume of data, but based on a key (set of columns)

    I am currently trying it as
    1. a db query node that extracts all data
    2. join node that compares the input against the db query output based on key values

    Instead of that, can i query the db based on the key values that come from a previous node directly? This greatly saves query and join execution time. There should be a way, as this can be done easily in PL/SQL. Please help me out.

    Thanks,
    Bharath

  2. #2

    Default

    Hi Team,

    Can someone please help me out? Im guessing this is a commonly faced issue.

    Thanks,
    Bharath

  3. #3
    Larry Tolleson ltolleson's Avatar
    Join Date
    Nov 2006
    Location
    Dallas-Fort Worth
    Posts
    189

    Default

    What type of DB are you trying to query and are you using the DB Query or the JDBC Query node? It is possible, but the technique is different depending on the type of DB, which is why I'm asking these questions.

    Thanks,
    Larry

  4. #4

    Default

    Hi Larry,
    We are using DB Query node.

  5. #5

    Default

    Hi Team,
    Please can you help me out ASAP?

    Thanks, Bharath

  6. #6
    Lavastorm Employee
    Join Date
    Apr 2014
    Location
    London
    Posts
    57

    Default

    Bharath,

    Have you tried using the Lookup node? If the second set of data is not too big (i.e. if you are comparing a reference file list to a larger transaction file) then the Lookup node can be considered.

    Thanks

  7. #7
    Larry Tolleson ltolleson's Avatar
    Join Date
    Nov 2006
    Location
    Dallas-Fort Worth
    Posts
    189

    Default

    I asked about the DB type because there are 2 different methods for binding a value to a query depending on the DB you are trying to query.

    In LAE there are 2 different sets of DB nodes that work with different DB types. For Oracle, Teradata, or any ODBC connection we use the (DBQUery, DBExecute, and DBStore) nodes. For any JDBC connection we use the (JDBCQuery, JDBCExecute, and JDBCStore) nodes. Based on which set of nodes you use will determine how we setup the nodes to bind a values to the query.

    Let's start with Oracle.

    If you want to perform a simple SELECT statement, then you would use the DBQuery node.
    If you want to pass in a set of values to query, then you would use the DBExecute node (Interfaces and Adapters). I think this is what you want to use based on the question you originally asked.

    Let's assume you have input data coming into your DBExecute node that contains a field called AccountNumber and you want to pass that value into an sql query of a table called Account that contains a field called AcctNbr.

    In the DBExecute node you would write the following BrainScript code.

    sqlSelect(1, "select * from Account where AcctNbr = :1", AccountNumber)

    You will also need to add an input and output to the DbExecute node to allow for input into the node and to produce the output of the query. By default the DBExecute node does not have any inputs or outputs configured.

    Let us know if you still have questions.

  8. #8

    Default

    Hi

    I am connecting to a teradata db with dbexecute node to have the output of query below, and I have "Bind name in teradata sql statements must not begin with a digit. Col: 68" error. How can I fix this?

    Code:
    sqlSelect(1, "select * from XXXXXX.v_dataspellxxxxx where Date_of_transaction = :1",date_of_transaction)
    Lae version : LAE 4.6 Enterprise

    Regards,

    Özgün

  9. #9
    Lavastorm Employee stonysmith's Avatar
    Join Date
    Nov 2006
    Location
    Grapevine Tx
    Posts
    799

    Default

    Try this:
    sqlSelect(1, "select * from XXXXXX.v_dataspellxxxxx where Date_of_transaction = ?",date_of_transaction)

  10. #10

    Default

    Hi Stony
    Got this one after updating query as you told.

    Code:
     ERROR initializing expert output:
    Position out of bounds: 0/0
    2014-10-01 16:49:54.000; WARN: Position out of bounds: 0/0
    	id: 0 chain: 0 group: 0
    	cppDetail: context:  build/linux-x86-64/release/code/source/jigsaw/db/teradata/TDStatement.cpp@130
    Thanks
    Özgün

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •