NoPublic, bureaucrat, administrator
6,632
edits
Changes
no edit summary
| [[Image:Link error2.png|600px|center]]
<div align="center">
'' Example where the first DEF has Combine instead of New + the DEF template had 2 multiple items''
</div>
|}
==Case Combine==__NOEDITSECTION__
The solution is actually '''not''' to add rows to the table ''link'' but to update some columns of existing rows.
<ol><li>''approval_state'' should be NEW_LOCATION or NEW depending on item<li>''to_guid'' should be to the Location GUID<li>''to_link_type'' should be LOCATION<li>''system_link'' should be NULL<li>''to_link_is_version'' verify that it is '''f'''<li>''update_type'' should be NULL</ol>
=== Location ===__NOEDITSECTION__
[[Image:Link error3.png|700px|center]]
<div align="center">
'' Note: this is NOT a simple combine error - this may also be caused by DEF templates with multiple items''
</div>
<pre>
SELECT
"public".location.location_guid,
"public".location.location_localid
from location
where not exists (
select 1
from locationinfoversion
where location.location_guid = locationinfoversion.location_guid
and exists (
select 1
from link
where approval_state = 'NEW_LOCATION'
and link.from_guid = locationinfoversion.locationinfoversion_guid
)
)
ORDER BY location_localid
</pre>
=== Land ===__NOEDITSECTION__
</pre>
=== UPDATE example ===__NOEDITSECTION__
<pre>
UPDATE link
SET
approval_state = 'NEW',
to_guid = "public".hazard.location_guid,
to_link_type = 'LOCATION',
system_link = NULL,
update_type = NULL
FROM
"public".hazardinfoversion
INNER JOIN "public".hazard ON "public".hazardinfoversion.hazard_guid = "public".hazard.hazard_guid
WHERE
"public".hazardinfoversion.hazardinfoversion_guid = "public".link.from_guid AND
(hazardinfoversion.hazard_guid, hazardinfoversion.dataentrydate) IN ((SELECT
"public".hazard.hazard_guid AS guid,
Min("public".hazardinfoversion.dataentrydate)
FROM
"public".hazard
INNER JOIN "public".hazardinfoversion ON "public".hazardinfoversion.hazard_guid = "public".hazard.hazard_guid
INNER JOIN "public".link ON "public".link.from_guid = "public".hazardinfoversion.hazardinfoversion_guid
WHERE
"public".hazardinfoversion.link_only = 'f' AND
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
)
)
GROUP BY
"public".hazard.hazard_guid))
AND
"public".link.approval_state = 'UPDATE' AND
"public".link.update_type <> 'UPDATE'
</pre>
[[Category:SQL Queries]]
[[Category:NoPublic]]
{{NavBox Hub}}