Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    134

    Red face Unanswered: How can I schedule this as a job

    I have a series of SQLStatement file that need to be run daily at 3:00 AM. Part of the file is posted as under. When I run this in TOAD it works fine but when I run this file in SQLPlus as SQLPlus>@c:\test.sql it craps out with many errors.
    Can someone help why I can not run it in SQLPlus and also how to schedule the whole file to run against "an instance"?

    Part of the file:

    DROP TABLE JCodeCorrection;
    DROP FUNCTION computealtqty;
    drop index JCODEDOE_IDX;
    DROP VIEW JCodeCorrection_View;
    DROP VIEW JCodeCorrectionSummary_View;
    DROP VIEW JCodeReversedCharges_view;
    DROP VIEW JCodeAutoCreditCorrection_view;
    CREATE OR REPLACE FUNCTION COMPUTEALTQTY (V_Qty in number, V_UnitsPerDose in number, V_RoundAlt in varchar2,
    V_DispSize in number, V_ChargedSize in number,V_AltFactor in number)
    return number deterministic
    is
    V_AltUnitsPerDose number;
    V_AltQty number(10,0);
    begin
    if V_RoundAlt = 'Y' then
    V_AltUnitsPerDose := Ceil(V_UnitsPerDose);
    else
    V_AltUnitsPerDose := V_UnitsPerDose * (V_DispSize/V_ChargedSize);
    end if;

    V_AltQty := (V_Qty/Ceil(V_UnitsPerDose)) * Ceil(V_AltFactor * V_AltUnitsPerDose);
    RETURN V_AltQty;
    end;
    CREATE TABLE JCodeCorrection("FACTOR" VARCHAR2(20), "FACILITYCODE"
    VARCHAR2(14), "NAME" VARCHAR2(30), "HOSPITALNUMBER" VARCHAR2(20),
    "SERVICETYPE" VARCHAR2(5), "DESCRIPTION" VARCHAR2(50),
    "RXNUMBER" NUMBER, "ITEMTYPE" CHAR(1), "SUFFIX" NUMBER,
    "THERAPYDOE" DATE, "DATEOFENTRY" DATE, "DATEOFCHARGE" DATE,
    "CREDITDATEOFCHARGE" DATE, "REASONTEXT" VARCHAR2(30), "USERCODE"
    VARCHAR2(30), "ITEMNUMBER" VARCHAR2(12), "BASEITEMNUMBER"
    VARCHAR2(12), "ITEMID" NUMBER, "CHARGEDSIZE" NUMBER(17, 5),
    "CORRECT_QUANTITY" NUMBER, "CHGED_QUANTITY" NUMBER, "DIFF_QTY"
    NUMBER, "CORRECT_CHG" NUMBER(12, 4), "CHGED_CHG" NUMBER(12, 4),
    "DIFF_CHG" NUMBER(12, 4), "UNITSPERDOSE" NUMBER(11, 5),
    "ALTERNATECONVERSIONFACTOR" NUMBER(10, 4), "ROUNDALTERNATEUP"
    CHAR(1), "DISPENSESIZE" NUMBER(17, 5), "CHARGESIZE" NUMBER(17,
    5), "BASEQUANTITY" NUMBER, "DOSEQUANTITY" NUMBER(10, 4),
    "BASECHARGE" NUMBER(20, 10));
    GRANT SELECT,INSERT,DELETE ON JCodeCorrection TO MEDICSUSER;
    CREATE INDEX JCODEDOE_IDX ON JCodeCorrection (DateOfEntry ASC);
    CREATE VIEW JCODECORRECTION_VIEW AS SELECT * FROM JCODECORRECTION;
    INSERT INTO JCODECORRECTION (FACTOR, FACILITYCODE, NAME, HOSPITALNUMBER, SERVICETYPE, DESCRIPTION,
    RXNUMBER, ITEMTYPE, SUFFIX, THERAPYDOE, DATEOFENTRY, DATEOFCHARGE,
    CREDITDATEOFCHARGE, REASONTEXT, USERCODE, ITEMNUMBER, BASEITEMNUMBER,
    ITEMID, CHARGEDSIZE, CORRECT_QUANTITY, CHGED_QUANTITY, DIFF_QTY,
    CORRECT_CHG, CHGED_CHG, DIFF_CHG, UNITSPERDOSE, ALTERNATECONVERSIONFACTOR,
    ROUNDALTERNATEUP, DISPENSESIZE, CHARGESIZE, BASEQUANTITY, DOSEQUANTITY,
    BASECHARGE)
    select 'Factor from Inv!!!' AS factor,
    TherapyCharge.FacilityCode, masterpatient.Name, PatientVisit.HospitalNumber,
    therapycharge.servicetype, therapyitem.Description, Itemcharge.rxnumber,
    itemcharge.itemtype, itemcharge.suffix, visittherapy.dateofentry AS TherapyDOE,
    therapycharge.dateofentry, itemcharge.dateofcharge, itemcharge.creditdateofcharge,
    reasontext, itemcharge.usercode, itemcharge.itemnumber, Itemcharge.baseitemnumber,
    therapyitem.itemid, Itemcharge.ChargedSize,
    hcs.computealtqty(basequantity, unitsperdose, itemdispensesize.roundalternateup, therapyitem.dispensesize, computed_chargesize, itemdispensesize.alternateconversionfactor) as Correct_Quantity,
    quantity as Chged_Quantity,
    quantity - hcs.computealtqty(basequantity, unitsperdose, itemdispensesize.roundalternateup, therapyitem.dispensesize,computed_chargesize, itemdispensesize.alternateconversionfactor) AS Diff_Qty,
    decode(BaseCharge,null,0,basecharge)*basequantity as Correct_Chg,
    decode(itemcharge.Charge ,null,0,itemcharge.charge) as Chged_Chg,
    decode(itemcharge.Charge ,null,0,itemcharge.charge) - decode(BaseCharge,null,0,basecharge)*basequantity AS Diff_Chg,
    UnitsPerDose,
    itemdispensesize.alternateconversionfactor,
    itemdispensesize.roundalternateup,
    therapyitem.dispensesize,therapyitem.computed_char gesize AS chargesize,
    basequantity,itemcharge.dosequantity,basecharge
    from itemcharge, therapycharge, therapyitem, visittherapy, patientvisit, masterpatient, itemdispensesize
    where itemcharge.itemnumber<>itemcharge.baseitemnumber
    and quantity <> hcs.computealtqty(basequantity, unitsperdose, itemdispensesize.roundalternateup, therapyitem.dispensesize, computed_chargesize, itemdispensesize.alternateconversionfactor)
    and itemcharge.rxnumber = therapyitem.rxnumber
    and itemcharge.itemtype = therapyitem.itemtype
    and itemcharge.suffix = therapyitem.suffix
    and therapycharge.rxnumber = itemcharge.rxnumber
    and therapycharge.dateofcharge = itemcharge.dateofcharge
    AND visittherapy.rxnumber = itemcharge.rxnumber
    AND patientvisit.masterpatientid = visittherapy.masterpatientid AND patientvisit.visitnumber = visittherapy.visitnumber
    AND masterpatient.masterpatientid = patientvisit.masterpatientid
    AND itemdispensesize.itemid = therapyitem.itemid
    AND itemdispensesize.servicetype = therapyitem.computed_servicetype
    AND itemdispensesize.therapytype = therapyitem.computed_therapytype
    AND itemdispensesize.dispensesize = therapyitem.computed_chargesize
    AND NOT (therapycharge.reasontext = 'AUTOMATIC CREDIT' AND itemcharge.dateofcharge = itemcharge.creditdateofcharge)
    AND TherapyCharge.DateofEntry >= to_date('03-01-2004 00:00:00', 'mm-dd-yy hh24:mi:ss')
    AND TherapyCharge.DateOfEntry < to_date('04-01-2004 00:00:00', 'mm-dd-yy hh24:mi:ss');
    /

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >How can I schedule this as a job
    Fix what is causing the errors & the use the "AT" command to schedule it.
    Those who live by the GUI will die by the GUI!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Why would you rebuild the table every day????? Use one of the following solutions

    Create a view ONCE

    create view JCodeCorrection as
    select 'Factor from Inv!!!' AS factor,
    TherapyCharge.FacilityCode, masterpatient.Name, PatientVisit.HospitalNumber,
    therapycharge.servicetype, therapyitem.Description, Itemcharge.rxnumber,
    itemcharge.itemtype, itemcharge.suffix, visittherapy.dateofentry AS TherapyDOE,
    therapycharge.dateofentry, itemcharge.dateofcharge, itemcharge.creditdateofcharge,
    reasontext, itemcharge.usercode, itemcharge.itemnumber, Itemcharge.baseitemnumber,
    therapyitem.itemid, Itemcharge.ChargedSize,
    hcs.computealtqty(basequantity, unitsperdose, itemdispensesize.roundalternateup, therapyitem.dispensesize, computed_chargesize, itemdispensesize.alternateconversionfactor) as Correct_Quantity,
    quantity as Chged_Quantity,
    quantity - hcs.computealtqty(basequantity, unitsperdose, itemdispensesize.roundalternateup, therapyitem.dispensesize,computed_chargesize, itemdispensesize.alternateconversionfactor) AS Diff_Qty,
    decode(BaseCharge,null,0,basecharge)*basequantity as Correct_Chg,
    decode(itemcharge.Charge ,null,0,itemcharge.charge) as Chged_Chg,
    decode(itemcharge.Charge ,null,0,itemcharge.charge) - decode(BaseCharge,null,0,basecharge)*basequantity AS Diff_Chg,
    UnitsPerDose,
    itemdispensesize.alternateconversionfactor,
    itemdispensesize.roundalternateup,
    therapyitem.dispensesize,therapyitem.computed_char gesize AS chargesize,
    basequantity,itemcharge.dosequantity,basecharge
    from itemcharge, therapycharge, therapyitem, visittherapy, patientvisit, masterpatient, itemdispensesize
    where itemcharge.itemnumber<>itemcharge.baseitemnumber
    and quantity <> hcs.computealtqty(basequantity, unitsperdose, itemdispensesize.roundalternateup, therapyitem.dispensesize, computed_chargesize, itemdispensesize.alternateconversionfactor)
    and itemcharge.rxnumber = therapyitem.rxnumber
    and itemcharge.itemtype = therapyitem.itemtype
    and itemcharge.suffix = therapyitem.suffix
    and therapycharge.rxnumber = itemcharge.rxnumber
    and therapycharge.dateofcharge = itemcharge.dateofcharge
    AND visittherapy.rxnumber = itemcharge.rxnumber
    AND patientvisit.masterpatientid = visittherapy.masterpatientid AND patientvisit.visitnumber = visittherapy.visitnumber
    AND masterpatient.masterpatientid = patientvisit.masterpatientid
    AND itemdispensesize.itemid = therapyitem.itemid
    AND itemdispensesize.servicetype = therapyitem.computed_servicetype
    AND itemdispensesize.therapytype = therapyitem.computed_therapytype
    AND itemdispensesize.dispensesize = therapyitem.computed_chargesize
    AND NOT (therapycharge.reasontext = 'AUTOMATIC CREDIT' AND itemcharge.dateofcharge = itemcharge.creditdateofcharge)
    AND TherapyCharge.DateofEntry >= to_date('03-01-2004 00:00:00', 'mm-dd-yy hh24:mi:ss')
    AND TherapyCharge.DateOfEntry < to_date('04-01-2004 00:00:00', 'mm-dd-yy hh24:mi:ss');

    Or truncate and reinsert the data.

    truncate table JCodeCorrection;

    insert into JCodeCorrection
    select 'Factor from Inv!!!' AS factor,
    TherapyCharge.FacilityCode, masterpatient.Name, PatientVisit.HospitalNumber,
    therapycharge.servicetype, therapyitem.Description, Itemcharge.rxnumber,
    itemcharge.itemtype, itemcharge.suffix, visittherapy.dateofentry AS TherapyDOE,
    therapycharge.dateofentry, itemcharge.dateofcharge, itemcharge.creditdateofcharge,
    reasontext, itemcharge.usercode, itemcharge.itemnumber, Itemcharge.baseitemnumber,
    therapyitem.itemid, Itemcharge.ChargedSize,
    hcs.computealtqty(basequantity, unitsperdose, itemdispensesize.roundalternateup, therapyitem.dispensesize, computed_chargesize, itemdispensesize.alternateconversionfactor) as Correct_Quantity,
    quantity as Chged_Quantity,
    quantity - hcs.computealtqty(basequantity, unitsperdose, itemdispensesize.roundalternateup, therapyitem.dispensesize,computed_chargesize, itemdispensesize.alternateconversionfactor) AS Diff_Qty,
    decode(BaseCharge,null,0,basecharge)*basequantity as Correct_Chg,
    decode(itemcharge.Charge ,null,0,itemcharge.charge) as Chged_Chg,
    decode(itemcharge.Charge ,null,0,itemcharge.charge) - decode(BaseCharge,null,0,basecharge)*basequantity AS Diff_Chg,
    UnitsPerDose,
    itemdispensesize.alternateconversionfactor,
    itemdispensesize.roundalternateup,
    therapyitem.dispensesize,therapyitem.computed_char gesize AS chargesize,
    basequantity,itemcharge.dosequantity,basecharge
    from itemcharge, therapycharge, therapyitem, visittherapy, patientvisit, masterpatient, itemdispensesize
    where itemcharge.itemnumber<>itemcharge.baseitemnumber
    and quantity <> hcs.computealtqty(basequantity, unitsperdose, itemdispensesize.roundalternateup, therapyitem.dispensesize, computed_chargesize, itemdispensesize.alternateconversionfactor)
    and itemcharge.rxnumber = therapyitem.rxnumber
    and itemcharge.itemtype = therapyitem.itemtype
    and itemcharge.suffix = therapyitem.suffix
    and therapycharge.rxnumber = itemcharge.rxnumber
    and therapycharge.dateofcharge = itemcharge.dateofcharge
    AND visittherapy.rxnumber = itemcharge.rxnumber
    AND patientvisit.masterpatientid = visittherapy.masterpatientid AND patientvisit.visitnumber = visittherapy.visitnumber
    AND masterpatient.masterpatientid = patientvisit.masterpatientid
    AND itemdispensesize.itemid = therapyitem.itemid
    AND itemdispensesize.servicetype = therapyitem.computed_servicetype
    AND itemdispensesize.therapytype = therapyitem.computed_therapytype
    AND itemdispensesize.dispensesize = therapyitem.computed_chargesize
    AND NOT (therapycharge.reasontext = 'AUTOMATIC CREDIT' AND itemcharge.dateofcharge = itemcharge.creditdateofcharge)
    AND TherapyCharge.DateofEntry >= to_date('03-01-2004 00:00:00', 'mm-dd-yy hh24:mi:ss')
    AND TherapyCharge.DateOfEntry < to_date('04-01-2004 00:00:00', 'mm-dd-yy hh24:mi:ss');


    To drop and rebuild the same table every day is just plain Silly! And the same goes for your Function, what reason do you have to drop and recreate it?
    Last edited by beilstwh; 12-16-05 at 17:35.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •