Help us understand this sql script built by a vendor

Prev: Query question
Next: SSIS - sql server integrated services - like OLAP?

From: Pat M - City of Reno on 1 Jul 2008 12:34

---

One of my co-workers and I are trying to figure out how this script works. I
do understand using multiple Select statements to get multiple result sets.
Can someone help us with what this script is doing? My take was that it's
like building arrays.

SELECT DISTINCT B1_ALT_ID, --B1_SUBDIVISION,
-- A1
(
SELECT B1_CHECKLIST_COMMENT
FROM BCHCKBOX BCB
WHERE
BCB.B1_PER_ID1 = B.B1_PER_ID1
AND BCB.B1_PER_ID2 = B.B1_PER_ID2
AND BCB.B1_PER_ID3 = B.B1_PER_ID3
AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
AND BCB.B1_CHECKBOX_DESC = 'Final Map Name'
) Final_Map_Name,
-- B
(
SELECT B1_CHECKLIST_COMMENT
FROM BCHCKBOX BCB
WHERE
BCB.B1_PER_ID1 = B.B1_PER_ID1
AND BCB.B1_PER_ID2 = B.B1_PER_ID2
AND BCB.B1_PER_ID3 = B.B1_PER_ID3
AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
AND BCB.B1_CHECKBOX_DESC = 'Security Release Date'
) Security_Release_Date,
-- B
(
SELECT B1_CHECKLIST_COMMENT
FROM BCHCKBOX BCB
WHERE
BCB.B1_PER_ID1 = B.B1_PER_ID1
AND BCB.B1_PER_ID2 = B.B1_PER_ID2
AND BCB.B1_PER_ID3 = B.B1_PER_ID3
AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
AND BCB.B1_CHECKBOX_DESC = '10 Month Inspection Date'
) Month_Inspection

FROM B1PERMIT B
INNER JOIN B3PARCEL BP ON
--LEFT OUTER JOIN B3PARCEL BP ON
BP.B1_PER_ID1 = B.B1_PER_ID1
AND BP.B1_PER_ID2 = B.B1_PER_ID2
AND BP.B1_PER_ID3 = B.B1_PER_ID3
AND BP.SERV_PROV_CODE = B.SERV_PROV_CODE
LEFT OUTER JOIN GPROCESS_HISTORY G ON
/*WHERE*/ B.SERV_PROV_CODE = 'RENO'
AND B.B1_PER_ID1 = G.B1_PER_ID1
AND B.B1_PER_ID2 = G.B1_PER_ID2
AND B.B1_PER_ID3 = G.B1_PER_ID3
AND B.SERV_PROV_CODE = G.SERV_PROV_CODE
AND UPPER(G.SD_PRO_DES) NOT LIKE '%CLOSED%'
AND UPPER(B.B1_APPL_STATUS) NOT LIKE '%CLOSED%'
AND G.SD_PRO_DES = 'Acceptance of Improvements'
AND (
G.SD_PRO_DES = 'Accepted'
OR
G.SD_PRO_DES = 'City Council Not Required'
)
AND EXISTS
(
SELECT B1_CHECKLIST_COMMENT
FROM BCHCKBOX BCB
WHERE
BCB.B1_PER_ID1 = B.B1_PER_ID1
AND BCB.B1_PER_ID2 = B.B1_PER_ID2
AND BCB.B1_PER_ID3 = B.B1_PER_ID3
AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
AND BCB.B1_CHECKBOX_DESC = 'Agreement Expiration'
AND B1_CHECKLIST_COMMENT != NULL
)
AND EXISTS
(
SELECT B1_CHECKLIST_COMMENT
FROM BCHCKBOX BCB
WHERE
BCB.B1_PER_ID1 = B.B1_PER_ID1
AND BCB.B1_PER_ID2 = B.B1_PER_ID2
AND BCB.B1_PER_ID3 = B.B1_PER_ID3
AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
AND BCB.B1_CHECKBOX_DESC = 'Final Map Name'
AND B1_CHECKLIST_COMMENT != NULL
)
WHERE
EXISTS
-- Security Release date check.
(
SELECT B1_CHECKLIST_COMMENT
FROM BCHCKBOX BCB
WHERE
--BCB.B1_CHECKBOX_DESC = 'Security Release Date'
BCB.B1_PER_ID1 = B.B1_PER_ID1
AND BCB.B1_PER_ID2 = B.B1_PER_ID2
AND BCB.B1_PER_ID3 = B.B1_PER_ID3
AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
AND BCB.B1_CHECKBOX_DESC = '10 Month Inspection Date'
AND TO_DATE(BCB.B1_CHECKLIST_COMMENT, 'MM/DD/YYYY') >= {?FROM_DATE}
AND TO_DATE(BCB.B1_CHECKLIST_COMMENT, 'MM/DD/YYYY') <= {?TO_DATE}
)
AND UPPER(B.B1_APPL_STATUS) NOT LIKE '%CLOSED%'



--
Regards,
Pat

From: Roy Harvey (SQL Server MVP) on 1 Jul 2008 13:00

---

The "script" is a single SELECT command, which will return a single
result set. Do you understand the basics of the SELECT? This is not
simple SQL, and it appears to have evolved over time and maintenance.
Without knowing how much you understand it is hard to know where to
start.

The first three subqueries are items in the outer most SELECT's SELECT
list, the list of things being returned by the query. Note that those
subqueries only return a single value, rather than a set. The outer
SELECT command's FROM clause queries three tables (B1PERMIT, B3PARCEL,
GPROCESS_HISTORY), joining them using keys. EXISTS subqueries are
used to perform tests against other tables; and EXISTS subquery does
not return any results, just whether there were any matches.

To try to get into this any farther would require more information on
all the tables referenced, most especially on their keys and unique
constraints.

Roy Harvey
Beacon Falls, CT

On Tue, 1 Jul 2008 09:34:03 -0700, Pat M - City of Reno
<PatMCityofReno(a)discussions.microsoft.com> wrote:

>One of my co-workers and I are trying to figure out how this script works. I
>do understand using multiple Select statements to get multiple result sets.
>Can someone help us with what this script is doing? My take was that it's
>like building arrays.
>
>SELECT DISTINCT B1_ALT_ID, --B1_SUBDIVISION,
>-- A1
>(
> SELECT B1_CHECKLIST_COMMENT
> FROM BCHCKBOX BCB
> WHERE
> BCB.B1_PER_ID1 = B.B1_PER_ID1
> AND BCB.B1_PER_ID2 = B.B1_PER_ID2
> AND BCB.B1_PER_ID3 = B.B1_PER_ID3
> AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
> AND BCB.B1_CHECKBOX_DESC = 'Final Map Name'
>) Final_Map_Name,
>-- B
>(
> SELECT B1_CHECKLIST_COMMENT
> FROM BCHCKBOX BCB
> WHERE
> BCB.B1_PER_ID1 = B.B1_PER_ID1
> AND BCB.B1_PER_ID2 = B.B1_PER_ID2
> AND BCB.B1_PER_ID3 = B.B1_PER_ID3
> AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
> AND BCB.B1_CHECKBOX_DESC = 'Security Release Date'
>) Security_Release_Date,
>-- B
>(
> SELECT B1_CHECKLIST_COMMENT
> FROM BCHCKBOX BCB
> WHERE
> BCB.B1_PER_ID1 = B.B1_PER_ID1
> AND BCB.B1_PER_ID2 = B.B1_PER_ID2
> AND BCB.B1_PER_ID3 = B.B1_PER_ID3
> AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
> AND BCB.B1_CHECKBOX_DESC = '10 Month Inspection Date'
>) Month_Inspection
>
>FROM B1PERMIT B
> INNER JOIN B3PARCEL BP ON
> --LEFT OUTER JOIN B3PARCEL BP ON
> BP.B1_PER_ID1 = B.B1_PER_ID1
> AND BP.B1_PER_ID2 = B.B1_PER_ID2
> AND BP.B1_PER_ID3 = B.B1_PER_ID3
> AND BP.SERV_PROV_CODE = B.SERV_PROV_CODE
> LEFT OUTER JOIN GPROCESS_HISTORY G ON
> /*WHERE*/ B.SERV_PROV_CODE = 'RENO'
> AND B.B1_PER_ID1 = G.B1_PER_ID1
> AND B.B1_PER_ID2 = G.B1_PER_ID2
> AND B.B1_PER_ID3 = G.B1_PER_ID3
> AND B.SERV_PROV_CODE = G.SERV_PROV_CODE
> AND UPPER(G.SD_PRO_DES) NOT LIKE '%CLOSED%'
> AND UPPER(B.B1_APPL_STATUS) NOT LIKE '%CLOSED%'
> AND G.SD_PRO_DES = 'Acceptance of Improvements'
> AND (
> G.SD_PRO_DES = 'Accepted'
> OR
> G.SD_PRO_DES = 'City Council Not Required'
> )
> AND EXISTS
> (
> SELECT B1_CHECKLIST_COMMENT
> FROM BCHCKBOX BCB
> WHERE
> BCB.B1_PER_ID1 = B.B1_PER_ID1
> AND BCB.B1_PER_ID2 = B.B1_PER_ID2
> AND BCB.B1_PER_ID3 = B.B1_PER_ID3
> AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
> AND BCB.B1_CHECKBOX_DESC = 'Agreement Expiration'
> AND B1_CHECKLIST_COMMENT != NULL
> )
> AND EXISTS
> (
> SELECT B1_CHECKLIST_COMMENT
> FROM BCHCKBOX BCB
> WHERE
> BCB.B1_PER_ID1 = B.B1_PER_ID1
> AND BCB.B1_PER_ID2 = B.B1_PER_ID2
> AND BCB.B1_PER_ID3 = B.B1_PER_ID3
> AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
> AND BCB.B1_CHECKBOX_DESC = 'Final Map Name'
> AND B1_CHECKLIST_COMMENT != NULL
> )
>WHERE
> EXISTS
> -- Security Release date check.
> (
> SELECT B1_CHECKLIST_COMMENT
> FROM BCHCKBOX BCB
> WHERE
> --BCB.B1_CHECKBOX_DESC = 'Security Release Date'
> BCB.B1_PER_ID1 = B.B1_PER_ID1
> AND BCB.B1_PER_ID2 = B.B1_PER_ID2
> AND BCB.B1_PER_ID3 = B.B1_PER_ID3
> AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
> AND BCB.B1_CHECKBOX_DESC = '10 Month Inspection Date'
> AND TO_DATE(BCB.B1_CHECKLIST_COMMENT, 'MM/DD/YYYY') >= {?FROM_DATE}
> AND TO_DATE(BCB.B1_CHECKLIST_COMMENT, 'MM/DD/YYYY') <= {?TO_DATE}
> )
> AND UPPER(B.B1_APPL_STATUS) NOT LIKE '%CLOSED%'

From: Pat M - City of Reno on 1 Jul 2008 13:10

---

Roy, that's enough info. Thanks very much. We will study from here and I
really appreciate the lead. Just learning all the quirks and trying to find
out what this was doing.
--
Regards,
Pat


"Pat M - City of Reno" wrote:

> One of my co-workers and I are trying to figure out how this script works. I
> do understand using multiple Select statements to get multiple result sets.
> Can someone help us with what this script is doing? My take was that it's
> like building arrays.
>
> SELECT DISTINCT B1_ALT_ID, --B1_SUBDIVISION,
> -- A1
> (
> SELECT B1_CHECKLIST_COMMENT
> FROM BCHCKBOX BCB
> WHERE
> BCB.B1_PER_ID1 = B.B1_PER_ID1
> AND BCB.B1_PER_ID2 = B.B1_PER_ID2
> AND BCB.B1_PER_ID3 = B.B1_PER_ID3
> AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
> AND BCB.B1_CHECKBOX_DESC = 'Final Map Name'
> ) Final_Map_Name,
> -- B
> (
> SELECT B1_CHECKLIST_COMMENT
> FROM BCHCKBOX BCB
> WHERE
> BCB.B1_PER_ID1 = B.B1_PER_ID1
> AND BCB.B1_PER_ID2 = B.B1_PER_ID2
> AND BCB.B1_PER_ID3 = B.B1_PER_ID3
> AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
> AND BCB.B1_CHECKBOX_DESC = 'Security Release Date'
> ) Security_Release_Date,
> -- B
> (
> SELECT B1_CHECKLIST_COMMENT
> FROM BCHCKBOX BCB
> WHERE
> BCB.B1_PER_ID1 = B.B1_PER_ID1
> AND BCB.B1_PER_ID2 = B.B1_PER_ID2
> AND BCB.B1_PER_ID3 = B.B1_PER_ID3
> AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
> AND BCB.B1_CHECKBOX_DESC = '10 Month Inspection Date'
> ) Month_Inspection
>
> FROM B1PERMIT B
> INNER JOIN B3PARCEL BP ON
> --LEFT OUTER JOIN B3PARCEL BP ON
> BP.B1_PER_ID1 = B.B1_PER_ID1
> AND BP.B1_PER_ID2 = B.B1_PER_ID2
> AND BP.B1_PER_ID3 = B.B1_PER_ID3
> AND BP.SERV_PROV_CODE = B.SERV_PROV_CODE
> LEFT OUTER JOIN GPROCESS_HISTORY G ON
> /*WHERE*/ B.SERV_PROV_CODE = 'RENO'
> AND B.B1_PER_ID1 = G.B1_PER_ID1
> AND B.B1_PER_ID2 = G.B1_PER_ID2
> AND B.B1_PER_ID3 = G.B1_PER_ID3
> AND B.SERV_PROV_CODE = G.SERV_PROV_CODE
> AND UPPER(G.SD_PRO_DES) NOT LIKE '%CLOSED%'
> AND UPPER(B.B1_APPL_STATUS) NOT LIKE '%CLOSED%'
> AND G.SD_PRO_DES = 'Acceptance of Improvements'
> AND (
> G.SD_PRO_DES = 'Accepted'
> OR
> G.SD_PRO_DES = 'City Council Not Required'
> )
> AND EXISTS
> (
> SELECT B1_CHECKLIST_COMMENT
> FROM BCHCKBOX BCB
> WHERE
> BCB.B1_PER_ID1 = B.B1_PER_ID1
> AND BCB.B1_PER_ID2 = B.B1_PER_ID2
> AND BCB.B1_PER_ID3 = B.B1_PER_ID3
> AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
> AND BCB.B1_CHECKBOX_DESC = 'Agreement Expiration'
> AND B1_CHECKLIST_COMMENT != NULL
> )
> AND EXISTS
> (
> SELECT B1_CHECKLIST_COMMENT
> FROM BCHCKBOX BCB
> WHERE
> BCB.B1_PER_ID1 = B.B1_PER_ID1
> AND BCB.B1_PER_ID2 = B.B1_PER_ID2
> AND BCB.B1_PER_ID3 = B.B1_PER_ID3
> AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
> AND BCB.B1_CHECKBOX_DESC = 'Final Map Name'
> AND B1_CHECKLIST_COMMENT != NULL
> )
> WHERE
> EXISTS
> -- Security Release date check.
> (
> SELECT B1_CHECKLIST_COMMENT
> FROM BCHCKBOX BCB
> WHERE
> --BCB.B1_CHECKBOX_DESC = 'Security Release Date'
> BCB.B1_PER_ID1 = B.B1_PER_ID1
> AND BCB.B1_PER_ID2 = B.B1_PER_ID2
> AND BCB.B1_PER_ID3 = B.B1_PER_ID3
> AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
> AND BCB.B1_CHECKBOX_DESC = '10 Month Inspection Date'
> AND TO_DATE(BCB.B1_CHECKLIST_COMMENT, 'MM/DD/YYYY') >= {?FROM_DATE}
> AND TO_DATE(BCB.B1_CHECKLIST_COMMENT, 'MM/DD/YYYY') <= {?TO_DATE}
> )
> AND UPPER(B.B1_APPL_STATUS) NOT LIKE '%CLOSED%'
>
>
>
> --
> Regards,
> Pat

From: Rich on 1 Jul 2008 13:14

---

SELECT DISTINCT B1_ALT_ID, --B1_SUBDIVISION,
-- A1 <-- this is just a marker
(
...
) Final_Map_Name, <--- this is a dynamic field (subquery based - should
return only one value per row
-- B <-- another marker
(
....
) Security_Release_Date, <-- another dynamic field (subquery based)
-- B
(
...
) Month_Inspection <-- and another dynamice field (subquery based)

--source data to pull from with a bunch of complex joins
FROM B1PERMIT B
INNER JOIN B3PARCEL BP ON
--LEFT OUTER JOIN B3PARCEL BP ON
BP.B1_PER_ID1 = B.B1_PER_ID1
AND BP.B1_PER_ID2 = B.B1_PER_ID2
AND BP.B1_PER_ID3 = B.B1_PER_ID3
AND BP.SERV_PROV_CODE = B.SERV_PROV_CODE
LEFT OUTER JOIN GPROCESS_HISTORY G ON
/*WHERE*/ B.SERV_PROV_CODE = 'RENO'
AND B.B1_PER_ID1 = G.B1_PER_ID1
AND B.B1_PER_ID2 = G.B1_PER_ID2
AND B.B1_PER_ID3 = G.B1_PER_ID3
AND B.SERV_PROV_CODE = G.SERV_PROV_CODE
AND UPPER(G.SD_PRO_DES) NOT LIKE '%CLOSED%'
AND UPPER(B.B1_APPL_STATUS) NOT LIKE '%CLOSED%'
AND G.SD_PRO_DES = 'Acceptance of Improvements'
AND (
G.SD_PRO_DES = 'Accepted'
OR
G.SD_PRO_DES = 'City Council Not Required'
)
AND EXISTS
(
SELECT B1_CHECKLIST_COMMENT
FROM BCHCKBOX BCB
WHERE
BCB.B1_PER_ID1 = B.B1_PER_ID1
AND BCB.B1_PER_ID2 = B.B1_PER_ID2
AND BCB.B1_PER_ID3 = B.B1_PER_ID3
AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
AND BCB.B1_CHECKBOX_DESC = 'Agreement Expiration'
AND B1_CHECKLIST_COMMENT != NULL
)
AND EXISTS
(
SELECT B1_CHECKLIST_COMMENT
FROM BCHCKBOX BCB
WHERE
BCB.B1_PER_ID1 = B.B1_PER_ID1
AND BCB.B1_PER_ID2 = B.B1_PER_ID2
AND BCB.B1_PER_ID3 = B.B1_PER_ID3
AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
AND BCB.B1_CHECKBOX_DESC = 'Final Map Name'
AND B1_CHECKLIST_COMMENT != NULL
)
--lastly a Where clause
WHERE
EXISTS
-- Security Release date check.
(
SELECT B1_CHECKLIST_COMMENT
FROM BCHCKBOX BCB
WHERE
--BCB.B1_CHECKBOX_DESC = 'Security Release Date'
BCB.B1_PER_ID1 = B.B1_PER_ID1
AND BCB.B1_PER_ID2 = B.B1_PER_ID2
AND BCB.B1_PER_ID3 = B.B1_PER_ID3
AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
AND BCB.B1_CHECKBOX_DESC = '10 Month Inspection Date'
AND TO_DATE(BCB.B1_CHECKLIST_COMMENT, 'MM/DD/YYYY') >= {?FROM_DATE}
AND TO_DATE(BCB.B1_CHECKLIST_COMMENT, 'MM/DD/YYYY') <= {?TO_DATE}
)
AND UPPER(B.B1_APPL_STATUS) NOT LIKE '%CLOSED%'

-----------------------------

I hate these kinds of queries. I think I would break this up into smaller
bite size procs and use #temp tables - lot easier to debug. I don't know the
context of this query, and it is possible this may be the only way to do it
(but I doubt that). Plese tell me this runs real slow because I am seeing a
lot of guess work on the part of the computer:


AND UPPER(G.SD_PRO_DES) NOT LIKE '%CLOSED%'
AND UPPER(B.B1_APPL_STATUS) NOT LIKE '%CLOSED%'

AND TO_DATE(BCB.B1_CHECKLIST_COMMENT, 'MM/DD/YYYY') >= {?FROM_DATE}
AND TO_DATE(BCB.B1_CHECKLIST_COMMENT, 'MM/DD/YYYY') <= {?TO_DATE}

LIKE '%CLOSED%'

----------------------------------

My guess is that the source data is probably not real clean and the author
is compensating for this -- instead of going through a full fledge data
cleaning process (which I am currently involved in - and taking upwards of 2
years - so I can sort of appreciate why all the quess work).

Rich


"Pat M - City of Reno" wrote:

> One of my co-workers and I are trying to figure out how this script works. I
> do understand using multiple Select statements to get multiple result sets.
> Can someone help us with what this script is doing? My take was that it's
> like building arrays.
>
> SELECT DISTINCT B1_ALT_ID, --B1_SUBDIVISION,
> -- A1
> (
> SELECT B1_CHECKLIST_COMMENT
> FROM BCHCKBOX BCB
> WHERE
> BCB.B1_PER_ID1 = B.B1_PER_ID1
> AND BCB.B1_PER_ID2 = B.B1_PER_ID2
> AND BCB.B1_PER_ID3 = B.B1_PER_ID3
> AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
> AND BCB.B1_CHECKBOX_DESC = 'Final Map Name'
> ) Final_Map_Name,
> -- B
> (
> SELECT B1_CHECKLIST_COMMENT
> FROM BCHCKBOX BCB
> WHERE
> BCB.B1_PER_ID1 = B.B1_PER_ID1
> AND BCB.B1_PER_ID2 = B.B1_PER_ID2
> AND BCB.B1_PER_ID3 = B.B1_PER_ID3
> AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
> AND BCB.B1_CHECKBOX_DESC = 'Security Release Date'
> ) Security_Release_Date,
> -- B
> (
> SELECT B1_CHECKLIST_COMMENT
> FROM BCHCKBOX BCB
> WHERE
> BCB.B1_PER_ID1 = B.B1_PER_ID1
> AND BCB.B1_PER_ID2 = B.B1_PER_ID2
> AND BCB.B1_PER_ID3 = B.B1_PER_ID3
> AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
> AND BCB.B1_CHECKBOX_DESC = '10 Month Inspection Date'
> ) Month_Inspection
>
> FROM B1PERMIT B
> INNER JOIN B3PARCEL BP ON
> --LEFT OUTER JOIN B3PARCEL BP ON
> BP.B1_PER_ID1 = B.B1_PER_ID1
> AND BP.B1_PER_ID2 = B.B1_PER_ID2
> AND BP.B1_PER_ID3 = B.B1_PER_ID3
> AND BP.SERV_PROV_CODE = B.SERV_PROV_CODE
> LEFT OUTER JOIN GPROCESS_HISTORY G ON
> /*WHERE*/ B.SERV_PROV_CODE = 'RENO'
> AND B.B1_PER_ID1 = G.B1_PER_ID1
> AND B.B1_PER_ID2 = G.B1_PER_ID2
> AND B.B1_PER_ID3 = G.B1_PER_ID3
> AND B.SERV_PROV_CODE = G.SERV_PROV_CODE
> AND UPPER(G.SD_PRO_DES) NOT LIKE '%CLOSED%'
> AND UPPER(B.B1_APPL_STATUS) NOT LIKE '%CLOSED%'
> AND G.SD_PRO_DES = 'Acceptance of Improvements'
> AND (
> G.SD_PRO_DES = 'Accepted'
> OR
> G.SD_PRO_DES = 'City Council Not Required'
> )
> AND EXISTS
> (
> SELECT B1_CHECKLIST_COMMENT
> FROM BCHCKBOX BCB
> WHERE
> BCB.B1_PER_ID1 = B.B1_PER_ID1
> AND BCB.B1_PER_ID2 = B.B1_PER_ID2
> AND BCB.B1_PER_ID3 = B.B1_PER_ID3
> AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
> AND BCB.B1_CHECKBOX_DESC = 'Agreement Expiration'
> AND B1_CHECKLIST_COMMENT != NULL
> )
> AND EXISTS
> (
> SELECT B1_CHECKLIST_COMMENT
> FROM BCHCKBOX BCB
> WHERE
> BCB.B1_PER_ID1 = B.B1_PER_ID1
> AND BCB.B1_PER_ID2 = B.B1_PER_ID2
> AND BCB.B1_PER_ID3 = B.B1_PER_ID3
> AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
> AND BCB.B1_CHECKBOX_DESC = 'Final Map Name'
> AND B1_CHECKLIST_COMMENT != NULL
> )
> WHERE
> EXISTS
> -- Security Release date check.
> (
> SELECT B1_CHECKLIST_COMMENT
> FROM BCHCKBOX BCB
> WHERE
> --BCB.B1_CHECKBOX_DESC = 'Security Release Date'
> BCB.B1_PER_ID1 = B.B1_PER_ID1
> AND BCB.B1_PER_ID2 = B.B1_PER_ID2
> AND BCB.B1_PER_ID3 = B.B1_PER_ID3
> AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
> AND BCB.B1_CHECKBOX_DESC = '10 Month Inspection Date'
> AND TO_DATE(BCB.B1_CHECKLIST_COMMENT, 'MM/DD/YYYY') >= {?FROM_DATE}
> AND TO_DATE(BCB.B1_CHECKLIST_COMMENT, 'MM/DD/YYYY') <= {?TO_DATE}
> )
> AND UPPER(B.B1_APPL_STATUS) NOT LIKE '%CLOSED%'
>
>
>
> --
> Regards,
> Pat

From: Pat M - City of Reno on 1 Jul 2008 13:38

---

Thanks Rich. I'm a Sr Systems Analyst and do SQL Server admin not a lot of
programming except for views and reports. A new systems analyst asked me
about this and my first reaction was why do all this seems convoluted. I just
thought I was very very ignorant. I will tell the analyst not to copy this
style unless the data requires it. The tables are very convoluted on this
product I can tell you that.
--
Regards,
Pat


"Rich" wrote:

> SELECT DISTINCT B1_ALT_ID, --B1_SUBDIVISION,
> -- A1 <-- this is just a marker
> (
> ...
> ) Final_Map_Name, <--- this is a dynamic field (subquery based - should
> return only one value per row
> -- B <-- another marker
> (
> ...
> ) Security_Release_Date, <-- another dynamic field (subquery based)
> -- B
> (
> ...
> ) Month_Inspection <-- and another dynamice field (subquery based)
>
> --source data to pull from with a bunch of complex joins
> FROM B1PERMIT B
> INNER JOIN B3PARCEL BP ON
> --LEFT OUTER JOIN B3PARCEL BP ON
> BP.B1_PER_ID1 = B.B1_PER_ID1
> AND BP.B1_PER_ID2 = B.B1_PER_ID2
> AND BP.B1_PER_ID3 = B.B1_PER_ID3
> AND BP.SERV_PROV_CODE = B.SERV_PROV_CODE
> LEFT OUTER JOIN GPROCESS_HISTORY G ON
> /*WHERE*/ B.SERV_PROV_CODE = 'RENO'
> AND B.B1_PER_ID1 = G.B1_PER_ID1
> AND B.B1_PER_ID2 = G.B1_PER_ID2
> AND B.B1_PER_ID3 = G.B1_PER_ID3
> AND B.SERV_PROV_CODE = G.SERV_PROV_CODE
> AND UPPER(G.SD_PRO_DES) NOT LIKE '%CLOSED%'
> AND UPPER(B.B1_APPL_STATUS) NOT LIKE '%CLOSED%'
> AND G.SD_PRO_DES = 'Acceptance of Improvements'
> AND (
> G.SD_PRO_DES = 'Accepted'
> OR
> G.SD_PRO_DES = 'City Council Not Required'
> )
> AND EXISTS
> (
> SELECT B1_CHECKLIST_COMMENT
> FROM BCHCKBOX BCB
> WHERE
> BCB.B1_PER_ID1 = B.B1_PER_ID1
> AND BCB.B1_PER_ID2 = B.B1_PER_ID2
> AND BCB.B1_PER_ID3 = B.B1_PER_ID3
> AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
> AND BCB.B1_CHECKBOX_DESC = 'Agreement Expiration'
> AND B1_CHECKLIST_COMMENT != NULL
> )
> AND EXISTS
> (
> SELECT B1_CHECKLIST_COMMENT
> FROM BCHCKBOX BCB
> WHERE
> BCB.B1_PER_ID1 = B.B1_PER_ID1
> AND BCB.B1_PER_ID2 = B.B1_PER_ID2
> AND BCB.B1_PER_ID3 = B.B1_PER_ID3
> AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
> AND BCB.B1_CHECKBOX_DESC = 'Final Map Name'
> AND B1_CHECKLIST_COMMENT != NULL
> )
> --lastly a Where clause
> WHERE
> EXISTS
> -- Security Release date check.
> (
> SELECT B1_CHECKLIST_COMMENT
> FROM BCHCKBOX BCB
> WHERE
> --BCB.B1_CHECKBOX_DESC = 'Security Release Date'
> BCB.B1_PER_ID1 = B.B1_PER_ID1
> AND BCB.B1_PER_ID2 = B.B1_PER_ID2
> AND BCB.B1_PER_ID3 = B.B1_PER_ID3
> AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
> AND BCB.B1_CHECKBOX_DESC = '10 Month Inspection Date'
> AND TO_DATE(BCB.B1_CHECKLIST_COMMENT, 'MM/DD/YYYY') >= {?FROM_DATE}
> AND TO_DATE(BCB.B1_CHECKLIST_COMMENT, 'MM/DD/YYYY') <= {?TO_DATE}
> )
> AND UPPER(B.B1_APPL_STATUS) NOT LIKE '%CLOSED%'
>
> -----------------------------
>
> I hate these kinds of queries. I think I would break this up into smaller
> bite size procs and use #temp tables - lot easier to debug. I don't know the
> context of this query, and it is possible this may be the only way to do it
> (but I doubt that). Plese tell me this runs real slow because I am seeing a
> lot of guess work on the part of the computer:
>
>
> AND UPPER(G.SD_PRO_DES) NOT LIKE '%CLOSED%'
> AND UPPER(B.B1_APPL_STATUS) NOT LIKE '%CLOSED%'
>
> AND TO_DATE(BCB.B1_CHECKLIST_COMMENT, 'MM/DD/YYYY') >= {?FROM_DATE}
> AND TO_DATE(BCB.B1_CHECKLIST_COMMENT, 'MM/DD/YYYY') <= {?TO_DATE}
>
> LIKE '%CLOSED%'
>
> ----------------------------------
>
> My guess is that the source data is probably not real clean and the author
> is compensating for this -- instead of going through a full fledge data
> cleaning process (which I am currently involved in - and taking upwards of 2
> years - so I can sort of appreciate why all the quess work).
>
> Rich
>
>
> "Pat M - City of Reno" wrote:
>
> > One of my co-workers and I are trying to figure out how this script works. I
> > do understand using multiple Select statements to get multiple result sets.
> > Can someone help us with what this script is doing? My take was that it's
> > like building arrays.
> >
> > SELECT DISTINCT B1_ALT_ID, --B1_SUBDIVISION,
> > -- A1
> > (
> > SELECT B1_CHECKLIST_COMMENT
> > FROM BCHCKBOX BCB
> > WHERE
> > BCB.B1_PER_ID1 = B.B1_PER_ID1
> > AND BCB.B1_PER_ID2 = B.B1_PER_ID2
> > AND BCB.B1_PER_ID3 = B.B1_PER_ID3
> > AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
> > AND BCB.B1_CHECKBOX_DESC = 'Final Map Name'
> > ) Final_Map_Name,
> > -- B
> > (
> > SELECT B1_CHECKLIST_COMMENT
> > FROM BCHCKBOX BCB
> > WHERE
> > BCB.B1_PER_ID1 = B.B1_PER_ID1
> > AND BCB.B1_PER_ID2 = B.B1_PER_ID2
> > AND BCB.B1_PER_ID3 = B.B1_PER_ID3
> > AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
> > AND BCB.B1_CHECKBOX_DESC = 'Security Release Date'
> > ) Security_Release_Date,
> > -- B
> > (
> > SELECT B1_CHECKLIST_COMMENT
> > FROM BCHCKBOX BCB
> > WHERE
> > BCB.B1_PER_ID1 = B.B1_PER_ID1
> > AND BCB.B1_PER_ID2 = B.B1_PER_ID2
> > AND BCB.B1_PER_ID3 = B.B1_PER_ID3
> > AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
> > AND BCB.B1_CHECKBOX_DESC = '10 Month Inspection Date'
> > ) Month_Inspection
> >
> > FROM B1PERMIT B
> > INNER JOIN B3PARCEL BP ON
> > --LEFT OUTER JOIN B3PARCEL BP ON
> > BP.B1_PER_ID1 = B.B1_PER_ID1
> > AND BP.B1_PER_ID2 = B.B1_PER_ID2
> > AND BP.B1_PER_ID3 = B.B1_PER_ID3
> > AND BP.SERV_PROV_CODE = B.SERV_PROV_CODE
> > LEFT OUTER JOIN GPROCESS_HISTORY G ON
> > /*WHERE*/ B.SERV_PROV_CODE = 'RENO'
> > AND B.B1_PER_ID1 = G.B1_PER_ID1
> > AND B.B1_PER_ID2 = G.B1_PER_ID2
> > AND B.B1_PER_ID3 = G.B1_PER_ID3
> > AND B.SERV_PROV_CODE = G.SERV_PROV_CODE
> > AND UPPER(G.SD_PRO_DES) NOT LIKE '%CLOSED%'
> > AND UPPER(B.B1_APPL_STATUS) NOT LIKE '%CLOSED%'
> > AND G.SD_PRO_DES = 'Acceptance of Improvements'
> > AND (
> > G.SD_PRO_DES = 'Accepted'
> > OR
> > G.SD_PRO_DES = 'City Council Not Required'
> > )
> > AND EXISTS
> > (
> > SELECT B1_CHECKLIST_COMMENT
> > FROM BCHCKBOX BCB
> > WHERE
> > BCB.B1_PER_ID1 = B.B1_PER_ID1
> > AND BCB.B1_PER_ID2 = B.B1_PER_ID2
> > AND BCB.B1_PER_ID3 = B.B1_PER_ID3
> > AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
> > AND BCB.B1_CHECKBOX_DESC = 'Agreement Expiration'
> > AND B1_CHECKLIST_COMMENT != NULL
> > )
> > AND EXISTS
> > (
> > SELECT B1_CHECKLIST_COMMENT
> > FROM BCHCKBOX BCB
> > WHERE
> > BCB.B1_PER_ID1 = B.B1_PER_ID1
> > AND BCB.B1_PER_ID2 = B.B1_PER_ID2
> > AND BCB.B1_PER_ID3 = B.B1_PER_ID3
> > AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
> > AND BCB.B1_CHECKBOX_DESC = 'Final Map Name'
> > AND B1_CHECKLIST_COMMENT != NULL
> > )
> > WHERE
> > EXISTS
> > -- Security Release date check.
> > (
> > SELECT B1_CHECKLIST_COMMENT
> > FROM BCHCKBOX BCB
> > WHERE
> > --BCB.B1_CHECKBOX_DESC = 'Security Release Date'
> > BCB.B1_PER_ID1 = B.B1_PER_ID1
> > AND BCB.B1_PER_ID2 = B.B1_PER_ID2
> > AND BCB.B1_PER_ID3 = B.B1_PER_ID3
> > AND BCB.SERV_PROV_CODE = B.SERV_PROV_CODE
> > AND BCB.B1_CHECKBOX_DESC = '10 Month Inspection Date'
> > AND TO_DATE(BCB.B1_CHECKLIST_COMMENT, 'MM/DD/YYYY') >= {?FROM_DATE}
> > AND TO_DATE(BCB.B1_CHECKLIST_COMMENT, 'MM/DD/YYYY') <= {?TO_DATE}
> > )
> > AND UPPER(B.B1_APPL_STATUS) NOT LIKE '%CLOSED%'
> >
> >
> >
> > --
> > Regards,
> > Pat

 |  Next  |  Last
Pages: 1 2
Prev: Query question
Next: SSIS - sql server integrated services - like OLAP?