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.