Solved

Alternative to "Do While"

Posted on 2014-09-25
12
162 Views
Last Modified: 2014-10-01
Hi guys,

This is probably a really easy question to answer but I just cannot seem to get my head round it.

I am compiling a report of orders on a voyage and I need to show the same total charge for each container regardless of how many orders there are in the container. For example:

Order Number      Number of Packages      Weight      Container Number       Charge
AB-123456                                  2                       1235      ABCD123456(0)              2500
AC-234598                                  1                       200              DEFG987654(2)      
AC-258468                                  2                       589              DEFG987654(2)      
AC-987654                                  5                      1800      DEFG987654(2)              2500
AC-789555                                  1                       200              THFC159753(1)      
AC-135879                                 20                      5521      THFC159753(1)              2500

I'm using "Select" to gather the information I need and put it in a table then I scanning it to change some of the values or add other charges. But I can seem to work out how to charge a set amount for all the orders in one container. I did think of using "Do While" but that seems antiquated. Is there a better way of doing it?

I've got this far:

      SELECT S_AOIM02.M_PONUM, S_AOIM02.M_REQNUM, S_AOIM02.M_UMNUM,;
            S_AOIM02.M_SHIP_FLT, S_AOIM02.M_BLCON_NO, S_AOIM02.M_QTYPACK,;
            S_AOIM02.M_WTPACK, S_AOIM02.M_CUBEPACK, S_AOIM02.M_CONTNUM,;
            S_AOIM02.M_PORTDEPT;
            FROM K:\SSS\S90\S-AOIM02.DBF S_AOIM02;
            WHERE S_AOIM02.M_SHIP_FLT = VOYNO;
            ORDER BY S_AOIM02.M_PONUM;
            INTO TABLE K:\SSS\TEMPWORK\TMPVOY.DBF
SCAN ALL

Please help. My brain hurts.

Thanks

David
0
Comment
Question by:davidtotty
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40344032
I don't understand the problem. Maybe if you could post the results you expect, that might help.
0
 

Author Comment

by:davidtotty
ID: 40344075
I obviously didn't explain it very well. I'm trying to give a group total charge for each container in the report. The same amount is charged for each container regardless of how many orders are in it. I want to show the charge next to the last order in the container. Similar to the example above.

Does that explain it better?

Thanks again
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40344102
So you need a charge for (say) the first time that a container is used.

You should use ROW_NUMBER() OVER(PARTITION BY S_AOIM02.M_CONTNUM ORDER BY S_AOIM02.M_PONUM) AS MyRowNumber

and then use a CTE to apply the charge when MyRowNumber = 1
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:davidtotty
ID: 40344157
Thanks Phillip I'll give that a go.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 40344163
Why the SCAN loop should be antiquated? It is still there so use it. I am sure it will be in all future versions...

OK, you may also create unique index and use it to set the charge:

Let suppose the container number is in the M_CONTNUM column and Charge is currency column containing the container total charge in your TMPVOY table.

SELECT TMPVOY
INDEX ON M_CONTNUM TO (ADDBS(SYS(2023)) + "M_CONTNUM") UNIQUE
REPLACE ALL Charge WITH $2500
SET INDEX TO

You may also delete the temp index now:
DELETE FILE (ADDBS(SYS(2023)) + "M_CONTNUM.IDX")

To make this REPLACE operation by SQL command would take longer time and the command complexity would also be much higher...

Above code works in VFP 9 SP2.
0
 

Author Comment

by:davidtotty
ID: 40344178
That sounds like a better solution pcelba. I didn't mean the Scan was antiquated I meant "Do While" it feels like an old xbase command.

I'll give your solution a try.

David
0
 
LVL 41

Expert Comment

by:pcelba
ID: 40344241
Both DO WHILE and SCAN are old xBase commands. That's OK. The worst thing is FoxPro itself becomes antiquated and unsupported by its owner...

OTOH, community is still improving it:
Unofficial patches:
http://www.baiyujia.com/vfpcompiler/en/default.asp

Free Add-ons:
http://vfpx.codeplex.com/
http://activevfp.codeplex.com/
http://www.report-sculptor.com/

etc.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 40345720
If this is for a report you can have the same charge value for all records of the same container name and make the report only print it once in a group footer.

I'm not a big fan of manipulating the data in the way you print it 1:1 as you see it in a BROWSE. The data is the data and the presentation is the presentation. It's the job of the presentation logic to display it as needed.

So you'd add grouping to your report and group by the container name. Then you put the Charge into the group footer and it will only be printed there.

Bye, Olaf.
0
 
LVL 41

Assisted Solution

by:pcelba
pcelba earned 250 total points
ID: 40345752
Olaf, your post is correct BUT we don't know the existing data structures...

If the container does not have its own table but it is entered into each order (which is wrong) then we should not place the container fee into each order. Such data do not describe the reality. To have the $2500 in each row and use "Print when" is simple a nonsense in such case.

Of course, I am voting for separate Container table which should have the Charge column. Then the "Print when" or Group footer solution is OK.

BTW, to prepare some complex calculations in cursors and then print it to a simple report layout is easier solution to me. I don't like reports where several fields are at one place and complex Print when formulas control printing...
0
 

Author Comment

by:davidtotty
ID: 40345759
Thanks Guys you both have valid points. I'll try using cursors instead.

Thanks for you suggestions.
0
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 250 total points
ID: 40346531
>BTW, to prepare some complex calculations in cursors and then print it to a simple report layout is easier solution to me. I don't like reports where several fields are at one place and complex Print when formulas control printing...

Let's say it this way: The ideal way is you neither need a overly complicated query to get the report cursor as needed nor to have an overly complicated report with complex print when conditions and tricky variables.

I saw the current case as needing the Charge in each last record of the group, which would be the value printed in a group footer, the other records won't matter, if filled with the same value. If you make a report variable summing values and being reset with a group change you'd of course not want the total in each group record, but that wasn't what I thought of.

I merely make a general observation than diving into this problems details.

Bye, Olaf.
0
 

Author Closing Comment

by:davidtotty
ID: 40354604
Thanks for your help guys
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: http://msdn.microsoft.com/en-us/vfoxpro/default.aspx (http://msd…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now