|
| webdev 20 stops working when I paste a query |
| Iniciado por guest, 12,may. 2015 16:16 - 8 respuestas |
| |
| | | |
|
| |
| Publicado el 12,mayo 2015 - 16:16 |
Hello, something weird happens when I paste a query in webdev 20 and the program stops working.
The syntax of the query is correct, since I have tested it in SQL Server. However, even if it was not correct, why would webdev stop working???? It is driving me crazy.
I am posting the query as well, even though it will be of little use since you don't know what analysis I am using:
SELECT t.niveli_organigram,t.kodi,COUNT(t.kodi) AS sasia_sherbimit,t.emertimi
FROM (
SELECT s.niveli_organigram,(SELECT label FROM ips_service_types WHERE id=s.id_sherbimit) AS kodi,(SELECT name FROM ips_service_types WHERE id=s.id_sherbimit) AS emertimi
FROM (
SELECT DISTINCT A.id AS id_aplikimit,r.niveli_organigram,art.svet_id AS id_sherbimit FROM ips_user_application_steps s,ips_applications AS A,ips_application_requests AS art,
(SELECT ips_organisation_elements.ogn_level AS niveli_organigram,ips_user_organigram_positions.usr_id
FROM ips_user_organigram_positions,ips_organisation_elements,ips_users,ips_applications
WHERE ips_organisation_elements.id=ips_user_organigram_positions.org_id AND
ips_users.id=ips_user_organigram_positions.usr_id AND ips_users.activ=1 AND ips_users.end_date IS NOT NULL ) AS r
WHERE r.usr_id=s.usr_id AND A.id=s.apn_id AND A.application_date >='2013-10-30' AND A.application_date<='2014-01-30' AND A.ast_id NOT IN (1000000,1000007,1000016)
AND art.apn_id=A.id
AND r.niveli_organigram LIKE '001%') AS s) t
GROUP BY t.niveli_organigram,t.kodi,t.emertimi |
| |
| |
| | | |
|
| | |
| |
| Publicado el 12,mayo 2015 - 16:32 |
| Did you use that SQL to create a WinDev-query or are you using HExecuteSQLQuery() to run the query? |
| |
| |
| | | |
|
| | |
| |
| Publicado el 12,mayo 2015 - 16:40 |
I will use HexecuteQuery("query_name","connection_name",hRequêteSansCorrection)...
However it doesn't let me save it , let alone run it... |
| |
| |
| | | |
|
| | |
| |
| Publicado el 12,mayo 2015 - 16:50 |
Hi Kozeta,
Split your request and and test each part. Then add a layer of complexity. When you have many nested selects, anything can go wrong. I find some stuff you are doing a bit strange, but it might be ok (or not).
Also, I'm not sure your query is very optimized. It does not look so. I would suggest that you try to use the query editor. Otherwise, I hope you are good in SQL and that you know what you are doing. You can do fantastic things in SQL, but also very bad things. Maybe using simple inner/outer joints could simplify many aspects of your query.
Again, just start with your core query, than add complexity one after the other.
Best regards,
Alexandre Leclerc
Edit 1: PS. You can try your queries directly in the HFSQL Control Center. This will help you test very quickly the query and any errors. Then if this works there, it should work in WB. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 12,mayo 2015 - 17:00 |
| Thank you Alexandre, I will see what I can do....It does not have errors since everything goes well when I test it on SQL Server (Since I am using SQL Server for this application). I don't know what is going wrong with webdev. Anyway I am following your advice about optimizations and "joins" and I hope this will resolve my problem. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 12,mayo 2015 - 17:47 |
Hi
you are saying that you are pasting the query in webdev, but you are not saying WHERE in webdev, and webdev is a big place...
If you are pasting it in the code, you shouldn't have any problem as long as there is no special character in the string... So an intermediate step via a text file could solve the problem
If you are pasting it in the query editor in order to use the retro engineering function, maybe you shouldn't, as a valid MSSQL query may not be understood by the webdev query editor.
If it's somewhere else... let us know
Best regards |
| |
| |
| | | |
|
| | |
| |
| Publicado el 12,mayo 2015 - 19:05 |
Hi Kozeta,
Adding to Fabrice, you say you are using HExecuteQuery() so must we understand that this is a query you made in the Query Editor? Because if this is a string query you try to run you must use HExecuteSQLQuery() with hRequêteSansCorrection.
Best regards,
Alexandre Leclerc |
| |
| |
| | | |
|
| | |
| |
| Publicado el 13,mayo 2015 - 08:14 |
I am pasting it in the query editor....
However, I tried to paste it in the code (as a string) and text file as well and I used HExecuteSQLQuery(). When I run it, it throws the same error and webdev stops working at all.
I guess the only option I have is : rebuilding the query from within webdev trying not to "upset" webdev..... It is very annoying that webdev won't accept a query just because it may be a little bit more complicated.
I will also try to optimize it, hoping that the error will go away... |
| |
| |
| | | |
|
| | |
| |
| Publicado el 13,mayo 2015 - 14:26 |
Hi
okay, now we know what's going on...
So the problem is:
when you paste it in the query editor, the editor tries to retro-engineer it into something it does understand, and fails (and crashes)
Now, as you query is SPECIFIC to MSSQL, that is not a problem, instead, do as Alexandre suggested and:
- paste it as a string in your code
- use HExecuteSQLQuery()
- AND DO NOT FORGET TO USE THE hQueryWithoutCorrection parameter in it.... THIS IS WHAT WILL MAKE IT WORK, as webdev will not try to interpret it/understand it, and therefore will just pass it along to your DB.
Best regards |
| |
| |
| | | |
|
| | | | |
| | |
|