asked
Here is a SQL statement that was previously returning results and then all of a sudden started returning none. Based on the query, what are some possible reasons this could have happened?
Here is a SQL statement that was previously returning results and then all of a sudden started returning none. Based on the query, what are some possible reasons this could have happened? SELECT SOP10100.SOPNUMBE, CSTPONBR,CUSTNMBR,BACHNUMB,Reqshipdate,MABD.USRDAT02, daysbeforemabd,MABD.USERDEF2,MAX(MDS_ORDER.CreatedDate) AS PACKED_DATE, MDS_ORDER.CreatedBy AS PACKED_BY FROM SOP10100 INNER JOIN SOP10106 AS MABD ON SOP10100.SOPNUMBE = MABD.SOPNUMBE INNER JOIN [sjdev].[SJcache].[dbo].RoutingCust RoutCust ON SOP10100.Custnmbr = RoutCust.CustId INNER JOIN SOP10106 ON SOP10100.SOPNUMBE = SOP10106.SOPNUMBE LEFT JOIN MDS_A1_ShipmentOrderXref AS MDS_ORDER ON SOP10100.SOPNUMBE = MDS_ORDER.DocumentNumber WHERE SOP10100.BACHNUMB like 'PACKED%' AND MABD.USERDEF2='' AND RTRIM(SOP10100.SOPNUMBE) NOT IN (SELECT RTRIM(SOPNUMBE) FROM [sjdev].[SJcache].[dbo].RoutingLog) GROUP BY MDS_ORDER.CreatedBy, SOP10100.SOPNUMBE, CSTPONBR, CUSTNMBR, BACHNUMB, Reqshipdate, MABD.USRDAT02, daysbeforemabd, MABD.USERDEF2
Just check all the inner joins and where clause conditions. Here are some of the possibilities - maybe there are now... - ...no rows in MABD.SOPNUMBE that are equal to any rows in SOP10100.SOPNUMBE - ...no rows in RoutCust.CustId that are equal to any rows in SOP10100.Custnmbr - ...no rows in SOP10106.SOPNUMBE that are equal to any rows in SOP10100.SOPNUMBE - ...no rows in SOP10100 where BACHNUMB like 'PACKED%' - ...no rows in MABD where USERDEF2 = '' - ...some rows where rtrim(SOP1011.SOPNUMBE) IN (SELECT RTRIM...) Outside of the above, maybe one these: - set rowcount 0 - set fmtonly on - a collation change that caused one of the first 6 conditions to be met. - one or more of the objects in the query are views that had their definition changed in a way that causes one of the first 6 conditions to be met - row level security changes that prevent the login running the query from seeing rows to which it used to have permission (or the query is run by a different login altogether).
17 People are following this question.
