Run SQL - Mysterious Problem

[alisaif]

Dear All,

I am facing a very strange problem, on running below query in command windows it gives correct records, whereas in click event of the button gives different result:

*dt1 = Thisform.contMaster.Contperiod1.Datefromto1._datepicker1.Value
*dt2 = Thisform.contMaster.Contperiod1.Datefromto1._datepicker2.Value

dt1 = {01/01/2015}
dt2 = {14/02/2016}

Select rawSom.Sono, rawSom.Sodate, rawSom.pcode, Customer.full_name, Customer.pTerms,;
	rawSom.enteredby, rawSom.Salesman, rawSom.refno, rawSom.refdate, ;
	rawSom.discamount, rawSo.categ, rawSo.ShpDate,;
	rawSom.Curr, rawSom.isocode, rawSom.Currency, rawSom.Sign, rawSom.currrate,;
	rawSom.discrem, rawSom.remarks, rawSo.Rcode, ;
	rawSo.quantity,  rawSo.qtyavail, rawSo.unit, ;
	rawSo.rate, Cast((quantity*rate) As N(10,2)) As original, ;
	CAST((qtyavail*rate) As N(10,2)) As delivered ;
	FROM ;
	village!rawSom ;
	inner Join village!rawSo ;
	ON  rawSom.Sono = rawSo.Sono ;
	inner Join village!Customer ;
	ON  rawSom.pcode = Customer.pcode;
	WHERE  rawSom.Sodate Between dt1 And dt2 ;
	INTO Cursor ViewSo Readwrite

In command windows gives 1814 records which is correct and,
in click event of the button gives 1719 records.

Can someone point me what is there which I am missing?

Thanks

Saif

 

[GriffMG]

What is the scope of the two variables dt1 and dt2?

Could it be different for the command window and
the click event?

Other trouble-shooting ideas, are you using the ; line continuation in both environments?

Could you try hard-coding the dates to test


Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Tore Bleken]

My guess is that some of the settings are different in the form's environment. Especially check Set Deleted and Set Ansi.

 

[alisaif]

Hi

Could you try hard-coding the dates to test

I checked the output of the date as below by keeping the set step on

*dt1 = Thisform.contMaster.Contperiod1.Datefromto1._datepicker1.Value
*dt2 = Thisform.contMaster.Contperiod1.Datefromto1._datepicker2.Value

In option I kept the Strict Date Level "Off"

Any idea?

Thanks

Saif

 

[Olaf Doschke]

You don't tell us, what you now tried instead of setting ct1 and dt2 via datepickers.

Instead you should set a value yourself, eg

dt1=DATE()-14
dt2=DATE()

Of course your test at command line and form has to query with the same date interval, or you get different results. That's your query parameterization and different time spans have different amount of data.
Besides that difference you might look at SET("DELETED"), this setting is per datasession and a form with private datasession may have that different in its session than at the command line, which is using the main and default first datasession the IDE itself starts.

Bye, Olaf.

 

[wtotten]

When you run the query from the Command box the environment may different than the form's environment that has you command button on it. tbleken suggested the same thing as I am suggesting. That's the only reason that the query would behave differently that I can think of.

 

[JRB-Bldr]

Have you tried to put a SET STEP ON command immediately before your SQL Query command line?

If you did that and then attempted to Run your Form (not execute SQL Query in the Command window), you should be able to then incrementally debug the SQL command.
With the WATCH window you can determine the values of the various selection criteria parameters.
You can also find out what the Environment parameters are at that precise point.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Another thing you can do - for debugging purposes - is to break the SQL command into three separate commands. Do the first join first, putting the results to a temporary cursor. Then use that cursor for the second join. Finally, apply the WHERE clause.

Examine the results at each stage, and check that they are what you expect.

I'm not suggesting that as a permanent solution to the problem, only as an aid in helping track down where it is going wrong.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[alisaif]

Thanks for the time sharing for this issue.

Saif