Passing multiple variables to Select SQL

Passing multiple variables to Select SQL

Post by ck la » Thu, 26 Sep 1996 04:00:00



Hi,

I am working on a program which allows users to select multiple items
from a listbox into another listbox.  The program then passes all the
selected items in the second listbox to a variable (m.source) in a Select
SQL statement:

m.source=" "
FOR i=1 TO THIS.Chose.ListCount
 m.source=source+","+ALLTRIM(THIS.Chose.Item(i))
ENDFOR
m.source=substr(m.source,3)

SELECT * FROM JOURNAL WHERE jnsource in (m.source) INTO CURSOR result

The program returns only records of the first selected item.  It seems
that SQL treats source as a single variable.

Does anyone know how to pass all the selected items to Select SQL?

 
 
 

Passing multiple variables to Select SQL

Post by TRobichau » Thu, 26 Sep 1996 04:00:00



> Hi,

> I am working on a program which allows users to select multiple items
> from a listbox into another listbox.  The program then passes all the
> selected items in the second listbox to a variable (m.source) in a Select
> SQL statement:

> m.source=" "
> FOR i=1 TO THIS.Chose.ListCount
>  m.source=source+","+ALLTRIM(THIS.Chose.Item(i))
> ENDFOR
> m.source=substr(m.source,3)

> SELECT * FROM JOURNAL WHERE jnsource in (m.source) INTO CURSOR result

> The program returns only records of the first selected item.  It seems
> that SQL treats source as a single variable.

> Does anyone know how to pass all the selected items to Select SQL?

I have had problems addressing memory variables that contain a period
using EVAL or () or &. I usually end up assigning another variable name.
Try a variable name without the period and see if it makes a difference.

 
 
 

Passing multiple variables to Select SQL

Post by C.K. La » Thu, 26 Sep 1996 04:00:00


I've tried that but it makes no difference.  The problem probably lies on
the fact that it reads the whole string as a single variable and can't
distinguish there are more than one variable.

When I stored value to individual variables and used the names of
variables it worked fine:

var1="item1"
var2="item2"
var3="item3"
SELECT * FROM JOURNAL WHERE source IN (var1, var2, var3)INTO CURSOR
result

My problem is: I don't know the number of items will be selected by
users.  Therefore I need a function to pass the items to SELECT-SQL
statement at run time.

Anyway, thanks for your reply.

 
 
 

Passing multiple variables to Select SQL

Post by C.K. La » Thu, 26 Sep 1996 04:00:00


I've tried that but it makes no difference.  The problem probably lies on
the fact that it reads the whole string as a single variable and can't
distinguish there are more than one variable.

When I stored value to individual variables and used the names of
variables it worked fine:

var1="item1"
var2="item2"
var3="item3"
SELECT * FROM JOURNAL WHERE source IN (var1, var2, var3)INTO CURSOR
result

My problem is: I don't know the number of items will be selected by
users.  Therefore I need a function to pass the items to SELECT-SQL
statement at run time.

Anyway, thanks for your reply.

 
 
 

Passing multiple variables to Select SQL

Post by C.K. La » Thu, 26 Sep 1996 04:00:00


I've tried that but it makes no difference.  The problem probably lies on
the fact that it reads the whole string as a single variable and can't
distinguish there are more than one variable.

When I stored value to individual variables and used the names of
variables it worked fine:

var1="item1"
var2="item2"
var3="item3"
SELECT * FROM JOURNAL WHERE source IN (var1, var2, var3)INTO CURSOR
result

My problem is: I don't know the number of items will be selected by
users.  Therefore I need a function to pass the items to SELECT-SQL
statement at run time.

Anyway, thanks for your reply.

 
 
 

Passing multiple variables to Select SQL

Post by Marc Grajowe » Thu, 26 Sep 1996 04:00:00



> Hi,

> I am working on a program which allows users to select multiple items
> from a listbox into another listbox.  The program then passes all the
> selected items in the second listbox to a variable (m.source) in a Select
> SQL statement:

> m.source=" "
> FOR i=1 TO THIS.Chose.ListCount
>  m.source=source+","+ALLTRIM(THIS.Chose.Item(i))
> ENDFOR
> m.source=substr(m.source,3)

I'd create a cursor for this and use a join in the SELECT.  The performance
penalty should be minimal.

create cursor temp;
        (Source C(30))

FOR i=1 TO THIS.Chose.ListCount
        select temp
        append blank
        replace Source with ALLTRIM(THIS.Chose.Item(i))
ENDFOR

Quote:

> SELECT * FROM JOURNAL,temp WHERE jnsource = temp.Source INTO CURSOR result

I have not tested it, but is should work.

Marc

 
 
 

Passing multiple variables to Select SQL

Post by Ruben Buel » Thu, 26 Sep 1996 04:00:00


You're very close....what you probably would want to do is something like
this...

instead of this:
SELECT * FROM JOURNAL WHERE jnsource in (m.source) INTO CURSOR result

do this:
m.source = "jnsource in (" + m.source + ")"
SELECT * FROM JOURNAL WHERE &m.source INTO CURSOR result

this should work fine.

 
 
 

Passing multiple variables to Select SQL

Post by Alex Lawso » Thu, 26 Sep 1996 04:00:00



> I've tried that but it makes no difference.  The problem probably lies on
> the fact that it reads the whole string as a single variable and can't
> distinguish there are more than one variable.

> When I stored value to individual variables and used the names of
> variables it worked fine:

> var1="item1"
> var2="item2"
> var3="item3"
> SELECT * FROM JOURNAL WHERE source IN (var1, var2, var3)INTO CURSOR
> result

> My problem is: I don't know the number of items will be selected by
> users.  Therefore I need a function to pass the items to SELECT-SQL
> statement at run time.

> Anyway, thanks for your reply.

Have you tried this:

IF NOT EMPTY( var1 ) THEN
        lc_WhereStr = " source IN ('" + var1
ENDIF

IF NOT EMPTY( var2 ) THEN
        IF NOT EMPTY ( lc_WhereStr ) THEN
                lc_WhereStr = lc_WhereStr + "','" + var2
        ELSE
                lc_WhereStr = " source IN ('" + var1
        ENDIF
ENDIF

IF NOT EMPTY( var3 ) THEN
        IF NOT EMPTY ( lc_WhereStr ) THEN
                lc_WhereStr = lc_WhereStr + "','" + var3
        ELSE
                lc_WhereStr = " source IN ('" var3
        ENDIF
ENDIF

IF NOT EMPTY( lc_WhereStr ) THEN
        lc_WhereStr = lc_WhereStr + "')"
ELSE
        lc_WhereStr = " (1=1) "
ENDIF

SELECT * FROM JOURNAL WHERE &lc_WhereStr INTO CURSOR

Hope this helps

Alex Lawson...Senior Consultant...M.S.F.W....Going To Devcon

 
 
 

Passing multiple variables to Select SQL

Post by Craig Berntso » Fri, 27 Sep 1996 04:00:00


How about this:

Build a cursor (c_Items) to hold the items selected by the users...one
record for each item, then:

SELECT * FROM JOURNAL, c_Items WHERE source = c_Items.Item INTO CURSOR
--
Craig Berntson
Teltrust, Inc.
Salt Lake City Fox User Group


Quote:> I've tried that but it makes no difference.  The problem probably lies on
> the fact that it reads the whole string as a single variable and can't
> distinguish there are more than one variable.

> When I stored value to individual variables and used the names of
> variables it worked fine:

> var1="item1"
> var2="item2"
> var3="item3"
> c> result

> My problem is: I don't know the number of items will be selected by
> users.  Therefore I need a function to pass the items to SELECT-SQL
> statement at run time.

> Anyway, thanks for your reply.

 
 
 

1. Passing Multiple Variables to a Select SQL

Instead of using a memory variable I would use a cursor

***************
create cursor tempcurs (field1 c(12))
for j = 1 to this.chose.listcount
insert into tempcurs (field1) values (this.chose.item(i))
endfor

sele * from journal where jnsource in (sele field1 from tempcurs) into cursor result

sele tempcurs
use
****************

That's what I would do.

George Clay


Microserv Inc.
(206)820-5605

2. Configuration control and Ingres.

3. T-SQL How To Pass Variables into SELECT

4. MSDE Merge module setup, extras...

5. Passing value from a PL/SQL variable to a SQL*Plus variable

6. Y2K and RR for the NLS_DATE_FORMAT

7. Passing Variable off Form to SQL Pass Through Query

8. Syslog

9. Passing multiple values to a variable

10. passing Multiple param values using one variable

11. Passing in a Variable with Multiple Values

12. Can you pass a where with multiple statements without a variable for each where statement