Clarity

Hi All  I am trying to right an NSQL query to total the planned cost of a set of Idea records.   We are not using the full financials module and the only cost attribute we use on the Idea is the Planned Cost.   When i look in Studio at the Idea Object, I beleive the Field is FIN_FINANCIALS.PLANNED_COST.  I have the following NSQL: (this is the NSQL from my TEST Lookup)  SELECT
 @SELECT:main.id:ID@,
 @SELECT:main.code:Code@,
 @SELECT:main.planned_cost:PlannedCost@from
(
select
f.id,
inv.code,
f.planned_costfrom
odf_ca_idea i,
fin_financials f,
inv_investments invwhere   f.id = '5001000'
and inv.id = i.id  ) mainwhere @FILTER@  and it returns data that looks like this:5001000          ID10146              [B@1f329f0[/indent]  So the PLANNED_COST field was not what I was expecting and I am led to beleive it is a BLOB based on the oracle error messages I get when trying to do comparison on this field.  So am I on the right track?   Is there a way of extracting the Planned Cost from this apparant BLOB field?   The ER diagram in the TechRef is not overly helpful.   Is it actually in another fild in another table?  All help and assistance greatly appreciated.    

Hi,           Fin_Financials.PLANNED_COST should be clob value. But Planned cost information is stored in PLANNED_CST_TOTAL column from FIN_FINANCIAL Table. please try the following line and also you can make NSQL based on the below query. You can apply this query for all investments. Only INV_IDEAS, ODF_CA_IDEA and OBF_OBJECT_CODE should be changed based on Investment type.    SELECT INV .ID, INV . CODE , INV .NAME, PLANNED_CST_TOTAL , PLANNED_CST_START , PLANNED_CST_FINISH FROM INV_INVESTMENTS INV , ODF_CA_IDEA ODF , INV_IDEAS IDEA , ODF_OBJECT_INSTANCE_MAPPING MAP ,  FIN_FINANCIALS FIN WHERE INV .ID = IDEA .ID AND IDEA .ID = ODF .ID AND INV .ID = MAP . PRIMARY_OBJECT_INSTANCE_ID  AND MAP . SECONDARY_OBJECT_INSTANCE_ID = FIN .ID AND INV . ODF_OBJECT_CODE = 'idea' AND INV .ID = 5000000      Thanks  Senthil  Message Edited by senthil on 01-12-2010 01:29 PM [left]

Thankyou very much, works perfectly!