|
FOROS PROFESIONALES
WINDEV, WEBDEV y WINDEV Mobile |
| | | | | |
| Iniciado por guest, 07,jul. 2017 12:50 - 3 respuestas |
| |
| | | |
|
| |
| Publicado el 07,julio 2017 - 12: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 |
| |
| |
| | | |
|
| | |
| |
| Publicado el 07,julio 2017 - 16:23 |
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 |
| |
| |
| | | |
|
| | |
| |
| Publicado el 10,julio 2017 - 19:18 |
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 |
| |
| |
| | | |
|
| | |
| |
| Publicado el 10,julio 2017 - 20:07 |
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 |
| |
| |
| | | |
|
| | | | |
| | |
| | |
| |
|
|
|