Merge Syntax Subquery Conditions

I need some guidance on subqueries. Can you use merge syntax in a subquery? I have an invoice line object connected to a Rebate Line object via the item object. I want a subquery condition that looks at the invoice date and see if it is in between the start and end date found on the rebate line. 

I get the below error:

1. An error occurred while attempting to perform the following SOQL query: SELECT SCMC__Invoicing__r.SCMC__Invoice_Date__c,SCMC__Item__c,SCMC__Item__r.Name,SCMC__Applied_Rebate_Amount__c,Id,CurrencyIsoCode FROM SCMC__Invoice_Line_Item__c WHERE (SCMC__Item__c in (SELECT SCMC__Item__c FROM SCMC__Rebate_Line__c WHERE (((SCMC__Rebate__r.SCMC__Start_Date__c >= SCMC__Invoicing__r.SCMC__Invoice_Date__c)AND(SCMC__Rebate__r.SCMC__End_Date__c <= SCMC__Invoicing__r.SCMC__Invoice_Date__c)))))AND(SCMC__Applied_Rebate_Amount__c != null) LIMIT 21 Error:expecting a colon, found ‘SCMC__Invoicing__r.SCMC__Invoice_Date__c’

Hard to understand the error, but I think you could use “Field from Another Model” for the value.

The “Field from Another Model” only looks at the first line of the model. I can get a flag created with ui-only fields to accomplish this but because I can’t use the ui-only field in a condition I can’t filter the actual data.

Actually, you can switch the operator from “=” to “in” and then all fieds values from all rows would be used.

Right, but I need greater than and less than operators.

I see. You only want to display invoice line items where the invoice date is between the start or end dates of rebates.

I  assume these records don’t have any direct relationships?

Exactly! You are correct there is not a direct relationship. 

So, by extension, you would display line items for the invoice(s) who’s date is between the start and end date of the rebates.

I’d create agg models for the rebate records for max and min. You can then use “Field from Another Model”.

Ahh that is a very good idea. Let me give it a shot. Thanks Pat.

Pat - I tried your suggestion but using the “Field from Another Model” for the Agg field still only looks at the first row when using greater or less than.

So global merge syntax in the sub-conditional logic of a conditional sub-query is not possible i take it?