Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL
WITH e AS
(
    Select* from enrollment 
    Where effective_end_dt IS NULL
),
h AS
(
    Select* from member_status_history
    Where effective_end_dt IS NULL
)
Select Distinct 
    (m.ssn),
    d.department_cd,
    m.last_nm,
    e.plan_id,
    p.plan_cd,
    S.enrollmemt_status_desc
From 
    member m,
    e,
    h,
    PLAN p,
    enrollment_status_s,
    department d
Where 
    m.member_id =e.member_id
AND 
    m.member_id = h.member_id
AND 
    e.plan_id = p.plan_id
AND 
    e.enrollment_status_id =s.enrollment_status_id
AND 
    s.enrollment_status_id='510'
AND 
    m.department_id=d.department_id
AND 
    h.member_status='530'
AND 
    e.plan_id IN (307,308,309)
AND 
    NOT EXISTS
    (
        SELECT t.member_id
        From transaction_register t
        Where m.member_id = t.member_id
        AND t.plan_id = e.plan_id
        AND t.update_dt >= TO_DATE('01/11/2016 00:00:00', 'mm/DD/yyyy hh24:mi:ss')
        AND t.update_dt <= TO_DATE('01/12/2016 23:59:59', 'mm/DD/yyyy hh24:mi:ss')
        AND t.transaction_type_cd='PAYDE'
        AND t.update_user_id='PAYPGM'
    )
Order By 
    e.plan_id, 
    ssn


What I have tried:

I have created equivalent stored procedure but it seems wrong can anyone help me out, what will be the equivalent stored procedure for above query.
SQL
CREATE OR REPLACE Procedure SP_GET_UNMATCHED_MEMBER
BEGIN
    WITH e AS
    (
        Select* from enrollment 
        Where effective_end_dt IS NULL
    ),
    h AS
    (
        Select* from member_status_history
        Where effective_end_dt IS NULL
    )
    Select Distinct 
        (m.ssn),
        d.department_cd,
        m.last_nm,
        e.plan_id,
        p.plan_cd,
        S.enrollmemt_status_desc
    From 
        member m,
        e,
        h,
        PLAN p,
        enrollment_status_s,
        department d
    Where
        m.member_id =e.member_id
    AND
        m.member_id = h.member_id
    AND
        e.plan_id = p.plan_id
    AND
        e.enrollment_status_id =s.enrollment_status_id
    AND
        s.enrollment_status_id='510'
    AND
        m.department_id=d.department_id
    AND
        h.member_status='530'
    AND
        e.plan_id IN (307,308,309)
    AND
        NOT EXISTS
        (
            SELECT t.member_id
            From transaction_register t
            Where m.member_id = t.member_id
            AND t.plan_id = e.plan_id
            AND t.update_dt >= TO_DATE('01/11/2016 00:00:00', 'mm/DD/yyyy hh24:mi:ss')
            AND t.update_dt <= TO_DATE('01/12/2016 23:59:59', 'mm/DD/yyyy hh24:mi:ss')
            AND t.transaction_type_cd='PAYDE'
            AND t.update_user_id='PAYPGM'
        )
    Order By 
        e.plan_id, 
        ssn
END;
Posted
Updated 1-Jul-20 0:08am
v4
Comments
Garth J Lancaster 1-Jul-20 3:09am    
"I have created equivalent stored procedure but it seems wrong" - why don't you use 'Improve question' to update with your attempt at a stored procedure, and, outline what the issue(s) are with it - that way we can help you fix your own code (we dont write code for people anyway, so unless you post your own code all you'll likely get back is a list of google links on stored procedures)
Richard Deeming 1-Jul-20 6:11am    
You should be using proper INNER JOINs in you query. Other than that, there's nothing obviously wrong with your stored procedure. You need to provide the details of the error you're getting.
Mukesh Pr@sad 1-Jul-20 7:13am    
The query is working fine and I am getting the required data, only issue is I am not able to write this query in the form of Stored procedure.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900