I want That the enddate is less than or equal to 'request_timestamp' The enddate here is all the time less than, I tried:

• &lte; : isnt accepted while deploying
• <= : same behviour as <

The >= is same as > (it's OK because I need >= )

<customdblookup>
        <connection>
            <pool>
                <dsName>jdbc/AuditDB</dsName>
            </pool>
        </connection>
        <statement>
            <sql>
              SELECT c.transaction_id, c.application_id, c.institute, c.legal_context, substring(c.endpoint, '[^/]*$') as endpoint, c.action, 
              to_char(c.request_timestamp, 'yyyy-mm-dd') || 'T' || to_char(c.request_timestamp,'hh24:mi:ss.MS') request_timestamp , c.response 
              FROM public.consumer_audit c 
              WHERE c.key_value ~ ? AND c.request_timestamp &gt;= ? AND c.request_timestamp &lt;= ?
            </sql>
            <parameter expression="$ctx:personNumber" type="VARCHAR"/>
            <parameter expression="$ctx:startDate" type="DATE"/>
            <parameter expression="$ctx:endDate" type="DATE"/>
            <result column="transaction_id" name="transaction_id" />
            <result column="application_id" name="application_id" />
            <result column="institute" name="institute" />
            <result column="legal_context" name="legal_context" />
            <result column="endpoint" name="endpoint" />
            <result column="action" name="action" />
            <result column="request_timestamp" name="request_timestamp" />
            <result column="response" name="response" />
        </statement>
    </customdblookup>

Thanks for your time; Regards,

CodePudding user response：

Maybe you can surround the query with <![CDATA[...]]> in order to compare by < or > sign

CodePudding user response：

You are using <customdblookup> so, it's not standard wso2 mediator? In WSO2 DB Lookup mediator is example that shows, sql statement surrounded with CDATA:

<sql><![CDATA[select * from company where name =?]]></sql>

This is from this documentation: WSO2 DBLookup Mediator.