Changes

Missing Local ID PGSQL

528 bytes added, 22:57, 12 November 2016

no edit summary

{{Under construction | still MySQL queries}}

{{TOC right}}

{{Note | All ID are important to be filled in. ''Form ID'' is important for the DEF's Reconcilation tab and I recommend that to update ''Form ID'' after you have have checked the items' ID.

SELECT

fieldreport.fieldreport_localid,

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`",

imsmaenum.enumvalue,

fieldreportdesc.fieldreportname

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.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.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.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.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.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.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

<pre>

update fieldreport

Inner Join hazardinfoversion ON hazardinfoversion.fieldreport_guid = fieldreport.fieldreport_guid

SET

fieldreport.fieldreport_localid = concat('FRDEF-', || trim(hazardinfoversion.hazard_localid))FROM hazardinfoversion

WHERE

hazardinfoversion.fieldreport_guid = fieldreport.fieldreport_guid AND

(fieldreport.fieldreport_localid is null or length(trim(fieldreport.fieldreport_localid)) = 0) and

hazardinfoversion.link_only = 'f' AND

<pre>

SELECT

"public".gazetteer.gazetteer_localid,"public".gazetteer.gazetteername,"public".gazetteerlevel."level"

FROM

"public".gazetteerINNER JOIN "public".gazetteerlevel ON "public".gazetteer.gazlevel_guid = "public".gazetteerlevel.gazlevel_guid

WHERE

"public".gazetteer.gazetteer_localid IS NULL OR length(trim(gazetteer_localid)) = 0ORDER BY"public".gazetteer.gazetteername ASC

</pre>

<pre>

SELECT

"public".organisation.org_localid,"public".organisation.orgname,"public".organisation.parentorg_guid

FROM

organisation

WHERE

org_localid IS NULL OR length(trim(org_localid)) = 0

ORDER BY

"public".organisation.orgname ASC

</pre>

<pre>

SELECT

"public".place.place_localid,"public".place.placename

FROM

place

WHERE

place_localid IS NULL OR length(trim(place_localid)) = 0

ORDER BY

"public".place.placename ASC

</pre>

<pre>

SELECT

"public".task.localid,"public".task."name"

FROM

task

WHERE

task.localid IS NULL or length(trim(task.localid))=0

ORDER BY

"public".task."name" ASC

</pre>

<pre>

SELECT

"public".work_item.localid,"public".work_item."name"

FROM

work_item

WHERE

work_item.localid IS NULL OR length(trim(work_item.localid)) = 0

ORDER BY

"public".work_item."name" ASC

</pre>

• ← Older edit

Alnaucler

NoPublic, bureaucrat, administrator

6,632

edits