Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
6
Erster Beitrag:
vor 3 Jahren, 2 Monaten
Letzter Beitrag:
vor 3 Jahren, 2 Monaten
Beteiligte Autoren:
Gary Williams, kingdr, Alexandre Leclerc, Arie

MOD() returns error when used in SQL WHEN clause

Startbeitrag von Gary Williams am 20.04.2015 22: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.

Antworten:

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

von kingdr - am 20.04.2015 22:42
Sorry I overlooked as you had it in HExecuteSQLQuery(..)

von kingdr - am 20.04.2015 22:45
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

von Arie - am 21.04.2015 07:05
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.

von Gary Williams - am 21.04.2015 07:38
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

von Alexandre Leclerc - am 21.04.2015 13:58
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.