Re: How can this be done??

["Brent S. Templeton" <btempleton@PMAI.ORG>]

Pass in a comma seperated list eg: hr,eng,sales,etc.  Write a routine that puts quotes around the string and pass that into the in clause.

Here is this exact procedure from one of my SQR programs.  It was written may years ago and much of the logic could be updated to use the let statement and other new commands, but it works.

Then use the variable in brackets [] for the in clause eg: where field in [$ql_out_list]
===============================================================

begin-procedure quote_list

! takes a comma seperated list and quotes each ql_item
! a,b,c => 'a','b','c'
!
! input $ql_in_list
! output $ql_out_list
! destroys $ql_in_list
!
move '' to $ql_out_list
move ',' to $ql_dummy
concat $ql_in_list with $ql_dummy
move $ql_dummy to $ql_in_list
concat ',' with $ql_in_list
! ,a,b,c,


move 1 to #ql_start_loc
move 1 to #ql_loc1
find ',' in $ql_in_list #ql_start_loc #ql_loc2

while #ql_loc2 != -1

  move #ql_loc2 to #ql_len
  subtract #ql_loc1 from #ql_len

  extract $ql_item from $ql_in_list #ql_loc1 #ql_len
  if #ql_num_flag != 0
    move $ql_item to #ql_tmp
    move #ql_tmp to $ql_item 099
  else
    move $ql_item to $ql_tmp xxxxx
    move $ql_tmp to $ql_item
  end-if

  string '''' $ql_item '''' by '' into $q_ql_item
  move #ql_loc2 to #ql_start_loc

  concat $q_ql_item with $ql_out_list

  find ',' in $ql_in_list #ql_start_loc #ql_loc1
  add 1 to #ql_loc1
  find ',' in $ql_in_list #ql_loc1 #ql_loc2

  if #ql_loc2 != -1
    concat ',' with $ql_out_list
  end-if
end-while
end-procedure
============================================================================
-----Original Message-----
From:   Raja Rajaram [SMTP:raja@WISE.COM]
Sent:   Friday, January 24, 1997 8:52 AM
To:     Multiple recipients of list SQR-USERS
Subject:        How can this be done??

Hi All,
 I have this question. I have a select statement which has an IN clause, for eg.

select emp_iid
from emp
where dept in ('HR',ENG')

This is the problem. I would like to have th IN clause parameters to be a
variable number. I have an upper limit though (say 10) I dont know how many
different Dept's i might have to include. I tried passing all the depts as a
comma seperated string. I UNSTRING them into 10 different variables and them
make them into a string like 'HR','ENG','SALES' (including the ' and , ) say
$Dept. so now the select clause could look like as
select emp_iid where dept in ($Dept).
This apparently does not work. Does anybody have any suggestion for this kinda
problem?

Thanks
Raja
raja@wise.com

Unrecognized Data: application/ms-tnef