Question

IAL with parameter

  • 19 August 2021
  • 1 reply
  • 455 views

Userlevel 4
Badge +9

 

I have the below SQL query which I used to get the full multilevel product structure.

I have to use this SQL query as an IAL and use that IAL on the crystal report

select ROWNUM AS ROW_NO, LEVEL AS M_LEVEL, 
SORT_NO, PART_NO, PART_REV, SUB_PART_NO, SUB_PART_REV, 
SUB_PART_DESCRIPTION, POS, QTY,  
IFSAPP.ENG_PART_MASTER_API.Get_Unit_Code(SUB_PART_NO) AS UNITS,
CF$_MATERIAL, CF$_SPARES, CF$_CERTIFICATE, CF$_ACT_ATTRIBUTE_SUMMARY, 
CF$_PART_FINISHING from IFSAPP.ENG_PART_STRUCTURE_EXT_CFV
CONNECT BY
PRIOR SUB_PART_NO = PART_NO AND PRIOR SUB_PART_REV = PART_REV
START WITH PART_NO = :PART_NO and PART_REV = :PART_REV ORDER SIBLINGS BY SORT_NO

 

Is it possible to pass an parameter in an IAL ? Or what is the best way to get the full multi-level product structure ?

 

 


This topic has been closed for comments

1 reply

Userlevel 4
Badge +9

IAL’s are database views and as such are not ‘dynamic’.

Have you considered a PIPELINE TABLE?

 

 

They are PL/SQL packages that You can pass parameters to then use PL/SQL logic to read through the database and return a table of data.

They are generated in memory so there are performance issues to consider but they are a potentially powerful way of reporting.