STORED PROCEDURE
Date: Mon, 23 Sep 2002 16:29:26 -0400
Message-ID: <3d8f7895_2_at_corp-news.newsgroups.com>
I have the SQL listed below. I want to turn it into a stored procedure where I pass it ENCOUNTERS.PAT_ID and ENCOUNTERS.START_DATE and then get back the appropriate recordset (should only be one record). How do I do that? I am admittedly an oracle newbie..
SELECT
TO_CHAR(ENCOUNTERS.START_DATE, 'yyyy-mm-dd') AS ENC_DATE,
ENCOUNTERS.PAT_ID AS PAT_ID,
TO_CHAR(MAX(PATIENT_LABS.DATE_OF_LAB), 'yyyy-mm-dd') AS DATE_OF_LAB,
MAX(PATIENT_LABS.WBC) AS WBC,
MAX(PATIENT_LABS.RBC) AS RBC,
MAX(PATIENT_LABS.HGB) AS HGB,
MAX(PATIENT_LABS.HCT) AS HCT,
MAX(PATIENT_LABS.PT) AS PT,
MAX(PATIENT_LABS.INR) AS INR,
MAX(PATIENT_LABS.PLATELETS) AS PLATELETS,
MAX(PATIENT_LABS.NA) AS NA,
MAX(PATIENT_LABS.K) AS K,
MAX(PATIENT_LABS.CL) AS CL,
MAX(PATIENT_LABS.CO2) AS CO2,
MAX(PATIENT_LABS.BUN) AS BUN,
MAX(PATIENT_LABS.CREAT) AS CREAT,
MAX(PATIENT_LABS.GLUCOSE) AS GLUCOSE,
MAX(PATIENT_LABS.ALB) AS ALB,
MAX(PATIENT_LABS.BILT) AS BILT,
MAX(PATIENT_LABS.BILD) AS BILD,
MAX (PATIENT_LABS.ALK_PHOS) AS ALK_PHOS,
MAX(PATIENT_LABS.LDH) AS LDH,
MAX(PATIENT_LABS.AST) AS AST,
MAX(PATIENT_LABS.ALT) AS ALT,
MAX(PATIENT_LABS.AFP) AS AFP,
MAX(PATIENT_LABS.CALCIUM) AS CALCIUM,
MAX(PATIENT_LABS.PHOSPHORUS) AS PHOSPHORUS,
MAX(PATIENT_LABS.MG) AS MG,
MAX(PATIENT_LABS.TOTAL_PROTEIN) AS TOTAL_PROTEIN,
MAX(PATIENT_LABS.GAMMA_GLOBULIN) AS GAMMA_GLOBULIN,
MAX(PATIENT_LABS.URIC_ACID) AS URIC_ACID,
MAX(PATIENT_LABS.PTT) AS PTT,
MAX(PATIENT_LABS.CHO) AS CHO,
MAX(PATIENT_LABS.BP_DIASTOLIC) AS BP_DIASTOLIC,
MAX(PATIENT_LABS.BP_SYSTOLIC) AS BP_SYSTOLIC,
MAX(PATIENT_LABS.HEART_RATE) AS HEART_RATE,
MAX(PATIENT_LABS.WEIGHT) AS WEIGHT,
MAX(PATIENT_LABS.TEMPERATURE ) AS TEMPERATURE
FROM
PATIENT_LABS,
ENCOUNTERS,
ENCOUNTER_LABS
WHERE
PATIENT_LABS.PAT_ID = ENCOUNTERS.PAT_ID
AND ENCOUNTER_LABS.ENCOUNTER_ID (+) = ENCOUNTERS.ENCOUNTER_ID
AND PATIENT_LABS.PAT_ID = 1568
AND (TO_CHAR(PATIENT_LABS.DATE_OF_LAB, 'yyyy-mm-dd') =
TO_CHAR(ENCOUNTERS.START_DATE,'yyyy-mm-dd')
OR PATIENT_LABS.DATE_OF_LAB = ENCOUNTER_LABS.DATE_OF_LAB)
GROUP BY
ENCOUNTERS.START_DATE,
ENCOUNTERS.PAT_ID.
Received on Mon Sep 23 2002 - 22:29:26 CEST