| View previous topic :: View next topic |
| Author |
Message |
user5
Beginner
Joined: 29 Dec 2006
Posts: 9
Topics: 4
|
 Posted: Thu Jan 18, 2007 4:36 am Post subject: Static SQL |
 |
|
Hi,
I have an SP which has a Static SQL.
I have passed the following parms to the SP:
| Code: |
IND = R
LOCSTR = '111','222','333'
FROMTS = 2006-01-09 00:58:53.878303
TOTS = 2007-01-30 00:58:53.878303
USERID = pu7105a
FUNCSTR = 'dialer','dialer1'
|
eg:
The 2nd input parm is the list of locations whose records should appear in the
output result set.
The same for the last input var.
The LOCSTR can contain as many as 16 locations strings which are in quotes and
are comma separated.
The user can send 0, 1 or all 16 lcoations
The FUNCSTR can contain as many as 100 function strings which are in quotes and
are comma separated.
The user can send 0, 1 or all 100 functions
| Code: |
SELECT LOG_TS ,
USER_ID ,
APPL_AREA ,
APPL_CMPNT ,
DIALER_FUNC ,
STACK_TRACE
FROM DIALER_LOG
WHERE (LOG_TS BETWEEN :WS-IN-FROM-TS AND :WS-IN-TO-TS)
AND LOCATION_CD IN (:WS-IN-LOC-STR)
AND USER_ID = :WS-IN-USER-ID
AND DIALER_FUNC IN (:WS-IN-FUNC-STR)
ORDER BY LOG_TS DESC
FETCH FIRST 5000 ROWS ONLY
|
Now when I use static sql as above,
the system adds a quote before & after the LOCSTR & FUNCSTR.
Thus if we pass LOC STR as: '111','222','333'
It becomes : ''111','222','333''
What can I do to eliminate these extra quotes?
Thanks & Regards,
Meghna |
|
| Back to top |
|
 |
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Thu Jan 18, 2007 6:22 am Post subject: |
|
|
When you use a variable to store an IN list, it should be stored without commas, without quotes. That's the way it works in a COBOL program; I assume (sorry, I don't have a lot of experience with SP) the same applies.
each 'item' within the list should be stored as the same length as the column definition.
If DIALER_FUNC is 8 char in length, the length of WS-IN-FUNC-STR needs to be 800 (100 x 8 ) ; values need to be stored as:where a . is a space. WS-IN-FUNC-STR should be spaced filled before STRINGing in your input parms.
same goes for the other Working-Storage IN lists.
your timestamps are incorrect. 2006-01-09 00:58:53.878303; you are missing something between the -09 and the 00:
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
user5
Beginner
Joined: 29 Dec 2006
Posts: 9
Topics: 4
|
| Posted: Fri Jan 19, 2007 4:35 am Post subject: |
|
|
HI Dick Brenholtz
I tried removing the quotes & commas.
My DIALER_FUNC column is 6 bytes long
I passed the following in the FUNCSTR :
dial11dial22
But the query is still not working.
Thanks |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Fri Jan 19, 2007 5:01 am Post subject: |
|
|
either you did or didn't remove the quotes and commas.
also, | Quote: | | FUNCSTR = 'dialer','dialer1' |
if DIALER_FUNC is 6 char, why are you inputing 7? dialer1??
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Fri Jan 19, 2007 8:51 am Post subject: |
|
|
few questions:
1. what language is the SP?
2. how is it invoked - SQL CALLed directly? or remote invocation?
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Fri Jan 19, 2007 9:08 am Post subject: |
|
|
the create statement, as well as LINKAGE or Arg statement in your SP
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
user5
Beginner
Joined: 29 Dec 2006
Posts: 9
Topics: 4
|
| Posted: Sat Jan 20, 2007 7:06 am Post subject: |
|
|
hi,
I have converted it to a Dynamic sequel.
I was passing dial11, which is 6 bytes.
My SP is written in COBOL.
Thanks & Regards,
Meghna |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
