Help with having clause

ID            
    discriminator     SOURCE_RECORD_DATE  
              ADDRESS_1

EXTERNAL_IDENTIFIER  ORGANIZATION

19766                
BillTo                
2009-08-12 10:34:43.893         4567 Test Dr.

31295-90                
AMER

19775                
BillTo                
2009-08-20 16:18:19.27            
    4567 Test Dr.        
        31295-90        
        AMER

19776                
BillTo                
2009-08-20 16:55:07.02            
    4567 Test Dr.        
        31295-90        
        AMER
Looking at the data, the records are virtually identical
all except for the SOURCE_RECORD_DATE. So basically I'm attempting a SELECT
based upon the records EXTERNAL_IDENTIFIER and the ORGANIZATION, which
will match all 3 records. So given a date of 2009-08-20 16:30:00.00, I
would like to retrieve the record that has a SOURCE_RECORD_DATE that has
the greatest value that is equal to or less than the supplied date, that
would be the row that has id 19775. If my understanding is correct, the
only way that I am going to retrieve the single matching row is to use
a HAVING clause to further filter the matching rows.
Hello,

I'm not sure I understand.  Are you trying to query data filtering
on 

the primary key?   If so, can you not just use entitymanager.find(class,

19775)

or use the Id field in a where clause as in "SELECT data FROM Class
data 

WHERE data.id=19776"? 

Having is used to filter data already filtered and grouped with the 

Where and Group By clauses.  If you are not grouping items, just use
a 

where clause.

Regards,

Chris

cmathrusse wrote:

> I'm using EclipseLink 1.1.1 running against Sybase ASE 15. The data
I am

> working with looks as follows:

>

> ID                
discriminator              
  SOURCE_RECORD_DATE          
      ADDRESS_1        
        EXTERNAL_IDENTIFIER

> ORGANIZATION

> 19766                
BillTo                
2009-08-12 10:34:43.893            
    4567 Test Dr.        
        31295-90        
        AMER

> 19775                
BillTo                
2009-08-20 16:18:19.27            
    4567 Test Dr.        
        31295-90        
        AMER

> 19776                
BillTo                
2009-08-20 16:55:07.02            
    4567 Test Dr.        
        31295-90        
        AMER

>

> I would like to write a query that would retrieve only the middle
row (ID =

> 19775) but I seem to be having some difficulty trying to locate the
correct

> SQL/JPAQL. Perhaps its my limited understanding but any assistance
would be

> greatly appreciated. I know that the HAVING clause should not be used

> without a GROUP BY and I guess that is where my difficulty lies.

>

>

> Thanks in advance.

>   

_______________________________________________

eclipselink-users mailing list

eclipselink-users@...

https://dev.eclipse.org/mailman/listinfo/eclipselink-users

You can use a sub-select for this.

Something like: (JPQL)

Select object from MyObject object where object.externalIdentifier = :id
and

object.organization = :org and object.sourceRecordDate = (Select

Max(inner.sourceRecordDate) from MyObject inner where

inner.externalIdentifier = :id and inner.organization = :org and

inner.sourceRecordDate <= :date)

cmathrusse wrote:

> 

> Well, if my situation was that simple I would use em.find(...) My

> apologies for not explaining it better.

> 

> ID               discriminator
    SOURCE_RECORD_DATE            ADDRESS_1

>          EXTERNAL_IDENTIFIER  ORGANIZATION

> 19766            BillTo    
     2009-08-12 10:34:43.893        
4567 Test 

> Dr.              31295-90  
             AMER

> 19775            BillTo    
     2009-08-20 16:18:19.27        
 4567 Test 

> Dr.              31295-90  
             AMER

> 19776            BillTo    
     2009-08-20 16:55:07.02        
 4567 Test 

> Dr.              31295-90  
             AMER

> 

> 

> Looking at the data, the records are virtually identical all except
for 

> the SOURCE_RECORD_DATE. So basically I'm attempting a SELECT based
upon 

> the records EXTERNAL_IDENTIFIER and the ORGANIZATION, which will match
all 

> 3 records. So given a date of 2009-08-20 16:30:00.00, I would like
to 

> retrieve the record that has a SOURCE_RECORD_DATE that has the greatest

> value that is equal to or less than the supplied date, that would
be the 

> row that has id 19775. If my understanding is correct, the only way
that I 

> am going to retrieve the single matching row is to use a HAVING clause
to 

> further filter the matching rows.

> 

> Thanks...

> Chris 

> 

> 

-----

http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland

http://www.eclipse.org/eclipselink/

 EclipseLink ,  http://www.oracle.com/technology/products/ias/toplink/

TopLink 

Wiki:  http://wiki.eclipse.org/EclipseLink EclipseLink , 

http://wiki.oracle.com/page/TopLink TopLink 

Forums:  http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink
, 

http://www.nabble.com/EclipseLink-f26430.html EclipseLink 

Book:  http://en.wikibooks.org/wiki/Java_Persistence Java Persistence

-- 

View this message in context: http://www.nabble.com/Help-with-having-clause-tp25138774p25151372.html

Sent from the EclipseLink - Users mailing list archive at Nabble.com.

_______________________________________________

eclipselink-users mailing list

eclipselink-users@...

https://dev.eclipse.org/mailman/listinfo/eclipselink-users

Help with having clause

Help with having clause

Re: Help with having clause

Re: Help with having clause

Re: Help with having clause

Re: Help with having clause