NoPublic, bureaucrat, administrator
6,632
edits
Changes
no edit summary
=== Example update query ===__NOEDITSECTION__
<pre>
update hazardinfoversion
Inner Join hazard ON hazardinfoversion.hazard_guid = hazard.hazard_guid
SET
hazardinfoversion.hazard_localid = hazard.hazard_localid
WHERE
hazardinfoversion.link_only = '0' AND
hazardinfoversion.hazard_localid IS NULL and
hazard.hazard_localid IS NOT NULL
</pre>
== Missing Local ID fieldreport ==__NOEDITSECTION__
=== Accident ===__NOEDITSECTION__
<pre>
SELECT
fieldreport.fieldreport_localid,
imsmaenum.enumvalue,
fieldreport.dateofreport AS `Date of Information`,
fieldreport.reportreceiveddate AS `Data Entry Date`,
fieldreport.reportcompleteddate AS `Submitted Date`,
fieldreport.reportverifieddate AS `Approved Date`,
fieldreport.dataenterer AS `Data enterer`,
fieldreport.verifiedby AS `Approved by`,
accidentinfoversion.accident_localid
FROM
fieldreport
Inner Join accidentinfoversion ON fieldreport.fieldreport_guid = accidentinfoversion.fieldreport_guid
Inner Join imsmaenum ON fieldreport.workbenchstatusenum_guid = imsmaenum.imsmaenum_guid
WHERE
(fieldreport.fieldreport_localid IS NULL or length(trim(fieldreport.fieldreport_localid)) = 0 )
AND
accidentinfoversion.link_only = '0'
</pre>
=== Hazard ===__NOEDITSECTION__
<pre>
SELECT
fieldreport.fieldreport_localid,
imsmaenum.enumvalue,
fieldreport.dateofreport AS `Date of Information`,
fieldreport.reportreceiveddate AS `Data Entry Date`,
fieldreport.reportcompleteddate AS `Submitted Date`,
fieldreport.reportverifieddate AS `Approved Date`,
fieldreport.dataenterer AS `Data enterer`,
fieldreport.verifiedby AS `Approved by`,
hazardinfoversion.hazard_localid
FROM
fieldreport
Inner Join hazardinfoversion ON fieldreport.fieldreport_guid = hazardinfoversion.fieldreport_guid
Inner Join imsmaenum ON fieldreport.workbenchstatusenum_guid = imsmaenum.imsmaenum_guid
WHERE
(fieldreport.fieldreport_localid IS NULL OR length(trim(fieldreport.fieldreport_localid)) = 0)
AND
hazardinfoversion.link_only = '0'
</pre>
=== Hazard Reduction ===__NOEDITSECTION__
<pre>
SELECT
fieldreport.fieldreport_localid,
imsmaenum.enumvalue,
fieldreport.dateofreport AS `Date of Information`,
fieldreport.reportreceiveddate AS `Data Entry Date`,
fieldreport.reportcompleteddate AS `Submitted Date`,
fieldreport.reportverifieddate AS `Approved Date`,
fieldreport.dataenterer AS `Data enterer`,
fieldreport.verifiedby AS `Approved by`,
hazreducinfoversion.hazreduc_localid
FROM
fieldreport
Inner Join hazreducinfoversion ON hazreducinfoversion.fieldreport_guid = fieldreport.fieldreport_guid
Inner Join imsmaenum ON imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid
WHERE
(fieldreport.fieldreport_localid IS NULL or length(trim(fieldreport.fieldreport_localid)) = 0) AND
hazreducinfoversion.link_only = '0' AND
hazreducinfoversion.hazreduc_localid IS NOT NULL
</pre>
=== Location ===__NOEDITSECTION__
<pre>
SELECT
fieldreport.fieldreport_localid,
imsmaenum.enumvalue,
fieldreport.dateofreport AS `Date of Information`,
fieldreport.reportreceiveddate AS `Data Entry Date`,
fieldreport.reportcompleteddate AS `Submitted Date`,
fieldreport.reportverifieddate AS `Approved Date`,
fieldreport.dataenterer AS `Data enterer`,
fieldreport.verifiedby AS `Approved by`,
locationinfoversion.location_localid
FROM
fieldreport
Inner Join locationinfoversion ON locationinfoversion.fieldreport_guid = fieldreport.fieldreport_guid
Inner Join imsmaenum ON fieldreport.workbenchstatusenum_guid = imsmaenum.imsmaenum_guid
WHERE
(fieldreport.fieldreport_localid IS NULL or length(trim(fieldreport.fieldreport_localid)) = 0 ) AND
locationinfoversion.link_only = '0'
</pre>
=== MRE ===__NOEDITSECTION__
<pre>
SELECT
fieldreport.fieldreport_localid,
imsmaenum.enumvalue,
fieldreport.dateofreport AS `Date of Information`,
fieldreport.reportreceiveddate AS `Data Entry Date`,
fieldreport.reportcompleteddate AS `Submitted Date`,
fieldreport.reportverifieddate AS `Approved Date`,
fieldreport.dataenterer AS `Data enterer`,
fieldreport.verifiedby AS `Approved by`,
mreinfoversion.mre_localid
FROM
fieldreport
Inner Join mreinfoversion ON mreinfoversion.fieldreport_guid = fieldreport.fieldreport_guid
Inner Join imsmaenum ON imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid
WHERE
( fieldreport.fieldreport_localid is null or length(trim(fieldreport.fieldreport_localid)) = 0) AND
mreinfoversion.link_only = '0'
</pre>
=== QM ===__NOEDITSECTION__
<pre>
SELECT
fieldreport.fieldreport_localid,
imsmaenum.enumvalue,
fieldreport.dateofreport AS `Date of Information`,
fieldreport.reportreceiveddate AS `Data Entry Date`,
fieldreport.reportcompleteddate AS `Submitted Date`,
fieldreport.reportverifieddate AS `Approved Date`,
fieldreport.dataenterer AS `Data enterer`,
fieldreport.verifiedby AS `Approved by`,
qainfoversion.qa_localid
FROM
fieldreport
Inner Join qainfoversion ON qainfoversion.fieldreport_guid = fieldreport.fieldreport_guid
Inner Join imsmaenum ON imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid
WHERE
(fieldreport.fieldreport_localid IS NULL or length(trim(fieldreport.fieldreport_localid)) = 0) AND
qainfoversion.link_only = '0'
</pre>
=== Victim ===__NOEDITSECTION__
<pre>
SELECT
fieldreport.fieldreport_localid,
imsmaenum.enumvalue,
fieldreport.dateofreport AS `Date of Information`,
fieldreport.reportreceiveddate AS `Data Entry Date`,
fieldreport.reportcompleteddate AS `Submitted Date`,
fieldreport.reportverifieddate AS `Approved Date`,
fieldreport.dataenterer AS `Data enterer`,
fieldreport.verifiedby AS `Approved by`,
victiminfoversion.victim_localid
FROM
fieldreport
Inner Join victiminfoversion ON fieldreport.fieldreport_guid = victiminfoversion.fieldreport_guid
Inner Join imsmaenum ON fieldreport.workbenchstatusenum_guid = imsmaenum.imsmaenum_guid
WHERE
(fieldreport.fieldreport_localid IS NULL or length(trim(fieldreport.fieldreport_localid)) = 0) AND
victiminfoversion.link_only = '0'
</pre>
=== Example update query ===__NOEDITSECTION__
<pre>
update fieldreport
Inner Join hazardinfoversion ON hazardinfoversion.fieldreport_guid = fieldreport.fieldreport_guid
SET
fieldreport.fieldreport_localid = concat('FR-', trim(hazardinfoversion.hazard_localid))
WHERE
(fieldreport.fieldreport_localid is null or length(trim(fieldreport.fieldreport_localid)) = 0) and
hazardinfoversion.link_only = '0' AND
hazardinfoversion.hazard_localid IS NOT NULL
</pre>