question on order of output table when no order is chosen 1

[kimsue]

Hello there,

I have 2 files (rm3head and rm3det) both ordered by recnum. There is a relationship set into rm3head (recnum field ties multiple detail records to one header record). Can anyone tell me why the output file in the select statement below would not be in the order of the records in rm3det? Thanks for your response.


SELECT rm3head.billed,rm3head.ad1b,rm3head.com2, rm3head.project, rm3head.account, rm3head.invoice, rm3head.program, rm3head.foreman, ;
rm3head.complete, rm3head.foreman, rm3head.accident, rm3head.special, rm3head.crewsize, rm3head.bridge, ;
rm3head.rptemptot, rm3head.rptmattot, rm3head.rpteqptot, rm3head.ad1, rm3head.ad2, rm3head.vendnam, rm3det.* ;
FROM rm3head , rm3det ;
WHERE rm3head.recnum = rm3det.recnum ;
AND (rm3head.complete BETWEEN Thisform.Pageframe1.Page1.bcomplete.VALUE AND Thisform.Pageframe1.Page1.ecomplete.VALUE) ;
AND (UPPER(rm3head.account) BETWEEN Thisform.Pageframe1.Page1.baccount.VALUE AND Thisform.Pageframe1.Page1.eaccount.VALUE) ;
AND (UPPER(rm3head.invoice) BETWEEN Thisform.Pageframe1.Page1.binvoice.VALUE AND Thisform.Pageframe1.Page1.einvoice.VALUE) ;
AND (UPPER(rm3head.project) BETWEEN Thisform.Pageframe1.Page1.bproject.VALUE AND Thisform.Pageframe1.Page1.eproject.VALUE) ;
AND (UPPER(rm3head.special) BETWEEN Thisform.Pageframe1.Page1.bspecial.VALUE AND Thisform.Pageframe1.Page1.especial.VALUE) ;
AND (UPPER(rm3head.foreman) BETWEEN Thisform.Pageframe1.Page1.bforeman.VALUE AND Thisform.Pageframe1.Page1.eforeman.VALUE) ;
AND (rm3head.crewsize BETWEEN Thisform.Pageframe1.Page1.bcrewsize.VALUE AND Thisform.Pageframe1.Page1.ecrewsize.VALUE) ;
AND (UPPER(rm3head.program) BETWEEN Thisform.Pageframe1.Page1.bprogram.VALUE AND Thisform.Pageframe1.Page1.eprogram.VALUE) ;
AND (UPPER(rm3head.bridge) BETWEEN Thisform.Pageframe1.Page1.bbridge.VALUE AND Thisform.Pageframe1.Page1.ebridge.VALUE) ;
AND (UPPER(rm3head.accident) BETWEEN Thisform.Pageframe1.Page1.baccident.VALUE AND Thisform.Pageframe1.Page1.eaccident.VALUE) ;
AND (rm3head.recnum BETWEEN Thisform.Pageframe1.Page1.brecnum.VALUE AND Thisform.Pageframe1.Page1.erecnum.VALUE) ;
AND (rm3head.estnum BETWEEN Thisform.Pageframe1.Page1.bestnum.VALUE AND Thisform.Pageframe1.Page1.eestnum.VALUE) ;
AND (UPPER(rm3det.twp) BETWEEN Thisform.Pageframe1.Page2.bunit.VALUE AND Thisform.Pageframe1.Page2.eunit.VALUE) ;
AND (UPPER(rm3det.rdname) BETWEEN Thisform.Pageframe1.Page2.broadname.VALUE AND Thisform.Pageframe1.Page2.eroadname.VALUE) ;
AND (UPPER(rm3det.route) BETWEEN Thisform.Pageframe1.Page2.broute.VALUE AND Thisform.Pageframe1.Page2.eroute.VALUE) ;
AND (UPPER(rm3det.loccode) BETWEEN Thisform.Pageframe1.Page2.bloccode.VALUE AND Thisform.Pageframe1.Page2.eloccode.VALUE) ;
AND (UPPER(rm3det.distrct) BETWEEN Thisform.Pageframe1.Page2.bdistrct.VALUE AND Thisform.Pageframe1.Page2.edistrct.VALUE) ;
AND (rm3det.sec BETWEEN Thisform.Pageframe1.Page2.bsec.VALUE AND Thisform.Pageframe1.Page2.esec.VALUE) ;
AND (rm3det.blog BETWEEN Thisform.Pageframe1.Page2.bblog.VALUE AND Thisform.Pageframe1.Page2.eblog.VALUE) ;
AND (rm3det.elog BETWEEN Thisform.Pageframe1.Page2.belog.VALUE AND Thisform.Pageframe1.Page2.eelog.VALUE) ;
AND (UPPER(rm3det.bloc) BETWEEN Thisform.Pageframe1.Page2.bbloc.VALUE AND Thisform.Pageframe1.Page2.ebloc.VALUE) ;
AND (UPPER(rm3det.eloc) BETWEEN Thisform.Pageframe1.Page2.beloc.VALUE AND Thisform.Pageframe1.Page2.eeloc.VALUE) ;
AND (rm3det.date BETWEEN Thisform.Pageframe1.Page3.bdate.VALUE AND Thisform.Pageframe1.Page3.edate.VALUE) ;
AND (UPPER(rm3det.opno) BETWEEN Thisform.Pageframe1.Page3.bopno.VALUE AND Thisform.Pageframe1.Page3.eopno.VALUE) ;
AND (rm3det.workcode BETWEEN Thisform.Pageframe1.Page3.bworkcode.VALUE AND Thisform.Pageframe1.Page3.eworkcode.VALUE) ;
AND (UPPER(rm3det.emp1) BETWEEN Thisform.Pageframe1.Page3.bemp1.VALUE AND Thisform.Pageframe1.Page3.eemp1.VALUE) ;
AND (UPPER(rm3det.mat1) BETWEEN Thisform.Pageframe1.Page3.bmat1.VALUE AND Thisform.Pageframe1.Page3.emat1.VALUE) ;
AND (UPPER(rm3det.mat1loc) BETWEEN Thisform.Pageframe1.Page3.bmat1loc.VALUE AND Thisform.Pageframe1.Page3.emat1loc.VALUE) ;
AND (UPPER(rm3det.eq1) BETWEEN Thisform.Pageframe1.Page3.beq1.VALUE AND Thisform.Pageframe1.Page3.eeq1.VALUE) ;
AND (UPPER(rm3det.paytype) BETWEEN Thisform.Pageframe1.Page3.bpaytype.VALUE AND Thisform.Pageframe1.Page3.epaytype.VALUE) ;
INTO TABLE "&CFSYSDRV.&CFI.&DWSTMP1"

 

[bborissov]

WOW!
What a select!
First why you use thisform... use variables, that will make SELECT faster.
Try (I am not going to write this

SELECT rm3head.billed,rm3head.ad1b,rm3head.com2,;
       rm3head.project, rm3head.account, rm3head.invoice,;
       rm3head.program, rm3head.foreman, ;
       rm3head.complete, rm3head.foreman, rm3head.accident,;
       rm3head.special, rm3head.crewsize, rm3head.bridge,;
       rm3head.rptemptot,rm3head.rptmattot,;
       rm3head.rpteqptot,rm3head.ad1, rm3head.ad2,;
       rm3head.vendnam, rm3det.* ;
  FROM rm3det ;
  LEFT JOIN rm3head ON rm3det.recnum == rm3head.recnum;
  WHERE (the rest of where clause here)
  INTO CURSOR "&CFSYSDRV.&CFI.&DWSTMP1"

BTW Why you use INTO TABLE ?

Borislav Borissov

 

[Stella740pl]

...order of output table when no order is chosen

Can anyone tell me why the output file in the select statement below would not be in the order of the records in rm3det?

I don't know why they should be in the order of records of one of the two original tables. You have 2 tables, multiple complex conditions, and no order set - you really cannot expect any particular order. If it was a simple SELECT from a single table which actually only puts a filter on the original table, then you might expect some original order.

The way it probably works is something like that: first it pre-selects the records to satisfy the first condition, then goes over the second condition among those pre-selected, etc. Also, FoxPro, possibly, optimizes it somewhat (take a look at SYS(3054)).

If you want an order, you should specify so in the query.

And as Borislav pointed out, you would be better off, in terms of query speed, if you use not form controls, but their ControlSource variables.

 

[Mike Gagnon]

Have you tried adding an index on your cursor?

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.
ReFox XI (www.mcrgsoftware.com)

 

[kimsue]

Thanks for your help. I'll use the variable names for speed and the LEFT JOIN that Borislav suggested. In this particular case, I want NO ORDER (order of entry). I output to a table instead of cursor because more things happen to the file after the selection. I had very similar code in the Foxpro 2.6 version and the output file came out as I had expected. Thanks again.

 

[TamarGranor]

Without an ORDER BY clause, you can't assume any particular order in query results.

In addition, there's no reason to have a relation set between tables to do a query. I don't think it'll do any harm, but it surely doesn't do any good.

As for cursor vs. table, you only need a table if you want to preserve these results beyond the program running the query. If you need to be able to change the results, add the READWRITE keyword to the query:

INTO CURSOR Whatever READWRITE

Tamar

 

[kimsue]

FROM rm3det ;
LEFT JOIN rm3head ON rm3det.recnum == rm3head.recnum;
where

That worked. Thank you!

 

[alvechurchdata]

Can anyone tell me why the output file in the select statement below would not be in the order of the records in rm3det?

The SQL is based on the mathematical concept of sets. It selects and manipulates groups of records without considering their order. An SQL statement will process records in whatever order seems best and there's no guarantee (unless you include an ORDER BY clause) that the same query will return the same records in the same order each time that you run it.

Geoff Franklin

http://www.alvechurchdata.co.uk