조회해온 데이터로 Insert 하기. INSERT INTO - SELECT
 Database / SQL2011/01/24 10:18 |
INSERT INTO cr_meal_dtl_eval
(
meal_dtl_eval_num, meal_eval_dt, meal_cl_cd, tot_cal, tot_carbo_qty, tot_prot_qty, tot_fat_qty, tot_choles_qty, tot_na_qty
, carbo_intk_rate, prot_intk_rate, fat_intk_rate
, rcmnd_cmpr_cal_rate, rcmnd_cmpr_carbo_rate, rcmnd_cmpr_prot_rate, rcmnd_cmpr_fat_rate, rcmnd_cmpr_choles_rate, rcmnd_cmpr_na_rate
, ptnt_mbr_num, cre_dtm, cre_mbr_num
)
(
SELECT cr_meal_dtl_eval_s.NEXTVAL, '20110121', '1', a.*, b.*, c.*, 1, SYSDATE, 1
FROM (
SELECT SUM(ROUND(cal * 0.7, 0)) totCal /* 총열량 */
, SUM(ROUND(carbo_qty * 0.7, 0)) totCarboQty /* 총당질량 */
, SUM(ROUND(prot_qty * 0.7, 0)) totProtQty /* 총단백질량 */
, SUM(ROUND(fat_qty * 0.7, 0)) totFatQty /* 총지방량 */
, SUM(ROUND(choles_qty * 0.7, 0)) totCholesQty /* 총콜레스테롤량 */
, SUM(ROUND(na_qty * 0.7, 0)) totNaQty /* 총나트륨량 */
FROM cr_fd /* 음식 테이블 */
WHERE fd_cd IN ('A016','A066','A129','A257','A204')
) a
, (
SELECT ROUND((62 * 4) / ((62 * 4) + (27 * 4) + (12 * 9)) * 100, 1) carboIntkRate /* 당질섭취비율 */
, ROUND((27 * 4) / ((62 * 4) + (27 * 4) + (12 * 9)) * 100, 1) protIntkRate /* 단백질섭취비율 */
, ROUND((12 * 9) / ((62 * 4) + (27 * 4) + (12 * 9)) * 100, 1) fatIntkRate /* 지방섭취비율 */
FROM dual
) b
, (
SELECT ROUND(458 / 1500 * 100, 1) rcmndCmprCalRate
, ROUND((62 * 400) / (1500 * 0.55), 1) rcmndCmprCarboRate
, ROUND((27 * 400) / (1500 * 0.2), 1) rcmndCmprProtRate
, ROUND((12 * 900) / (1500 * 0.25), 1) rcmndCmprFatRate
, ROUND(33 / 300 * 100, 1) rcmndCmprCholesRate
, ROUND(1932 / 2400 * 100, 1) rcmndCmprNaRate
FROM dual
) c
)
