-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 2 posts ] 
Author Message
 Post subject: in clause with subquery on a class with multiple column key
PostPosted: Fri Oct 17, 2008 5:38 am 
Newbie

Joined: Tue Sep 16, 2008 4:36 am
Posts: 11
Hi everybody,

Here is my problem, I am trying to write a query like this :

from MyClass cls where cls in (select cls2.cls from MyOtherClass)

The problem is that MyClass has a multiple column key (key1,key2,key3)
Because there are three elements that identifies my object of class MyClass.

Now the issue:

We are using MSSQL 2005 and the query that has been produced doesn't work. The problem comes from the in clause on the subquery.

I am not sure but it looks like having a subquery with multilpe columns doesn't work. Can someone help me knowing why it doesn't work and why am I doing wrong? Thanks in advance

Here is the HQL query:
Code:

from Invoice inv where (inv.shipments.size=:param1 and inv.key.invoiceNumber in ( select key.Invoice from ItemPosition it))


Here is the generated query:
Code:
    select
        invoice0_.INVOICENUMBER as INVOICEN1_38_,
        invoice0_.deliveryNumber as delivery2_38_,
        invoice0_.IDINVOICEPLANT as IDINVOIC3_38_,
    from
        Invoice invoice0_
    where
        (
            select
                count(shipments1_.InvoiceNumber)
            from
                ShipmentInvoices shipments1_
            where
                invoice0_.INVOICENUMBER=shipments1_.InvoiceNumber
                and invoice0_.deliveryNumber=shipments1_.deliveryNumber
                and invoice0_.IDINVOICEPLANT=shipments1_.idInvoicePlant
        )=?
        and (
            (
                invoice0_.INVOICENUMBER, invoice0_.deliveryNumber, invoice0_.IDINVOICEPLANT
            ) in (
                select
                    itempos2_.InvoiceNumber,
                    itempos2_.deliveryNumber,
                    itempos2_.idInvoicePlant
                from
                    ItemPosition itempos2_
            )
        )
        )


Top
 Profile  
 
 Post subject: answer for the problem, still fighting for an answer
PostPosted: Fri Oct 17, 2008 7:42 am 
Newbie

Joined: Tue Sep 16, 2008 4:36 am
Posts: 11
Hi everybody,

After some search, it looks like SQL Server cannot do a "in" on multiple columns. This is why it doesn't work. I will try to do a join to see if it resolves my problem and I'll let you know.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 2 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.