Michael:

Could you remove the *pre* tags and use *b* and */b* around the parts that are different, we are having a hard time seeing the difference between the two code options. At least I saw no difference between the 2 snippets.

> SELECT A.*, 0 AS NAPPROVE, X.ctextlong,
> RTRIM( S1.cfirstname)||' '||RTRIM( S1.cmiddleinitial)||' '||RTRIM( S1.clastname) CINVESTNAME,
> RTRIM( S2.cfirstname)||' '||RTRIM( S2.cmiddleinitial)||' '||RTRIM( S2.clastname) CSUPERNAME
> FROM APPROVAL A, STAFF S1, STAFF S2, APPROVALXACTION X, OFFICES O
> WHERE X.ctranstypeid = A.ctranstypeid
> AND ( ( A.capproved = 'P'
> AND A.corigofficeno = ?lcVwCurrentOfficeNo )
> OR ( A.capproved = ?lcVwLowerOfficeFlag
> AND INSTR( ?lcVwLowerOfficeNo, A.corigofficeno ) > 0 ) )
> AND INSTR( ?lcVwOfficeNo, O.cofficeno ) > 0
> AND A.corigofficeno = O.cofficeno
> AND A.coriginvest = S1.cstaffid(+)
> AND A.corigsupvr = S2.cstaffid(+)
> AND INSTR( ?lcVwApprovalLevel, DECODE( O.cofficelvl, 1, X.cnolevel, 2, X.crolevel, 3, X.cdolevel ) ) > 0
> ORDER BY A.dsubmitted DESC
>
>If I passed a single value through my parameter I receive the records I want.
>When I attempt to send multiple values through my parameter, as in,
>'33','22','11','44' then I get nothing back. After much frustration I received help and modified my SQL statement to look like this below:
>
>SELECT A.*, 0 AS NAPPROVE, X.ctextlong,
> RTRIM( S1.cfirstname)||' '||RTRIM( S1.cmiddleinitial)||' '||RTRIM( S1.clastname) CINVESTNAME,
> RTRIM( S2.cfirstname)||' '||RTRIM( S2.cmiddleinitial)||' '||RTRIM( S2.clastname) CSUPERNAME
> FROM APPROVAL A, STAFF S1, STAFF S2, APPROVALXACTION X, OFFICES O
> WHERE X.ctranstypeid = A.ctranstypeid
> AND ( ( A.capproved = 'P'
> AND A.corigofficeno = ?lcVwCurrentOfficeNo )
> OR ( A.capproved = ?lcVwLowerOfficeFlag
> AND INSTR( ?lcVwLowerOfficeNo, A.corigofficeno ) > 0 ) )
> AND INSTR( ?lcVwOfficeNo, O.cofficeno ) > 0
> AND A.corigofficeno = O.cofficeno
> AND A.coriginvest = S1.cstaffid(+)
> AND A.corigsupvr = S2.cstaffid(+)
> AND INSTR( ?lcVwApprovalLevel, DECODE( O.cofficelvl, 1, X.cnolevel, 2, X.crolevel, 3, X.cdolevel ) ) > 0
> ORDER BY A.dsubmitted DESC
>
>
>This seems to work!!!! At last, a solution!!! If anyone can tell me why the one above does not work, other than "It just doesn't", I'd like to hear why.
>
>Michael