Re: QM Query, DDM file, and VARCHAR_FORMAT

[Peter Dow <petercdow@xxxxxxxxx>]

Hi John,

Good catch, but it was just a typo.

The actual problem was something else.  I was a bit misleading when I 
said "On v7r3...".  While it is true that I was working on a QM Query 
that was on a v7r3 IBM i, the remote system pointed to by the DDM file 
is a v5r4 IBM i.  And on v5r4, varchar_format only handles timestamp 
fields.  In my case, the first argument was a numeric field.

I should have remembered where the remote file was :(

It demonstrates that SQL is doing the selection and formatting on the 
remote system.  Something I haven't tested is whether a UDF must exist 
on the local system as well as the remote system.

--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx <mailto:petercdow@xxxxxxxxx>
pdow@xxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxx> /

On 7/24/2020 7:52 PM, John Yeung wrote:
> On Fri, Jul 24, 2020 at 7:58 PM Peter Dow <petercdow@xxxxxxxxx> wrote:
> > On v7r3, I have a QM query with SQL like this:
> >
> > SELECT varchar_format( qty9, '999999990')
> >      FROM my_ddm_file
> >
> > where qry9 is defined as dec(9,0) and it fails with
> >
> > SQL0171 Argument 1 of function VARCHAR_FORMAT not valid.
> Hmm... well, could it have to do with `qty9` not being the same thing as `qry9`?
>
> John Y.