RV Variable Sorting??

[MaxAmsterdam]

[frown]
Can anyone help me? I have produced a report based on a BV Join of F4211 & F4209 tables. The sql query from MS Query listed is what I recieved from a functional guy who ask me to produce a report from this.
*******************************************************************
SELECT SDDOCO ,
COUNT(SDDOCO) ,
SUM(CASE VALUE(HOLNID, 0) WHEN 0 THEN 0 ELSE 1 END) ,
SDAN8 ,
SDDCTO FROM F4211 LEFT OUTER JOIN F4209 ON SDDOCO=HODOCO AND HOKCOO= SDKCOO AND HOLNID = SDLNID AND HOHCOD='PA' AND HORDC<>'PD' WHERE
SDMCU=' 100' OR
SDMCU=' 110' OR
SDMCU=' 700' AND
SDLNTY='S' AND SDNXTR<>'999'
GROUP BY SDDOCO , SDAN8, SDDCTO
HAVING SUM(CASE VALUE(HOLNID, 0) WHEN 0 THEN 0 ELSE 1 END)<>0
ORDER BY Count(SDDOCO)
******************************************************************
I have managed to produce this report using Level Break and Event rules. But, because the Count(SDDOCO) is declared as a RV Variable, how can I use this variable to sort the whole report? The only columns available in the 'Define Data Sequence' are the ones in the Business View... Can any one help in knowing how to sort on this RV Variable????
Max

 

[BBritain]

Max,

I hope I'm reading you right. First, are you saving each and every COUNT(SDDOCO) somewhere? If you are not, then I am not sure how you can sort on just one value. In order to sort on this value(?) you must also have some sort of link to the rest of the values. I know, I know that I am making this more difficult than it needs to be, but I didn't want to just blurt out the answer because the answer is usually followed with "Well, we can't do it that way".

You must save the count along with the other information in some sort of array. Array? you say! Well since we don't have arrays in OneWorld then you must either create a whole bunch of variables, or create a workfile. Then use another section to retrieve the file information in any order you want (OK after you create the table, and then the business view, and then attach the business view to the new section, etc. etc. etc. ). You can also use any other array(?) that you have available if you can get away without creating the worktable and still have a link to the keys for the other tables. As an example, you could use some of the user reserved fields in the Sales Order Header (one to keep track of which orders are in this particular selection - using say Job No., and one to store the count). You could then process the counting portion first and store the data in the user reserved fields. The keys to the rest of the data are right there (possibly?) as the keys to the SO Header?

Now that we can see the part missing is hanging on to the multiple COUNT(SDDOCO) values AND their link to the rest of the data, we could probably come up with five other ways to accomplish this (but none of them would be as easy as the workfile method - unless you know how to cache data!)

Ben again

 

[Larry_Jones]

Max,

quite frankly, this kind of reporting is best done using another tool than RDA. Crystal Reports would be one option. What is a simple task in a third-party report writer is a major effort in RDA.

If you must continue in RDA then I'll give this hint.
Create your summarized join as a "database" view which you then describe to OneWorld as a table and then create a BSVW and UBE to report off of that. The other option of course is a "Work" table.

Regards,

 

[jdedebug]

Hi
I don't understand how to describe "databse" view as OneWorld table? - Can you give more details on this?

Thanks
Bal

 

[MaxAmsterdam]

Thanks Ben
I ended up using a work file and this works fine.
I have used a business function to empty the table ready to load all the
unsorted records in that table. Then in one hidden columar section I do the
select, fetch next and count statements, In a second visible section I use
a BV on the populated work file table and bingo it gives me the results all
sorted.

Now another Question??
Now does the UBE act as first come first serve. in other words I haven't
tested it if two or three users submit a ube at the same time. What happens
to the table? does it lock out other users until the total ube has
completed? Or can I expect a complete mess????
regards
Max





BBritain replied to your post at the site: .
http://www.jdelist.com/ubb/showthreaded.php?Cat=&Board=OWDEV&Number=65259

Max,

I hope I'm reading you right. First, are you saving each and every
COUNT(SDDOCO) somewhere? If you are not, then I am not sure how you can
sort on just one value. In order to sort on this value(?) you must also
have some sort of link to the rest of the values. I know, I know that I am
making this more difficult than it needs to be, but I didn't want to just
blurt out the answer because the answer is usually followed with "Well, we
can't do it that way".

You must save the count along with the other information in some sort of
array. Array? you say! Well since we don't have arrays in OneWorld then
you must either create a whole bunch of variables, or create a workfile.
Then use another section to retrieve the file information in any order you
want (OK after you create the table, and then the business view, and then
attach the business view to the new section, etc. etc. etc. ). You can
also use any other array(?) that you have available if you can get away
withou!
t creating the worktable and still have a link to the keys for the other
tables. As an example, you could use some of the user reserved fields in
the Sales Order Header (one to keep track of which orders are in this
particular selection - using say Job No., and one to store the count). You
could then process the counting portion first and store the data in the
user reserved fields. The keys to the rest of the data are right there
(possibly?) as the keys to the SO Header?

Now that we can see the part missing is hanging on to the multiple
COUNT(SDDOCO) values AND their link to the rest of the data, we could
probably come up with five other ways to accomplish this (but none of them
would be as easy as the workfile method - unless you know how to cache
data!)

Ben again






AS400 XE SP21, FINANCE, CS, WAREHOUSE

 

[MaxAmsterdam]

Larry,
I've manage to uses a work file method, but I like your idea of creating a
'summarized join as a "database" view' I use to do this while working with
MS SQL, but now with this project can it be done on an IBM AS400???

regards
Chris





Larry_Jones replied to your post at the site: .
http://www.jdelist.com/ubb/showthreaded.php?Cat=&Board=OWDEV&Number=65266

Max,

quite frankly, this kind of reporting is best done using another tool than
RDA. Crystal Reports would be one option. What is a simple task in a
third-party report writer is a major effort in RDA.

If you must continue in RDA then I'll give this hint.
Create your summarized join as a "database" view which you then describe to
OneWorld as a table and then create a BSVW and UBE to report off of that.
The other option of course is a "Work" table.

Regards,






AS400 XE SP21, FINANCE, CS, WAREHOUSE

 

[eydeak]

When using report work tables, I add the following columns and make them the
table primary key

EDUS (user running report)
EDBT (populate using bsfn "EDI Batch Number, Get Next Number")
EDLN (incremented counter for each record in work file)

Fill these fields in on the insert, then include them in the select and
delete on the file. This way, the user is only looking at the data he wrote
to the report.

I also throw in the standard user/date stamp at the end (USER, PID, JOBN,
UPMJ, TDAY). Then, if there are any problems you can check the date/time
stamp to see if there is old data sitting around in the file.




Ellen Deak
Senior Analyst/Programmer
Cooper-Standard Automotive
OneWorld Xe (B7333, Service Pack 22)
AS/400 DB2

 

[TRathjen]

See post http://www.jdelist.com/ubb/showthreaded.php?Cat=&Board=OWDEV&Number=30361&page=&view=&sb=&o=
It is a nice description of how to design key fields for work files to avoid the problems of multiple ubes running.
There is a lot of info on this topic in the forums free for the searching.

 

[Larry_Jones]

Max,

I don't have any AS/400 experience, but I'd be very surprised if DB2/UDB couldn't handle database views. To see prior discussions of the topic search the forums for "database views".

Cheers,

 

[Sebastien Leclerc]

You can indeed create such a view on the AS/400. You have to issue a CREATE VIEW type statement.

If you don't have a SQL tool on the 400, you can create an MS Access pass through query and use any SQL statement. Upon execution, the statement will be passed directly to the AS/400, without alteration from the ODBC drivers.

If you need some help with the AS/400 SQL, I suggest you follow this link to the Infocenter for the I-Series.

http://publib.boulder.ibm.com/html/as400/infocenter.html