Signed field to a packed field in AS/400 query

60 pts.
Tags:
AS/400 administration
AS/400 Query
Packed Fields
Query tuning
Does anyone know of a way to move a signed numeric to a packed numeric in query? Is it even possible?   Been playing with it and can't find a way.  Any answer appreciated. 

Software/Hardware used:
as400, query

Answer Wiki

Thanks. We'll let you know when a new response is added.

I believe the main purpose of Query is a report generator.
That being the case, you would never have a packed field.
Is it possible for you to use SQL instead?
You would then be able ot output a packed field.

Discuss This Question: 9  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    Assuming the term "query" means the 'Query for i5/OS' (i.e., "Query/400") product rather than a more generic meaning, it's not clear what is meant by move a signed numeric to a packed numeric. Conversions between numeric formats shouldn't be relevant at all. Can you first clarify what product is being used? Then, please describe the location of your "packed numeric" field. The only thing I can think it means is that you are trying to cause a query (a *QRYDFN object) to put its results into an outfile and that outfile has a packed numeric field. But such a query should be defined over that file from the beginning. Can you add any clarification? Tom
    125,585 pointsBadges:
    report
  • Lilarose22
    Not sure how to clarify it further. We get files from external sources that we upload to our 400, then use Query(qrydfn) to manipulate the data into the form we need it. We have a file coming in that has signed numeric fields. We use packed numeric fields. So when I add a packed field in my query, I'd like to more the signed field to the packed field. Query won't allow it that I can see. No, we don't use SQL here. Our senior guy here says it's impossible to do, I was just double checking if anyone else out there knew of anything. If not, we'll do it in COBOL.
    60 pointsBadges:
    report
  • TomLiotta
    ...when I add a packed field in my query, I’d like to more the signed field to the packed field. Query won’t allow it that I can see. Okay, I think I see what you mean. You create a result field and would like it to be packed-decimal in the outfile; but it always turns out to be zoned-decimal instead. Yes, I'm not aware of a way to do that with the Query/400 product. Not directly, at least. But if you really must use Query/400 (which I'd recommend not be used), you can create a *QRYDFN object in your normal way and use it as input to the RTVQMQRY command. Once the query elements are retrieved into a source member, you could use the source as input to the CRTQMQRY command to generate a *QMQRY object. A *QMQRY object can be executed with the STRQMQRY command, and QM queries will create packed-decimal numerics in preference to zoned-decimal. The RTVQMQRY and CRTQMQRY commands provide a means to convert older Query/400 objects into newer, more flexible QM query objects without actually needing the Query Manager product. For straightforward object conversions, it's often not even necessary to look at the source in the middle. It can be treated as temporary. It's also often far quicker to run a couple commands than coding COBOL or any other language if you're simply doing data conversions that can be mapped by Query/400. I know you said "we don’t use SQL here" and QM queries are based in SQL, but it's all internal in this kind of case. The SQL Development product isn't required nor needed. I'm not aware of any useful alternative other than your COBOL direction. Tom
    125,585 pointsBadges:
    report
  • Lilarose22
    Ok......I know nothing about this QMQRY you're referring to.
    60 pointsBadges:
    report
  • HMSSL2K
    here is a good link for QMQRY
    3,175 pointsBadges:
    report
  • TomLiotta
    I know nothing about this QMQRY you’re referring to. Query Manager queries technically superseded (and practically obsoleted) Query/400 queries back around 1990. Although they were mostly visible as part of the SQL Development Kit product, the capability of creating and executing them has been part of the base AS/400 system from almost the beginning. The nice user interfaces for prompting and managing them are the biggest parts of what the SQL product provided. The RTVQMQRY and CRTQMQRY commands are in AS/400s that don't have the SQL product, just like RUNQRY is in systems that don't have the Query/400 product. A particularly handy use is for converting compiled Query/400 queries into QM queries. One common need is to pass parms into queries. You might, for example, want a query to list orders received during the past 30 days. You run the query every night, so you need it to adapt to each new date. It's difficult to cause such daily adjustments for a Query/400 query since there's no good way to get the new date into the query when you run it even though you can define the argument in the query. The RUNQRY command has the RCDSLT(*YES) parameter, but no one wants to type in new values every time. However, by converting the compiled *QRYDFN to a *QMQRY with RTVQMQRY + CRTQMQRY, you gain the ability to pass dates (and other values) in at run-time with the STRQMQRY command. CL programs can format parameter values to pass into STRQMQRY. There are many more advantages, but they're not relevant to your question. The only useful piece for you is that a QM query can generate packed-decimal fields at points where Query/400 can't and that you can generate a QM query from a Query/400 query with a couple quick commands (plus a step to create a source file if you don't have one at hand). From your problem viewpoint, you don't need much info about *QMQRY objects. They are simply available if you ever want to use them. Tom
    125,585 pointsBadges:
    report
  • Lilarose22
    Thanks to all!
    60 pointsBadges:
    report
  • Lilarose22
    [...] Lilarose22 received some assistance from TomLiotta, HMSSL2K, and CharlieBrowne when trying to move a signed field to a packed field in AS/400 query. 6. Having trouble with your Watchlist on IT Knowledge Exchange? Don’t hesitate to post [...]
    0 pointsBadges:
    report
  • TomLiotta
    BTW, you might try using two files. One file will be output from your query and have the field(s) as zoned-decimal; it may be temporary, in QTEMP. The other file will have those fields defined as packed-decimal. Fields in both files will have the same names. After the query populates the first file, use CPYF FMTOPT(*MAP) to copy data and convert formats. Then use the second file for the packed-decimal data. Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following