| Author |
Message
|
| AkankshA |
 Posted: Mon Jan 22, 2007 5:42 am Post subject: Fetch multiple rows and columns |
 |
|
Grand Master
Joined: 12 Jan 2006
Posts: 1494
Location: Singapore
|
Hi,
WBI MB v6 / oracle 9i
i have a table from which i need to fetch data based on the value of filter specified in my input message. and store it in environment tree.
More then one rows would match the criteria and i need to extract value of 5 columns
Tried with ROW and list but no luck...
any ideas |
|
| Back to top |
|
 |
| elvis_gn |
| Posted: Mon Jan 22, 2007 5:46 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi Akanksha,
Post the code that you have tried.
Regards. |
|
| Back to top |
|
|
| AkankshA |
| Posted: Mon Jan 22, 2007 5:55 am Post subject: |
|
|
Grand Master
Joined: 12 Jan 2006
Posts: 1494
Location: Singapore
|
Given below id my query and i need to store the o/p in env tree -->
Environment.return
SELECT S.E, S.C, S.T, S.U, S.S FROM
Database.<tableName> AS S WHERE S.CO = abc;
i tried using ROW/LIST before query also tried with making return an array but to no avail....
as i understand
LIST is for one column multiple rows
and ROW is for multiple columns but one row...
could not find anything for multiple rows and multiple columns... |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Mon Jan 22, 2007 5:59 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Set Environment.Variables.DBResult.Results[]=SELECT S.E, S.C, S.T, S.U, S.S FROM Database.<tableName> AS S WHERE S.CO = abc;
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Mon Jan 22, 2007 6:05 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi Akanksha,
| Akanksha wrote: |
Given below id my query and i need to store the o/p in env tree -->
Environment.return
SELECT S.E, S.C, S.T, S.U, S.S FROM
Database.<tableName> AS S WHERE S.CO = abc; |
Expected you to paste the code that you ran in your flow and not what u know of the select statement...
Try,
| Code: |
SET Environment.Return[] = SELECT S.E, S.C, S.T, S.U, S.S FROM
Database.<tableName> AS S WHERE S.CO = abc; |
I have a feeling ur abc is a string, if yes put it in single quotes...
| Akanksha wrote: |
| could not find anything for multiple rows and multiple columns... |
A normal select will return multiple rows, and for multiple columns use a 'select * from...'...ofcourse that will return all the columns...
Regards. |
|
| Back to top |
|
|
| AkankshA |
| Posted: Mon Jan 22, 2007 6:12 am Post subject: |
|
|
Grand Master
Joined: 12 Jan 2006
Posts: 1494
Location: Singapore
|
Jeff,
The code you provided does not fetch anything from database. Nothing gets copied in env tree...
Elvis,
I am trying all permutations so pasted the core prob here....
abc is not a string but a declared variable... |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Mon Jan 22, 2007 6:20 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
| Akanksha wrote: |
Jeff,
The code you provided does not fetch anything from database. Nothing gets copied in env tree...
Elvis,
I am trying all permutations so pasted the core prob here....
abc is not a string but a declared variable... |
Nice standard being followed on the variables  kidding
Anyway, first please verify that ur table is accessible, the mqsisetdbparms is set, that u have some data in the table...run the select on the DB command prompt, if it works there, then it will work in the flow too...
Are u throwing exceptions on database errors ?
Regards. |
|
| Back to top |
|
|
| AkankshA |
| Posted: Mon Jan 22, 2007 6:23 am Post subject: |
|
|
Grand Master
Joined: 12 Jan 2006
Posts: 1494
Location: Singapore
|
hehehehehe.... i changed the variable name too while pasting the query
YEs everything is fine since if i put THE and use the query in such a way that it sould satisfy one row then everything works fine... |
|
| Back to top |
|
|
| AkankshA |
| Posted: Mon Jan 22, 2007 6:28 am Post subject: Solved |
|
|
Grand Master
Joined: 12 Jan 2006
Posts: 1494
Location: Singapore
|
Thanks elvis and jeff for the support......
jeff your query worked... |
|
| Back to top |
|
|
|
|
