|
| MOD() returns error when used in SQL WHEN clause |
| Débuté par Gary Williams, 21 avr. 2015 00:33 - 5 réponses |
| |
| | | |
|
| |
Posté le 21 avril 2015 - 00:33 |
I am using WinDev 19 and if I use MOD() in an SQL WHERE clause WinDev returns error;
"Error returned by <> server:
Internal Error in WDSQL DLL."
Basically I need to know if the pages being selected are exactly divisible by the pagecount field in the data file.
I have simplified my code to do a test proof and this generates the error;
dsSTMT is Data Source cConnection is Connection = DatabaseConnection nPageValue is int = 64 cFilterString is string = StringBuild("WHERE MOD(%1,pagecount)=0",nPageValue) SQLStatement is string = "Select * from laydown %1" IF HExecuteSQLQuery(dsSTMT ,cConnection ,hQueryWithoutCorrection,StringBuild(SQLStatement,cFilterString)) THEN Info("Success") ELSE Info(HErrorInfo()) END
Strangely if I wanted the MOD the other way around, It does not error;
MOD(pagecount,%1)=0
I have a work around by using;
DIV(%1,pagecount)=ROUND(DIV(%1,pagecount),0)
But that not very neat, has anyone else come across this? or is there another way to achieve the same result?
I have used MOD() in another programming languages SQL WHERE so I don't thinks it's an SQL standard issue.
I am relatively new to WinDev so maybe it's me, but I can't see that it is.
Gary. |
| |
| |
| | | |
|
| | |
| |
Posté le 21 avril 2015 - 00:42 |
Gary
I guess you miss out one line of code as below:
...
cFilterString is string = StringBuild("WHERE MOD(%1,pagecount)=0",nPageValue)
SQLStatement is string = "Select * from laydown %1"
...
SQLStatement = StringBuild(SQLStatement, cFilterString)
info(SQLStatement) // to see if sql is OK or not
HTH
Cheers
King |
| |
| |
| | | |
|
| | |
| |
Posté le 21 avril 2015 - 00:45 |
Sorry I overlooked as you had it in HExecuteSQLQuery(..) |
| |
| |
| | | |
|
| | |
| |
Posté le 21 avril 2015 - 09:05 |
Hi Gary,
I did a small test and with the same error.
I looks like MOD is not supported in the WHERE clause??
But you can use it in the SELECT part.
Which in turn gives you the option to do something like
SELECT * FROM (
SELECT field1,field2, MOD(%1,pagecount) as count FROM laydown)
WHERE count=0 |
| |
| |
| | | |
|
| | |
| |
Posté le 21 avril 2015 - 09:38 |
Thanks Arie,
I'll have a play with that, it looks neater than mine, I'll try and do some tests and see if your way is faster than mine.
I'll also raise with PcSoft as a bug.
Gary. |
| |
| |
| | | |
|
| | |
| |
Posté le 21 avril 2015 - 15:58 |
Hi guys,
Unless I'm missing a point, it is not necessary to make a sub query. You can do that in a simple query.
SELECT *, MOD(%1,pagecount) as modpc FROM laydown WHERE modpc=0
The down side is that you will have a modpc column with 0 values, but at least it will work.
Kind regards,
Alexandre Leclerc |
| |
| |
| | | |
|
| | | | |
| | |
|