EXISTS and NOT EXISTS condition

[wkim]

Hi,

I am working on a report about gettings # of orders in the last 12 months, but not the last 6months.

I tried so many times with EXISTS and NOT EXISTS conditions, but I could not get the result.

Here is my query look like:

Where o.odr_type = 'S' and o.odr_status != 'D'
             and Not Exists (select o.ordernum
                                          from orders o
                                          inner join payments p
                                          on o.paynum = p.paynum
                                          where o.odr_date between DATEADD(mm,-6,current_timestamp) and current_timestamp)
             and Exists (select o.ordernum from orders o Inner join payments p
                             on o.paynum = p.paynum
                             where o.odr_date between DATEADD(mm,-12,current_timestamp) and                                                                            

                             DATEADD(mm,-6,current_timestamp))

 

I don't think iReport support EXIST and NOT EXIST conditions. Well, I think it doesn't fully support SQL commands anyway.

Please give me a way to solv this. Many Thanks!!!!

[gdmoreno]

Using a database client like Squirrel, have you made sure that the query executes properly? What errors does iReport return?

Another possibility would be to create a stored procedure. Then in iReport, you would just call the stored procedure. What database are you using?

 

[wkim]

@gdmoreno: The DB that I'm using is SQL, and I'm using MSSQL JDBC driver to connect iReport and the DB Server.

Actually, iReport doesn't say anything. I wish it shows me some error messages so that I know what is wrong with it.

I don't think I can run stored procedure, since I'm just generating report. My computer at work doesn't have MS SQL Server.

 

The one thing that I know is if I take out Exists condition from query like this:

Where o.odr_type = 'S' and o.odr_status != 'D'
             and o.odr_date between DATEADD(mm,-12,current_timestamp) and DATEADD(mm,-6,current_timestamp)

It obviously returns the value. But if I put Exists, it doesn't.

Does anyone use Exists condition for your report? How could you do that?

Please, help me out...