May 24, 2012 at 12:01 pm
Ok... this is just friggin wierd. We have a field called "ISDELETED". If the record is current the field is Null (or 0). When we query the table we use....
SELECT *
FROM TABLE_1 T (nolocks)
WHERE ISNULL(ISDELETED,0) = 0
Now... I didn't make the rules here... but... to eliminate the No Locks hint, and the ISDELETED predicate, they created a view so now I just query:
SELECT *
FROM vw_TABLE_1
Very simple... However, I've been migrated some of the procedures to the view instead of being table table based and (GASP) I get more records. WTF(x) (that stands for What the Function)....
Ok... to test the view I did this:
SELECT count(*)
FROM TABLE_1 T (nolocks)
WHERE ISNULL(ISDELETED,0) = 0
SELECT COUNT(*)
FROM vw_TABLE_1
answer, SAME NUMBER OF RECORDS!!!
Ok.. so what if the count is the same, but the records aren't the same. I can see how that MIGHT cause a different result. So I did this:
SELECT *
FROM TABLE_1 T (nolocks)
WHERE ISNULL(ISDELETED,0) = 0
EXCEPT
SELECT *
FROM vw_TABLE_1
RESULT... Empty recordset. So in other words, the results set from the view exactly matches the result set from the table. In the main query, I can switch back and form from the table to the view and the results are considtently different in the the same 6 records keep showing up for the View and not the table.
OMG this is frustrating... Please, someone tell this simple geek what the Function is going on!
Thanks
Crusty.
May 24, 2012 at 12:12 pm
can you post those 6 records which are appearing in view and not in table?
Also the main query where it is being used?
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
May 24, 2012 at 12:22 pm
You are reading this query wrong:
SELECT *
FROM TABLE_1 T (nolocks)
WHERE ISNULL(ISDELETED,0) = 0
EXCEPT
SELECT *
FROM vw_TABLE_1
It will only show you records from TABLE_1 where ISNULL(ISDELETED,0) = 0 that aren't in the view vw_TABLE_1. If you want to see the records in vw_TABLE_1 that aren't in TABLE_1 where ISNULL(ISDELETED,0) = 0 you need to run the following query.
SELECT *
FROM vw_TABLE_1
EXCEPT
SELECT *
FROM TABLE_1 T (nolocks)
WHERE ISNULL(ISDELETED,0) = 0
May 24, 2012 at 12:30 pm
Lynn, My typing was wrong, but the test was correct. You're right the ISDELETED flag needs to on the table not the view... BUT.. I ran the test the "correct" way (not the way I typed it here) and the result was NULL, or nothing, or whatever.
No, I cannot post the 6 records as the information is proprietary... but honestly, should it matter? The view and the table should be identicle. and the tests I've run are showing that they are... Isn't that one of the advantages of using the view? Less code, same result?
Aliens? Gremlins?
May 24, 2012 at 12:32 pm
Lynn, I just realized what your reply was REALLY saying... I think I did that, but I'll double check.
May 24, 2012 at 12:34 pm
Can you post the DDL for the view? Our biggest problem is that we can't see from here what you see there. Makes it really hard to give you good answers rather than shots in the dark.
May 24, 2012 at 12:41 pm
I'll do better than that... I'll write you a pretty little script and show the output... I'll even put a ribbon on it... gimmie a couple minutes....
May 24, 2012 at 12:53 pm
Lynn Pettis (5/24/2012)
Can you post the DDL for the view? Our biggest problem is that we can't see from here what you see there. Makes it really hard to give you good answers rather than shots in the dark.
Merry Christmas
DDL for view.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[v_EVI_Devices_Asset] AS SELECT * FROM EVI_Devices_Asset with (nolock) WHERE ISNULL(ISDELETED,0) = 0
GO
----------------------------------------------------------------
just the SQL... cutting and pasting was being a pain.
select *
from v_evi_devices_asset
EXCEPT
select *
from EVI_Devices_Asset with (nolock)
where ISNULL(ISDELETED,0)=0
(0 row(s) affected)
select *
from EVI_Devices_Asset with (nolock)
where ISNULL(ISDELETED,0)=0
EXCEPT
select *
from v_evi_devices_asset
(0 row(s) affected)
May 24, 2012 at 12:56 pm
Since YODA is your avetar... you might like this....
May 24, 2012 at 12:56 pm
What abt the main query where it is showing diff results..
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
May 24, 2012 at 12:58 pm
CptCrusty1 (5/24/2012)
Since YODA is your avetar... you might like this....
May 24, 2012 at 1:01 pm
Actually, you didn't eliminate the NOLOCKS hint, you just hide them in the view.
What is with the hint any way? You do know the issues with using the hint, correct?
May 24, 2012 at 1:03 pm
SELECT DISTINCT P.PRE_DTT_PRESERVATION_ID,
CASE horizondm_dev2.dbo.LookupValue_dev(D.EVI_DEV_MEDIA_TYPE)
WHEN 'ORIGINAL STORAGE'
THEN 'Original: '+ EVI_DEV_BC_Barcode +' SN:'+ EVI_DEV_SN
WHEN 'WORKING STORAGE'
THEN 'Working: '+ EVI_DEV_BC_Barcode +' SN:'+ EVI_DEV_SN
WHEN 'For Remediation'
THEN 'For_Remediation: '+ EVI_DEV_BC_Barcode +' SN:'+ EVI_DEV_SN
ELSE
horizondm_dev2.dbo.LookupValue_dev(D.EVI_DEV_MEDIA_TYPE) + ': '+EVI_DEV_BC_Barcode +' SN:'+ EVI_DEV_SN
END AS STORAGE_INFO
INTO #TMP_STORAGE
FROM horizondm_dev.dbo.v_PRE_PRESERVATIONS_HOMI P
INNER JOIN horizondm_dev.dbo.v_EVI_DEV_PRE_LINK L ON L.PRE_DTT_PRESERVATION_ID = P.PRE_DTT_PRESERVATION_ID
INNER JOIN horizondm_dev.dbo.v_EVI_Devices D ON L.EVI_DEV_DTT_Device_ID = D.EVI_DEV_DTT_Device_ID
INNER JOIN horizondm_dev.dbo.v_EVI_Devices_Asset A ON A.EVI_DEV_DTT_Device_ID = D.EVI_DEV_DTT_Device_ID
INNER JOIN horizondm_dev.dbo.v_EVI_DEV_BARCODES B ON D.EVI_DEV_DTT_Device_ID = B.EVI_DEV_DTT_Device_ID
--WHERE ISNULL(P.ISDELETED,0) = 0 AND ISNULL(D.ISDELETED,0) = 0 AND ISNULL(L.ISDELETED,0) = 0 AND ISNULL(A.ISDELETED,0) = 0 AND ISNULL(B.ISDELETED,0) = 0
--50842 Table BAsed. Production
--50842 View Based. Production
SELECT
ADBL.[PRE_DTT_PRESERVATION_ID]AS [Preservation ID]
, ISNULL(ADBL.CUS_DTT_Custodian_ID,'')AS [Custodian ID]
, ISNULL(dbo.UDF_GET_CUSTODIAN_NAME_dev(ADBL.CUS_DTT_Custodian_ID),'')AS [Custodian Name]
, ISNULL(IND.CUS_IND_NTID,'')AS [NTID]
, ISNULL(COL.[COL_DTT_Collection_ID],'')AS [Collection ID]
, ISNULL(DEV.EVI_DEV_DTT_Device_ID,'')AS [Parent Device ID]
, ISNULL(dbo.LookupValue_dev(DEVA.EVI_DEV_Asset_TYPE),'')AS [Parent Asset Type]
--, ISNULL(BDEV.[EVI_DEV_BC_Barcode],'')AS [Barcode]
, ISNULL(DEVA.[EVI_DEV_Manufacturer],'')AS [Parent Manufacturer]
, ISNULL(DEVA.[EVI_DEV_Model],'')AS [Parent Model]
, ISNULL(DEVA.[EVI_DEV_SN],'')AS [Parent Serial Number]
, ISNULL(DEVA.[EVI_DEV_Asset_Tag],'')AS [Parent Asset Tag]
, ISNULL(CHILD.EVI_DEV_DTT_Device_ID,'')AS [Child Device ID]
, ISNULL(dbo.LookupValue_dev(CHILD.EVI_DEV_Asset_TYPE),'')AS [Child Asset Type]
--, ISNULL(CHILD.[EVI_DEV_BC_Barcode],'')AS [Barcode]
, ISNULL(CHILD.[EVI_DEV_Manufacturer],'')AS [Child Manufacturer]
, ISNULL(CHILD.[EVI_DEV_Model],'')AS [Child Model]
, ISNULL(CHILD.[EVI_DEV_SN],'')AS [Child Serial Number]
, ISNULL(CHILD.[EVI_DEV_Asset_Tag],'')AS [Child Asset Tag]
, ISNULL(dbo.LookupValue_dev(PRE.[PRE_DTT_Preservation_Type]),'')AS [Preservation Type]
, ISNULL(dbo.LookupValue_dev(PRE.[PRE_DTT_Status]),'')AS [Status of Preservation Item]
, ISNULL(PRE.[PRE_BARCODE_ID],'')AS [Image Barcode]
, ISNULL(PRE.[PRE_IMAGE_NAME],'')AS [Image Name]
, ISNULL(PRE.PRE_IMAGE_DISPLAY_NAME,'')AS [Display Image Name]
, ISNULL(convert(nvarchar(10),PRE.[PRE_ACQUISITION_DATE],101),'')AS [Acquisition Date]
, ISNULL(K.STORAGE,'')AS [Storage Device Info]
--, ISNULL(PRE.[PRE_ORIGINAL_COPY_DEVICE]AS [Original Copy Device]
--, ISNULL(dbo.COALESCE_BARCODE(PRE.[PRE_ORIGINAL_COPY_DEVICE])AS [Original Copy Barcodes]
--, ISNULL(PRE.[PRE_WORKING_COPY_DEVICE]AS [Working Copy Device]
--, ISNULL(dbo.COALESCE_BARCODE(PRE.[PRE_WORKING_COPY_DEVICE])AS [Working Copy Barcodes]
, ISNULL(PRE.[PRE_PRESERVATIONS_PARENT_IMAGE],'')AS [Source Image]
--, ISNULL(PRE.[EVI_DEV_CHILD_ID]AS [Child Device ID]
, ISNULL(dbo.LookupValue_dev(PRE.[PRE_QC_Result]),'')AS [QC Result]
, ISNULL(convert(nvarchar(10),PRE.[PRE_QC_Date],101),'')AS [QC Date]
, ISNULL(PRE.[PRE_COMMENTS],'')AS [General Comments]
, ISNULL(HOMI.[PRE_HOMI_LOCATION],'')AS [Location]
--, ISNULL(HOMI.[PRE_HOMI_COLLECTION_DATA_SIZE]AS [Collection Data Size]
--, ISNULL(dbo.LookupValue_dev(HOMI.[PRE_HOMI_COLLECTION_DATA_SIZE_UNIT])AS [Data Size Unit]
, ISNULL(HOMI.[PRE_HOMI_ACQUISITION_FILE_COUNT],'')AS [Acquisition File Count]
, ISNULL(dbo.LookupValue_dev(HOMI.[PRE_HOMI_COLLECTION_METHOD_TYPE]),'')AS [Collection Method Type]
, ISNULL(dbo.LookupValue_dev(HOMI.[PRE_HOMI_IMAGE_TYPE]),'')AS [Image Type]
, ISNULL(dbo.LookupValue_dev(HOMI.[PRE_HOMI_COLLECTION_DATA_TYPE]),'')AS [Collection Data Type]
, ISNULL(HOMI.[PRE_HOMI_COMPANY_NAME],'')AS [Company Name]
, ISNULL(HOMI.[PRE_HOMI_COLLECTION_PERFORMED_BY_FIRSTNAME],'')AS [First Name of Collection Performer]
, ISNULL(HOMI.[PRE_HOMI_COLLECTION_PERFORMED_BY_LASTNAME],'')AS [Last Name of Collection Performer]
, ISNULL(HOMI.[PRE_HOMI_TIME_OF_ACQUISITION],'')AS [Acquisition Time]
, ISNULL(dbo.LookupValue_dev([PRE_HOMI_TIME_ZONE_DURING_ACQUISITION]),'')AS [Time Zone During Acquisition]
, CASE WHEN HOMI.[PRE_HOMI_VERIFICATION_REPORTS_EXPORTED]=1 THEN 'Yes'
WHEN HOMI.[PRE_HOMI_VERIFICATION_REPORTS_EXPORTED]=0 THEN 'No'
ELSE 'No' ENDAS [Verification Reports Exported]
, ISNULL(HOMI.[PRE_HOMI_VERIFIED_BY],'')AS [Verified By]
, ISNULL(dbo.LookupValue_dev(HOMI.[PRE_HOMI_ACQUISITION_SOFTWARE_OR_TOOL]),'')AS [Acquisition Software]
, ISNULL(HOMI.[PRE_HOMI_SOFTWARE_VERSION],'')AS [Software Version]
, CASE WHEN HOMI.[PRE_HOMI_IMAGE_OPENS]=1 THEN 'Yes'
WHEN HOMI.[PRE_HOMI_IMAGE_OPENS]=0 THEN 'No'
ELSE 'No' ENDAS [Image Opens]
, CASE WHEN HOMI.[PRE_HOMI_ENCRYPTION_FLAG]=1 THEN 'Yes'
WHEN HOMI.[PRE_HOMI_ENCRYPTION_FLAG]=0 THEN 'No'
ELSE NULL ENDAS [Encryption Flag]
, CASE WHEN HOMI.[PRE_HOMI_HASH_VERIFIED_FLAG]=1 THEN 'Yes'
WHEN HOMI.[PRE_HOMI_HASH_VERIFIED_FLAG]=0 THEN 'No'
ELSE NULL ENDAS [Hash Verified Flag]
, CASE WHEN HOMI.[PRE_HOMI_FILE_OPENS_AFTER_EXPORT_FLAG]=1 THEN 'Yes'
WHEN HOMI.[PRE_HOMI_FILE_OPENS_AFTER_EXPORT_FLAG]=0 THEN 'No'
ELSE NULL ENDAS [File Opens After Export Flag]
, CASE WHEN HOMI.[PRE_HOMI_PAPERWORK_FOUND_FLAG]=1 THEN 'Yes'
WHEN HOMI.[PRE_HOMI_PAPERWORK_FOUND_FLAG]=0 THEN 'No'
ELSE NULL ENDAS [Paper Work Found Flag]
, ISNULL(HOMI.[PRE_HOMI_REMEDIATION_COMMENTS],'')AS [Remediation Comments]
, ISNULL(dbo.LookupValue_Dev(HOMI.[PRE_HOMI_ACCESSDATA_PROCESSED_FLAG]),'')AS [AccessData Processed Status]
, ISNULL(HOMI.[PRE_HOMI_ACCESSDATA_COLLECTION_NAME],'')AS [AccessData Collection Name]
, ISNULL(HOMI.[PRE_HOMI_LEGAL_MATTER_NAME],'')AS [Legal Matter Name]
, ISNULL(dbo.LookupValue_dev(HOMI.[PRE_HOMI_FK_COLORCODE]),'')AS [Color Code]
, ISNULL(HOMI.[PRE_HOMI_MD5_HASH_VALUE],'')AS [MD5 Hash Value]
, ISNULL(HOMI.[PRE_HOMI_FILE_PATH],'')AS [File Path]
, ISNULL(HOMI.[PRE_HOMI_FORMS_AVAIL_IN_EROOM],'')AS [Forms Available in eRoom]
, ISNULL(HOMI.[PRE_HOMI_HARD_DRIVE_SIZE],'')AS [Hard Drive Size]
, ISNULL(dbo.LookupVALUE_Dev(HOMI.PRE_HOMI_STREAM),'')AS [Preservation Stream]
, ISNULL(PRE.[PRE_PRESERVATIONS_LEGACY_SOURCE],'')AS [Legacy Source]
, ISNULL(PRE.[PRE_PRESERVATION_LEGACY_ID],'')AS [Legacy ID]
, ISNULL(dbo.LookupVALUE_Dev(PRE.PRE_PROJECT),'')AS [Project Type]
--, ISNULL(PRE.[PRE_PRESERVATION_STUB_RECORD]AS [Stub Record]
--Select COUNT(*)
FROM HORIZONDM_DEV.dbo.vw_All_Database_Links ADBL
--FROM horizondm_dev.dbo.PRE_PRESERVATIONS PRE
INNER JOIN horizondm_dev.dbo.v_PRE_PRESERVATIONS_HOMI HOMI
ON ADBL.PRE_DTT_PRESERVATION_ID=HOMI.PRE_DTT_PRESERVATION_ID
--Still need this to link in the Col_collections table since there is no col_dtt_collection_id in the view.
--"LEFT JOIN EVERYTHING"
LEFT JOIN horizondm_dev.dbo.v_COL_COLLECTIONS_DEVICE_LINK LI
ON ADBL.COL_DTT_Device_Collection_ID=LI.COL_DTT_Device_Collection_ID
LEFT JOIN horizondm_dev.dbo.v_COL_COLLECTIONS COL
ON LI.COL_DTT_Collection_ID=COL.COL_DTT_Collection_ID
--Replace with Inner join to PK to pull in Evi_Dev PK
--LEFT JOIN horizondm_dev.dbo.CUS_Custodian_Device_LINK DL
--ON LI.CUS_DTT_CUS_DEV_ID = DL.CUS_DTT_CUS_DEV_ID
--Join to the CUS_DTT_Cus_DEV_ID pk from View.
LEFT JOIN horizondm_dev.dbo.v_CUS_Custodian_Device_LINK DL
ON ADBL.CUS_DTT_CUS_DEV_ID = DL.CUS_DTT_CUS_DEV_ID
LEFT JOIN horizondm_dev.dbo.v_EVI_Devices DEV
ON DL.EVI_DEV_DTT_Device_ID=DEV.EVI_DEV_DTT_Device_ID
LEFT JOIN horizondm_dev.dbo.v_EVI_Devices_Asset DEVA
ON DEV.EVI_DEV_DTT_Device_ID=DEVA.EVI_DEV_DTT_Device_ID
--Bring Pre_Preservations back in for Child ID link to Evi_devices
LEFT JOIN HORIZONDM_DEV.dbo.v_PRE_PRESERVATIONS PRE
ON PRE.PRE_DTT_PRESERVATION_ID = ADBL.PRE_DTT_PRESERVATION_ID
LEFT JOIN horizondm_dev.dbo.v_EVI_Devices_Asset CHILD
ON PRE.EVI_DEV_CHILD_ID = CHILD.EVI_DEV_DTT_Device_ID😎
LEFT JOIN horizondm_dev.dbo.v_Cus_Custodian CUS
ON DL.CUS_DTT_Custodian_ID=CUS.CUS_DTT_Custodian_ID
--REPLACED WITH JOIN TO VIEW.
LEFT JOIN horizondm_dev.dbo.v_Cus_Custodian_Individual IND
ON ADBL.CUS_DTT_CUSTODIAN_ID = IND.CUS_DTT_Custodian_ID
--Replaced with view.
--LEFT JOIN horizondm_dev.dbo.CUS_Custodian_PRESERVATION_LINK PRE_LINK
--ON PRE.PRE_DTT_PRESERVATION_ID=PRE_LINK.PRE_DTT_PRESERVATION_ID
LEFT JOIN
(SELECT DISTINCT m.PRE_DTT_PRESERVATION_ID
, SUBSTRING(LTRIM(RTRIM(COALESCE(
(
SELECT ' | ' + m1.STORAGE_INFO AS [text()]
FROM #TMP_STORAGE AS m1
WHERE m1.PRE_DTT_PRESERVATION_ID = m.PRE_DTT_PRESERVATION_ID
GROUP BY m1.STORAGE_INFO
FOR XML PATH(''), TYPE).value('.[1]', 'VARCHAR(MAX)'
)
, ''))),3,255) AS STORAGE
FROM #TMP_STORAGE m) K
ON K.PRE_DTT_PRESERVATION_ID = HOMI.PRE_DTT_PRESERVATION_ID
--HOMI.PRE_DTT_PRESERVATION_ID
WHERE ISNULL(PRE.ISDELETED,0) = 0 AND ISNULL(HOMI.ISDELETED,0) = 0 AND ISNULL(LI.ISDELETED,0) = 0 AND ISNULL(COL.ISDELETED,0) = 0
AND ISNULL(DL.ISDELETED,0) =0 AND ISNULL(DEV.ISDELETED,0) = 0 AND ISNULL(DEVA.ISDELETED,0) = 0 AND ISNULL(CHILD.ISDELETED,0) = 0
AND ISNULL(CUS.ISDELETED,0) = 0 AND ISNULL(IND.ISDELETED,0) = 0 --AND ISNULL(PRE_LINK.ISDELETED,0) = 0
May 24, 2012 at 1:05 pm
Lynn Pettis (5/24/2012)
Actually, you didn't eliminate the NOLOCKS hint, you just hide them in the view.What is with the hint any way? You do know the issues with using the hint, correct?
Whatchoo talken 'bout Willis?
The database masters tell me that there are issues with NOT using the NOLOCKS... It's there cuz I was told to use it.. Actually, I didn't write this one.
May 24, 2012 at 1:07 pm
CptCrusty1 (5/24/2012)
SELECT DISTINCT P.PRE_DTT_PRESERVATION_ID,
CASE horizondm_dev2.dbo.LookupValue_dev(D.EVI_DEV_MEDIA_TYPE)
WHEN 'ORIGINAL STORAGE'
THEN 'Original: '+ EVI_DEV_BC_Barcode +' SN:'+ EVI_DEV_SN
WHEN 'WORKING STORAGE'
THEN 'Working: '+ EVI_DEV_BC_Barcode +' SN:'+ EVI_DEV_SN
WHEN 'For Remediation'
THEN 'For_Remediation: '+ EVI_DEV_BC_Barcode +' SN:'+ EVI_DEV_SN
ELSE
horizondm_dev2.dbo.LookupValue_dev(D.EVI_DEV_MEDIA_TYPE) + ': '+EVI_DEV_BC_Barcode +' SN:'+ EVI_DEV_SN
END AS STORAGE_INFO
INTO #TMP_STORAGE
FROM horizondm_dev.dbo.v_PRE_PRESERVATIONS_HOMI P
INNER JOIN horizondm_dev.dbo.v_EVI_DEV_PRE_LINK L ON L.PRE_DTT_PRESERVATION_ID = P.PRE_DTT_PRESERVATION_ID
INNER JOIN horizondm_dev.dbo.v_EVI_Devices D ON L.EVI_DEV_DTT_Device_ID = D.EVI_DEV_DTT_Device_ID
INNER JOIN horizondm_dev.dbo.v_EVI_Devices_Asset A ON A.EVI_DEV_DTT_Device_ID = D.EVI_DEV_DTT_Device_ID
INNER JOIN horizondm_dev.dbo.v_EVI_DEV_BARCODES B ON D.EVI_DEV_DTT_Device_ID = B.EVI_DEV_DTT_Device_ID
--WHERE ISNULL(P.ISDELETED,0) = 0 AND ISNULL(D.ISDELETED,0) = 0 AND ISNULL(L.ISDELETED,0) = 0 AND ISNULL(A.ISDELETED,0) = 0 AND ISNULL(B.ISDELETED,0) = 0
--50842 Table BAsed. Production
--50842 View Based. Production
SELECT
ADBL.[PRE_DTT_PRESERVATION_ID]AS [Preservation ID]
, ISNULL(ADBL.CUS_DTT_Custodian_ID,'')AS [Custodian ID]
, ISNULL(dbo.UDF_GET_CUSTODIAN_NAME_dev(ADBL.CUS_DTT_Custodian_ID),'')AS [Custodian Name]
, ISNULL(IND.CUS_IND_NTID,'')AS [NTID]
, ISNULL(COL.[COL_DTT_Collection_ID],'')AS [Collection ID]
, ISNULL(DEV.EVI_DEV_DTT_Device_ID,'')AS [Parent Device ID]
, ISNULL(dbo.LookupValue_dev(DEVA.EVI_DEV_Asset_TYPE),'')AS [Parent Asset Type]
--, ISNULL(BDEV.[EVI_DEV_BC_Barcode],'')AS [Barcode]
, ISNULL(DEVA.[EVI_DEV_Manufacturer],'')AS [Parent Manufacturer]
, ISNULL(DEVA.[EVI_DEV_Model],'')AS [Parent Model]
, ISNULL(DEVA.[EVI_DEV_SN],'')AS [Parent Serial Number]
, ISNULL(DEVA.[EVI_DEV_Asset_Tag],'')AS [Parent Asset Tag]
, ISNULL(CHILD.EVI_DEV_DTT_Device_ID,'')AS [Child Device ID]
, ISNULL(dbo.LookupValue_dev(CHILD.EVI_DEV_Asset_TYPE),'')AS [Child Asset Type]
--, ISNULL(CHILD.[EVI_DEV_BC_Barcode],'')AS [Barcode]
, ISNULL(CHILD.[EVI_DEV_Manufacturer],'')AS [Child Manufacturer]
, ISNULL(CHILD.[EVI_DEV_Model],'')AS [Child Model]
, ISNULL(CHILD.[EVI_DEV_SN],'')AS [Child Serial Number]
, ISNULL(CHILD.[EVI_DEV_Asset_Tag],'')AS [Child Asset Tag]
, ISNULL(dbo.LookupValue_dev(PRE.[PRE_DTT_Preservation_Type]),'')AS [Preservation Type]
, ISNULL(dbo.LookupValue_dev(PRE.[PRE_DTT_Status]),'')AS [Status of Preservation Item]
, ISNULL(PRE.[PRE_BARCODE_ID],'')AS [Image Barcode]
, ISNULL(PRE.[PRE_IMAGE_NAME],'')AS [Image Name]
, ISNULL(PRE.PRE_IMAGE_DISPLAY_NAME,'')AS [Display Image Name]
, ISNULL(convert(nvarchar(10),PRE.[PRE_ACQUISITION_DATE],101),'')AS [Acquisition Date]
, ISNULL(K.STORAGE,'')AS [Storage Device Info]
--, ISNULL(PRE.[PRE_ORIGINAL_COPY_DEVICE]AS [Original Copy Device]
--, ISNULL(dbo.COALESCE_BARCODE(PRE.[PRE_ORIGINAL_COPY_DEVICE])AS [Original Copy Barcodes]
--, ISNULL(PRE.[PRE_WORKING_COPY_DEVICE]AS [Working Copy Device]
--, ISNULL(dbo.COALESCE_BARCODE(PRE.[PRE_WORKING_COPY_DEVICE])AS [Working Copy Barcodes]
, ISNULL(PRE.[PRE_PRESERVATIONS_PARENT_IMAGE],'')AS [Source Image]
--, ISNULL(PRE.[EVI_DEV_CHILD_ID]AS [Child Device ID]
, ISNULL(dbo.LookupValue_dev(PRE.[PRE_QC_Result]),'')AS [QC Result]
, ISNULL(convert(nvarchar(10),PRE.[PRE_QC_Date],101),'')AS [QC Date]
, ISNULL(PRE.[PRE_COMMENTS],'')AS [General Comments]
, ISNULL(HOMI.[PRE_HOMI_LOCATION],'')AS [Location]
--, ISNULL(HOMI.[PRE_HOMI_COLLECTION_DATA_SIZE]AS [Collection Data Size]
--, ISNULL(dbo.LookupValue_dev(HOMI.[PRE_HOMI_COLLECTION_DATA_SIZE_UNIT])AS [Data Size Unit]
, ISNULL(HOMI.[PRE_HOMI_ACQUISITION_FILE_COUNT],'')AS [Acquisition File Count]
, ISNULL(dbo.LookupValue_dev(HOMI.[PRE_HOMI_COLLECTION_METHOD_TYPE]),'')AS [Collection Method Type]
, ISNULL(dbo.LookupValue_dev(HOMI.[PRE_HOMI_IMAGE_TYPE]),'')AS [Image Type]
, ISNULL(dbo.LookupValue_dev(HOMI.[PRE_HOMI_COLLECTION_DATA_TYPE]),'')AS [Collection Data Type]
, ISNULL(HOMI.[PRE_HOMI_COMPANY_NAME],'')AS [Company Name]
, ISNULL(HOMI.[PRE_HOMI_COLLECTION_PERFORMED_BY_FIRSTNAME],'')AS [First Name of Collection Performer]
, ISNULL(HOMI.[PRE_HOMI_COLLECTION_PERFORMED_BY_LASTNAME],'')AS [Last Name of Collection Performer]
, ISNULL(HOMI.[PRE_HOMI_TIME_OF_ACQUISITION],'')AS [Acquisition Time]
, ISNULL(dbo.LookupValue_dev([PRE_HOMI_TIME_ZONE_DURING_ACQUISITION]),'')AS [Time Zone During Acquisition]
, CASE WHEN HOMI.[PRE_HOMI_VERIFICATION_REPORTS_EXPORTED]=1 THEN 'Yes'
WHEN HOMI.[PRE_HOMI_VERIFICATION_REPORTS_EXPORTED]=0 THEN 'No'
ELSE 'No' ENDAS [Verification Reports Exported]
, ISNULL(HOMI.[PRE_HOMI_VERIFIED_BY],'')AS [Verified By]
, ISNULL(dbo.LookupValue_dev(HOMI.[PRE_HOMI_ACQUISITION_SOFTWARE_OR_TOOL]),'')AS [Acquisition Software]
, ISNULL(HOMI.[PRE_HOMI_SOFTWARE_VERSION],'')AS [Software Version]
, CASE WHEN HOMI.[PRE_HOMI_IMAGE_OPENS]=1 THEN 'Yes'
WHEN HOMI.[PRE_HOMI_IMAGE_OPENS]=0 THEN 'No'
ELSE 'No' ENDAS [Image Opens]
, CASE WHEN HOMI.[PRE_HOMI_ENCRYPTION_FLAG]=1 THEN 'Yes'
WHEN HOMI.[PRE_HOMI_ENCRYPTION_FLAG]=0 THEN 'No'
ELSE NULL ENDAS [Encryption Flag]
, CASE WHEN HOMI.[PRE_HOMI_HASH_VERIFIED_FLAG]=1 THEN 'Yes'
WHEN HOMI.[PRE_HOMI_HASH_VERIFIED_FLAG]=0 THEN 'No'
ELSE NULL ENDAS [Hash Verified Flag]
, CASE WHEN HOMI.[PRE_HOMI_FILE_OPENS_AFTER_EXPORT_FLAG]=1 THEN 'Yes'
WHEN HOMI.[PRE_HOMI_FILE_OPENS_AFTER_EXPORT_FLAG]=0 THEN 'No'
ELSE NULL ENDAS [File Opens After Export Flag]
, CASE WHEN HOMI.[PRE_HOMI_PAPERWORK_FOUND_FLAG]=1 THEN 'Yes'
WHEN HOMI.[PRE_HOMI_PAPERWORK_FOUND_FLAG]=0 THEN 'No'
ELSE NULL ENDAS [Paper Work Found Flag]
, ISNULL(HOMI.[PRE_HOMI_REMEDIATION_COMMENTS],'')AS [Remediation Comments]
, ISNULL(dbo.LookupValue_Dev(HOMI.[PRE_HOMI_ACCESSDATA_PROCESSED_FLAG]),'')AS [AccessData Processed Status]
, ISNULL(HOMI.[PRE_HOMI_ACCESSDATA_COLLECTION_NAME],'')AS [AccessData Collection Name]
, ISNULL(HOMI.[PRE_HOMI_LEGAL_MATTER_NAME],'')AS [Legal Matter Name]
, ISNULL(dbo.LookupValue_dev(HOMI.[PRE_HOMI_FK_COLORCODE]),'')AS [Color Code]
, ISNULL(HOMI.[PRE_HOMI_MD5_HASH_VALUE],'')AS [MD5 Hash Value]
, ISNULL(HOMI.[PRE_HOMI_FILE_PATH],'')AS [File Path]
, ISNULL(HOMI.[PRE_HOMI_FORMS_AVAIL_IN_EROOM],'')AS [Forms Available in eRoom]
, ISNULL(HOMI.[PRE_HOMI_HARD_DRIVE_SIZE],'')AS [Hard Drive Size]
, ISNULL(dbo.LookupVALUE_Dev(HOMI.PRE_HOMI_STREAM),'')AS [Preservation Stream]
, ISNULL(PRE.[PRE_PRESERVATIONS_LEGACY_SOURCE],'')AS [Legacy Source]
, ISNULL(PRE.[PRE_PRESERVATION_LEGACY_ID],'')AS [Legacy ID]
, ISNULL(dbo.LookupVALUE_Dev(PRE.PRE_PROJECT),'')AS [Project Type]
--, ISNULL(PRE.[PRE_PRESERVATION_STUB_RECORD]AS [Stub Record]
--Select COUNT(*)
FROM HORIZONDM_DEV.dbo.vw_All_Database_Links ADBL
--FROM horizondm_dev.dbo.PRE_PRESERVATIONS PRE
INNER JOIN horizondm_dev.dbo.v_PRE_PRESERVATIONS_HOMI HOMI
ON ADBL.PRE_DTT_PRESERVATION_ID=HOMI.PRE_DTT_PRESERVATION_ID
--Still need this to link in the Col_collections table since there is no col_dtt_collection_id in the view.
--"LEFT JOIN EVERYTHING"
LEFT JOIN horizondm_dev.dbo.v_COL_COLLECTIONS_DEVICE_LINK LI
ON ADBL.COL_DTT_Device_Collection_ID=LI.COL_DTT_Device_Collection_ID
LEFT JOIN horizondm_dev.dbo.v_COL_COLLECTIONS COL
ON LI.COL_DTT_Collection_ID=COL.COL_DTT_Collection_ID
--Replace with Inner join to PK to pull in Evi_Dev PK
--LEFT JOIN horizondm_dev.dbo.CUS_Custodian_Device_LINK DL
--ON LI.CUS_DTT_CUS_DEV_ID = DL.CUS_DTT_CUS_DEV_ID
--Join to the CUS_DTT_Cus_DEV_ID pk from View.
LEFT JOIN horizondm_dev.dbo.v_CUS_Custodian_Device_LINK DL
ON ADBL.CUS_DTT_CUS_DEV_ID = DL.CUS_DTT_CUS_DEV_ID
LEFT JOIN horizondm_dev.dbo.v_EVI_Devices DEV
ON DL.EVI_DEV_DTT_Device_ID=DEV.EVI_DEV_DTT_Device_ID
LEFT JOIN horizondm_dev.dbo.v_EVI_Devices_Asset DEVA
ON DEV.EVI_DEV_DTT_Device_ID=DEVA.EVI_DEV_DTT_Device_ID
--Bring Pre_Preservations back in for Child ID link to Evi_devices
LEFT JOIN HORIZONDM_DEV.dbo.v_PRE_PRESERVATIONS PRE
ON PRE.PRE_DTT_PRESERVATION_ID = ADBL.PRE_DTT_PRESERVATION_ID
LEFT JOIN horizondm_dev.dbo.v_EVI_Devices_Asset CHILD
ON PRE.EVI_DEV_CHILD_ID = CHILD.EVI_DEV_DTT_Device_ID:cool:
LEFT JOIN horizondm_dev.dbo.v_Cus_Custodian CUS
ON DL.CUS_DTT_Custodian_ID=CUS.CUS_DTT_Custodian_ID
--REPLACED WITH JOIN TO VIEW.
LEFT JOIN horizondm_dev.dbo.v_Cus_Custodian_Individual IND
ON ADBL.CUS_DTT_CUSTODIAN_ID = IND.CUS_DTT_Custodian_ID
--Replaced with view.
--LEFT JOIN horizondm_dev.dbo.CUS_Custodian_PRESERVATION_LINK PRE_LINK
--ON PRE.PRE_DTT_PRESERVATION_ID=PRE_LINK.PRE_DTT_PRESERVATION_ID
LEFT JOIN
(SELECT DISTINCT m.PRE_DTT_PRESERVATION_ID
, SUBSTRING(LTRIM(RTRIM(COALESCE(
(
SELECT ' | ' + m1.STORAGE_INFO AS [text()]
FROM #TMP_STORAGE AS m1
WHERE m1.PRE_DTT_PRESERVATION_ID = m.PRE_DTT_PRESERVATION_ID
GROUP BY m1.STORAGE_INFO
FOR XML PATH(''), TYPE).value('.[1]', 'VARCHAR(MAX)'
)
, ''))),3,255) AS STORAGE
FROM #TMP_STORAGE m) K
ON K.PRE_DTT_PRESERVATION_ID = HOMI.PRE_DTT_PRESERVATION_ID
--HOMI.PRE_DTT_PRESERVATION_ID
WHERE ISNULL(PRE.ISDELETED,0) = 0 AND ISNULL(HOMI.ISDELETED,0) = 0 AND ISNULL(LI.ISDELETED,0) = 0 AND ISNULL(COL.ISDELETED,0) = 0
AND ISNULL(DL.ISDELETED,0) =0 AND ISNULL(DEV.ISDELETED,0) = 0 AND ISNULL(DEVA.ISDELETED,0) = 0 AND ISNULL(CHILD.ISDELETED,0) = 0
AND ISNULL(CUS.ISDELETED,0) = 0 AND ISNULL(IND.ISDELETED,0) = 0 --AND ISNULL(PRE_LINK.ISDELETED,0) = 0
Viewing 15 posts - 1 through 15 (of 67 total)
You must be logged in to reply to this topic. Login to reply