Re: [eclipselink-users] Help with having clause

[Christopher.Mathrusse@xxxxxxxxxx]

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

Re: [eclipselink-users] Help with having clause

christopher delahunt

to:

EclipseLink User Discussions

08/25/2009 10:50 AM

Sent by:

eclipselink-users-bounces@xxxxxxxxxxx

Please respond to EclipseLink User Discussions

---

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@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users