OraFAQ Forum: SQL & PL/SQL » Sum on Varchar2 field in Oracle Report Builder

Home » SQL & PL/SQL » SQL & PL/SQL » Sum on Varchar2 field in Oracle Report Builder

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Sum on Varchar2 field in Oracle Report Builder [message #7604]

Tue, 24 June 2003 15:13

dmc
Messages: 3
Registered: June 2003

Junior Member

The Tables I am using are in Oracle Human Resources Appications. The hr_all_positions_f table has an budgeted salary amount stored in the Attribute4 field. How do I SUM these values in a report using Oracle report builder? The field is defind as a Varchar2.

Thank You.

Report message to a moderator

Re: Sum on Varchar2 field in Oracle Report Builder [message #7606 is a reply to message #7604]

Tue, 24 June 2003 15:33

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

_{quote from dmc:}
----------------------------------------------------------------------

The Tables I am using are in Oracle Human Resources Appications. The hr_all_positions_f table has an budgeted salary amount stored in the Attribute4 field. How do I SUM these values in a report using Oracle report builder? The field is defind as a Varchar2.

Thank You.

----------------------------------------------------------------------
Hopefully, the descriptive flexfield is set up to contain only NUMBERs. If that's the case, then you should be able to say

SELECT   haou.name                        organization_name
,        SUM(TO_NUMBER(hapf.attribute4))  budgeted_salary
FROM     hr.hr_all_positions_f            hapf
,        hr.hr_all_organization_units     haou
WHERE    hapf.organization_id = haou.organization_id
AND      TRUNC(SYSDATE) BETWEEN haou.date_from
                            AND NVL(haou.date_to
                                ,   TO_DATE('47121231'
                                    ,       'YYYYMMDD'))
GROUP BY haou.name
ORDER BY haou.name

Good luck,

Art

Report message to a moderator

Previous Topic:	Transposition SQL
Next Topic:	query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 03:39:27 CDT 2024