Usar variables para optimizar el codigo

SAP Business One
1

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
1 me gusta
11

Por lo visto nadie te puede aportar una optimización de código.
Si tu no la has encontrado, deberías cerrar el mensaje.

12

Buenas… prueba con este código :slight_smile:

SELECT 
  T0."callID",
  T2."Name" AS "Estado",
  T0."DocNum",
  T0."createDate", 
  T0."closeDate",
  SCL4_Pedido."DocNumber" AS "N° Doc. Pedido",
  SCL4_Factura."DocNumber" AS "N° Doc. Factura",
  OPCH_Factura."DocTotal" AS "Monto Factura",
  OPCH_Factura."Comments" 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"
LEFT JOIN SCL4 SCL4_Pedido ON T0."callID" = SCL4_Pedido."SrcvCallID" AND SCL4_Pedido."Object" = 22
LEFT JOIN SCL4 SCL4_Factura ON T0."callID" = SCL4_Factura."SrcvCallID" AND SCL4_Factura."Object" = 18
LEFT JOIN OPCH OPCH_Factura ON SCL4_Factura."DocNumber" = OPCH_Factura."DocNum"
ORDER BY T0."callID" DESC;