RE: Leading minus sign in QMQRY result?

[Roger Harman <roger.harman@xxxxxxxxxxx>]

Just to close this out...

I ended up using a case statement.  When value < 0, concat a hyphen in front of the trimmed char(number) value and I cast the whole thing to char(15).

Quick to implement and easy to understand.

Roger Harman
COMMON Certified Application Developer – ILE RPG on IBM i on Power







-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Vernon Hamberg
Sent: Tuesday, June 02, 2020 8:24 PM
To: Midrange Systems Technical Discussion
Subject: Re: Leading minus sign in QMQRY result?

Hi Roger

I just tried this - the sign in the the outfile is displayed on the 
right - now that makes some sense, since neither a packed or zoned 
column has a visible sign, right?

I had the idea that I could override the QPQXPRTF (that's what QM uses 
for *PRINT) with a PF - it wouldn't even take it. The reason I tried it 
was, the sign would be on the left in the SPLF, could I get it to go to 
a text file automatically.

Now another thought - CPYTOIMPF, is there an FDF spec that could make 
the formatting be what you want?

The other idea is something others have mentioned - use the SQL in QM to 
put all the stuff together as CSV - something like this -

select '"' || strip(replace('"', charfld, '""')) || '"' || ',' 
varchar_format(numfld, 'format_string') from somefile

I put in the replace() function to double up any embedded full quotes.

varchar_format can give you a character representation with lots of options.

With this one, you would use the CPY IFS command to populate the CSV file.

You could maybe just do the varchar_format for that numeric field, then 
the CPYTOIMPF would just take care of the rest.

How's that for late-nightish thoughts!!

Regards
Vern

On 6/2/2020 9:43 PM, Roger Harman wrote:
> Even if it's just going to an outfile?  Not sure how that would work.  Outfile gets copied to CSV and emailed.
>
> I'll look at it in the morning.
>
> For most of these things, I just use SQL Workbench/J which has XLSX output.  But, this is a scheduled daily job.
>
>
>
> -----Original Message-----
> From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Peter Dow
> Sent: Tuesday, June 02, 2020 4:45 PM
> To: midrange-l@xxxxxxxxxxxxxxxxxx
> Subject: Re: Leading minus sign in QMQRY result?
>
> Hi Roger,
>
> As I recall, you have to create a QM Report Form (STRQM option 2). That
> form will let you specify edit codes for numeric columns among other
> things.  I think P is for leading minus.
>
> Then when you run the query, you specify the name of the form.
>
> --
> *Peter Dow* /
> Dow Software Services, Inc.
> 909 793-9050
> petercdow@xxxxxxxxx <mailto:petercdow@xxxxxxxxx>
> pdow@xxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxx> /
>
> On 6/2/2020 4:36 PM, Roger Harman wrote:
> > This shouldn't be baffling me as much as it is but it's been quite a day....
> >
> > I have a QM Query object that I need to change to have a leading minus instead of trailing in a particular column.  Is there an easy way to do this?  It's escaping me at the moment.
> >
> > V7R2
> >
> > Thanks.
> >
> > Roger Harman
> > COMMON Certified Application Developer - ILE RPG on IBM i on Power
> >
> >
> >
> >
> >    
> >