LAE

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

Originally posted by: ngbtri

Dear community,

I'm trying to use DB execute node to retrieve multiple records from Oracle table.

The input to this node is an array of "names" and the output expected is the list of addresses in accordance.

I constructed an array of queries as below and threw it into the sqlSelect() function.

sqlQuery = strcat("SELECT address FROM table WHERE name = '", 'names',"'")

However, there is error as Lavastorm told me "sql querry changed". (the queries work fine when executed individually)

I have been trying to search around but haven't found solution yet.

May I know how do to execute sqlSelect() multiple times with dynamically changed query?

Thank you! :D

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

Originally posted by: stonysmith

Use DB Execute with this code:

The one problem with this version is that you will need to supply only one name per record. An "array" of names per record won't work.
If that doesn't work for you please re-post here and we can possibly offer another solution.

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

Originally posted by: ngbtri

Hi Smith,

By array, I meant I have a "name" field with multiple records. (not a record with multiple names inside)

The code where I explicitly spell out name works:
I gave the given code a try: (without the single quote after ":1", because it gives me error)
This works! Thank you Smith!

-----------
May I know how to output to "1" both the names and addresses fields? (for matching/merging later)

I tried to select both field as a work around but this gives me error:

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

Originally posted by: ngbtri

Hi Smith,

As the previous post is still pending, I need to write in another one to inform the silly mistake made in that pending post.

The SQL syntax I used was wrong, everything is in good order:
will help in my situation.

Thank you very much for your help!

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

Originally posted by: stonysmith

			
				May I know how to output to "1" both the names and addresses fields? (for matching/merging later)
			

You have to pass the name thru the query as a constant, but that also means you have to repeat the column name in the sqlSelect twice.

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

Originally posted by: nidpatel

Hi all,

I'm facing the similar problem and trying to use the below solution but it's giving me 0 record in output(query runs without error). However, while running the query in Sqldeveloper, I know the output shouldn't be zero.
[/QUOTE]

here's what I'm trying to do. I need data from a table1 where ID is in 'Run_ID'

Input:
Run_ID
('i1','i2','i3')
('i4','i5','i6','i7')

and so on..

my query:

sqlquery = strcat("select NAME, ADDRESS, ID from TABLE1 where ID =:1")
sqlselect(1,sqlquery,'Run_ID')

The query runs fine but gives me 0 rec in output.
However, when I use below query, it works and give me desired output. But only if I give just one line in input.
e.g. input:
Run_ID
('i1','i2','i3')

my query: sqlSelect(
1,strcat("select NAME, ADDRESS, ID from TABLE1 where ID in (",Run_ID,")
"))

If I pass multiple lines in input, it doesn't work.
Please help.

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

Originally posted by: stonysmith

Let's start with: are you using Oracle? It could require a different syntax for another database engine.

Are the ID numbers in the database such as 'i1' actual numbers or are they strings? You should ensure that the incoming data is the same type as the value in the database. (doesn't contain blanks, etc)

You are mixing the "=" equality operator and the "IN" operator. Why is your input grouped in that manner? ('i1','i2','i3')
If the Run_ID column in the database is numeric then the example below should work just fine. I just ran it on a database I have access to and it worked properly.

Your third example above fails because the "query changes" - Oracle can't determine if the output column order might have changed, so it throws an error.