sql having 1

[mjcmkrsr]

Hi,

This code executed from a page's click event works fine and the results are correct

Select Stagiaires.cName, ;
	Stagiaires.cGender, ;
	Stagiaires.dBirth, ;
	Stagiaires.cEmail, ;
	NVL(COUNT(Students.dEntry), 0), ;
	NVL(MIN(Students.dEntry), {}), ;
	NVL(MAX(Students.dExit), {}), ;
	Stagiaires.PKey ;
	From Stagiaires ;
		LEFT JOIN Students ON Stagiaires.PKey = Students.PKey ;
	Where IIF(This.chkEmail.Value = .T., AT("@", Stagiaires.cEmail) != 0, .T. ;
	GROUP BY 1, 2, 3, 4, 8 ;
	[highlight #FCE94F]HAVING DATE() - MAX(Students.dExit) < 3650 ;[/highlight]
	Order by 1, 3 ;
Into Cursor curStagiaires

but I want to toggle the value of chkEmail and make the results depending on it. However the following code throws an error: Alias chkEmail not found

Select Stagiaires.cName, ;
	Stagiaires.cGender, ;
	Stagiaires.dBirth, ;
	Stagiaires.cEmail, ;
	NVL(COUNT(Students.dEntry), 0), ;
	NVL(MIN(Students.dEntry), {}), ;
	NVL(MAX(Students.dExit), {}), ;
	Stagiaires.PKey ;
	From Stagiaires ;
		LEFT JOIN Students ON Stagiaires.PKey = Students.PKey ;
	Where IIF(This.chkEmail.Value = .T., AT("@", Stagiaires.cEmail) != 0, .T. ;
	GROUP BY 1, 2, 3, 4, 8 ;
	[highlight #FCE94F]HAVING IIF(This.chkEmail.Value = .T., DATE() - MAX(Students.dExit) < 3650, .T.) ;[/highlight]
	Order by 1, 3 ;
Into Cursor curStagiaires

Hence my question: does HAVING not accept conditional settings

Thanks

MarK

 

[Olaf Doschke]

That's strange. In general, you can use IIF within HAVING.

Create Cursor crsTest (num int)
Insert into crsTest values (1)
Insert into crsTest values (2)
Insert into crsTest values (2)
Insert into crsTest values (3)
Insert into crsTest values (3)
Insert into crsTest values (3)
Select num, Count(*) from crsTest group by num having Iif(.t., Count(*)>1, .t.) Into Cursor crsMultiples
Select num, Count(*) from crsTest group by num having Iif(.f., Count(*)>1, .t.) Into Cursor crsAll

If something would qualify as an alias it would be "THIS", not "chkEmail". THIS.chkEmail.Value is a triple part name, so it can't be alias.field and THIS should trigger looking for members of the object the code is in. In your case a checkbox of the page. But I also tried something like _screen.object.value after adding an object, the parser tries to find "object" as an alias, no matter whether you use THIS or another parent object referenced by name directly. Indeed only the first level of a multi part name should be considered an alias by SQL.

You could first set a variable lSwitch = This.chkEmail.value:

lSwitch = .t.
Select num, Count(*) from crsTest group by num having Iif(m.lSwitch, Count(*)>1, .t.) Into Cursor crsMultiples

But notice IIF always executes both expressions, so you'd evaluate DATE() - MAX(Students.dExit) < 3650 in any case, so you don't spare calculations anyway, you would need to do both queries in a THEN and ELSE branch of an IF-Statement outside of the SQL, and write out both variants of the SQL query if you'd want to avoid making that calculation in case This.chkEmail.Value=.f..

I'd opt for GOMONTH(MAX(Students.dExit),120)>DATE() anyway, and vary the years from 10 to something astronomical:

MaxYears = IIF(This.chkEmail.Value,10,5000)
Select Stagiaires.cName, ;
	Stagiaires.cGender, ;
	Stagiaires.dBirth, ;
	Stagiaires.cEmail, ;
	NVL(COUNT(Students.dEntry), 0), ;
	NVL(MIN(Students.dEntry), {}), ;
	NVL(MAX(Students.dExit), {}), ;
	Stagiaires.PKey ;
	From Stagiaires ;
		LEFT JOIN Students ON Stagiaires.PKey = Students.PKey ;
	Where IIF(This.chkEmail.Value = .T., AT("@", Stagiaires.cEmail) != 0, .T. ;
	GROUP BY 1, 2, 3, 4, 8 ;
	HAVING GOMONTH(MAX(Students.dExit),m.MaxYears*12)> DATE();
	Order by 1, 3 ;
Into Cursor curStagiaires

Bye. Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

Besides, there is a closing bracket missing in the IIF of the WHERE clause.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

And by the way, all 3 SQL engines error on this, as SET ENGINEBEHAVIOR 80 or 70 doesn't help. I'm not sure that points out the problem existed in VFP7 (and maybe earlier), but at least it doesn't help to go back to the older engines included in VFP9 for backward compatibility.

Bye, Olaf.



Olaf Doschke Software Engineering

https://www.doschke.name

 

[mjcmkrsr]

Hi Olaf,

llSwitch = This.chkEmail.Value did the trick

Thanks

marK

 

[atlopes]

Mark,

Olaf already presented you cleaner approaches to your expression but, for the record, what you experienced is a glitch in the VFP native SQL parser. Preceding the [tt]This[/tt] reference with a mdot resolves the contextualization that the parser fails to carry out.

That is:

HAVING IIF([highlight #FCE94F]m.[/highlight]This.chkEmail.Value = .T., DATE() - MAX(Students.dExit) < 3650, .T.) ;

(because of this quirk, which is inconsistent, by the way, I avoid to / never reference [tt]This[/tt] or [tt]Thisform[/tt] in VFP native SQL statements).

 

[Olaf Doschke]

Agreed, mainly not using any object reference. Because as said _screen also didn't work and m._screen is no solution to that, either, so step 1 is to put a value into a variable, even if m.This also works.

If you want to make sure you express yourself unambiguous use ?m.variable, that also makes it compatible with SQL passthrough SQLEXEC, the parameterization is not limited to SQLEXEC, it also works in native SQL and should indicate using an external parameter anyway.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[atlopes]

Unfortunately, Olaf, the support for the parametrization of native SQL statements stopped half-way (or less). That's not a big deal because using memory variables serves the same purpose, but the different syntax will diminish the statement's portability.

CREATE CURSOR Test (t1 Int, t2 Int)
INSERT INTO Test VALUES (1, 1)
INSERT INTO Test VALUES (1, 2)
m.vn = 2
UPDATE Test SET t1 = 2 WHERE t2 = m.vn   && this works
UPDATE Test SET t1 = 2 WHERE t2 = ?m.vn  && but this doesn't

 

[mjcmkrsr]

Hi

Thanks for all the hints. I switched to local logical variables. Works as expected. Below the code snippet

LOCAL loPage, liTotal, lcOrderBy, llEMail, llActif, llBlist, llCancelled, llCCV, llEName, llENation
LOCAL ARRAY laMales[1], laFemales[1]

loPage = ThisForm.pgfSearch.Page8

llEName = EMPTY(ALLTRIM(This.txtName.Value))
llENation = EMPTY(ALLTRIM(This.cboNat.Value))
llActif = This.chkActif.Value
llBlist = This.chkBlist.Value
llCancelled = This.chkCancelled.Value
llCCV = This.chkCCV.Value
llEmail = This.chkEmail.Value

Select Stagiaires.cName, ;
	Stagiaires.cGender, ;
	Stagiaires.dBirth, ;
	Stagiaires.cEmail, ;
	NVL(COUNT(Students.dEntry), 0), ;
	NVL(MIN(Students.dEntry), {}), ;
	NVL(MAX(Students.dExit), {}), ;
	Stagiaires.cCountry, ;
	Stagiaires.lFileClosed, ;
	Stagiaires.lCancel, ;
	Stagiaires.lBList, ;
	IIF(AT("CCV", mContact) != 0, .T., .F.) AS lCCV, ;
	Stagiaires.PKey ;
	From Stagiaires ;
		LEFT JOIN Students ON Stagiaires.PKey = Students.PKey ;
	Where IIF(llEName, .T., AT(Upper(AllTrim(This.txtName.Value)), Upper(Stagiaires.cName)) != 0) ;
		AND IIF(llENation, .T., AT(Upper(AllTrim(This.cboNat.Value)), Upper(Stagiaires.cCountry)) != 0) ;
		AND IIF(llActif, Stagiaires.lFileClosed = .F., .T.) ;
		AND IIF(llBList, Stagiaires.lBList = .T., .T.) ;
		AND IIF(llCancelled, Stagiaires.lCancel = .T., .T.) ;
		AND IIF(llCCV, AT("CCV", mContact) != 0, .T.) ;
		AND IIF(llEmail, AT("@", Stagiaires.cEmail) != 0 ;
					AND Stagiaires.lCancel = .F. ;
					AND Stagiaires.lBlist = .F., .T.) ;
	GROUP BY 1, 2, 3, 4, 8, 9, 10, 11, 12, 13 ;
	HAVING IIF(llEmail, DATE() - MAX(Students.dExit) < 3650, .T.) ;
	Order by 1, 3 ;
	Into Cursor curStagiaires

marK

 

[Olaf Doschke]

Atlopes, I get it, but we talk of SELECT-SQL.

OK, that's quite a lot of variables. And they all (mostly) come from controls. You could create a cursor with parameters as its fields and bind controls to that, then use the cursor as source of the parameter values. the control binding would remove the need to read all the control value properties, the binding updates the cursor without any further code.

Likewise an object you might create from data by SCATTER NAME loParams and use ? m.loParams.fieldname as parameters or base loParams on the EMPTY class and binding to object properties in the filter form.

Bye, Olaf.


Olaf Doschke Software Engineering

https://www.doschke.name

 

[mjcmkrsr]

Hi Olaf,

Thanks for those further hints. I'll think about implementing them.

Bye

marK