Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Pull the string inside the stored procedure definition Expand / Collapse
Author
Message
Posted Monday, May 5, 2014 12:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 21, 2014 1:48 PM
Points: 8, Visits: 91
In the below procedure definition, i need to find a way parse the definition and get the list of places where the where clause is being used.

SELECT DISTINCT
FC.CASE_ID,
UPPER(FC.CASE_NUMBER) AS CASE_NUMBER,
UPPER(REPLACE(FC.CASE_SHORT_TITLE,CHAR(13)+CHAR(10),'')) AS CASE_SHORT_TITLE,
FC.CASE_STATUS_DESCR,
FC.CASE_TYP_DESC, FC.CASE_SUB_TYP_DESC,
UPPER(AJ.DISPLAYNAME) AS JUDGE,
UPPER(PP.DISPLAYNAME) AS PARTY_DISPLAYNAME,
UPPER(AP.ATTORNEY_DISPLAYNAME)AS ATTORNEY_DISPLAYNAME ,
CASE WHEN RC_PARENT.PARENT_CASE_ID IS NOT NULL
THEN 'PARENT'
ELSE
CASE WHEN RC_CHILD.CHILD_CASE_ID IS NOT NULL
THEN 'CHILD'
ELSE NULL
END
END AS CONSOLIDATED,
PP.PARTY_STATUS_TYP_DESC,
LAST_EVENT_DT = CONVERT(VARCHAR(15),(SELECT TOP 1 FILING_DT FROM SW_ACM_V_EVENT_CASE WHERE (CASE_ID=FC.CASE_ID AND FILING_DT < GETDATE())ORDER BY FILING_DT DESC),101),
LAST_EVENT_ENTRY_DESC = UPPER((SELECT TOP 1 EVENT_ENTRY_DESC FROM SW_ACM_V_EVENT_CASE WHERE CASE_ID=FC.CASE_ID and void_event ='N' ORDER BY FILING_DT DESC )),
LAST_SCHEDULED_DT = CONVERT(VARCHAR(15),(SELECT TOP 1 ITEM_START_DT FROM ACC_SESSION_ITEM WHERE (CASE_ID=FC.CASE_ID AND ITEM_START_DT < GETDATE()) ORDER BY ITEM_START_DT DESC),101),
LAST_TICKLER_DT = CONVERT(VARCHAR(15), (SELECT TOP 1 TERMINATING_DATE FROM SW_ACM_V_TICKLERS_NOTSATISFIED WHERE (CASE_ID=FC.CASE_ID AND TERMINATING_DATE < GETDATE()) ORDER BY TERMINATING_DATE DESC),101)

FROM
AZLJ_ACM_V_FACTS_CASE AS FC
LEFT OUTER JOIN AZLJ_ACM_V_JUDGE AJ ON AJ.JUDGE_CD = FC.JUDGE_CD
LEFT OUTER JOIN ACM_RELATED_CASES RC_PARENT ON RC_PARENT.PARENT_CASE_ID=fc.CASE_ID AND RC_PARENT.CASE_RELATED_TYP_ID=(SELECT [VALUE] FROM CFG_ELEMENTS WHERE ELEMENT_NAME='REPORT_CONSOLIDATEDCASES_CASERELATEDTYPID')
LEFT OUTER JOIN ACM_RELATED_CASES RC_CHILD ON RC_CHILD.CHILD_CASE_ID=FC.CASE_ID AND RC_CHILD.CASE_RELATED_TYP_ID=(SELECT [VALUE] FROM CFG_ELEMENTS WHERE ELEMENT_NAME ='REPORT_CONSOLIDATEDCASES_CASERELATEDTYPID')
LEFT OUTER JOIN AZLJ_ACM_V_PERS_PARTY AS PP ON PP.CASE_ID = FC.CASE_ID AND PP.PARTY_ROLE_TYP_ID NOT IN (SELECT PARTY_ROLE_TYP_ID FROM ACM_PARTY_ROLE_TYP WHERE ROLE_FOR_ATT = 'Y')
LEFT OUTER JOIN SW_ACM_V_ATTORNEY_PARTY AS AP ON AP.PARTY_ID = PP.PARTY_ID AND AP.PARTY_ROLE_TYP_CD = 'Y'
LEFT OUTER JOIN SW_ACM_V_TICKLERS_NOTSATISFIED AS TNS ON TNS.CASE_ID = FC.CASE_ID
LEFT OUTER JOIN ACM_TICKLER_TYP_EVENT_TYP_MAP AS TTETM ON TTETM.TICKLER_TYP_ID =TNS.TICKLER_TYP_ID

WHERE
((TNS.SATISFACTION_EVENT_ENTRY_ID IS NULL) AND (TNS.TERMINATING_DATE < GETDATE()) AND (ISNULL(TTETM.NEXT_EVENT_ENTRY_TYP_ID,0) = 0))
AND (FC.NEXT_SESSION_DATE IS NULL OR FC.NEXT_SESSION_DATE < GETDATE() )
AND FC.CASE_ID NOT IN (SELECT CASE_ID FROM ACC_SESSION_ITEM WHERE ITEM_START_DT > GETDATE())
AND (FC.CASE_STATUS_TYP_ID NOT IN (SELECT DATA_VALUE FROM AICMS_REPORT_PARAMETERS WHERE VARIABLE_NAME='REPORT_ACTIONPEND_NOTINCASESTSID'))
AND ((PP.PARTY_STATUS_TYP_ID NOT IN (SELECT DATA_VALUE FROM AICMS_REPORT_PARAMETERS WHERE VARIABLE_NAME='REPORT_INACTIVE_PARTYSTSID'))
OR (PP.PARTY_STATUS_TYP_ID IS NULL))
AND(FC.CASE_ID IN (SELECT DISTINCT CASE_ID FROM SW_ACM_V_TICKLERS_NOTSATISFIED WHERE TERMINATING_DATE < GETDATE()
EXCEPT SELECT DISTINCT CASE_ID FROM SW_ACM_V_TICKLERS_NOTSATISFIED WHERE TERMINATING_DATE > GETDATE()))


In the above query there are more than one places and the where clause may not have the same string the where clause it can be with a space between the "=" and the value in single quotes.



Result set should be in the below format:
TABLE NAME Column Name VALUE
CFG_ELEMENTS ELEMENT_NAME REPORT_CONSOLIDATEDCASES_CASERELATEDTYPID

Post #1567678
Posted Monday, May 5, 2014 12:51 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:43 AM
Points: 271, Visits: 603
Any whitespace (blanks, tabs, line ends, comments /* */) around an "=" sign in a where clause are ignored by SQL. You can have one, ten, one hundred etc., or none at all.
Post #1567681
Posted Monday, May 5, 2014 12:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 21, 2014 1:48 PM
Points: 8, Visits: 91
I'm not sure what your saying here.

Please explain. Thanks,
Post #1567682
Posted Monday, May 5, 2014 1:05 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:43 AM
Points: 271, Visits: 603
I mean that the following WHERE clauses are equivalent:

1. No space
WHERE a=b

2. Some spaces
WHERE    a    =    b   

3. Some lines between
WHERE


a

=

b


Post #1567688
Posted Monday, May 5, 2014 1:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 21, 2014 1:48 PM
Points: 8, Visits: 91
ohh ok, that is good if they are ignored by SQL.

But, how do i get the list of where clauses from the definition.
Post #1567692
Posted Monday, May 5, 2014 1:41 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 8:26 AM
Points: 364, Visits: 2,261
http://stackoverflow.com/questions/6008839/bison-flex-sql-parser

http://stackoverflow.com/questions/8656926/sql-lex-yacc-grammar

edit:
this looks interesting too, although I couldn't discern with my short read if you could use it for your case:

http://social.msdn.microsoft.com/Forums/en-US/559470b8-0d01-415d-9117-d707e708db3d/parse-tree-for-sql-expressions?forum=vstsdb
Post #1567699
Posted Monday, May 5, 2014 2:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 21, 2014 1:48 PM
Points: 8, Visits: 91
I don't think this solves my question.
Post #1567708
Posted Monday, May 5, 2014 2:41 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:11 AM
Points: 13,093, Visits: 11,922
vamsye (5/5/2014)
I don't think this solves my question.


What exactly is the question? It seems like you want a list of filtering predicates? The sql you posted has multiple select statements. Given the sql you posted what is the desired output?


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1567710
Posted Monday, May 5, 2014 2:47 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:11 AM
Points: 13,093, Visits: 11,922
For anybody else wanting to view this query I formatted it.



SELECT DISTINCT FC.CASE_ID
,UPPER(FC.CASE_NUMBER) AS CASE_NUMBER
,UPPER(REPLACE(FC.CASE_SHORT_TITLE, CHAR(13) + CHAR(10), '')) AS CASE_SHORT_TITLE
,FC.CASE_STATUS_DESCR
,FC.CASE_TYP_DESC
,FC.CASE_SUB_TYP_DESC
,UPPER(AJ.DISPLAYNAME) AS JUDGE
,UPPER(PP.DISPLAYNAME) AS PARTY_DISPLAYNAME
,UPPER(AP.ATTORNEY_DISPLAYNAME) AS ATTORNEY_DISPLAYNAME
,CASE
WHEN RC_PARENT.PARENT_CASE_ID IS NOT NULL
THEN 'PARENT'
ELSE CASE
WHEN RC_CHILD.CHILD_CASE_ID IS NOT NULL
THEN 'CHILD'
ELSE NULL
END
END AS CONSOLIDATED
,PP.PARTY_STATUS_TYP_DESC
,LAST_EVENT_DT = CONVERT(VARCHAR(15), (
SELECT TOP 1 FILING_DT
FROM SW_ACM_V_EVENT_CASE
WHERE (
CASE_ID = FC.CASE_ID
AND FILING_DT < GETDATE()
)
ORDER BY FILING_DT DESC
), 101)
,LAST_EVENT_ENTRY_DESC = UPPER((
SELECT TOP 1 EVENT_ENTRY_DESC
FROM SW_ACM_V_EVENT_CASE
WHERE CASE_ID = FC.CASE_ID
AND void_event = 'N'
ORDER BY FILING_DT DESC
))
,LAST_SCHEDULED_DT = CONVERT(VARCHAR(15), (
SELECT TOP 1 ITEM_START_DT
FROM ACC_SESSION_ITEM
WHERE (
CASE_ID = FC.CASE_ID
AND ITEM_START_DT < GETDATE()
)
ORDER BY ITEM_START_DT DESC
), 101)
,LAST_TICKLER_DT = CONVERT(VARCHAR(15), (
SELECT TOP 1 TERMINATING_DATE
FROM SW_ACM_V_TICKLERS_NOTSATISFIED
WHERE (
CASE_ID = FC.CASE_ID
AND TERMINATING_DATE < GETDATE()
)
ORDER BY TERMINATING_DATE DESC
), 101)
FROM AZLJ_ACM_V_FACTS_CASE AS FC
LEFT JOIN AZLJ_ACM_V_JUDGE AJ ON AJ.JUDGE_CD = FC.JUDGE_CD
LEFT JOIN ACM_RELATED_CASES RC_PARENT ON RC_PARENT.PARENT_CASE_ID = fc.CASE_ID
AND RC_PARENT.CASE_RELATED_TYP_ID = (
SELECT [VALUE]
FROM CFG_ELEMENTS
WHERE ELEMENT_NAME = 'REPORT_CONSOLIDATEDCASES_CASERELATEDTYPID'
)
LEFT JOIN ACM_RELATED_CASES RC_CHILD ON RC_CHILD.CHILD_CASE_ID = FC.CASE_ID
AND RC_CHILD.CASE_RELATED_TYP_ID = (
SELECT [VALUE]
FROM CFG_ELEMENTS
WHERE ELEMENT_NAME = 'REPORT_CONSOLIDATEDCASES_CASERELATEDTYPID'
)
LEFT JOIN AZLJ_ACM_V_PERS_PARTY AS PP ON PP.CASE_ID = FC.CASE_ID
AND PP.PARTY_ROLE_TYP_ID NOT IN (
SELECT PARTY_ROLE_TYP_ID
FROM ACM_PARTY_ROLE_TYP
WHERE ROLE_FOR_ATT = 'Y'
)
LEFT JOIN SW_ACM_V_ATTORNEY_PARTY AS AP ON AP.PARTY_ID = PP.PARTY_ID
AND AP.PARTY_ROLE_TYP_CD = 'Y'
LEFT JOIN SW_ACM_V_TICKLERS_NOTSATISFIED AS TNS ON TNS.CASE_ID = FC.CASE_ID
LEFT JOIN ACM_TICKLER_TYP_EVENT_TYP_MAP AS TTETM ON TTETM.TICKLER_TYP_ID = TNS.TICKLER_TYP_ID
WHERE (
(TNS.SATISFACTION_EVENT_ENTRY_ID IS NULL)
AND (TNS.TERMINATING_DATE < GETDATE())
AND (ISNULL(TTETM.NEXT_EVENT_ENTRY_TYP_ID, 0) = 0)
)
AND (
FC.NEXT_SESSION_DATE IS NULL
OR FC.NEXT_SESSION_DATE < GETDATE()
)
AND FC.CASE_ID NOT IN (
SELECT CASE_ID
FROM ACC_SESSION_ITEM
WHERE ITEM_START_DT > GETDATE()
)
AND (
FC.CASE_STATUS_TYP_ID NOT IN (
SELECT DATA_VALUE
FROM AICMS_REPORT_PARAMETERS
WHERE VARIABLE_NAME = 'REPORT_ACTIONPEND_NOTINCASESTSID'
)
)
AND (
(
PP.PARTY_STATUS_TYP_ID NOT IN (
SELECT DATA_VALUE
FROM AICMS_REPORT_PARAMETERS
WHERE VARIABLE_NAME = 'REPORT_INACTIVE_PARTYSTSID'
)
)
OR (PP.PARTY_STATUS_TYP_ID IS NULL)
)
AND (
FC.CASE_ID IN (
SELECT DISTINCT CASE_ID
FROM SW_ACM_V_TICKLERS_NOTSATISFIED
WHERE TERMINATING_DATE < GETDATE()

EXCEPT

SELECT DISTINCT CASE_ID
FROM SW_ACM_V_TICKLERS_NOTSATISFIED
WHERE TERMINATING_DATE > GETDATE()
)
)



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1567712
Posted Tuesday, May 6, 2014 9:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 21, 2014 1:48 PM
Points: 8, Visits: 91
TABLE NAME Column Name VALUE
CFG_ELEMENTS ELEMENT_NAME REPORT_CONSOLIDATEDCASES_CASERELATEDTYPID
Post #1568029
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse