LAE

Welcome to the LAE community!  Please feel free to start a discussion in the discussion tab or join in a conversation.

Discussions

Members

Resources

Events

 View Only
Back to discussions
Expand all | Collapse all

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

  • 1.  Need to Query based on dynamic array of input as query condition

    Employee
    Posted 04-11-2014 02:58

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: snbkumar

    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.  RE: Need to Query based on dynamic array of input as query condition

    Employee
    Posted 04-17-2014 21:24

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: snbkumar

    Hi Team,

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

    Thanks,
    Bharath


  • 3.  RE: Need to Query based on dynamic array of input as query condition

    Employee
    Posted 04-18-2014 08:08

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: ltolleson

    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.  RE: Need to Query based on dynamic array of input as query condition

    Employee
    Posted 04-22-2014 22:09

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: snbkumar

    Hi Larry,
    We are using DB Query node.


  • 5.  RE: Need to Query based on dynamic array of input as query condition

    Employee
    Posted 04-28-2014 23:04

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: snbkumar

    Hi Team,
    Please can you help me out ASAP?

    Thanks, Bharath


  • 6.  RE: Need to Query based on dynamic array of input as query condition

    Employee
    Posted 04-29-2014 07:25

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: andycooper

    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.  RE: Need to Query based on dynamic array of input as query condition

    Employee
    Posted 04-29-2014 10:51

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: ltolleson

    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.  RE: Need to Query based on dynamic array of input as query condition

    Employee
    Posted 10-01-2014 04:25

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: Wizardous

    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?

    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.  RE: Need to Query based on dynamic array of input as query condition

    Employee
    Posted 10-01-2014 06:39

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: stonysmith

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


  • 10.  RE: Need to Query based on dynamic array of input as query condition

    Employee
    Posted 10-01-2014 06:50

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: Wizardous

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

     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


  • 11.  RE: Need to Query based on dynamic array of input as query condition

    Employee
    Posted 10-01-2014 08:03

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: stonysmith

    Okay: try this <grin> My apoligies for the trial and error.. I'm not all that familiar with Teradata.
    sqlSelect(1, "select * from XXXXXX.v_dataspellxxxxx where Date_of_transaction = :date_of_transaction",date_of_transaction)


  • 12.  RE: Need to Query based on dynamic array of input as query condition

    Employee
    Posted 10-01-2014 08:11

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: Wizardous

    Also tried this one before and the error code is below. And my apoligies as I didn't mention my previous trials.

    ERROR initializing expert output:
Length 0 is not allowed for a CHAR, VARCHAR, BYTE, VARBYTE column.
2014-10-01 18:09:03.000; WARN: Length 0 is not allowed for a CHAR, VARCHAR, BYTE, VARBYTE column.
	id: 0 chain: 0 group: 0
	cppDetail: context:  build/linux-x86-64/release/code/source/jigsaw/db/teradata/TDParcels.cpp@88

2014-10-01 18:09:03.000; WARN: ...handling expression #1 in file
	id: 0 chain: 0 group: 0
	cppDetail: context: Salmon::NED::ExprTableIo::initialize() build/linux-x86-64/release/code/source/brain/node/ExprTableIo.cpp@86

2014-10-01 18:09:03.000; WARN: ...exception seen
	id: 0 chain: 0 group: 0
	cppDetail: context: Salmon::NED::NodeControl::initializeExpert() build/linux-x86-64/release/code/source/brain/node/NodeControl.cpp@1331

2014-10-01 18:09:03.000; ERROR: ERROR initializing expert output:
Length 0 is not allowed for a CHAR, VARCHAR, BYTE, VARBYTE column.
	id: 0 chain: 0 group: 0
	cppDetail: context: Salmon::NED::NodeMain::errorMsg() build/linux-x86-64/release/code/source/brain/node/NodeMain.cpp@28


    Thank you

    �zg�n


  • 13.  RE: Need to Query based on dynamic array of input as query condition

    Employee
    Posted 10-01-2014 08:28

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: stonysmith

    Okay: try this <grin> My apoligies for the trial and error.. I'm not all that familiar with Teradata.
    sqlSelect(1, "select * from XXXXXX.v_dataspellxxxxx where Date_of_transaction = :date_of_transaction",date_of_transaction)