aaasam - Wednesday, December 14, 2011 6:11 PM:
Hello everyone...
Just like my prior post, I am needing some help on solving this SQL issue.
In this case, when i add the property (column) comp_type = 'volume' to the where clause, no errors are generated -- but NO rows are returned from the select (results.getItemCount() returns -1).
However, if I perform a "manual simple search" on the item-type (table) using the exact same criteria, I am returned 3 rows???
I am wondering if the issue is the fact that this particular property (column) is of Data Type "Item" with a data source of a different item-type (table) called "AP_Complaint_Type".
So, here is my SQL statement.
var results = inn.applySQL("select 'Complaint Category', comp_type, Month, fiscal_year " +
"from innovator.[AP_Customer_Complaint] " +
"where" +
" month = '" + curMonth + "'" +
" and" +
" fiscal_year = '" + curCCYY + "'" +
" and" +
" comp_type = 'volume'");
Might this be a case of where I am going to have to code sub selects or joins of some kind?
I do know that without the comp_cat and comp_type that I do get results but the first property returned is the Guid ID property. So, might I then have to take that Guid ID and use it to query the other item-type (table) "AP_Complaint_Type" to find the matching Guid ID wihere the comp_cat = 'package' and comp_type = 'volume'?
Thanks!
aaasam - Thursday, December 15, 2011 3:36 PM:
**RESOLVED**
In this case, we decided to populate a new property on the item-type (table) to contain the string value of the complaint type. Therefore, there was no need to link the two tables together to get what I was needing due to this being a data-type of Item.
-Scott
