Debugging VSQL Queries
VisionR/VSQL may become quite complicated sometimes. Supposse you want to debug a query. You can do so by firing the following command into the VisionR console (being it a v5 web console, or v6 console).
You need to first select the base object :
set -o module.some_entry
Then you can generate the query by adding some false condition to the query (i.e. 0=1)
debug -c select id where 0 = 1 group by id having count(stocks) = 0 and count(sales) = 0
The result looks like :
Executing : SELECT o0."id" AS PVA_0 FROM VISIONR.pv_m_ope AS o0 LEFT JOIN VISIONR.pv_m_prt AS a1 ON (o0.id = a1."opg_entry") AND ( ( ( ( ( ( ( ( a1."session_id" IS NULL OR a1."session_id" = §_ROOT_SESSION_ID§ ) ) AND a1."is_archived" = 0 ) AND a1."id" <> 0 ) ) AND a1."objectdef" IN ( 3 , 5646 , 5647 , 5648 , 12288 ) ) ) ) LEFT JOIN VISIONR.pv_c_objdef AS a2 ON (a1."objectdef" = a2.id) AND ((a2."id" <> 0 ) ) LEFT JOIN VISIONR.pv_m_prt AS a1_RF0 ON (a1_RF0.id = a1.id) AND (( ( ( a2."code" = 'protocol_stock' or a2."code" = 'protocol_return' ) ) ) ) LEFT JOIN VISIONR.pv_m_prt AS a1_RF1 ON (a1_RF1.id = a1.id) AND (( ( a2."code" = 'protocol_sale' ) ) ) WHERE ( ( o0."session_id" IS NULL OR o0."session_id" = §_ROOT_SESSION_ID§ ) ) AND (o0."is_archived" = 0 ) AND (o0."id" <> 0 ) AND ( 0 = 1 ) GROUP BY o0."id" HAVING (count ( a1_R0."id" ) = 0 and count ( a1_RF1."id" ) = 0 )
The ROOT_SESSION_ID may be removed from the query and the resulting text executed. The SELECT may be changed to DELETE and issued directly to the RDBMS back-end.
nb: please make sure the VisionR server is NOT running in the same time, so that cache collisions are avoided.