DEVELOPERS FORUM - SQL select/where a property is a Foreign data-type??

aaasam - Wednesday, December 14, 2011 5:50 PM:

Does anyone happen to know how to go about building an aras SQL select statement to retrieve properties (rows & columns) when one of the properties (columns) is of Data-Type Foreign?

The property I have, in question, exists on the primary item-type (table) as a Foreign data-type to another item-type (table).  Below is my select that works 100% until I add this property to the where clause:

My Innovator error msg is: invalid column near comp_cat

var results = inn.applySQL("select 'Complaint Category', comp_type, Month, fiscal_year " +

aaasam - Thursday, December 15, 2011 3:48 PM:

** RESOLVED **

Since we decided to add a new property (column) to the item-type (table) of the customer complaints, trying to access the data-type of foreign for the category of the complaint was no longer needed.  However, in my trials....  I was able to write an SQL select statement to perform a subselect.  Essentially, selected from the comp_type table and matched the guid of the item (comp_type) to the id of the table customer_complaints.  It did not give me back what I was exactly searching for, but it did "work".

var results = inn.applySQL("select APCT.type, sum(count(*)) as 'total' " +
    "from innovator.[AP_Complaint_Type] as  APCT " +
    "where Category = 'Package' " +
    "  and " +
    "    APCT.id in (select APCC.comp_type " +
    "     from innovator.[AP_Customer_Complaint] as APCC " +
    "     where APCC.Month = '" + curMonth  + "'" +
    "       and " +
    "           APCC.fiscal_year = " + curCCYY + ")" +
    "group by APCT.type");

We select the complaint_type "type" property and summarize the total found assigning it to an XML tag from the complaint_type table
where the complaint_type category is 'Package' AND the complaint_type guid id is in a group of customer_complaint types from the
customer_complaint table where the customer_complaint month = 'November' and the customer_complaint fiscal_year = 2011 finally
grouping all of the results by the complaint_type type (ie: barcode, bad seal, volume, etc....)

I was trying to reverse the query to try and get the customer_complaints for November, 2011 in a group of types (ie: bardcode, bad seal, volume, etc....) with a complaint_category of 'Package' and return the total # found grouped by the customer_complaint type.

Hope this makes sense.

AbhishekSrivastava - Friday, March 25, 2016 5:33 AM:

Hi SM,

I need help on fetching value from SQl,

Actually i want to fetch Customer Name from Customer Item Type to My Custom Item Type in a dropdown way.

Now i want to select Customer Name  and accordingly related  value fetch on my custom item type like Customer Segment=Automotive.

kindly guide me with sample code.

Thanks

Abhishek Srivastava