Database

Etienne_123 wrote:

some of the columns come back as NULL. However, when I SELECT TOP 1000 rows
from the view, the records in these columns are returned with the correct values

UPPER(DOC.ORIGINAL_FILE_NAME) like '%TEST\_%'

Select
KIDID,  
STUDENTNUMBER,  
KIDNAME,  
KIDLASTNAME ,  
SCHOOLNAME ,  
KIDGRADE ,  
KIDCLASS ,  
SCHOOLID, 
TEMP_BARCODE,
Cast(PHOTO as Image) as [PHOTO],
ACCOUNTID,
Balance
From
(
SELECT  
K.KIDID,  
K.STUDENTNUMBER,  
KIDNAME,  
KIDLASTNAME ,  
SC.SCHOOLNAME ,  
KIDGRADE ,  
KIDCLASS ,  
SC.SCHOOLID, 
k.TEMP_BARCODE,
Cast(PHOTO as Varbinary) Photo, ---- Some Changes
A.ACCOUNTID
,SUM(TRANSACTION_AMOUNT) AS [BALANCE] 
FROM  
KIDS_DETAILS K  
INNER JOIN SCHOOL SC  
ON K.SCHOOLID = SC.SCHOOLID  
INNER JOIN PARENTKID PK  
ON PK.KIDID = K.KIDID
INNER JOIN USERS U
ON U.USERID = K.USERID
INNER JOIN ACCOUNTS A
ON A.USERID = U.USERID
INNER JOIN ACCOUNTRANSATIONS AT
ON AT.ACCOUNTID = A.ACCOUNTID
LEFT OUTER JOIN KIDSPHOTO KP
ON KP.KIDID = K.KIDID
Group by 
K.KIDID,  
K.STUDENTNUMBER,  
KIDNAME,  
KIDLASTNAME ,  
SC.SCHOOLNAME ,  
KIDGRADE ,  
KIDCLASS ,  
SC.SCHOOLID, 
k.TEMP_BARCODE,
Cast(PHOTO as Varbinary),
A.ACCOUNTID
) X 

SELECT      
K.KIDID,      
K.STUDENTNUMBER,      
KIDNAME,      
KIDLASTNAME ,      
SC.SCHOOLNAME ,      
KIDGRADE ,      
KIDCLASS ,      
SC.SCHOOLID,  
k.TEMP_BARCODE,
(SELECT PHOTO FROM USER UP WHERE U.USERID = UP.USERID) AS PHOTO,
A.ACCOUNTID
,SUM(TRANSACTION_AMOUNT) AS [BALANCE]  
FROM       
KIDS_DETAILS K      
INNER JOIN SCHOOL SC       
ON K.SCHOOLID = SC.SCHOOLID      
INNER JOIN PARENTKID PK       
ON PK.KIDID = K.KIDID
INNER JOIN USERS U 
ON U.USERID  = K.USERID 
INNER JOIN ACCOUNTS A 
ON A.USERID = U.USERID
INNER JOIN ACCOUNTRANSATIONS AT
ON AT.ACCOUNTID  = A.ACCOUNTID
LEFT OUTER JOIN KIDSPHOTO KP
ON KP.KIDID = K.KIDID
 
  GROUP BY
K.KIDID,      
K.STUDENTNUMBER,      
KIDNAME,      
KIDLASTNAME ,      
SC.SCHOOLNAME ,      
KIDGRADE ,      
KIDCLASS ,      
SC.SCHOOLID,  
k.TEMP_BARCODE,
A.ACCOUNTID