Re: Creating a Stored Procedure

Humm, homework here right?

"Gigi Lipori" <pflugg_at_bellsouth.net> wrote in message news:3d8f62eb$1_1_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 - 21:16:55 CEST