Re: QM Query, DDM file, and VARCHAR_FORMAT

Hi Peter

I don't think SQL works with DDM files - they are not "tables".

So the message is kind of misleading - but you should be able to use 3-part naming to run the query against the remote PF, I think.

I did google for "ddm sql ibm i" and found several links - this one was interesting, as it does both DDM and SQL in the same procedure - but not at the same time!

https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/ddp/rbal1ddmsql.htm

The CRTSQLxxx command points to the remote system, and so does the CRTDDMF. But the DDMF can be used for what it can do, then an embedded SELECT can go after data in the same table that the DDMF points to. But it doesn't use the DDMF in the SELECT.

Vern

On 7/24/2020 6:58 PM, Peter Dow 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.

I get the same failure with STRSQL.  And within STRSQL, if I use CONNECT TO rmtsys, where rmtsys is the system the DDM file points to, I get the same failure.

If I copy the DDM file to a physical file, it works fine.

Is this "working as designed"?  A known issue with DDM files?  Or something new?


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