about formula

[qwertyu1]

Hello all

I am using crystal xi and oracle 9.

I have a sql like this:

select sum(r.ACRES_AMT)
FROM fr_recon r, fr_timber_type tt
where r.PRIM_TIMBER_TYPE = tt.TIMBER_TYPE
and tt.FOREST_FLAG = 'F'
and nvl(instr(r.STAND_PREFIX_CODE,'R'),0) = 0
and nvl(instr(r.STAND_PREFIX_CODE,'Y'),0) = 0
and nvl(instr(r.STAND_PREFIX_CODE,'Z'),0) = 0

can i create a formula field using above sql?

 

[synapsevampire]

A formula field to do what?

You can use the above as the datasource for a report, if that's what you mean.

Just below your Oracle connection you'll see Add Command, paste the SQL in there.

You give no indication of what you're trying to accomplish, try posting meaningful technical information:

Example data
Expected output

-k

 

[qwertyu1]

Actually my sql is like this

select r.PRIM_TIMBER_TYPE ptt,
tt.TIMBER_TEXT ptx,
count(r.RECON_ID),
sum(r.ACRES_AMT) Acres,

TO_CHAR(sum(r.ACRES_AMT)/TO_NUMBERP53_RECON_ACRES,'999999999')*100,'990.99') P,

TO_CHAR(sum(r.ACRES_AMT)/TO_NUMBERP53_FORESTED_ACRES,'999999999')*100,'990.99') P2

from fr_recon r, fr_timber_type tt

where r.FR_PROP_CODE = 0_PROPERTY
and
r.PRIM_TIMBER_TYPE = tt.TIMBER_TYPE
and
tt.FOREST_FLAG = 'F'

group by r.PRIM_TIMBER_TYPE, tt.TIMBER_TEXT




note:
sql for :53_RECON_ACRES

(select sum(r.ACRES_AMT)
from fr_recon r
where r.FR_PROP_CODE = 0_PROPERTY)



sql for:53_FORESTED_ACRES

(select sum(r.ACRES_AMT)
FROM fr_recon r, fr_timber_type tt
where r.PRIM_TIMBER_TYPE = tt.TIMBER_TYPE
and tt.FOREST_FLAG = 'F'
and r.FR_PROP_CODE = 0_PROPERTY
and nvl(instr(r.STAND_PREFIX_CODE,'R'),0) = 0
and nvl(instr(r.STAND_PREFIX_CODE,'Y'),0) = 0
and nvl(instr(r.STAND_PREFIX_CODE,'Z'),0) = 0)




how i proceed for report. actually i can able to get the below fields on report.

r.PRIM_TIMBER_TYPE ptt,
tt.TIMBER_TEXT ptx,
count(r.RECON_ID),
sum(r.ACRES_AMT) Acres,

for last 2 fields i confused.
anybody can tell how can i get last 2 fields on report.

 

[synapsevampire]

You don't use multiple sql statements in Crystal unless they're joined, and even that is a bad idea to do within Crystal.

Did you think using SQL for :53 described something to us about what sort of database this was, we don't know if this is the same database as the first query...

Please think through your posts and describe your environment and requirements.

You can try pasting the SQL you reference in a SQL Expression.

Hard to know since you share too little technical information.

-k

 

[qwertyu1]

sorry,

Database is oracle9 for all sql's.