Hi All,
I am stuck in writing one complex SQL in HQL. I have tried my best but no luck!!!
Here I am pasting my SQL query containing some complex joins, if anybody can help, it would be highly appreciated. Its Urgent too!!!
SQL Query:
Code:
select distinct top 10
product.product_id OID, product.product_name NAME, product.product_name, product.submittal_type,
company.company_id, company.company_name, lower (product.product_name)
,(RTRIM(product.product_name) + CAST(product.product_id as nvarchar)) as unique_column
FROM dp_product AS product
JOIN dp_company AS company
ON product.company_id = company.company_id
LEFT JOIN dp_prod_map appmap
ON product.product_id = appmap.product_id
LEFT JOIN dp_prod_map osmap
ON product.product_id = osmap.product_id
LEFT JOIN dp_prod_map hwmap
ON product.product_id = hwmap.product_id
WHERE ( LOWER (product.product_name) like '%alterpath%') AND ( LOWER (company.company_name) like '%cyclades%' ) AND ( company.country IN ('US')) AND ( (
appmap.prod_map_id IN (98703)
) AND (
osmap.prod_map_id IN (61302)
) AND (
hwmap.prod_map_id IN (61205)
) ) AND (( lower (product.product_name) like '%windows%' ) OR( lower(CAST (product.long_desc AS varchar(8000))) like '%windows%' OR product.long_desc like '%windows%' ) ) AND
product.submittal_type = 64101
AND ((product.product_id IN (select product_id FROM
dp_company_product_distributor_map AS sub_dist_map
WHERE sub_dist_map.distributor_id IS NULL) )
OR (product.company_id = company.company_id)) AND
(product.admin_status= 98206
OR product.admin_status= 98201 )
AND product.isWithdrawn!=1
AND company.mop_flag=1
AND company.account_status= 62002
Converted HQL Query:Code:
[quote]select comp.CompanyId, comp.CompanyName, prod.ProductId, prod.ProductName, prod.SubmittalType" +
" from DpProduct prod" +
" join prod.DpCompany as comp" +
" join prod.SetOfDpProdMap.id.DpProduct as appmap" +
" join prod.SetOfDpProdMap.id.DpProduct as osmap" +
" join prod.SetOfDpProdMap.id.DpProduct as hwmap" +
" where (LOWER (prod.ProductName) like '%alterpath%') AND" +
" (LOWER (comp.CompanyName) like '%cyclades%') AND" +
" (comp.Country IN ('US')) AND" +
" ((appmap.SetOfDpProdMap.id.DpTaxonomy.TaxonomyId IN (98703)) AND" +
" (osmap.SetOfDpProdMap.id.DpTaxonomy.TaxonomyId IN (61302)) AND" +
" (hwmap.SetOfDpProdMap.id.DpTaxonomy.TaxonomyId IN (61205))) AND" +
" ((LOWER (prod.ProductName) like '%windows%') OR (" +
" prod.LongDesc like '%windows%')) AND" +
" prod.SubmittalType = 64101 AND" +
" ((prod.ProductId IN (select md.id.DpProduct.ProductId FROM" +
" DpCompanyProductDistributorMap AS md WHERE md.id.DpDistributor.DistributorId IS NULL))" +
" OR (prod.DpCompany.CompanyId = comp.CompanyId)) AND" +
" (prod.AdminStatus = 98206 OR prod.AdminStatus = 98201) AND" +
" prod.IsWithdrawn <> 1 AND comp.MopFlag = 1 AND comp.AccountStatus = 62002[/quote]
The above HQL query is not giving any results and same SQL qyery returing two rows. I tried to use createSQLQuery() method also so that I can directly use this native SQL query, but no luck. In logs it is showing that 2 rows returned, but when I tried to retrieve them from the list collection and iterated it , it is throwing exception.
I am really helpless now. Please help!!! :(
Login
FAQ
Search