Home » SQL & PL/SQL » SQL & PL/SQL » union (10g, windows)
union [message #433945] Sat, 05 December 2009 08:24 Go to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
ishika_20 wrote on Sat, 05 December 2009 15:24
i 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 Go to previous messageGo to next message
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 #433948 is a reply to message #433947] Sat, 05 December 2009 09:38 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
PLEASE read section the "How to format your post?" in URL below:
http://www.orafaq.com/forum/t/88153/0/
Re: union [message #434007 is a reply to message #433948] Sun, 06 December 2009 10:20 Go to previous messageGo to next message
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 #434009 is a reply to message #434007] Sun, 06 December 2009 10:22 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ThomasG wrote on Sat, 05 December 2009 15:30
ishika_20 wrote on Sat, 05 December 2009 15:24
i 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?

Nothing more to add.

Regards
Michel
Re: union [message #434011 is a reply to message #433945] Sun, 06 December 2009 10:26 Go to previous message
BlackSwan
Messages: 24912
Registered: January 2009
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') 
Previous Topic: finding the missing sequences in a column
Next Topic: Record display problem
Goto Forum:
  


Current Time: Thu Sep 29 07:20:26 CDT 2016

Total time taken to generate the page: 0.07198 seconds