Need help in correlated sub query logic

[venkataramana_paidi]

Hi ,

I have one procedure with cursor. I have given small part of the cursor as shown below .

SELECT

  A.DAY_DATE,

  A.COUNTRY_CODE,

  A.INST_CODE,

  a.bra_code,

  a.cus_num,

  a.cur_code,

  a.led_code,

  a.sub_acct_code,

  (tra_amt * SPOT_RATE) pre_day_crnt_bal,

  t.mat_date,

  t.NEW_INT_RATE int_rate,

  t.DEB_CRE_IND int_chg_pai_ind,

  a.ACCT_TYPE,

  (SELECT COUNT (

  DISTINCT TO_CHAR (day_date, 'mm')

  )

  FROM PIO_TIMES

  WHERE day_date BETWEEN a.day_date

  AND NVL ( T.MAT_DATE,A.DAY_DATE) 

  AND TO_CHAR (day_date, 'dd') = '31') Test



----

-----

)

Here how can I build the logic for inside select statement.

I tried to build the separate stream for the inside select statement but I am confusing how to handle the count in the inside the query.

Please help me how to build the logic for the inside query statement .

Thanks & Regards,

Ramana.

[former_member205887]

Hi Venkata,

With my understanding with your above question, i see lookup_ext would help you in handling this....

[venkataramana_paidi]

Hi Balakrishna,

How can we implement COUNT ( DISTINCT TO_CHAR (day_date, 'mm') )  using lookup_ext.

If you have any idea Please tell me.

Thanks & Regards,

Ramana.

[former_member205887]

Hi Venkata

You should use Custom SQL feature of Lookup_Ext to achieve this.

[venkataramana_paidi]

Hi Balakrishna,

How can I use custom SQL for this requirement. Please give me sample statement based on my query for inner SQL query.

Thanks & Regards,

Ramana.

[former_member205887]

Hi Venkata,

Below sql statement should go in Custom SQL, Replace NUM_MM with some column which is of type number in your PIO_TIMES table,this is just a place holder, but should have a column of the same datatype returned by the below sql

Lookup table : PIO_TIMES

Custom SQL:  (This will act as your lookup table)

SELECT COUNT (DISTINCT TO_CHAR (day_date, 'mm')) NUM_MM,day_date
  FROM PIO_TIMES
  WHERE
  TO_CHAR (day_date, 'dd') = '31'
 
 
Your below condition should go in the Condition list

Condition:
 
  1st Condition day_date>=a.day_date
  2nd Condition day_date<=NVL( T.MAT_DATE,A.DAY_DATE) 
 
  Output:
 
    NUM_MM  -- Replace this with your column name used as Alias in the above query
 

Hope this helps...

[venkataramana_paidi]

Hi Balakrishna,

Thanks for your support

I have applied but I am not getting the column in the output columns from the custom sql function.

As per the procedure we are using the all conditions like between and month or date conditions  to find the count of distinct months.

If we use partial conditional in the custom SQL  and partial conditionals in the lookup condition clause is it give the same result?

How can we get the field of custom sql in the output field.

Thanks & Regards,

Ramana.

[former_member205887]

I think Partial condition should work as it does lookup for each record.

If you go thru my above post, i asked you to give an alias name to the select field in custom sql with some column name in your PIO_TIMES of type number, as count returns a number. Use that column name in the output and it will work.

[venkataramana_paidi]

Hi Balakrishna,

I have given the one of the column from PIO_TIMES as alias name for count column. But this I am getting error like DAY_DATE should be in group by clause. Please check the below error.

Please give me your inputs.

Thanks & Regards,

Ramana.

[former_member205887]

Yes put DAY_DATE in groupby clause as we are doing aggregation(count)  in custom sql, and also use \ before quotes like \'