Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
4
Erster Beitrag:
vor 4 Monaten, 2 Wochen
Letzter Beitrag:
vor 4 Monaten, 1 Woche
Beteiligte Autoren:
Gianni Spano, Peter Holemans, Fabrice Harari

[WD] Query Parameters

Startbeitrag von Gianni Spano am 07.07.2017 10:50

Hello to All

I have a little issue trying to set a parameter in a predefined query.

This is the query content:

SELECT R.data,G.descrizione AS PuntoGuardia,SUM(R.importo_prestazione) AS tot_impo_prescr, COUNT(*) AS tot_prescr,
(SELECT COUNT(*) FROM registro LEFT JOIN prestazioni ON registro.tipo_prestazione = prestazioni.id WHERE registro.tipo_prestazione = 1 AND registro.data = r.data AND R.id_gmt = registro.id_gmt) VISIT_AMB,
(SELECT COUNT(*) FROM registro LEFT JOIN prestazioni ON registro.tipo_prestazione = prestazioni.id WHERE registro.tipo_prestazione = 2 AND registro.data = r.data AND R.id_gmt = registro.id_gmt) VISIT_DOC,
(SELECT COUNT(*) FROM registro LEFT JOIN prestazioni ON registro.tipo_prestazione = prestazioni.id WHERE registro.tipo_prestazione = 3 AND registro.data = r.data AND R.id_gmt = registro.id_gmt) RIP_PRESCR_FARM,
(SELECT COUNT(*) FROM registro LEFT JOIN prestazioni ON registro.tipo_prestazione = prestazioni.id WHERE registro.tipo_prestazione = 4 AND registro.data = r.data AND R.id_gmt = registro.id_gmt) RICH_ACCERT_DIAGN_O_RIC,
(SELECT COUNT(*) FROM registro LEFT JOIN prestazioni ON registro.tipo_prestazione = prestazioni.id WHERE registro.tipo_prestazione IN(5,7) AND registro.data = r.data AND R.id_gmt = registro.id_gmt) varie,
(SELECT COUNT(*) FROM registro LEFT JOIN prestazioni ON registro.tipo_prestazione = prestazioni.id WHERE registro.tipo_prestazione = 6 AND registro.data = r.data AND R.id_gmt = registro.id_gmt) INR,
(SELECT COUNT(*) FROM registro LEFT JOIN prestazioni ON registro.tipo_prestazione = prestazioni.id WHERE registro.tipo_prestazione = 8 AND registro.data = r.data AND R.id_gmt = registro.id_gmt) TERAP_INIETTIVA,
(SELECT COUNT(*) FROM registro LEFT JOIN prestazioni ON registro.tipo_prestazione = prestazioni.id WHERE registro.tipo_prestazione = 9 AND registro.data = r.data AND R.id_gmt = registro.id_gmt) MISUR_PRESSIONE_ART,
(SELECT COUNT(*) FROM registro LEFT JOIN prestazioni ON registro.tipo_prestazione = prestazioni.id WHERE registro.tipo_prestazione = 10 AND registro.data = r.data AND R.id_gmt = registro.id_gmt) VIS_BREVE,
(SELECT COUNT(*) FROM registro LEFT JOIN prestazioni ON registro.tipo_prestazione = prestazioni.id WHERE registro.tipo_prestazione = 11 AND registro.data = r.data AND R.id_gmt = registro.id_gmt) VIS_CONTROLLO,
(SELECT COUNT(*) FROM registro LEFT JOIN prestazioni ON registro.tipo_prestazione = prestazioni.id WHERE registro.tipo_prestazione = 12 AND registro.data = r.data AND R.id_gmt = registro.id_gmt) CONS_TELEF
FROM registro AS R
LEFT JOIN id_user I ON R.id_medico = I.User_ID
LEFT JOIN guardie G ON R.id_gmt = G.id
LEFT JOIN prestazioni P ON R.tipo_prestazione = P.id
WHERE g.distretto={pDistretto}
GROUP BY R.data, G.descrizione
ORDER BY r.data DESC



As you can see i have a unique parameter to set (pDistretto).
Reading this forum and the online help, the reccomandation is to use this approach:


nDistretto is int=COMBO_Distretti - 1

{"QRY_Globale.pDistretto"}=nDistretto

bRes=HExecuteQuery(QRY_Globale,hQueryDefault)



I would know if the code above is correct or i need to modify something, because (in case it's correct) i don't obtain the list of records in the table...
But if i put the value directly (..WHERE g.distretto=1..) the records found are shown on the table...(?!?!?)

Some suggestion??
Thanks in advance.

Gianni Spano

Antworten:

Hi

if you query was defined in the query editor (and that is MUCH simpler), then simply do queryName.ParamName=value

If your query is simply a string, then use the REPLACE function to replace the parameter with it's value.

Best regards

von Fabrice Harari - am 07.07.2017 14:23
Hello Fabrice

Thank you for your reply.

The query QRY_Globale has been built using the query editor and the parameter (pDistretto) has been defined as you suggested.

QRY_Globale.pDistretto=some value

The main problem remains an error when i try to run the query.
This is the error message when i try to execute the query.

Error message:
******************************************************
Error at line 33 of Local Procedure ReportIncassiPrestazioni process.
The HFSQL 'QRY_Globale.pDistretto' item is unknown.
The data source is not initialized.
- If it is a data file, the file was not found in the analysis or it was not described by HDeclare/HDeclareExternal.
- If it is a query or a view, the execution may have failed.
To retrieve the corresponding error, check the result of HExecuteQuery/HExecuteSQLQuery/HCreateView.

*************************************************************

I'm trying to execute this query on a MySql database.
Is there something to extra check or set??

Thanks in advance
Gianni

von Gianni Spano - am 10.07.2017 17:18
Hi Gianni,

Why are you using the reserved 'indirection' {} characters when assigning the query param.
- If the query is named QRY_Global it will work like QRY_Global then QRY_Globale.pDistretto=nDistretto will work just fine.
- If QRY_Global is a string variable that sets to a certain query you need to use indirection:{QRY_Globale+"."+pDistretto, indQueryParam}=nDistretto

This is just by heart... (Been some months I worked a lot in WX)

Cheers,

Peter Holemans

von Peter Holemans - am 10.07.2017 18:07
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.