SQL Query

[CarlesCostaMun]

Hi everybody,

I don't know if this place is the best to ask the question, but i will try it.

I tell you. I'm trying to do a query but it is very slow (> 12 sec)

WHERE 
AND T0.DocEntry not in(
				select T.D FROM
				(
				select T2.BaseEntry as [D] from DRF1 T0 
				INNER JOIN ODRF T1 ON T0.U_XBaseEntry = T1.DocEntry
				INNER JOIN DRF1 T2 ON T0.U_XBaseEntry = T2.DocEntry
				where T0.ObjType = '13' and T0.U_XBaseType ='15'
				union all
				select T1.U_Xdocentryps as [D] from INV1 T0 INNER JOIN ODLN T1 ON T0.BaseEntry = T1.DocEntry
				) T WHERE 1=1	
			)

For this, less than 1 sec.

WHERE 
AND T0.DocEntry not in(
				select T.D FROM
				(
					select T1.U_Xdocentryps as [D] from INV1 T0 INNER JOIN ODLN T1 ON T0.BaseEntry = T1.DocEntry
				) T WHERE 1=1	
			)

For this, less than 1 sec.

WHERE 
AND T0.DocEntry not in(
				select T.D FROM
				(
					select T2.BaseEntry as [D] from DRF1 T0 
				INNER JOIN ODRF T1 ON T0.U_XBaseEntry = T1.DocEntry
				INNER JOIN DRF1 T2 ON T0.U_XBaseEntry = T2.DocEntry
				where T0.ObjType = '13' and T0.U_XBaseType ='15'
				) T WHERE 1=1	
			)

For this, less than 1 sec.

				select T.D FROM
				(
					select T2.BaseEntry as [D] from DRF1 T0 
				INNER JOIN ODRF T1 ON T0.U_XBaseEntry = T1.DocEntry
				INNER JOIN DRF1 T2 ON T0.U_XBaseEntry = T2.DocEntry
				where T0.ObjType = '13' and T0.U_XBaseType ='15'
				) T WHERE 1=1	

I think that the problem is the UNION inside where.

Is possible to do a query before the where? Something like:

Declare @x as Xx = 	select T.D FROM
				(
					select T2.BaseEntry as [D] from DRF1 T0 
				INNER JOIN ODRF T1 ON T0.U_XBaseEntry = T1.DocEntry
				INNER JOIN DRF1 T2 ON T0.U_XBaseEntry = T2.DocEntry
				where T0.ObjType = '13' and T0.U_XBaseType ='15'
				) T WHERE 1=1
...
...
WHERE 
T0.Docentry not in(@x)

Thanks

Carles

Edited by: Carles Costa on Jul 22, 2011 1:47 PM

Edited by: Carles Costa on Jul 22, 2011 1:49 PM

[Former Member]

Hi,

You can post your inquiry at SAP Business One - SAP Business One Core.

Yes you put where statement.

	select xT0.D FROM
				(
					select T2.BaseEntry as [D] from DRF1 T0 
				INNER JOIN ODRF T1 ON T0.U_XBaseEntry = T1.DocEntry
				INNER JOIN DRF1 T2 ON T0.U_XBaseEntry = T2.DocEntry
				where T0.ObjType = '13' and T0.U_XBaseType ='15'
				) as xT0


WHERE 
T0.Docentry not in( SQL Statement)

Thanks,

Clint

[former_member184473]

Hello Carles,

Maybe a thread in the ABAP forum:

/community [original link is broken]

Regards,

Eduardo Rezende