view.sql

Delphi6开发的HIS程序

create or replace view v_medicinestock as
select medicinestock.medicinestockid ms_medicinestockid,
       medicinestock.created         ms_created,
       medicinestock.updated         ms_updated,
       medicinestock.amount          ms_amount,
       medicinestock.minamount       ms_minamount,
       medicinestock.medicineid      ms_medicineid,
       medicine.medicineid           m_medicineid,
       medicine.code                 m_code,
       medicine.topqty               m_topqty,
       medicine.downqty              m_downqty,
       medicine.memo                 m_memo,
       medicine.alais                m_alais,
       medicine.model                m_model,
       medicine.pinyin               m_pinyin,
       medicine.caption              m_caption,
       medicine.doseid               m_doseid,
       medicine.unitsid              m_unitsid,
       medicine.usemethodid          m_usemethodid,
       medicine.medicinecatalogid    m_medicinecatalogid,
       medicine.minunitsid           m_minunitsid,
       medicine.packamount           m_packamount
from   medicinestock, medicine
where  medicinestock.medicineid = medicine.medicineid;

create or replace view v_medicinestockdetail as
select medicinestockdetail.medicinestockdetailid,
       medicinestockdetail.created,
       medicinestockdetail.updated,
       medicinestockdetail.medicinestockid,
       medicinestockdetail.siomedicinedetailid,
       medicinestockdetail.amount,
       siomedicinedetail.sprice,
       medicinestockdetail.amount * medicine.packamount minamount,
       siomedicinedetail.sprice / medicine.packamount minsprice,
       medicinestock.medicineid,
from   medicinestockdetail, medicinestock, medicine, siomedicinedetail
where  medicinestockdetail.medicinestockid = medicinestock.medicinestockid and
       medicinestock.medicineid = medicine.medicineid and
       medicinestockdetail.siomedicinedetailid =
       siomedicinedetail.siomedicinedetailid;

create or replace view v_soomedicinedetail as
select soomedicinedetail         .*,
       medicine.medicineid        m_medicineid,
       medicine.code              m_code,
       medicine.topqty            m_topqty,
       medicine.downqty           m_downqty,
       medicine.memo              m_memo,
       medicine.alais             m_alais,
       medicine.model             m_model,
       medicine.pinyin            m_pinyin,
       medicine.caption           m_caption,
       medicine.doseid            m_doseid,
       medicine.unitsid           m_unitsid,
       medicine.usemethodid       m_usemethodid,
       medicine.medicinecatalogid m_medicinecatalogid,
       medicine.minunitsid        m_minunitsid,
       medicine.packamount        m_packamount,
       units.caption              u_caption,
       medicinecatalog.caption    mc_cpation
from   soomedicinedetail, medicine, units, medicinecatalog
where  soomedicinedetail.medicineid = medicine.medicineid and
       medicine.unitsid = units.unitsid and
       medicine.medicinecatalogid = medicinecatalog.medicinecatalogid;

create or replace view v_scomedicine as
select scomedicine                .*,
       medicinestockdetail.amount  msd_amount,
       medicinestockdetail.created msd_created,
       siomedicinedetail.price     siomd_price,
       siomedicinedetail.sprice    siomd_sprice,
       medicine.medicineid         m_medicineid,
       medicine.code               m_code,
       medicine.topqty             m_topqty,
       medicine.downqty            m_downqty,
       medicine.memo               m_memo,
       medicine.alais              m_alais,
       medicine.model              m_model,
       medicine.pinyin             m_pinyin,
       medicine.caption            m_caption,
       medicine.doseid             m_doseid,
       medicine.unitsid            m_unitsid,
       medicine.usemethodid        m_usemethodid,
       medicine.medicinecatalogid  m_medicinecatalogid,
       medicine.minunitsid         m_minunitsid,
       medicine.packamount         m_packamount
from   scomedicine, medicinestockdetail, siomedicinedetail, medicinestock, medicine
where  scomedicine.medicinestockdetailid = medicinestockdetail.medicinestockdetailid and
       medicinestockdetail.siomedicinedetailid = siomedicinedetail.siomedicinedetailid and
       medicinestockdetail.medicinestockid = medicinestock.medicinestockid and
       medicinestock.medicineid = medicine.medicineid;

create or replace view v_medicinestockdetail2 as
select medicinestockdetail.*,
       medicine.medicineid         m_medicineid,
       medicine.code               m_code,
       medicine.topqty             m_topqty,
       medicine.downqty            m_downqty,
       medicine.memo               m_memo,
       medicine.alais              m_alais,
       medicine.model              m_model,
       medicine.pinyin             m_pinyin,
       medicine.caption            m_caption,
       medicine.doseid             m_doseid,
       medicine.unitsid            m_unitsid,
       medicine.usemethodid        m_usemethodid,
       medicine.medicinecatalogid  m_medicinecatalogid,
       medicine.minunitsid         m_minunitsid,
       medicine.packamount         m_packamount,
       siomedicinedetail.price     siomd_price,
       siomedicinedetail.sprice    siomd_sprice
from   medicinestockdetail, medicinestock, medicine, siomedicinedetail
where  medicinestockdetail.medicinestockid = medicinestock.medicinestockid and
       medicinestock.medicineid = medicine.medicineid and
       medicinestockdetail.siomedicinedetailid =  siomedicinedetail.siomedicinedetailid;