SQL Saved search Different Location: Difference between revisions

Latest revision as of 15:01, 20 May 2014

These queries finds records where the assigned locations are not the same. They are added to V6.0 as Saved searches with a syntax that works as Saved search
Accident and Victim

SELECT DISTINCT accident.accident_guid 
FROM accident 
Inner Join link ON accident.accident_guid = link.from_guid 
Inner Join victim ON link.to_guid = victim.victim_guid 
WHERE accident.location_guid <> victim.location_guid

Hazard/Land and HazRed/Activity

SELECT DISTINCT hazard.hazard_guid 
FROM hazard Inner Join link ON hazard.hazard_guid = link.from_guid Inner Join hazreduc ON link.to_guid = hazreduc.hazreduc_guid 
WHERE hazard.location_guid <> hazreduc.location_guid

QM and HazRed/Activty

SELECT DISTINCT qa.qa_guid 
FROM qa INNER JOIN link ON qa_guid = link.from_guid INNER JOIN hazreduc ON link.to_guid = hazreduc.hazreduc_guid 
WHERE qa.location_guid <> hazreduc.location_guid

{{#switch:|subgroup|child=|none=|#default=

}}{{#if:|}}{{#if:Upgrade Process|<td style="text-align:left;border-left-width:2px;border-left-style:solid;|{{#if:|}}}}{{#if:|{{#if:IMSMA Hub{{#switch:{{#if:|{{{border}}}|child}}|subgroup|child=|none=|#default=

}}{{#ifeq:|Template|{{#ifeq:{{#if:|{{{border}}}|child}}|child||{{#ifeq:{{#if:|{{{border}}}|child}}|subgroup||{{#switch:sql saved search different location

|doc
|sandbox
|testcases =
|#default = {{#switch:
 |plainlist
 |hlist
 |hlist hnum
 |hlist vcard
 |vcard hlist = 
 |#default = 
 }}
}}

}}}}}}|}}{{#if:|{{{group2}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list2}}}

}}{{#if:|{{#if:IMSMA Hub{{#switch:{{#if:|{{{border}}}|child}}|subgroup|child=|none=|#default=

}}{{#ifeq:|Template|{{#ifeq:{{#if:|{{{border}}}|child}}|child||{{#ifeq:{{#if:|{{{border}}}|child}}|subgroup||{{#switch:sql saved search different location

|doc
|sandbox
|testcases =
|#default = {{#switch:
 |plainlist
 |hlist
 |hlist hnum
 |hlist vcard
 |vcard hlist = 
 |#default = 
 }}
}}

}}}}}}|}}{{#if:|{{{group3}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list3}}}

}}{{#if:|{{#if:|{{{group4}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list4}}}

}}{{#if:|{{#if:|{{{group5}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list5}}}

}}{{#if:|{{#if:|{{{group6}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list6}}}

}}{{#if:|{{#if:|{{{group7}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list7}}}

}}{{#if:|{{#if:|{{{group8}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list8}}}

}}{{#if:|{{#if:|{{{group9}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list9}}}

}}{{#if:|{{#if:|{{{group10}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list10}}}

}}{{#if:|{{#if:|{{{group11}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list11}}}

}}{{#if:|{{#if:|{{{group12}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list12}}}

}}{{#if:|{{#if:|{{{group13}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list13}}}

}}{{#if:|{{#if:|{{{group14}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list14}}}

}}{{#if:|{{#if:|{{{group15}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list15}}}

}}{{#if:|{{#if:|{{{group16}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list16}}}

}}{{#if:|{{#if:|{{{group17}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list17}}}

}}{{#if:|{{#if:|{{{group18}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list18}}}

}}{{#if:|{{#if:|{{{group19}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list19}}}

}}{{#if:|{{#if:|{{{group20}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list20}}}

}}{{#if:|{{#if:IMSMA Hub{{#switch:{{#if:|{{{border}}}|child}}|subgroup|child=|none=|#default=

}}{{#ifeq:|Template|{{#ifeq:{{#if:|{{{border}}}|child}}|child||{{#ifeq:{{#if:|{{{border}}}|child}}|subgroup||{{#switch:sql saved search different location

|doc
|sandbox
|testcases =
|#default = {{#switch:
 |plainlist
 |hlist
 |hlist hnum
 |hlist vcard
 |vcard hlist = 
 |#default = 
 }}
}}

}}}}}}|}}

{{{below}}}

}}{{#switch:|subgroup|child=

|none=|#default=}}{{#ifeq:|Template|{{#ifeq:|child||{{#ifeq:|subgroup||{{#switch:sql saved search different location

|doc
|sandbox
|testcases =
|#default = {{#switch:hlist
 |plainlist
 |hlist
 |hlist hnum
 |hlist vcard
 |vcard hlist = 
 |#default = 
 }}
}}

}}}}}}

@@ Line 1: / Line 1: @@
-These queries finds records where the assigned locations is not the same. They are added to V6.0 as Saved searches with a syntax that works as Saved search <br />
+These queries finds records where the assigned locations are not the same. They are added to V6.0 as Saved searches with a syntax that works as Saved search <br />
 '''Accident and Victim'''
 <pre>
-SELECT DISTINCT
+SELECT DISTINCT accident.accident_guid
-accident_localid
+FROM accident
-FROM
+Inner Join link ON accident.accident_guid = link.from_guid
-accident
+Inner Join victim ON link.to_guid = victim.victim_guid
-Inner Join link ON accident.accident_guid = link.from_guid
+WHERE accident.location_guid <> victim.location_guid
-Inner Join victim ON link.to_guid = victim.victim_guid
-Inner Join location AS AccLoc ON accident.location_guid = AccLoc.location_guid
-Inner Join location AS VicLoc ON victim.location_guid = VicLoc.location_guid
-WHERE
-link.from_link_type =  'Accident'
-AND (AccLoc.location_guid <> VicLoc.location_guid)
-ORDER BY
-accident_localid
 </pre>
-'''Hazard and HazRed'''
+'''Hazard/Land and HazRed/Activity'''
 <pre>
-SELECT DISTINCT
+SELECT DISTINCT hazard.hazard_guid
-hazard_localid
+FROM hazard Inner Join link ON hazard.hazard_guid = link.from_guid Inner Join hazreduc ON link.to_guid = hazreduc.hazreduc_guid
-FROM
+WHERE hazard.location_guid <> hazreduc.location_guid
-hazard
-Inner Join link ON hazard.hazard_guid = link.from_guid
-Inner Join hazreduc ON link.to_guid = hazreduc.hazreduc_guid
-Inner Join location AS HazLoc ON hazard.location_guid = HazLoc.location_guid
-Inner Join location AS HazRedLoc ON hazreduc.location_guid = HazRedLoc.location_guid
-WHERE
-link.from_link_type =  'Hazard'
-AND (HazLoc.location_guid <> HazRedLoc.location_guid)
-ORDER BY
-hazard_localid
 </pre>
-'''QM and HazRed'''
+'''QM and HazRed/Activty'''
 <pre>
-SELECT DISTINCT
+SELECT DISTINCT qa.qa_guid
-qa_localid
+FROM qa INNER JOIN link ON qa_guid = link.from_guid INNER JOIN hazreduc ON link.to_guid = hazreduc.hazreduc_guid
-FROM
+WHERE qa.location_guid <> hazreduc.location_guid
-link
-Inner Join hazreduc ON link.to_guid = hazreduc.hazreduc_guid
-Inner Join location AS HazLoc
-Inner Join location AS HazRedLoc ON hazreduc.location_guid = HazRedLoc.location_guid
-Inner Join qa ON link.from_guid = qa.qa_guid AND HazLoc.location_guid = qa.location_guid
-WHERE
-link.from_link_type =  'QA' AND
-(HazLoc.location_guid <>  HazRedLoc.location_guid)
-ORDER BY
-qa_localid
 </pre>
+{{NavBox Upgrade}}
 [[Category:NoPublic]]
 [[Category:SQL Queries]]

SQL Saved search Different Location: Difference between revisions

Latest revision as of 15:01, 20 May 2014

Navigation menu

Search