Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
11
Erster Beitrag:
vor 4 Jahren, 8 Monaten
Letzter Beitrag:
vor 4 Jahren, 8 Monaten
Beteiligte Autoren:
kimor, Arie, Alexandre Leclerc, Stefan Bentvelsen, Ned!

[Solved] Query Windev 18 (or any ver)

Startbeitrag von kimor am 16.05.2013 06:10

Hi!

I'm trying to accomplish a thing in WinDev.
I have a table which result is based on five different check boxes that are
possible values in one column in the database. It's also based on one Combo Box (Grupp).

The SQL (from the query) look like:
SELECT
[Behorigheter$].Grupp AS Grupp,
[Behorigheter$].Behörighetsgrupp AS Behörighetsgrupp,
[Behorigheter$].Behörighetsgruppnamn AS Behörighetsgruppnamn,
[Behorigheter$].Miljö AS Miljö,
[Behorigheter$].Förklaring AS Förklaring
FROM
[Behorigheter$]
WHERE
[Behorigheter$].Grupp = {Par_Group}
AND
(
[Behorigheter$].Miljö = {Par_IMLR}
OR [Behorigheter$].Miljö = {Par_IMSQ}
OR [Behorigheter$].Miljö = {Par_IMSB}
OR [Behorigheter$].Miljö = {Par_IMSA}
OR [Behorigheter$].Miljö = {Par_UDSN}
OR [Behorigheter$].Miljö = {Par_PDSN}
OR [Behorigheter$].Miljö = {Par_ALL}
)


The result is not what I want. I usually work with SQL but this is the first time I use
the QUERY in Windev/WebDev.

Thanks!
Kim

Antworten:

Re: Query Windev 18 (or any ver)

Kim,
we need more info to give you an answer.
Your query looks fine to me, so what result do you get and what result would you expect.
Maybe you can post some examples of your data and your code?
How do you pass values to the parameters, when running the query?
How do you retrieve the resultset? Is it linked to a tablecontrol or are you processing the data yourself.
Things like that.

von Arie - am 16.05.2013 07:06

Re: Query Windev 18 (or any ver)

Hi Arie,
Thanks for your quick response!

I just noticed that the querycode was at the top, not the bottom which of course not give the expected result:
HExecuteQuery(QRY_Behorighet, MyConnection)
TableDisplay(TABLE_QRY_Behorighet,taCurrentFirst)


The parameters is passed on the code of the "go"-button.
IF INT_Interrupteur_2_colonnes[1]..Value = True THEN QRY_Behorighet.Par_IMSB = "IMSB"
IF INT_Interrupteur_2_colonnes[2]..Value = True THEN QRY_Behorighet.Par_IMSQ = "IMSQ"
IF INT_Interrupteur_2_colonnes[3]..Value = True THEN QRY_Behorighet.Par_IMLR = "IMLR"
IF INT_Interrupteur_2_colonnes[4]..Value = True THEN QRY_Behorighet.Par_IMSA = "IMSA"
IF INT_Interrupteur_2_colonnes[5]..Value = True THEN QRY_Behorighet.Par_UDSN = "UDSN"
IF INT_Interrupteur_2_colonnes[6]..Value = True THEN QRY_Behorighet.Par_PDSN = "PDSN"

The table is not linked since I only need Select, but of course the Content is based on the Query.

The expected result is:
Based on the selected Group (Grupp) and the selected Environment (Miljo). The Environment could be one-six in combination or all.

Hope that clears things up a bit, otherwise ask again!

Regards,
/Kim

von kimor - am 16.05.2013 07:23

Re: Query Windev 18 (or any ver)

With GO-button you mean you are just testing the query itself?

Normally, if you don't pass a value to a parameter, this whole piece is excluded from the query.
Afaik you cannot exclude params when using the test window.
So maybe you are passing some params as '' (empty string).
Which will then result records with let's say UDSN = empty in your database.
I guess you don't want these records either?
Please try to run your application and then set parameters and execute the query.

Maybe it is the TabelDisplay causing the unexpected result and not the query
Add a few lines like this to see the actual result of the queyr
FOR EACH QRY_Behorighet
Trace(QRY_Behorighet.Grupp, QRY_Behorighet.etc etc)
END

von Arie - am 16.05.2013 09:00

Re: Query Windev 18 (or any ver)

Hi Arie,

you can exclude params when using the test window, just by unchecking the checkbox before the param. In that case Null is used for that param. I use it a lot!

von Stefan Bentvelsen - am 16.05.2013 09:08

Re: Query Windev 18 (or any ver)

Hi Kimor,

On the code of your button, are you setting a parameter for the Grupp from the combo box before you execute the query? e.g. QRY_Behorighet.PAR_Group = Combo_Grupp

Like what Arie said, if this (or any) parameter isn't being included, then the results won't be what you expect.

Thanks,
Ned!

von Ned! - am 16.05.2013 10:06

Re: Query Windev 18 (or any ver)

Hi All

Thanks for all the answers!

This is the code of the "Execute search" (i.e Go-button)

QRY_Behorighet.Par_Group = COMBO_Roll

QRY_Behorighet.Par_IMLR = ""
QRY_Behorighet.Par_IMSQ = ""
QRY_Behorighet.Par_IMSB = ""
QRY_Behorighet.Par_IMSA = ""
QRY_Behorighet.Par_UDSN = ""
QRY_Behorighet.Par_PDSN = ""
QRY_Behorighet.Par_ALL = ""

IF INT_Interrupteur_2_colonnes[1]..Value = True THEN QRY_Behorighet.Par_IMSB = "IMSB"
IF INT_Interrupteur_2_colonnes[2]..Value = True THEN QRY_Behorighet.Par_IMSQ = "IMSQ"
IF INT_Interrupteur_2_colonnes[3]..Value = True THEN QRY_Behorighet.Par_IMLR = "IMLR"
IF INT_Interrupteur_2_colonnes[4]..Value = True THEN QRY_Behorighet.Par_IMSA = "IMSA"
IF INT_Interrupteur_2_colonnes[5]..Value = True THEN QRY_Behorighet.Par_UDSN = "UDSN"
IF INT_Interrupteur_2_colonnes[6]..Value = True THEN QRY_Behorighet.Par_PDSN = "PDSN"

IF INT_Interrupteur_2_colonnes[1]..Value = True AND INT_Interrupteur_2_colonnes[2]..Value = True AND
INT_Interrupteur_2_colonnes[3]..Value = True AND INT_Interrupteur_2_colonnes[4]..Value = True AND
INT_Interrupteur_2_colonnes[5]..Value = True AND INT_Interrupteur_2_colonnes[6]..Value = True THEN
QRY_Behorighet.Par_ALL = "ALLA"
END

HExecuteQuery(QRY_Behorighet, MyConnection)
TableDisplay(TABLE_QRY_Behorighet,taCurrentFirst)


Any suggestions for a better solution or this ok?
Kim

von kimor - am 16.05.2013 12:26

Re: Query Windev 18 (or any ver)

Hi kimor,

You do not need (and I think in your case you should not) set the parameters with empty string. This means that the parameter must be an empty string, and not that it must be omited.

Example:
QRY_Behorighet.Par_IMLR = "" // Means that is MUST be an empty value to be selected
QRY_Behorighet.Par_IMLR = Null // Means that the parameter must NOT be considered

This make the DB check for empty value and consumes query time for nothing.

Your code, starting from IF's should work ok. Just keep the IF's. If a parameter is not specified, it will be ignored. A little tip here: you do not need to use ..Value for the INT control. Simply INT_X[n] will do.

In your query you could also use the IN operator ("is in the list") and simply send the list of Miljö you want to select ("UDSN"+cr+"PDSN"). But it will prety much end up giving the same result.

Best regards,
Alexandre Leclerc

von Alexandre Leclerc - am 16.05.2013 14:22

Re: Query Windev 18 (or any ver)

Hi Alexandre,

The QRY_Behorighet.Par_IMLR = "" is to reset the parameter from previous search(es).
The parameters is set below these statements. If not set, still empty...
The DB is not long so it doesn't matter if it searches for "empties".

"In your query you could also use the IN operator ("is in the list") and simply send the list of Miljö you want to select ("UDSN"+cr+"PDSN"). But it will prety much end up giving the same result."
I don't know how to do that in the Query Editor and if it will give me the same result it might as well be as it is...

Regards
Kim

von kimor - am 16.05.2013 15:36

Re: Query Windev 18 (or any ver)

Hi kimor,

Instead of setting an empty string, set it to Null.

QRY_Behorighet.Par_IMLR = Null

From query editor, when you define the selection conditions of an item, you can choose the operator in the drop box. By defaut it is "is equal to" and the you select "the parameter", etc.

In the same drop box, you can chose (translated from French) "is in the list". It will the use the IN operator and then you submit a single parameter containing a list of values separated by either ; TAB or CR.

Best regards,
Alexandre Leclerc

von Alexandre Leclerc - am 16.05.2013 15:45

Re: Query Windev 18 (or any ver)

Hi Alexandre,

Ok, I see..
I'll try that, thanks

Regards
Kim

von kimor - am 16.05.2013 16:13
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.