Michael,
1) it really isn't that easy to spot the differences between the two. Why don't you also TELL US, to save us having to play interpreter on such a long and HARD TO READ statement.
2) WHICH parameter is it that you are trying to pass many through?. . . And does INSTR recognize and accept such a parameters list?
Don't give us riddles - Try to make it easier for us to help you.
Cheers,
Jim N
>Here is my actual code for my remote view:
>
> 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
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only