Re: Creating a Stored Procedure

Nope. A real-life deal. I know SQL, but I just don't know oracle.

"Kevin Gillins" <k.gillins_at_verizon.net> wrote in message news:amnpsa$ofg$1_at_nntp-m01.news.aol.com...
> 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 - 22:25:51 CEST