[eclipselink-users] JPQL : Not able to use IN clause on only the values

[bhakti b <bhaktikeni@xxxxxxxxx>]

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.