Problems using 5=Add to member using WRKQRY

[kreynolds]

This is my last ditch effort before I rewrite my code to use separate members (which I have done before and it works great).

I had to enhance an existing program that takes two files created using WRKQRY to fill a third file using an RPG program. Several users use these files. In the past they have had to just be careful to run their queries when no one else was using them. They used option 2=Replace member. I rewrote the logic to have them create batches noted by a specified batch name instead. They would run their queries to fill the work files adding their batch name and using Add to member. We are now running into a problem where the calculated amount field will not map to the work file. Attached are the work file and query definitions. If anyone has any ideas, I'd be glad to give them a try. Otherwise....back to my original plan of allowing them to use different members. I have tried defning the MOCPO field as DECIMAL(17,5) and also as FLOAT. I have also tried creating the file using SQL (my preferred way) and DDS.

[EmmanuelW1]

I must admit that I didn't look at your attachments, but I don't think your question is very clear. You say "...running into a problem where the calculated amount field will not map to the work file.", but you don't provide the names of the fields in question, nor what you mean by "will not map". What, exactly, is the issue you're facing?

Cheers,

Emmanuel

[kreynolds]

They get the following error which we believe comes from the MOCPO field they are trying to fill in MGRORD0:

Query output definition does not match file MGRORD0 in ALFILES.

[tomliotta]

Is there a reason you actually need to generate physical rows into a "work file" rather than to simply SELECT the rows/columns? It almost seems as if a VIEW could do nearly all of the work.

Why is A.ZIP limited as RANGE '00001' '99999' in your query? Does your file allow 5-digit zip codes outside that range and the query needs to exclude them? If some odd value like '1000I' (letter "I" at the end) happens to end up in that field, your query will allow it to get past your 'filter'. Do different users select different zip codes?
The only obvious difference for field MOCPO is that your query creates zoned-decimal output, but you have it defined as packed-decimal in your SQL. Try changing the SQL from DECIMAL(17, 5) to NUMERIC(17, 5).

[kreynolds]

Thank you very much for your responses. I was able to fix my issue but to do so I had to create my file using DDS (allowing multiple members) instead of using an SQL created file. SQL does not support multiple members and that is what I am trying to accomplish in this situation.

Thanks so much!
Karen

[DeadManWalks]

SQL just creates a table. You can do a ChgPF on the table to allow it to have members.

[kreynolds]

We tried that....it didn't like it. I did however learn how I could use SQL to query the individual members. I am using CREATE ALIAS. I love a learning experience.