Buenos días, estaba realizando la siguiente consulta, la cual me arroja los resultados esperados según lo que deseo, pero me preguntaba si podría optimizar el código a través del uso de variables y así tener menos lineas sobre todo en las subconsultas.
SELECT
T0."callID"
,T2."Name" AS "Estado"
,T0."DocNum"
,T0."createDate"
,T0."closeDate"
,(Select TX."DocNumber"
FROM SCL4 TX
WHERE TX."Object"=22 AND T0."callID"=TX."SrcvCallID")AS "N° Doc. Pedido"
,(Select TX."DocNumber"
FROM SCL4 TX
WHERE TX."Object"=18 AND T0."callID"=TX."SrcvCallID")AS "N° Doc. Factura"
,(Select TX."DocTotal"
FROM OPCH TX
WHERE TX."DocNum" = (Select TX."DocNumber"
FROM SCL4 TX
WHERE TX."Object"=18 AND T0."callID"=TX."SrcvCallID") )AS "Monto Factura"
,(Select TX."Comments"
FROM OPCH TX
WHERE TX."DocNum" = (Select TX."DocNumber"
FROM SCL4 TX
WHERE TX."Object"=18 AND T0."callID"=TX."SrcvCallID") )AS "Comentarios Factura"
,T0."U_SIS_PLACA"
,T0."U_SIS_MODELO"
,T0."U_SIS_MARCA"
,T0."U_SIS_KILMTR"
,T0."U_SIS_PXMTO"
,T0."subject"
,T1."lastName" || ', '|| T1."firstName" AS "Técnico"
,T0."descrption" AS "Comentarios Llamada"
,T0."customer"
,T0."custmrName"
FROM OSCL T0
LEFT JOIN OHEM T1 ON T0."technician" = T1."empID"
INNER JOIN OSCS T2 ON T0."status" = T2."statusID"
ORDER BY "callID" desc