Home » SQL & PL/SQL » SQL & PL/SQL » want to put range on output (oracle 10g, report 10g)
want to put range on output [message #354628] Mon, 20 October 2008 10:43 Go to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
dear all,

i want to put one range on my output. please help me on this.

following is my output---


SELECT distinct pol_prod_code,count(*)"count", sum(prai_prem_lc_1)"premium"

FROM PGIT_POLICY, PGIT_POL_RISK_ADDL_INFO
where

PGIT_POL_RISK_ADDL_INFO.PRAI_SI_LC_1 <2000

and PGIT_POLICY.POL_SYS_ID=PGIT_POL_RISK_ADDL_INFO.PRAI_POL_SYS_ID
AND PGIT_POLICY.POL_FM_DT=PGIT_POL_RISK_ADDL_INFO.PRAI_EFF_FM_DT
AND PGIT_POLICY.POL_TO_DT=PGIT_POL_RISK_ADDL_INFO.PRAI_EFF_TO_DT
AND PGIT_POLICY.POL_BUS_TYPE IN ('1','2') AND NVL(PGIT_POLICY.POL_REN_REC,'0') = 0
AND PGIT_POLICY.POL_DIVN_CODE IN ('45','20','44','31','42','10','43','40','41','30')
AND PGIT_POLICY.POL_APPR_STS <> 'E' and (PGIT_POLICY.POL_DS_TYPE='2')
and (PGIT_POLICY.POL_CATG='01')
group by pol_prod_code
union
SELECT distinct pol_prod_code,count(*)"count", sum(prai_prem_lc_1)"premium"


FROM PGIT_POLICY, PGIT_POL_RISK_ADDL_INFO
where

PGIT_POL_RISK_ADDL_INFO.PRAI_SI_LC_1 between 2000 and 4000

and PGIT_POLICY.POL_SYS_ID=PGIT_POL_RISK_ADDL_INFO.PRAI_POL_SYS_ID
AND PGIT_POLICY.POL_FM_DT=PGIT_POL_RISK_ADDL_INFO.PRAI_EFF_FM_DT
AND PGIT_POLICY.POL_TO_DT=PGIT_POL_RISK_ADDL_INFO.PRAI_EFF_TO_DT
AND PGIT_POLICY.POL_BUS_TYPE IN ('1','2') AND NVL(PGIT_POLICY.POL_REN_REC,'0') = 0
AND PGIT_POLICY.POL_DIVN_CODE IN ('45','20','44','31','42','10','43','40','41','30')
AND PGIT_POLICY.POL_APPR_STS <> 'E' and (PGIT_POLICY.POL_DS_TYPE='2')
and (PGIT_POLICY.POL_CATG='01')
group by pol_prod_code

---------------------------------- out put -------------

POL_PROD_CODE count premium

1001 266 12000.172
1001 678 83618.23
1002 1223 70733.561
1102 2 118.1
---------------------------- Required Output -----------------

POL_PROD_CODE count premium range

1001 266 12000.172 0-2000
1001 678 83618.23 2000-4000
1002 1223 70733.561 0-2000
1102 2 118.1 2000-4000
-------------------------------------------------------

please suggest me how will i do?

Re: want to put range on output [message #354633 is a reply to message #354628] Mon, 20 October 2008 10:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You was able to format some of your post in the past, why are you unable to do it for this one?

Regards
Michel
Re: want to put range on output [message #354637 is a reply to message #354628] Mon, 20 October 2008 11:29 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@ishika_20,

What is stopping you to put a hard-coded value(the range you require) in both of your select statement and the corresponding group by statement? http://img2.mysmiley.net/imgs/smile/confused/confused0006.gif

Regards,
Jo
Re: want to put range on output [message #354720 is a reply to message #354637] Tue, 21 October 2008 00:22 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
dear,

I cannot put it hard coded, i want to show the range in output what i am putting on where condition. Can you help me on this?

Re: want to put range on output [message #354734 is a reply to message #354628] Tue, 21 October 2008 01:12 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Where are you getting the range from ? If it is the input hard code and use in select query.

Thumbs Up
Rajuvan.
Re: want to put range on output [message #354745 is a reply to message #354734] Tue, 21 October 2008 01:34 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear Raju,

The range is provided in where condtion. i need to show that range.


POL_PROD_CODE count premium Range

1001 266 12000.172 0-2000
1001 678 83618.23 2000-4000
1002 1223 70733.561 0-2000
1102 2 118.1 2000-4000


i am not able to show this data...
three columns coming perfect. only i need to show those records with its range.

please provide me some help.


thanks
Re: want to put range on output [message #354746 is a reply to message #354628] Tue, 21 October 2008 01:39 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

If you can Hard code PGIT_POL_RISK_ADDL_INFO.PRAI_SI_LC_1 in your query , what prevent you from hard coding the range in the select query like

select col1, col2 , col3 , '0-2000'
from table1
where col4 < 2000
UNION
select col1, col2 , col3 , '2000-4000'
from table1
where col4 between 2000 and 4000


Thumbs Up
Rajuvan.

[Updated on: Tue, 21 October 2008 06:35]

Report message to a moderator

Re: want to put range on output [message #354747 is a reply to message #354746] Tue, 21 October 2008 01:44 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Thanks raju..

you solved my problem.

thanks once again
Re: want to put range on output [message #354760 is a reply to message #354747] Tue, 21 October 2008 02:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you explain to us how this solution is different to the 'hard-coding' that you stated wasn't allowable as a solution?
Re: want to put range on output [message #355617 is a reply to message #354760] Sun, 26 October 2008 09:15 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member

Dear All,

My output is coming in ascending order.

------------------ output ------------------------------


'0-500000' count premium

0 - 500000 164 18183.662
1000000 - 2000000 24 13676.76
15000000 - 20000000 1 9832.511
2000000 - 3000000 12 9046.529
3000000 - 4000000 4 6226.205
4000000 - 5000000 4 7698.487
500000 - 1000000 26 13281.766
Above 25000000 2 149321.4

-----------------

---------- reqd. output ---------------

'0-500000' count premium

0 - 500000 164 18183.662
500000 - 1000000 26 13281.766
1000000 - 2000000 24 13676.76
2000000 - 3000000 12 9046.529
3000000 - 4000000 4 6226.205
4000000 - 5000000 4 7698.487
15000000 - 20000000 1 9832.511
Above 25000000 2 149321.4


-----------------------------------------------

Query is same as above...
now i need the same reqd. output.

can you please help me on this?

thanks


Re: want to put range on output [message #355630 is a reply to message #355617] Sun, 26 October 2008 12:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 20 October 2008 17:54
You was able to format some of your post in the past, why are you unable to do it for this one?

Regards
Michel


Re: want to put range on output [message #355639 is a reply to message #355617] Sun, 26 October 2008 13:58 Go to previous message
Barbara Boehmer
Messages: 8632
Registered: November 2002
Location: California, USA
Senior Member
The problem is that you are either not using an ORDER BY clause at all or you are ordering by the range as character values where anything starting with 1 comes before anything starting with 5, so 10 would come before 5. You need to ORDER BY one of the numeric values, either the starting or ending range. Notice the differences in the two queries below.

-- ordered by range as characters:
SCOTT@orcl_11g> WITH your_data AS
  2    (SELECT '0 - 5' AS range FROM DUAL
  3  	UNION ALL
  4  	SELECT '5 - 10' AS range FROM DUAL
  5  	UNION ALL
  6  	SELECT '10 - 20' AS range FROM DUAL)
  7  SELECT range
  8  FROM   your_data
  9  ORDER  BY range
 10  /

RANGE
-------
0 - 5
10 - 20
5 - 10


-- ordered by the range as numeric values:
SCOTT@orcl_11g> WITH your_data AS
  2    (SELECT 0 AS start_range, 5 AS end_range FROM DUAL
  3  	UNION ALL
  4  	SELECT 5 AS start_range, 10 AS end_range FROM DUAL
  5  	UNION ALL
  6  	SELECT 10 AS start_range, 20 AS end_range FROM DUAL)
  7  SELECT start_range || ' - ' || end_range AS range
  8  FROM   your_data
  9  ORDER  BY start_range
 10  /

RANGE
--------------------------------------------------------------------------------
0 - 5
5 - 10
10 - 20

SCOTT@orcl_11g> 

Previous Topic: Inline View
Next Topic: Cursor update
Goto Forum:
  


Current Time: Mon Dec 05 20:56:39 CST 2016

Total time taken to generate the page: 0.09317 seconds