RE: SQL Acting Strangely

["Jonathan Mason" <jonathan.mason@xxxxxxxxxxxxxxxx>]

Hi Sean

Thanks for that.  The parameter for STRSQL was set to *JOB which should have
picked up the *BLANK that the job was changed to. When I explicitly set the
parameter to *PERIOD it still kicks out the error.  The QDECFMT system value
is set to "3" which gives a comma and suppresses zero - although there's no
reason why it should be set this way (being in the UK and all that).

Thanks

Jonathan



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of McGovern, Sean
Sent: 10 February 2010 13:05
To: Midrange Systems Technical Discussion
Subject: RE: SQL Acting Strangely

If you try it in STRSQL, prompt STRSQL and change the DECPNT parameter
also.



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jonathan Mason
Sent: 10 February 2010 13:01
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL Acting Strangely

Hi Sean

I checked and you were right, it was set to "I".  However, when I
changed
the job to *BLANK to match my job and then tried a select with a
substring
in STRSQL I still got the same error.

Thanks

Jonathan

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of McGovern, Sean
Sent: 10 February 2010 12:27
To: Midrange Systems Technical Discussion
Subject: RE: SQL Acting Strangely

Problem is due to the DECFMT value on your job.

On the session where you have a failure, your job will be using a comma
to
denote decimal format.



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jonathan Mason
Sent: 10 February 2010 12:23
To: midrange-l@xxxxxxxxxxxx
Subject: SQL Acting Strangely

Hi All

We are having a strange thing happening with SQL and I was wondering if
anybody had had a similar experience.

We have an SQL script in a source member that is updating a string value
in a table such that positions 1-7 of the string are moved to positions
9-15 and positions 8-15 become positions 1-8.

The SQL states:

   update TVBJREP
      set BJOAT2 = substr(BJOAT2,8,8) concat substr(BJOAT2,1,7)
    where BJJ4S8 = '0'
      and BJJ4S3 = '0'
      and substr(BJOAT2,1,7)='TSTDATA'

When I run this on our Development box it finds and updates the records
without a problem.  However, when it's run on the Test box it falls over
with a message that it doesn't like the ",8" in the first substr
function.

If I change the substr to be "substr(BJOAT, 8, 8)" and insert the blanks
it runs fine (I need to change all substr's).  If I do a similar SELECT
on the Production box and don't include the blanks it runs fine, it's
only on the Test box that we have the issue.

I have checked QAQQINI on all boxes and they match, similarly the PTF
levels for QU1 and ST1 are the same.  The options for the RUNSQLSTM
command matches on both DEV and TEST, as do the CCSID's and all boxes
are on V5R4.

If anybody has any thoughts or ideas, they'd be most gratefully welcome.

Thanks

Jonathan



_______________________________________________________
This message was sent using NOCC v1.14 webmail software
_______________________________________________________