NoPublic, bureaucrat, administrator
6,632
edits
Changes
no edit summary
{{TOC right}}
Each item that is updated should have one DEF that has ''approval_state'' = '''NEW'''. We have found few items in several database that does not have any row with ''approval_state'' = '''NEW'''.
The solution is actually '''not''' to add rows to the table ''link'' but to update some columns of existing rows.
=== Location ===__NOEDITSECTION__
<pre>
</pre>
=== Land ===__NOEDITSECTION__
<pre>
SELECT
"public".hazard.hazard_guid,
"public".hazard.location_guid,
"public".hazard.hazard_localid
from hazard
where not exists (
select 1
from hazardinfoversion
where hazard.hazard_guid = hazardinfoversion.hazard_guid
and exists (
select 1
from link
where approval_state = 'NEW'
and link.from_guid = hazardinfoversion.hazardinfoversion_guid
)
)
ORDER BY hazard_localid
</pre>
=== Activity===__NOEDITSECTION__
<pre>
select
"public".hazreduc.hazreduc_guid,
"public".hazreduc.location_guid,
"public".hazreduc.hazreduc_localid
from hazreduc
where not exists (
select 1
from hazreducinfoversion
where hazreduc.hazreduc_guid = hazreducinfoversion.hazreduc_guid
and exists (
select 1
from link
where approval_state = 'NEW'
and link.from_guid = hazreducinfoversion.hazreducinfoversion_guid
)
)
ORDER BY "public".hazreduc.hazreduc_localid
</pre>
=== Accident===__NOEDITSECTION__
<pre>
select
"public".accident.accident_guid,
"public".accident.location_guid,
"public".accident.accident_localid
from accident
where not exists (
select 1
from accidentinfoversion
where accident.accident_guid = accidentinfoversion.accident_guid
and exists (
select 1
from link
where approval_state = 'NEW'
and link.from_guid = accidentinfoversion.accidentinfoversion_guid
)
)
ORDER BY "public".accident.accident_localid
</pre>
=== Victim===__NOEDITSECTION__
<pre>
SELECT
"public".victim.victim_guid,
"public".victim.location_guid,
"public".victim.victim_localid
from victim
where not exists (
select 1
from victiminfoversion
where victim.victim_guid = victiminfoversion.victim_guid
and exists (
select 1
from link
where approval_state = 'NEW'
and link.from_guid = victiminfoversion.victiminfoversion_guid
)
)
ORDER BY
"public".victim.victim_localid ASC
</pre>
=== Education ===__NOEDITSECTION__
<pre>
SELECT
"public".mre.mre_guid,
"public".mre.location_guid,
"public".mre.mre_localid
from mre
where not exists (
select 1
from mreinfoversion
where mre.mre_guid = mreinfoversion.mre_guid
and exists (
select 1
from link
where approval_state = 'NEW'
and link.from_guid = mreinfoversion.mreinfoversion_guid
)
)
ORDER BY
"public".mre.mre_localid ASC
</pre>
=== QM===__NOEDITSECTION__
<pre>
SELECT
"public".qa.qa_guid,
"public".qa.location_guid,
"public".qa.qa_localid
from qa
where not exists (
select 1
from qainfoversion
where qa.qa_guid = qainfoversion.qa_guid
and exists (
select 1
from link
where approval_state = 'NEW'
and link.from_guid = qainfoversion.qainfoversion_guid
)
)
ORDER BY
"public".qa.qa_localid ASC
</pre>
[[Category:SQL Queries]]
[[Category:NoPublic]]
{{NavBox Hub}}
