Apex Code Development You need to sign in to do that
Don't have an account?
Lokeswara Reddy Dynamic SOQL binding issue
I'm working on custom product search, which is a replacement for standard product search from Opportunity (Add Product from the related list). The VF page will have couple of Select options, if you select one value from first select option then it should filter the possible values in other select options and it should display the available products as well.
I have to filter values based on pricebook entry and account currency. I'm passing these two values in the url while invoking the vf page from the search button.
The issue:
The dynamic query works when there is only one condition in the where clasuse, I'm trying to add two filter conditions in the where clause then the query returns no rows, no error/exception thrown.
Filter 1:
String QUERY_STRING = 'select Name, ProductCode, CBS_Segment__c, Description, Family, CBS_Item_Type_Description__c, Model_Series__c, Life_Cycle_Status__c FROM Product2 ';
String WHERE_CLAUSE = 'WHERE Id IN (SELECT Product2Id FROM PricebookEntry where Pricebook2Id =: pb2Id and CurrencyIsoCode =: currencyCode)';
QUERY_STRING += WHERE_CLAUSE;
Database.query(QUERY_STRING);
This returned values as expected
Filter 2:
String QUERY_STRING = 'select Name, ProductCode, CBS_Segment__c, Description, Family, CBS_Item_Type_Description__c, Model_Series__c, Life_Cycle_Status__c FROM Product2 ';
String WHERE_CLAUSE = ' WHERE Family =: selectedPG' ;
QUERY_STRING += WHERE_CLAUSE;
Database.query(QUERY_STRING);
This also returned values
When I combine both the conditions then it does not return any values.
String QUERY_STRING = 'select Name, ProductCode, CBS_Segment__c, Description, Family, CBS_Item_Type_Description__c, Model_Series__c, Life_Cycle_Status__c FROM Product2 ';
String WHERE_CLAUSE = 'WHERE Id IN (SELECT Product2Id FROM PricebookEntry where Pricebook2Id =: pb2Id and CurrencyIsoCode =: currencyCode)';
WHERE_CLAUSE += ' AND Family =: selectedPG' ;
QUERY_STRING += WHERE_CLAUSE;
Database.query(QUERY_STRING);
I have tried different approaches like combiting both the conditions in the single variable and then adding to the query string, no use.
Can some one through light why this is not working? do we have any limitations on having filter condtions in dynyamic query.
Please note that the binding variables are in scope with the method and when used alone it is working, but with combination not working.
I have to filter values based on pricebook entry and account currency. I'm passing these two values in the url while invoking the vf page from the search button.
The issue:
The dynamic query works when there is only one condition in the where clasuse, I'm trying to add two filter conditions in the where clause then the query returns no rows, no error/exception thrown.
Filter 1:
String QUERY_STRING = 'select Name, ProductCode, CBS_Segment__c, Description, Family, CBS_Item_Type_Description__c, Model_Series__c, Life_Cycle_Status__c FROM Product2 ';
String WHERE_CLAUSE = 'WHERE Id IN (SELECT Product2Id FROM PricebookEntry where Pricebook2Id =: pb2Id and CurrencyIsoCode =: currencyCode)';
QUERY_STRING += WHERE_CLAUSE;
Database.query(QUERY_STRING);
This returned values as expected
Filter 2:
String QUERY_STRING = 'select Name, ProductCode, CBS_Segment__c, Description, Family, CBS_Item_Type_Description__c, Model_Series__c, Life_Cycle_Status__c FROM Product2 ';
String WHERE_CLAUSE = ' WHERE Family =: selectedPG' ;
QUERY_STRING += WHERE_CLAUSE;
Database.query(QUERY_STRING);
This also returned values
When I combine both the conditions then it does not return any values.
String QUERY_STRING = 'select Name, ProductCode, CBS_Segment__c, Description, Family, CBS_Item_Type_Description__c, Model_Series__c, Life_Cycle_Status__c FROM Product2 ';
String WHERE_CLAUSE = 'WHERE Id IN (SELECT Product2Id FROM PricebookEntry where Pricebook2Id =: pb2Id and CurrencyIsoCode =: currencyCode)';
WHERE_CLAUSE += ' AND Family =: selectedPG' ;
QUERY_STRING += WHERE_CLAUSE;
Database.query(QUERY_STRING);
I have tried different approaches like combiting both the conditions in the single variable and then adding to the query string, no use.
Can some one through light why this is not working? do we have any limitations on having filter condtions in dynyamic query.
Please note that the binding variables are in scope with the method and when used alone it is working, but with combination not working.
For example: if I have cars and buses:
Select * from vehicle where type = cars --> will have results
Select * from vehicle where type = buses--> will have results
Select * from vehicle where type = cars AND type = buses --> will have NO results -> as expected.
Why don't you change your queries to 'OR' and see if results are returning:
WHERE_CLAUSE += ' OR Family =: selectedPG' ;
Thx
All Answers
For example: if I have cars and buses:
Select * from vehicle where type = cars --> will have results
Select * from vehicle where type = buses--> will have results
Select * from vehicle where type = cars AND type = buses --> will have NO results -> as expected.
Why don't you change your queries to 'OR' and see if results are returning:
WHERE_CLAUSE += ' OR Family =: selectedPG' ;
Thx
Sounds good. Let me know if you still have issues.
Also, as a common practice, if your question is answered, please choose 1 best answer.
But you can give every answer a thumb up if that answer is helpful to you.
This will help keep the forum clean and help future users determine what answers are useful
and what answer was the best in resolving the user's issue.
Thanks
It was working as expected, but as the data was not there the the VF did not get refresh, silly mistake from side in not checking the data :-)
Glad it worked out.
I do it all the times, you learn from experience :-)
Thx