Thanks for the suggestions everyone.  An update: What I ended up doing is post processing both the View and the Query that was already working very well.  The join of these two was very efficient in the front end as local cursors.  Alls well that ends well :)

Kind regards

Bhavbhuti

 

Hi,

 

is this your real query? I ask, because  below WHERE can be not intentionaly written by OR

 

WHERE     (
                    heir.iaccttreeid = 47
          AND       cast(tsi.tdt AS date) < '2018-04-01' )
OR        (
                    heir.iaccttreeid <> 47
          AND       cast(tsi.tdt AS date) BETWEEN '2018-03-01' AND       '2018-03-31' )
AND       tsi.cdoctype NOT LIKE 'PROFORMA%'
AND       tsi.cdoctype NOT LIKE 'CANCELLED%'
AND       ssifooter.cdrcr IN ('DR',
                              'CR')

i suppose you need more parenthesis:

 

WHERE    

(

        (

            heir.iaccttreeid = 47

            AND

            cast(tsi.tdt AS date) < '2018-04-01'

        )

    OR       

        (

            heir.iaccttreeid <> 47

            AND      

            cast(tsi.tdt AS date) BETWEEN '2018-03-01' AND       '2018-03-31'

        )

)       

AND       tsi.cdoctype NOT LIKE 'PROFORMA%'

AND       tsi.cdoctype NOT LIKE 'CANCELLED%'

AND       ssifooter.cdrcr IN ('DR',

                              'CR')

 

regards,

Karol Bieniaszewski

Virus-free. www.avg.com

-- 


Thanking you.

Yours Faithfully,
For Venus Software Operations
----
Mr. Bhavbhuti Nathwani
___________________________________________
Softwares for Indian Businesses at: http://www.venussoftop.com

venussoftop@...
venussoftop@...
___________________________________________

Please note: We reserve complete rights for policy changes in the future and the same will be applicable immediately as and when made.  Attachments may get corrupted before reaching you, in such a situation please let us know and we will resend you the same at the earliest.  We do not take any responsibility for data loss of any type and kind.  Data safety remains the sole the responsibility of the users of our softwares.
___________________________________________

Internet email confidentiality:

This message may contain information that may be privileged or confidential.  If you are not the addressee nor are you responsible for the delivery of the message to the addressee indicated in this email, then you may not copy or deliver this email to anyone and you should notify the sender by reply email and then destroy this message.

Please reply email immediately to this message with REMOVE in the subject, if you or your employer do not consent to email of this kind.

Opinions, conclusions and other information in this message that do not relate to the official business of my firm shall be understood as neither given nor endorsed by my company.