|
View previous topic :: View next topic
|
| Author |
Message |
rinkubhat
New User
Joined: 13 Sep 2005
Posts: 35
Location: India
|
|
|
|
I am getting an SQL code of 100 in one of my SQLs in an online program
When I try to run the same query through SPUFI it is fetching a record.What could be the reason |
|
| Back to top |
|
 |
William Thompson
Global Moderator
Joined: 18 Nov 2006
Posts: 3156
Location: Tucson AZ
|
|
|
|
| rinkubhat wrote: |
I am getting an SQL code of 100 in one of my SQLs in an online program
When I try to run the same query through SPUFI it is fetching a record.What could be the reason |
The simplest answer is that something is different between either the querys or the environments. |
|
| Back to top |
|
|
rinkubhat
New User
Joined: 13 Sep 2005
Posts: 35
Location: India
|
|
|
|
| No both the queries are same and also the environments |
|
| Back to top |
|
|
vini_srcna
Active User
Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark
|
|
|
|
| Can you paste the sql used in the online program and also the one you used in SPUFI..? |
|
| Back to top |
|
|
rinkubhat
New User
Joined: 13 Sep 2005
Posts: 35
Location: India
|
|
|
|
Hey Vinay
The Spufi Code is
| Code: |
select
iss_alt_id ,
setup_rcpn ,
coalesce(setup_tmst,'0001-01-01-00.00.00.000000') ,
coalesce(req_tmst,'0001-01-01-00.00.00.000000') ,
req_stat_c ,
rtry_cnt_n ,
coalesce(rply_sent_tmst,'0001-01-01-00.00.00.000000'),
rply_stat_c ,
rply_c ,
coalesce(sl.create_tmst,'0001-01-01-00.00.00.000000'),
sl.upd_id ,
coalesce(sl.upd_tmst,'0001-01-01-00.00.00.000000'),
req_id ,
req_ty_c ,
coalesce(cd.ref_code_abbr, 'Desc not avail') ,
coalesce(cd1.ref_code_abbr, 'Desc not avail') ,
coalesce(cd2.ref_code_abbr, 'Sec type not avail')
from
smd13dbo.t_smsl_setup_log as sl
left outer join
smd13dbo.t_smcd_code_set as cd
on cd.ref_code_nm = 'PMRQSTCD' and
cd.code_low_val = req_stat_c
and cd.multi_co_n = 0
left outer join
smd13dbo.t_smcd_code_set as cd1
on cd1.ref_code_nm = 'PMRPLYCD' and
cd1.code_low_val = rply_stat_c
and cd1.multi_co_n = 0
left outer join
smd13dbo.t_smcd_code_set as cd2
on cd2.ref_code_nm = 'ISSIDTYP' and
cd2.code_low_val = iss_xref_ty_c
and cd2.multi_co_n = 0
where
setup_rcpn ='XTRAC' and
req_id ='W589873-26MAR07'
and sl.multi_co_n = 0
|
[/b]
And the code in program is
| Code: |
exec sql
select
iss_alt_id ,
setup_rcpn ,
coalesce(setup_tmst,'0001-01-01-00.00.00.000000') ,
coalesce(req_tmst,'0001-01-01-00.00.00.000000') ,
req_stat_c ,
rtry_cnt_n ,
coalesce(rply_sent_tmst,'0001-01-01-00.00.00.000000'),
rply_stat_c ,
rply_c ,
coalesce(sl.create_tmst,'0001-01-01-00.00.00.000000'),
sl.upd_id ,
coalesce(sl.upd_tmst,'0001-01-01-00.00.00.000000'),
req_id ,
req_ty_c ,
coalesce(cd.ref_code_abbr, 'Desc not avail') ,
coalesce(cd1.ref_code_abbr, 'Desc not avail') ,
coalesce(cd2.ref_code_abbr, 'Sec type not avail')
into
:sl-iss-alt-id ,
:sl-setup-rcpn ,
:sl-setup-tmst ,
:sl-req-tmst ,
:sl-req-stat-c ,
:sl-rtry-cnt-n ,
:sl-rply-sent-tmst ,
:sl-rply-stat-c ,
:sl-rply-c ,
:sl-create-tmst ,
:sl-upd-id ,
:sl-upd-tmst ,
:sl-req-id ,
:sl-req-ty-c ,
:ps308-req-sta-desc-o ,
:ps308-rply-stat-desc-o ,
:ps308-alt-id-desc-o
from
t_smsl_setup_log as sl
left outer join
t_smcd_code_set as cd
on cd.ref_code_nm = 'PMRQSTCD' and
cd.code_low_val = req_stat_c
and cd.multi_co_n = :ws-multi-co-n
left outer join
t_smcd_code_set as cd1
on cd1.ref_code_nm = 'PMRPLYCD' and
cd1.code_low_val = rply_stat_c
and cd1.multi_co_n = :ws-multi-co-n
left outer join
t_smcd_code_set as cd2
on cd2.ref_code_nm = 'ISSIDTYP' and
cd2.code_low_val = iss_xref_ty_c
and cd2.multi_co_n = :ws-multi-co-n
where
setup_rcpn = :sl-setup-rcpn and
req_id = :sl-req-id
and sl.multi_co_n = :ws-multi-co-n
end-exec
|
[/b] |
|
| Back to top |
|
|
prabs2006
Active User
Joined: 12 Jan 2006
Posts: 103
|
|
|
|
| Check the where clause values in your program. Is it the same value that you have given in SPUFI? |
|
| Back to top |
|
|
rinkubhat
New User
Joined: 13 Sep 2005
Posts: 35
Location: India
|
|
|
|
| yes u can see it in code also |
|
| Back to top |
|
|
vini_srcna
Active User
Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark
|
|
|
|
rinkubhat,
DB2 cannot behave so strange. The only thing i can suspect is the values of the host variables during the run time. Try putting the dispaly of these variables in the program before the query is executed and check with the values you have hard coded in SPUFI. This can be the reason provided that your program is not deleting any rows in any of the tables before it executes this query.
Please check for the values of the host variables during the run time.
:ws-multi-co-n ..?
:sl-setup-rcpn ..?
:sl-req-id.........? |
|
| Back to top |
|
|
rinkubhat
New User
Joined: 13 Sep 2005
Posts: 35
Location: India
|
|
|
|
I checked the values in Intertest they are same as they are in SPUFI
Dont know why this is happening?????????????? |
|
| Back to top |
|
|
vini_srcna
Active User
Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark
|
|
|
|
ok..what is the OWNER & QUALIFIER keywords used in the BIND step.
This time let me make sure both online and spufi are referring to the right tables. |
|
| Back to top |
|
|
William Thompson
Global Moderator
Joined: 18 Nov 2006
Posts: 3156
Location: Tucson AZ
|
|
|
|
Is setup_rcpn 5 bytes and is :sl-setup-rcpn 5 bytes?
Is req_id 15 bytes and is :sl-req-id 15 bytes? |
|
| Back to top |
|
|
rinkubhat
New User
Joined: 13 Sep 2005
Posts: 35
Location: India
|
|
|
|
Hi William
Both setup_rcpn and :sl-setup-rcpn are defined for 8 bytes
And req_id and is :sl-req-id for 20 bytes
does that make a difference? |
|
| Back to top |
|
|
William Thompson
Global Moderator
Joined: 18 Nov 2006
Posts: 3156
Location: Tucson AZ
|
|
|
|
It could, it depends on how the one backfills the literals or pads the dissimilar lengths.
Assuming the host variables are standard character definitions with trailing blanks, try the Spufi with all 8 and 20 bytes between the quotes.
That's just about the only thing I can think of.....  |
|
| Back to top |
|
|
vini_srcna
Active User
Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark
|
|
|
|
I can still only say there is something wrong from your perspective.
Though i dont know whats wrong i could suspect that because i cannot suspect DB2 
Hope you will not come up by saying something wrong with DB2 |
|
| Back to top |
|
|
rinkubhat
New User
Joined: 13 Sep 2005
Posts: 35
Location: India
|
|
|
|
Thanks Everyone for their suggestions
I was able to fetch the data there was nothing wrong with the SQL
I compiled the program which was calling this particular program and it worked
I guess it was earlier compiled for some other region.
But I have still one query Since the Calling program was NON DB2
How come the re compilation of that program solved the problem?
As even before compiling the calling program again I was able to process the subprogram through Intertest.!!! |
|
| Back to top |
|
|
William Thompson
Global Moderator
Joined: 18 Nov 2006
Posts: 3156
Location: Tucson AZ
|
|
|
|
| It must have something the way the host variables were defined and passed.... |
|
| Back to top |
|
|
vini_srcna
Active User
Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark
|
|
|
|
Yes, that could be the only reason then.
You might have changed the calling program to initialise the host variable or the way they get values. It still wont through timestamp error since it does not have any DBRM's. |
|
| Back to top |
|
|
|
|
