Home » SQL & PL/SQL » SQL & PL/SQL » union (10g, windows)
union [message #433945] |
Sat, 05 December 2009 08:24 |
ishika_20
Messages: 339 Registered: December 2006 Location: delhi
|
Senior Member |
|
|
hello all,
below is my query
SELECT polh_no,praih_risk_id "Risk ID", polh_fm_dt "policy From Dt", polh_to_dt "Policy to dt",
polh_end_no "Endorsement No", polh_end_dt "Endorsement Dt", polh_end_eff_fm_dt "Endorsemnt Eff fm date", polh_end_eff_to_dt "Endorsemnt Eff to date",
polh_acnt_doc_dt "Appr Dt", polh_assr_name "Insured Name", PRAIH_SI_LC_1 "Sum-Insured",
PRAIH_PREM_LC_1 "Premium", praih_code_03 "Vehicle type" , VT.PC_DESC,
praih_code_04 "Vehicle Code",MVM.PC_DESC " Vehicle Desc", polh_period , praih_data_03 "Registration No.",
praih_data_01 "Chasis No.", praih_num_01 "Manufacturing Year"
FROM FM_COMPANY,PGITH_POLICY,
pgith_pol_risk_addl_info,
(select PC_CODE, pc_desc from pcom_codes where PC_TYPE = 'MOT_VEH_MAKE' ) MVM,
(select PC_CODE, pc_desc from pcom_codes where PC_TYPE = 'VEHI_TYPE' ) VT
WHERE POLH_COMP_CODE = '001'
and PGITh_POLICY.POLh_SYS_ID = PGITh_POL_RISK_ADDL_INFO.PRAIh_POL_SYS_ID
AND PGITh_POLICY.POLh_END_NO_IDX = PGITh_POL_RISK_ADDL_INFO.PRAIh_END_NO_IDX
AND PGITh_POLICY.POLh_END_SR_NO = PGITh_POL_RISK_ADDL_INFO.PRAIh_END_SR_NO
and pgith_pol_risk_addl_info.PRAIH_CODE_04=MVM.pc_code
and pgith_pol_risk_addl_info.PRAIH_CODE_03=VT.pc_code
AND NVL(POLH_PROD_CODE,'0') in ('1001')
--AND NVL(POLH_PROD_CODE,'0') in ('1001', '1002')
--AND POLH_DIVN_CODE = '30'
--AND POLH_APPR_STS = 'C'
and polh_no='P-1001-31001-09-01680'
--and praih_sys_id='73408'
--and praih_rec_type='I'
--and praih_code_03='002'
AND TRUNC(POLH_ACNT_DOC_DT) BETWEEN TO_DATE('01/09/2009', 'DD/MM/RRRR') AND TO_DATE('30/11/2009', 'DD/MM/RRRR')
--AND abs(PRAIH_SI_LC_1) >=3000
--AND abs(PRAIH_SI_LC_1) >=30000
POLH_NO Risk ID policy From Dt Policy to dt Endorsement No Endorsement Dt Endorsemnt Eff fm date Endorsemnt Eff to date Appr Dt Insured Name Sum-Insured Premium Vehicle type PC_DESC Vehicle Code Vehicle Desc POLH_PERIOD Registration No. Chasis No. Manufacturing Year
P-1001-31001-09-01680 1 10/04/2009 12:02:20 10/16/2009 00:00:00 E-102-1001-31001-09-509 10/16/2009 00:00:00 10/16/2009 00:00:00 10/03/2010 23:59:00 10/15/2009 00:00:00 Mr. ID. 0256495 0.00 -1,230.31 001 Private Purpose Only 060027 Ferrari Saloon. 12 TBA ZFFJA54T580159080 2,008.00
P-1001-31001-09-01680 1 10/04/2009 12:02:20 10/03/2010 23:59:00 10/04/2009 00:00:00 Mr. ID. 0256495 65,000.00 1,279.52 001 Private Purpose Only 060027 Ferrari Saloon. 364 TBA ZFFJA54T580159080 2,008.00
SELECT polh_no,praih_risk_id "Risk ID", polh_fm_dt "policy From Dt", polh_to_dt "Policy to dt",
polh_end_no "Endorsement No", polh_end_dt "Endorsement Dt", polh_end_eff_fm_dt "Endorsemnt Eff fm date", polh_end_eff_to_dt "Endorsemnt Eff to date",
polh_acnt_doc_dt "Appr Dt", polh_assr_name "Insured Name", PRAIH_SI_LC_1 "Sum-Insured",
PRAIH_PREM_LC_1 "Premium", praih_code_03 "Vehicle type" , VT.PC_DESC,
praih_code_04 "Vehicle Code",MVM.PC_DESC " Vehicle Desc", polh_period , praih_data_03 "Registration No.",
praih_data_01 "Chasis No.", praih_num_01 "Manufacturing Year"
FROM FM_COMPANY,PGITH_POLICY,
pgith_pol_risk_addl_info,
(select PC_CODE, pc_desc from pcom_codes where PC_TYPE = 'MOT_VEH_MAKE' ) MVM,
(select PC_CODE, pc_desc from pcom_codes where PC_TYPE = 'VEHI_TYPE' ) VT
WHERE POLH_COMP_CODE = '001'
and PGITh_POLICY.POLh_SYS_ID = PGITh_POL_RISK_ADDL_INFO.PRAIh_POL_SYS_ID
AND PGITh_POLICY.POLh_END_NO_IDX = PGITh_POL_RISK_ADDL_INFO.PRAIh_END_NO_IDX
AND PGITh_POLICY.POLh_END_SR_NO = PGITh_POL_RISK_ADDL_INFO.PRAIh_END_SR_NO
and pgith_pol_risk_addl_info.PRAIH_CODE_04=MVM.pc_code
and pgith_pol_risk_addl_info.PRAIH_CODE_03=VT.pc_code
AND NVL(POLH_PROD_CODE,'0') in ('1001')
--AND NVL(POLH_PROD_CODE,'0') in ('1001', '1002')
--AND POLH_DIVN_CODE = '30'
--AND POLH_APPR_STS = 'C'
and polh_no='P-1001-31001-09-01680'
--and praih_sys_id='73408'
--and praih_rec_type='I'
--and praih_code_03='002'
AND TRUNC(POLH_ACNT_DOC_DT) BETWEEN TO_DATE('01/09/2009', 'DD/MM/RRRR') AND TO_DATE('30/11/2009', 'DD/MM/RRRR')
AND abs(PRAIH_SI_LC_1) >=3000
POLH_NO Risk ID policy From Dt Policy to dt Endorsement No Endorsement Dt Endorsemnt Eff fm date Endorsemnt Eff to date Appr Dt Insured Name Sum-Insured Premium Vehicle type PC_DESC Vehicle Code Vehicle Desc POLH_PERIOD Registration No. Chasis No. Manufacturing Year
P-1001-31001-09-01680 1 10/04/2009 12:02:20 10/03/2010 23:59:00 10/04/2009 00:00:00 Mr. ID. 0256495 65,000.00 1,279.52 001 Private Purpose Only 060027 Ferrari Saloon. 364 TBA ZFFJA54T580159080 2,008.00
i want the output as below
POLH_NO Risk ID policy From Dt Policy to dt Endorsement No Endorsement Dt Endorsemnt Eff fm date Endorsemnt Eff to date Appr Dt Insured Name Sum-Insured Premium Vehicle type PC_DESC Vehicle Code Vehicle Desc POLH_PERIOD Registration No. Chasis No. Manufacturing Year
P-1001-31001-09-01680 1 10/04/2009 12:02:20 10/16/2009 00:00:00 E-102-1001-31001-09-509 10/16/2009 00:00:00 10/16/2009 00:00:00 10/03/2010 23:59:00 10/15/2009 00:00:00 Mr. ID. 0256495 0.00 -1,230.31 001 Private Purpose Only 060027 Ferrari Saloon. 12 TBA ZFFJA54T580159080 2,008.00
P-1001-31001-09-01680 1 10/04/2009 12:02:20 10/03/2010 23:59:00 10/04/2009 00:00:00 Mr. ID. 0256495 65,000.00 1,279.52 001 Private Purpose Only 060027 Ferrari Saloon. 364 TBA ZFFJA54T580159080 2,008.00
|
|
|
Re: union [message #433946 is a reply to message #433945] |
Sat, 05 December 2009 08:30 |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
ishika_20 wrote on Sat, 05 December 2009 15:24i want the output as below
Is there any specific reason you want a completely unformatted, unreadable output that will give headaches to anyone trying to understand it, or are you just plain cruel?
|
|
|
Re: union [message #433947 is a reply to message #433946] |
Sat, 05 December 2009 09:30 |
ishika_20
Messages: 339 Registered: December 2006 Location: delhi
|
Senior Member |
|
|
Can you please provide me the tips for the format so that i can post it nicely once again? Thanks.
|
|
|
|
Re: union [message #434007 is a reply to message #433948] |
Sun, 06 December 2009 10:20 |
ishika_20
Messages: 339 Registered: December 2006 Location: delhi
|
Senior Member |
|
|
hello all,
below is my query
SELECT polh_no,praih_risk_id "Risk ID", polh_fm_dt "policy From Dt", polh_to_dt "Policy to dt",
polh_end_no "Endorsement No", polh_end_dt "Endorsement Dt", polh_end_eff_fm_dt "Endorsemnt Eff fm date", polh_end_eff_to_dt "Endorsemnt Eff to date",
polh_acnt_doc_dt "Appr Dt", polh_assr_name "Insured Name", PRAIH_SI_LC_1 "Sum-Insured",
PRAIH_PREM_LC_1 "Premium", praih_code_03 "Vehicle type" , VT.PC_DESC,
praih_code_04 "Vehicle Code",MVM.PC_DESC " Vehicle Desc", polh_period , praih_data_03 "Registration No.",
praih_data_01 "Chasis No.", praih_num_01 "Manufacturing Year"
FROM FM_COMPANY,PGITH_POLICY,
pgith_pol_risk_addl_info,
(select PC_CODE, pc_desc from pcom_codes where PC_TYPE = 'MOT_VEH_MAKE' ) MVM,
(select PC_CODE, pc_desc from pcom_codes where PC_TYPE = 'VEHI_TYPE' ) VT
WHERE POLH_COMP_CODE = '001'
and PGITh_POLICY.POLh_SYS_ID = PGITh_POL_RISK_ADDL_INFO.PRAIh_POL_SYS_ID
AND PGITh_POLICY.POLh_END_NO_IDX = PGITh_POL_RISK_ADDL_INFO.PRAIh_END_NO_IDX
AND PGITh_POLICY.POLh_END_SR_NO = PGITh_POL_RISK_ADDL_INFO.PRAIh_END_SR_NO
and pgith_pol_risk_addl_info.PRAIH_CODE_04=MVM.pc_code
and pgith_pol_risk_addl_info.PRAIH_CODE_03=VT.pc_code
AND NVL(POLH_PROD_CODE,'0') in ('1001')
--AND NVL(POLH_PROD_CODE,'0') in ('1001', '1002')
--AND POLH_DIVN_CODE = '30'
--AND POLH_APPR_STS = 'C'
and polh_no='P-1001-31001-09-01680'
--and praih_sys_id='73408'
--and praih_rec_type='I'
--and praih_code_03='002'
AND TRUNC(POLH_ACNT_DOC_DT) BETWEEN TO_DATE('01/09/2009', 'DD/MM/RRRR') AND TO_DATE('30/11/2009', 'DD/MM/RRRR')
--AND abs(PRAIH_SI_LC_1) >=3000
--AND abs(PRAIH
POLH_NO Risk ID policy From Dt Policy to dt Endorsement No Endorsement Dt Endorsemnt Eff fm date Endorsemnt Eff to date Appr Dt Insured Name Sum-Insured Premium Vehicle type PC_DESC Vehicle Code Vehicle Desc POLH_PERIOD Registration No. Chasis No. Manufacturing Year
P-1001-31001-09-01680 1 10/04/2009 12:02:20 10/16/2009 00:00:00 E-102-1001-31001-09-509 10/16/2009 00:00:00 10/16/2009 00:00:00 10/03/2010 23:59:00 10/15/2009 00:00:00 Mr. ID. 0256495 0.00 -1,230.31 001 Private Purpose Only 060027 Ferrari Saloon. 12 TBA ZFFJA54T580159080 2,008.00
P-1001-31001-09-01680 1 10/04/2009 12:02:20 10/03/2010 23:59:00 10/04/2009 00:00:00 Mr. ID. 0256495 65,000.00 1,279.52 001 Private Purpose Only 060027 Ferrari Saloon. 364 TBA ZFFJA54T580159080 2,008.00
SELECT polh_no,praih_risk_id "Risk ID", polh_fm_dt "policy From Dt", polh_to_dt "Policy to dt",
polh_end_no "Endorsement No", polh_end_dt "Endorsement Dt", polh_end_eff_fm_dt "Endorsemnt Eff fm date", polh_end_eff_to_dt "Endorsemnt Eff to date",
polh_acnt_doc_dt "Appr Dt", polh_assr_name "Insured Name", PRAIH_SI_LC_1 "Sum-Insured",
PRAIH_PREM_LC_1 "Premium", praih_code_03 "Vehicle type" , VT.PC_DESC,
praih_code_04 "Vehicle Code",MVM.PC_DESC " Vehicle Desc", polh_period , praih_data_03 "Registration No.",
praih_data_01 "Chasis No.", praih_num_01 "Manufacturing Year"
FROM FM_COMPANY,PGITH_POLICY,
pgith_pol_risk_addl_info,
(select PC_CODE, pc_desc from pcom_codes where PC_TYPE = 'MOT_VEH_MAKE' ) MVM,
(select PC_CODE, pc_desc from pcom_codes where PC_TYPE = 'VEHI_TYPE' ) VT
WHERE POLH_COMP_CODE = '001'
and PGITh_POLICY.POLh_SYS_ID = PGITh_POL_RISK_ADDL_INFO.PRAIh_POL_SYS_ID
AND PGITh_POLICY.POLh_END_NO_IDX = PGITh_POL_RISK_ADDL_INFO.PRAIh_END_NO_IDX
AND PGITh_POLICY.POLh_END_SR_NO = PGITh_POL_RISK_ADDL_INFO.PRAIh_END_SR_NO
and pgith_pol_risk_addl_info.PRAIH_CODE_04=MVM.pc_code
and pgith_pol_risk_addl_info.PRAIH_CODE_03=VT.pc_code
AND NVL(POLH_PROD_CODE,'0') in ('1001')
--AND NVL(POLH_PROD_CODE,'0') in ('1001', '1002')
--AND POLH_DIVN_CODE = '30'
--AND POLH_APPR_STS = 'C'
and polh_no='P-1001-31001-09-01680'
--and praih_sys_id='73408'
--and praih_rec_type='I'
--and praih_code_03='002'
AND TRUNC(POLH_ACNT_DOC_DT) BETWEEN TO_DATE('01/09/2009', 'DD/MM/RRRR') AND TO_DATE('30/11/2009', 'DD/MM/RRRR')
AND abs(PRAIH_SI_LC_1) >=3000
[/code]
POLH_NO Risk ID policy From Dt Policy to dt Endorsement No Endorsement Dt Endorsemnt Eff fm date Endorsemnt Eff to date Appr Dt Insured Name Sum-Insured Premium Vehicle type PC_DESC Vehicle Code Vehicle Desc POLH_PERIOD Registration No. Chasis No. Manufacturing Year
P-1001-31001-09-01680 1 10/04/2009 12:02:20 10/03/2010 23:59:00 10/04/2009 00:00:00 Mr. ID. 0256495 65,000.00 1,279.52 001 Private Purpose Only 060027 Ferrari Saloon. 364 TBA ZFFJA54T580159080 2,008.00
i want the output as below
POLH_NO Risk ID policy From Dt Policy to dt Endorsement No Endorsement Dt Endorsemnt Eff fm date Endorsemnt Eff to date Appr Dt Insured Name Sum-Insured Premium Vehicle type PC_DESC Vehicle Code Vehicle Desc POLH_PERIOD Registration No. Chasis No. Manufacturing Year
P-1001-31001-09-01680 1 10/04/2009 12:02:20 10/16/2009 00:00:00 E-102-1001-31001-09-509 10/16/2009 00:00:00 10/16/2009 00:00:00 10/03/2010 23:59:00 10/15/2009 00:00:00 Mr. ID. 0256495 0.00 -1,230.31 001 Private Purpose Only 060027 Ferrari Saloon. 12 TBA ZFFJA54T580159080 2,008.00
P-1001-31001-09-01680 1 10/04/2009 12:02:20 10/03/2010 23:59:00 10/04/2009 00:00:00 Mr. ID. 0256495 65,000.00 1,279.52 001 Private Purpose Only 060027 Ferrari Saloon. 364 TBA ZFFJA54T580159080 2,008.00
how do i get this. Don't use union.
|
|
|
|
Re: union [message #434011 is a reply to message #433945] |
Sun, 06 December 2009 10:26 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
SELECT polh_no,
praih_risk_id "Risk ID",
polh_fm_dt "policy From Dt",
polh_to_dt "Policy to dt",
polh_end_no "Endorsement No",
polh_end_dt "Endorsement Dt",
polh_end_eff_fm_dt "Endorsemnt Eff fm date",
polh_end_eff_to_dt "Endorsemnt Eff to date",
polh_acnt_doc_dt "Appr Dt",
polh_assr_name "Insured Name",
praih_si_lc_1 "Sum-Insured",
praih_prem_lc_1 "Premium",
praih_code_03 "Vehicle type",
vt.pc_desc,
praih_code_04 "Vehicle Code",
mvm.pc_desc " Vehicle Desc",
polh_period,
praih_data_03 "Registration No.",
praih_data_01 "Chasis No.",
praih_num_01 "Manufacturing Year"
FROM fm_company,
pgith_policy,
pgith_pol_risk_addl_info,
(SELECT pc_code,
pc_desc
FROM pcom_codes
WHERE pc_type = 'MOT_VEH_MAKE') mvm,
(SELECT pc_code,
pc_desc
FROM pcom_codes
WHERE pc_type = 'VEHI_TYPE') vt
WHERE polh_comp_code = '001'
AND pgith_policy.polh_sys_id = pgith_pol_risk_addl_info.praih_pol_sys_id
AND pgith_policy.polh_end_no_idx = pgith_pol_risk_addl_info.praih_end_no_idx
AND pgith_policy.polh_end_sr_no = pgith_pol_risk_addl_info.praih_end_sr_no
AND pgith_pol_risk_addl_info.praih_code_04 = mvm.pc_code
AND pgith_pol_risk_addl_info.praih_code_03 = vt.pc_code
AND Nvl(polh_prod_code,'0') IN ('1001')
--AND NVL(POLH_PROD_CODE,'0') in ('1001', '1002')
--AND POLH_DIVN_CODE = '30'
--AND POLH_APPR_STS = 'C'
AND polh_no = 'P-1001-31001-09-01680'
--and praih_sys_id='73408'
--and praih_rec_type='I'
--and praih_code_03='002'
AND Trunc(polh_acnt_doc_dt) BETWEEN To_date('01/09/2009','DD/MM/RRRR') AND To_date('30/11/2009','DD/MM/RRRR')
|
|
|
Goto Forum:
Current Time: Wed Nov 13 00:26:56 CST 2024
|