OraFAQ Forum: Reports & Discoverer

Home » Developer & Programmer » Reports & Discoverer » formula column

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

formula column [message #131479]

Sun, 07 August 2005 02:30

sudhir.sukumar
Messages: 52
Registered: August 2005
Location: India

Member

hi,

using oracle reports (6i).

i have 2 queries based on 2 tables.

i want to create a formula column (i know that) that calculates values from 2 columns, each in different table / query.

how to accomplish that?

thnx, su.

Report message to a moderator

Re: formula column [message #131557 is a reply to message #131479]

Mon, 08 August 2005 04:45

weekend79
Messages: 198
Registered: April 2005
Location: Islamabad

Senior Member

Dear sudhir sukumar

I also require the same, but I can’t find solution.
So I develop the following technique. It is not the proper way but it work very well you can adopt it till you find proper solution.

Technique:-
Press F3 create a separate “queer node” as follows

Select (a) + (b) from dual;

Replace “a” with any query and “b” with other query.

i.e. select (select col_cash_sale from Tbl_Market_sale) + (select col_credit_Sale from Tbl_Office_Sale) from dual;

if not clear please feel free to ask again.

Wishes
Jawad

Report message to a moderator

Re: formula column [message #131591 is a reply to message #131557]

Mon, 08 August 2005 08:41

sudhir.sukumar
Messages: 52
Registered: August 2005
Location: India

Member

thx for the reply jawad
but the prob remains as it is.

for both queries users will pass arguments, so the fetch varies.

for instance:

select inventory_item_id, segment1, description, sum(qty) qty
from j.inventoryqty where trunc(creation_date) <=:p_as_on_date
group by inventory_item_id, segment1, description

and

select distinct inventory_item_id, max(nvl(new_cost,0)) new_cost
from mtl_cst_actual_cost_details
where organization_id =12
and trunc(creation_date) <= :p_as_on_date
group by inventory_item_id

i want to do qty * new_cost for every repeating row.

su.

Report message to a moderator

Re: formula column [message #131599 is a reply to message #131557]

Mon, 08 August 2005 09:39

Shaila.Mehra
Messages: 88
Registered: July 2005
Location: mumbai

Member

Jawad i encountered the message-

ORA-01427: single-row subquery returns more than one row
while writing this query-
select (select rec_id from emp_tbl_det) + select (select sale_id from sales_tbl) from dual;

So plz clarify this..to me.

Thanks in advace
Shaila.

Report message to a moderator

Re: formula column [message #131708 is a reply to message #131591]

Tue, 09 August 2005 01:54

weekend79
Messages: 198
Registered: April 2005
Location: Islamabad

Senior Member

sudhir.sukumar

Well your situation is complex.
What I understand is that you are dealing with stock and want one or both of following.
1. Sale price at the time of sale.
2. Stock price lying with you according the increase prices.

In both cases why to travel complex path. If you like easy subways then I have solution for you.
For case (1) add a column in your sale table which store the value “qty * new_cost”.

For case (2) add 2 columns (if you not have already added) in Item_Details table Col_Item_Balance & Col_Item_Price. Keep both columns update with your stock balance and price and when ever you want result you can generate simple “Col_Item_Balance * Col_Item_Price” report.

If I fail to analyze your problem, you are welcome to ask again.

But one more thing I am binger like you. My solution will solve problems but don’t consider my solutions as master advice.

Wishes
Jawad

Report message to a moderator

Re: formula column [message #131710 is a reply to message #131599]

Tue, 09 August 2005 01:55

weekend79
Messages: 198
Registered: April 2005
Location: Islamabad

Senior Member

Shaila

You can only “+, -, *, /” single values with query:-
Select (a) + (b) from dual;

Make sure that query (a) will return a single value and query (b) will also return a single value.

Alter your query as follow:-
Select (select Sum(rec_id) from emp_tbl_det) + select (select Sum(sale_id) from sales_tbl) from dual;

Wishes
Jawad

Report message to a moderator

Re: formula column [message #131732 is a reply to message #131710]

Tue, 09 August 2005 04:07

karan.shandilya
Messages: 64
Registered: July 2005
Location: mumbai

Member

Got it, Jawa.
Thanx.

Jaw, can you tell me tht how we convert salary column in figure.

EXAMPLE-
select salary from table_name;
it gives o/p in the word form.

like if figure is 3,50,054 then it results -
THREE LAKHS FIFITY THOUSAND AND FIFITY FOUR.
in crores,lakhs,thounsand FORMAT..

AND if we do like this
select to_char(to_date('1254','j'),'jsp');
it works BUT not for ,

if we select whole column
select to_char(to_date('salary','j'),'jsp');
it gives error..

I have to use this in report.

Waiting Jawad..

Shaila.

Report message to a moderator

Re: formula column [message #131902 is a reply to message #131732]

Wed, 10 August 2005 00:31

weekend79
Messages: 198
Registered: April 2005
Location: Islamabad

Senior Member

Shaila

Well you teach me new functionality. I don’t know this conversion before.

Your solution is like ever before:-
Add a Column “Col_ Alphabetic_Salary” in the table contain salary column and with the same code which you teach me:-
select to_char(to_date('1254','j'),'jsp') from dual;

Store the alphabetic salary in column “Col_ Alphabetic_Salary” through form triggers.
Lastly in report display “Col_ Alphabetic_Salary” when ever you need.

Wishes
Jawad

Report message to a moderator

Re: formula column [message #131913 is a reply to message #131902]

Wed, 10 August 2005 01:12

sudhir.sukumar
Messages: 52
Registered: August 2005
Location: India

Member

hi shaila
try this out.

SQL>SELECT TRANSLATE('12,00,000','~' ||
TRANSLATE('12,000','~0123456789','~'),'~') from dual

TRANSLATE
---------
1200000

regards, sudhir.

Report message to a moderator

Previous Topic:	Migrating report 6i and 9i to 10G Pleeeeaaase help it is urgent
Next Topic:	Displying Path in Report

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jun 26 00:52:01 CDT 2024