I am trying to pull the data from the Item Master Plan table based on the user selection as follows, but I am not getting the result even though the data is on the table.

Any help would be great...

1) i.plnc -Scenario
2) i.item.f - Item (zoom field from Item-Planning)
3) i.citg - Item Groups (zoom field from Item-Groups)
4) i.pren - Period (1-12)

select cprmp300.plni,
cprmp300.pern,
cprmp300.demf
from cprmp300
where cprmp300.plni IN (select cprrp100.item from cprrp100
where cplb = :i.citg
and cprrp100.item >=:i.item.f
and cprrp100.item <=:i.item.t)
and cprmp300.plnc = :i.plnc
and cprmp300.pern >= :i.pren
and cprmp300.pern <= :i.pren + 11

regards,

Hi,

Did you try debugging it and look if the variables are getting correct value from Form.

Try to separate the subquery and see if it working stand alone.

I dont see anything wrong in code , except that you can include cprmp300.plnc in selection statement.

Regards,
Abattoir

This statement may be causing issues since I don't always arithmetic expressions in the where clause. You might want to try using input variables for 'i.pren.f' (current i.pren) and 'i.pren.t'. Actually, the expression for 'i.pren + 11' probably isn't needed since the result will always be 12 or greater.

"and cprmp300.pern >= :i.pren and cprmp300.pern <= :i.pren + 11"

You might also want to ensure that all string variables used in the where clause are declared as 'fixed'.

It probably is not causing the problem, but we found using arithmetic in the sql slows things down, better to do the arithmetic outside the SQL.

Thank you very much for all your suggestions, now I modified the query by seperating the sub query as suggested by Abattoir. Also it is faster in performance point of view.

The code as follows

select cprmp300.plni,
cprmp300.pern,
cprmp300.demf
from cprmp300
where cprmp300._index2 = { :i.plnc,
:i.plni,
""
}
and cprmp300.pern >= :i.pren
and cprmp300.pern <= :i.pren + 11