[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-users] JPQL : Not able to use IN clause on only the values of an element collection map

Hi Bhakti,

JPQL provides a VALUE keyword that lets you navigate to map values. Have you tried that keyword?

-Tom

On 29/11/2012 11:56 PM, bhakti b wrote:
I am querying a map using JPQL, but am not able to use the IN clause on only
the values of the map. I do not want to have the key columns when using the
IN clause. Can anyone suggested a way to get this working


The following is the setup

The ProductGroup entity mapping :
	<entity class="com.test.ProductGroup"
		name="com.test.ProductGroup">
		

		
		<attributes>
			<embedded-id
				attribute-type="com.test.ProductGroupKey"
				name="productGroupKey">
				<attribute-override name="productGroupCode">
					<column name="PRODUCT_GROUP_CODE" />
				</attribute-override>
			</embedded-id>
			
			<element-collection name="groupCreditPolicyLinkage">
				<map-key-column name="BUSINESS_UNIT" />
				<column name="CREDIT_POLICY_ID" />
				<collection-table name="CREDIT_TEMP_LINK">
					<join-column name="PRODUCT_GROUP_CODE"
						referenced-column-name="PRODUCT_GROUP_CODE" />
				</collection-table>
			</element-collection>


</attributes> </entity> <embeddable class="com.test.ProductGroupKey"> <attributes> <basic name="productGroupCode" attribute-type="java.lang.String"> <column name="PRODUCT_GROUP_CODE" /> </basic> </attributes> </embeddable>

The CreditPolicyTemplate mapping :
	
	<entity
		class="com.test.CreditPolicyTemplate"
		name="com.test.CreditPolicyTemplate">
		

		<attributes>
			<embedded-id
				attribute-type="com.test.CreditPolicyTemplateKey"
				name="creditPolicyTemplateKey">
				<attribute-override name="creditPolicyTemplateId">
					<column name="ID" />
				</attribute-override>
			</embedded-id>
			
			<element-collection attribute-type="java.util.Set"
				fetch="EAGER" name="allowedPurposeCodes">
				<cascade />
				<collection-table name="ALLOWED_PURPOSES">
					<join-column name="CREDIT_POLICY_TMPLT_ID"
						referenced-column-name="ID" />
				</collection-table>

			</element-collection>
			
		</attributes>	
	</entity>

	<embeddable
		class="com.test.CreditPolicyTemplateKey">
		<attributes>
			<basic name="creditPolicyTemplateId" attribute-type="java.lang.String">
				<column name="ID" />
			</basic>
		</attributes>
	</embeddable>


<embeddable name="AllowedPurposeCodes" class="com.test.AllowedPurposeCodes" access="FIELD"> <attributes> <basic name="allowedPurposeCodes"> <column name="PURPOSE_CODE" /> </basic> </attributes> </embeddable>

The Query :
	<named-query name="fetchAllowedPurposeCodes">
		<query>
		select distinct
		a.allowedPurposeCodes from com.test.CreditPolicyTemplate
		c JOIN FETCH c.allowedPurposeCodes a where
c.creditPolicyTemplateKey.creditPolicyTemplateId IN
		(	select
			lnk
			from
			com.test.ProductGroup p JOIN
			p.groupCreditPolicyLinkage lnk
			where
			p.productGroupKey.productGroupCode in
			(:productGroup)
			)

		</query>
	</named-query>
	
	
Basically the requirement in the above query is :
p.groupCreditPolicyLinkage is map of the type Map<String, String> where the
BUSINESS_UNIT column is mapped to the key and the CREDIT_POLICY_ID column is
mapped to the value.
The IN clause above should check the value field (that is CREDIT_POLICY_ID)
and NOT the key field (i.e BUSINESS_UNIT).
Currently the above query when executed, throws : ORA-00913: too many values


Generated Query : SELECT DISTINCT t0.PURPOSE_CODE FROM ALLOWED_PURPOSES t0, CREDIT_POLICY t1 WHERE (t1.ID IN (SELECT DISTINCT *t2.CREDIT_POLICY_ID, t2.BUSINESS_UNIT* FROM PRODUCT_GRP_ALL t3, CREDIT_TEMP_LINK t2 WHERE ((t3.PRODUCT_GROUP_CODE IN (?)) AND (t2.PRODUCT_GROUP_CODE = t3.PRODUCT_GROUP_CODE))) AND (t0.CREDIT_POLICY_TMPLT_ID = t1.ID))

	
*Required Query :*
SELECT
	DISTINCT t0.PURPOSE_CODE FROM ALLOWED_PURPOSES t0, CREDIT_POLICY t1
	WHERE (t1.ID IN
			(SELECT DISTINCT *t2.CREDIT_POLICY_ID*
				FROM PRODUCT_GRP_ALL t3, CREDIT_TEMP_LINK t2
				WHERE ((t3.PRODUCT_GROUP_CODE IN (?))
				AND (t2.PRODUCT_GROUP_CODE = t3.PRODUCT_GROUP_CODE)))
	AND (t0.CREDIT_POLICY_TMPLT_ID = t1.ID))




-- View this message in context: http://eclipse.1072660.n5.nabble.com/JPQL-Not-able-to-use-IN-clause-on-only-the-values-of-an-element-collection-map-tp156050.html Sent from the EclipseLink - Users mailing list archive at Nabble.com. _______________________________________________ eclipselink-users mailing list eclipselink-users@xxxxxxxxxxx https://dev.eclipse.org/mailman/listinfo/eclipselink-users