Forum Discussion

paul_sheehy's avatar
paul_sheehy
Ideator III
6 years ago
Solved

using the SQL item type as a view

Querying a SQL view I built in SSMS from an Aras server method: string SQL_View = "innovator.my_custom_sql_view"; string SQL = "select * from " + SQL_View; Item SQL_qry = inn.applySQL(SQL); Th...
IOM
Item Views
Server Methods
SQL
  • Gopikrishnan's avatar
    Gopikrishnan
    6 years ago

    Hi Paul

    You can use below method to get the required view. I used item_number of NCMR as ID is unique for each item type

    Innovator inn = this.getInnovator();
    //Verify the Item type name in system
    var ncmrItem = inn.newItem("NCMR MANAGEMENT", "get");
    ncmrItem.setAttribute("select", "id,item_number");
    ncmrItem = ncmrItem.apply();
    if (ncmrItem.isError())
    {
    return inn.newError("No NCMR Item Found");
    }
    else
    {
    var ncmrIdList = new List < string > ();
    for (int i = 0; i < ncmrItem.getItemCount(); i++)
    {
    var ncmrOneItem = ncmrItem.getItemByIndex(i);
    //I'm using item_number here as ID is unique for each item type and we might not have same ID used for Part and NCMR. If you want to use ID, then change item_number to id
    ncmrIdList.Add("'" + ncmrOneItem.getProperty("item_number", string.Empty) + "'");
    }
    var partView = inn.newItem("Part", "get");
    partView.setAttribute("select", "id,item_number");
    partView.setProperty("is_current", "1");
    partView.setProperty("state", "Released");
    partView.setProperty("item_number", string.Join(",", ncmrIdList)); // Change it to ID if you want to use ID instead item_number
    partView.setPropertyAttribute("item_number", "condition", "not in"); // Change it to ID if you want to use ID instead item_number
    partView = partView.apply();
    if (partView.isError())
    {
    return inn.newError("No Part item found using this criteria");
    }
    else
    {
    return partView;
    }
    }

    We can SQL also but it is good practice to use AML as SQL directly interacts with tables and other server events will not be triggered.

    Views in database is created for Poly Item and are system created. If you want to use SQL for any complex get query, you can use stored procedure and call the SQL Process in your method

Gopikrishnan's avatar
Gopikrishnan
Accelerator II
6 years ago

Hi Paul

Can you post the innovator.my_custom_sql_view SQL query  here so that we can have a look

You need to convert your SQL query to AML query and use it in method. ("SQL" in your method will get the SQL item types but you need to use the correct item type which you are querying in you SQL view).

paul_sheehy's avatar
paul_sheehy
Ideator III
6 years ago

my view query is fairly simple:

select
id
,ITEM_NUMBER

from innovator.part
where [state] = 'Released'
and IS_CURRENT = 1
and id not in (select NCMR_PART from innovator.NCMR_MANAGEMENT)

if i have to convert the sql query to AML, then what is the point of a SQL item with type=view? how is it used?

  • Gopikrishnan's avatar
    Gopikrishnan
    Accelerator II
    6 years ago

    Hi Paul

    You can use below method to get the required view. I used item_number of NCMR as ID is unique for each item type

    Innovator inn = this.getInnovator();
    //Verify the Item type name in system
    var ncmrItem = inn.newItem("NCMR MANAGEMENT", "get");
    ncmrItem.setAttribute("select", "id,item_number");
    ncmrItem = ncmrItem.apply();
    if (ncmrItem.isError())
    {
    return inn.newError("No NCMR Item Found");
    }
    else
    {
    var ncmrIdList = new List < string > ();
    for (int i = 0; i < ncmrItem.getItemCount(); i++)
    {
    var ncmrOneItem = ncmrItem.getItemByIndex(i);
    //I'm using item_number here as ID is unique for each item type and we might not have same ID used for Part and NCMR. If you want to use ID, then change item_number to id
    ncmrIdList.Add("'" + ncmrOneItem.getProperty("item_number", string.Empty) + "'");
    }
    var partView = inn.newItem("Part", "get");
    partView.setAttribute("select", "id,item_number");
    partView.setProperty("is_current", "1");
    partView.setProperty("state", "Released");
    partView.setProperty("item_number", string.Join(",", ncmrIdList)); // Change it to ID if you want to use ID instead item_number
    partView.setPropertyAttribute("item_number", "condition", "not in"); // Change it to ID if you want to use ID instead item_number
    partView = partView.apply();
    if (partView.isError())
    {
    return inn.newError("No Part item found using this criteria");
    }
    else
    {
    return partView;
    }
    }

    We can SQL also but it is good practice to use AML as SQL directly interacts with tables and other server events will not be triggered.

    Views in database is created for Poly Item and are system created. If you want to use SQL for any complex get query, you can use stored procedure and call the SQL Process in your method

    • paul_sheehy's avatar
      paul_sheehy
      Ideator III
      6 years ago

      Gopikrishnan, thanks for the helpful info!