Subquerying filtering on main query field












2















My intention is to filter the child records which are != to a field of the master.



Account: Master
Application__c : Child



Using Subquery:



select acc.id,acc.Callista_Org_Unit_Code__c , (select Agent_Org_Unit_Code__c from Applications__r)from Account acc 


I would like to add the following filter in the subquery:



acc.Callista_Org_Unit_Code__c!=Agent_Org_Unit_Code__c


This is not allowed in SOQL.



Or alternatively, filter from a child record:



select id,Agent_Org_Unit_Code__c,Agent__r.Callista_Org_Unit_Code__c  from Application__c where Agent__r.Callista_Org_Unit_Code__c!=Agent_Org_Unit_Code__c


This also gives an error.



Is there any way I can build out these filters?










share|improve this question





























    2















    My intention is to filter the child records which are != to a field of the master.



    Account: Master
    Application__c : Child



    Using Subquery:



    select acc.id,acc.Callista_Org_Unit_Code__c , (select Agent_Org_Unit_Code__c from Applications__r)from Account acc 


    I would like to add the following filter in the subquery:



    acc.Callista_Org_Unit_Code__c!=Agent_Org_Unit_Code__c


    This is not allowed in SOQL.



    Or alternatively, filter from a child record:



    select id,Agent_Org_Unit_Code__c,Agent__r.Callista_Org_Unit_Code__c  from Application__c where Agent__r.Callista_Org_Unit_Code__c!=Agent_Org_Unit_Code__c


    This also gives an error.



    Is there any way I can build out these filters?










    share|improve this question



























      2












      2








      2








      My intention is to filter the child records which are != to a field of the master.



      Account: Master
      Application__c : Child



      Using Subquery:



      select acc.id,acc.Callista_Org_Unit_Code__c , (select Agent_Org_Unit_Code__c from Applications__r)from Account acc 


      I would like to add the following filter in the subquery:



      acc.Callista_Org_Unit_Code__c!=Agent_Org_Unit_Code__c


      This is not allowed in SOQL.



      Or alternatively, filter from a child record:



      select id,Agent_Org_Unit_Code__c,Agent__r.Callista_Org_Unit_Code__c  from Application__c where Agent__r.Callista_Org_Unit_Code__c!=Agent_Org_Unit_Code__c


      This also gives an error.



      Is there any way I can build out these filters?










      share|improve this question
















      My intention is to filter the child records which are != to a field of the master.



      Account: Master
      Application__c : Child



      Using Subquery:



      select acc.id,acc.Callista_Org_Unit_Code__c , (select Agent_Org_Unit_Code__c from Applications__r)from Account acc 


      I would like to add the following filter in the subquery:



      acc.Callista_Org_Unit_Code__c!=Agent_Org_Unit_Code__c


      This is not allowed in SOQL.



      Or alternatively, filter from a child record:



      select id,Agent_Org_Unit_Code__c,Agent__r.Callista_Org_Unit_Code__c  from Application__c where Agent__r.Callista_Org_Unit_Code__c!=Agent_Org_Unit_Code__c


      This also gives an error.



      Is there any way I can build out these filters?







      apex soql






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited 4 hours ago









      Adrian Larson

      108k19115243




      108k19115243










      asked 4 hours ago









      Shalini SFShalini SF

      448615




      448615






















          1 Answer
          1






          active

          oldest

          votes


















          3














          Please note from the SOQL and SOSL Reference that you cannot compare one field to another.




          fieldExpression Syntax



          The field expression syntax of the WHERE clause in a SOQL query consists of a field name, a comparison operator, and a value that’s used to compare with the value in the field name.



          fieldExpression uses the following syntax:



            fieldName comparisonOperator value


          where:



          fieldName

          The name of a field in the specified object. Use of single or double quotes around the name will result in an error. You must have at least read-level permissions to the field. It can be any field except a long text area field, encrypted data field, or base64-encoded field. It does not need to be a field in the fieldList.



          comparisonOperator

          Case-insensitive operators that compare values.



          value
          A value used to compare with the value in fieldName. You must supply a value whose data type matches the field type of the specified field. You must supply a native value—other field names or calculations are not permitted. If quotes are required (for example, they are not for dates and numbers), use single quotes. Double quotes result in an error.




          Again, note:




          You must supply a native value—other field names or calculations are not permitted.






          This is a very common shortcoming of SOQL to need to circumvent. The standard workaround is to use a formula.



          You need to create a formula on the child record to basically extend SOQL in this way. Name your formula something like Org_Unit_Code_Matches_Parent__c with an output type of Boolean, then simply set it to:



          Agent_Org_Unit_Code__c = Account__r.Callista_Org_Unit_Code__c


          The above assumes the lookup field has an API Name of Account__c.






          share|improve this answer


























          • Thanks @adrian. Why wouldn't it work from a child relationship? select id,Agent_Org_Unit_Code__c,Agent__r.Callista_Org_Unit_Code__c from Application__c where Agent__r.Callista_Org_Unit_Code__c!=Agent_Org_Unit_Code__c

            – Shalini SF
            1 hour ago











          • @ShaliniSF You can't compare one field to another.

            – Adrian Larson
            35 mins ago











          Your Answer








          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "459"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          autoActivateHeartbeat: false,
          convertImagesToLinks: false,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: null,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });














          draft saved

          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsalesforce.stackexchange.com%2fquestions%2f249905%2fsubquerying-filtering-on-main-query-field%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          1 Answer
          1






          active

          oldest

          votes








          1 Answer
          1






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes









          3














          Please note from the SOQL and SOSL Reference that you cannot compare one field to another.




          fieldExpression Syntax



          The field expression syntax of the WHERE clause in a SOQL query consists of a field name, a comparison operator, and a value that’s used to compare with the value in the field name.



          fieldExpression uses the following syntax:



            fieldName comparisonOperator value


          where:



          fieldName

          The name of a field in the specified object. Use of single or double quotes around the name will result in an error. You must have at least read-level permissions to the field. It can be any field except a long text area field, encrypted data field, or base64-encoded field. It does not need to be a field in the fieldList.



          comparisonOperator

          Case-insensitive operators that compare values.



          value
          A value used to compare with the value in fieldName. You must supply a value whose data type matches the field type of the specified field. You must supply a native value—other field names or calculations are not permitted. If quotes are required (for example, they are not for dates and numbers), use single quotes. Double quotes result in an error.




          Again, note:




          You must supply a native value—other field names or calculations are not permitted.






          This is a very common shortcoming of SOQL to need to circumvent. The standard workaround is to use a formula.



          You need to create a formula on the child record to basically extend SOQL in this way. Name your formula something like Org_Unit_Code_Matches_Parent__c with an output type of Boolean, then simply set it to:



          Agent_Org_Unit_Code__c = Account__r.Callista_Org_Unit_Code__c


          The above assumes the lookup field has an API Name of Account__c.






          share|improve this answer


























          • Thanks @adrian. Why wouldn't it work from a child relationship? select id,Agent_Org_Unit_Code__c,Agent__r.Callista_Org_Unit_Code__c from Application__c where Agent__r.Callista_Org_Unit_Code__c!=Agent_Org_Unit_Code__c

            – Shalini SF
            1 hour ago











          • @ShaliniSF You can't compare one field to another.

            – Adrian Larson
            35 mins ago
















          3














          Please note from the SOQL and SOSL Reference that you cannot compare one field to another.




          fieldExpression Syntax



          The field expression syntax of the WHERE clause in a SOQL query consists of a field name, a comparison operator, and a value that’s used to compare with the value in the field name.



          fieldExpression uses the following syntax:



            fieldName comparisonOperator value


          where:



          fieldName

          The name of a field in the specified object. Use of single or double quotes around the name will result in an error. You must have at least read-level permissions to the field. It can be any field except a long text area field, encrypted data field, or base64-encoded field. It does not need to be a field in the fieldList.



          comparisonOperator

          Case-insensitive operators that compare values.



          value
          A value used to compare with the value in fieldName. You must supply a value whose data type matches the field type of the specified field. You must supply a native value—other field names or calculations are not permitted. If quotes are required (for example, they are not for dates and numbers), use single quotes. Double quotes result in an error.




          Again, note:




          You must supply a native value—other field names or calculations are not permitted.






          This is a very common shortcoming of SOQL to need to circumvent. The standard workaround is to use a formula.



          You need to create a formula on the child record to basically extend SOQL in this way. Name your formula something like Org_Unit_Code_Matches_Parent__c with an output type of Boolean, then simply set it to:



          Agent_Org_Unit_Code__c = Account__r.Callista_Org_Unit_Code__c


          The above assumes the lookup field has an API Name of Account__c.






          share|improve this answer


























          • Thanks @adrian. Why wouldn't it work from a child relationship? select id,Agent_Org_Unit_Code__c,Agent__r.Callista_Org_Unit_Code__c from Application__c where Agent__r.Callista_Org_Unit_Code__c!=Agent_Org_Unit_Code__c

            – Shalini SF
            1 hour ago











          • @ShaliniSF You can't compare one field to another.

            – Adrian Larson
            35 mins ago














          3












          3








          3







          Please note from the SOQL and SOSL Reference that you cannot compare one field to another.




          fieldExpression Syntax



          The field expression syntax of the WHERE clause in a SOQL query consists of a field name, a comparison operator, and a value that’s used to compare with the value in the field name.



          fieldExpression uses the following syntax:



            fieldName comparisonOperator value


          where:



          fieldName

          The name of a field in the specified object. Use of single or double quotes around the name will result in an error. You must have at least read-level permissions to the field. It can be any field except a long text area field, encrypted data field, or base64-encoded field. It does not need to be a field in the fieldList.



          comparisonOperator

          Case-insensitive operators that compare values.



          value
          A value used to compare with the value in fieldName. You must supply a value whose data type matches the field type of the specified field. You must supply a native value—other field names or calculations are not permitted. If quotes are required (for example, they are not for dates and numbers), use single quotes. Double quotes result in an error.




          Again, note:




          You must supply a native value—other field names or calculations are not permitted.






          This is a very common shortcoming of SOQL to need to circumvent. The standard workaround is to use a formula.



          You need to create a formula on the child record to basically extend SOQL in this way. Name your formula something like Org_Unit_Code_Matches_Parent__c with an output type of Boolean, then simply set it to:



          Agent_Org_Unit_Code__c = Account__r.Callista_Org_Unit_Code__c


          The above assumes the lookup field has an API Name of Account__c.






          share|improve this answer















          Please note from the SOQL and SOSL Reference that you cannot compare one field to another.




          fieldExpression Syntax



          The field expression syntax of the WHERE clause in a SOQL query consists of a field name, a comparison operator, and a value that’s used to compare with the value in the field name.



          fieldExpression uses the following syntax:



            fieldName comparisonOperator value


          where:



          fieldName

          The name of a field in the specified object. Use of single or double quotes around the name will result in an error. You must have at least read-level permissions to the field. It can be any field except a long text area field, encrypted data field, or base64-encoded field. It does not need to be a field in the fieldList.



          comparisonOperator

          Case-insensitive operators that compare values.



          value
          A value used to compare with the value in fieldName. You must supply a value whose data type matches the field type of the specified field. You must supply a native value—other field names or calculations are not permitted. If quotes are required (for example, they are not for dates and numbers), use single quotes. Double quotes result in an error.




          Again, note:




          You must supply a native value—other field names or calculations are not permitted.






          This is a very common shortcoming of SOQL to need to circumvent. The standard workaround is to use a formula.



          You need to create a formula on the child record to basically extend SOQL in this way. Name your formula something like Org_Unit_Code_Matches_Parent__c with an output type of Boolean, then simply set it to:



          Agent_Org_Unit_Code__c = Account__r.Callista_Org_Unit_Code__c


          The above assumes the lookup field has an API Name of Account__c.







          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited 36 mins ago

























          answered 4 hours ago









          Adrian LarsonAdrian Larson

          108k19115243




          108k19115243













          • Thanks @adrian. Why wouldn't it work from a child relationship? select id,Agent_Org_Unit_Code__c,Agent__r.Callista_Org_Unit_Code__c from Application__c where Agent__r.Callista_Org_Unit_Code__c!=Agent_Org_Unit_Code__c

            – Shalini SF
            1 hour ago











          • @ShaliniSF You can't compare one field to another.

            – Adrian Larson
            35 mins ago



















          • Thanks @adrian. Why wouldn't it work from a child relationship? select id,Agent_Org_Unit_Code__c,Agent__r.Callista_Org_Unit_Code__c from Application__c where Agent__r.Callista_Org_Unit_Code__c!=Agent_Org_Unit_Code__c

            – Shalini SF
            1 hour ago











          • @ShaliniSF You can't compare one field to another.

            – Adrian Larson
            35 mins ago

















          Thanks @adrian. Why wouldn't it work from a child relationship? select id,Agent_Org_Unit_Code__c,Agent__r.Callista_Org_Unit_Code__c from Application__c where Agent__r.Callista_Org_Unit_Code__c!=Agent_Org_Unit_Code__c

          – Shalini SF
          1 hour ago





          Thanks @adrian. Why wouldn't it work from a child relationship? select id,Agent_Org_Unit_Code__c,Agent__r.Callista_Org_Unit_Code__c from Application__c where Agent__r.Callista_Org_Unit_Code__c!=Agent_Org_Unit_Code__c

          – Shalini SF
          1 hour ago













          @ShaliniSF You can't compare one field to another.

          – Adrian Larson
          35 mins ago





          @ShaliniSF You can't compare one field to another.

          – Adrian Larson
          35 mins ago


















          draft saved

          draft discarded




















































          Thanks for contributing an answer to Salesforce Stack Exchange!


          • Please be sure to answer the question. Provide details and share your research!

          But avoid



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.




          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsalesforce.stackexchange.com%2fquestions%2f249905%2fsubquerying-filtering-on-main-query-field%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown





















































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown

































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown







          Popular posts from this blog

          Knooppunt Holsloot

          Altaar (religie)

          Gregoriusmis