Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
Silver Contributor
Posts: 835
Registered: ‎03-24-2009

HQL Gone Wild!

Well this is about as ugly as it gets.....

 

The SQL is, basically.

Account to Opp Associated Accounts to Opportunity.

Show me Opportunity Description, Status, Account Manager, Date Opened, Estimated Close, Sales Potential.

I know SLX\you need to get to UserInfo to show the Account Manager's name.....and SecRights to tie the loop on Opp.SECCODEID

 

but this is a real rat's nest:

 

The HQL is: attached.

 

 

Bottom Line:

 

I'm getting NHibernate errors on this based on data not being 'right'......possibly a recursively looked at Account Record's SecCodeID.....

 

Question: is it 'safer' to just create a SQL view and be done with it? I need to filter the grid at user picklist selection time....two data sources (iList, GetByMethod).

 

 

RJ Samp
Silver Contributor
Posts: 835
Registered: ‎03-24-2009

Re: HQL Gone Wild!

Here's the same datagrid layout, without a filter, using a 'normal' grid datasource with Smart Columns, paging and sorting enabled:

 

Much Calmer (and faster) and NO NHibernate errors:

 

---------- Client SQL ---------
SELECT TOP 10 child1_.OPP_ASSOC_ACCOUNTID as y0_, opportunit2_.ACCOUNTMANAGERID as y1_, opportunit2_.DESCRIPTION as y2_, opportunit2_.STATUS as y3_, opportunit2_.OPPORTUNITYID as y4_, opportunit2_.SALESPOTENTIAL as y5_, opportunit2_.DATEOPENED as y6_, opportunit2_.ESTIMATEDCLOSE as y7_, child1_.ACCOUNTROLE as y8_, opportunit2_.PRODUCTLINE as y9_ FROM ACCOUNT this_ inner join OPP_ASSOC_ACCOUNT child1_ on this_.ACCOUNTID=child1_.ACCOUNTID inner join OPPORTUNITY opportunit2_ on child1_.OPPORTUNITYID=opportunit2_.OPPORTUNITYID WHERE this_.ACCOUNTID = [DBTYPE_STR,"A6UJ9A0003V3"] WITH FIRSTROW 1  
---------- Executed SQL ----------
SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as SLXRN,  child1_.OPP_ASSOC_ACCOUNTID as y0_, opportunit2_.ACCOUNTMANAGERID as y1_, opportunit2_.DESCRIPTION as y2_, opportunit2_.STATUS as y3_, opportunit2_.OPPORTUNITYID as y4_, opportunit2_.SALESPOTENTIAL as y5_, opportunit2_.DATEOPENED as y6_, opportunit2_.ESTIMATEDCLOSE as y7_, child1_.ACCOUNTROLE as y8_, opportunit2_.PRODUCTLINE as y9_,THIS_.SECCODEID SLXSECCODEID12,OPPORTUNIT2_.SECCODEID SLXSECCODEID13 FROM ACCOUNT this_ INNER JOIN SECRIGHTS S_AA ON (S_AA.ACCESSID = 'UNRC2B00009I' AND THIS_.SECCODEID = S_AA.SECCODEID )  inner join OPP_ASSOC_ACCOUNT child1_ on this_.ACCOUNTID=child1_.ACCOUNTID inner join OPPORTUNITY opportunit2_ on child1_.OPPORTUNITYID=opportunit2_.OPPORTUNITYID INNER JOIN SECRIGHTS S_AB ON (S_AB.ACCESSID = 'UNRC2B00009I' AND OPPORTUNIT2_.SECCODEID = S_AB.SECCODEID )   WHERE this_.ACCOUNTID = ?) query WHERE SLXRN >= 1 AND SLXRN < 11 ORDER BY SLXRN

RJ Samp
Bronze Super Contributor
Posts: 236
Registered: ‎03-30-2009

Re: HQL Gone Wild!

[ Edited ]

RJ - assuming your entity is named 'OppAssociatedAccount' and contains a relationship to the current account and an opportunitiy your HQL would probably need to look something like this:

 

OppAssociatedAccounts oa inner join oa.Opportunity o inner join oa.Account a

 

Note the following:

  • In HQL you leave the conditions off the join as it uses the relationships
  • Joins to seccode will be done by the provider - you should not need to join to it here
  • AccountmanagerId from the opp table will suffice as you would make it a user column in the grid the same way you always would

Then for the grid, bind your HQL datasource where clause to the account.Id property using a format string: a.Id = '{0}'

Mike LaSpina
Professional Services Consultant

Sage
8800 N Gainey Center Dr Ste 200
Scottsdale AZ 85258
Tel: 480-383-5344
Cell: 480-321-6637
Fax: 480-556-4090
mike.laspina@sage.com

Your Business in Mind.
Silver Contributor
Posts: 835
Registered: ‎03-24-2009

Re: HQL Gone Wild!

It's working as loong as ALL of the data is correct.....we're blowing up on NULLOpp. DateOpened values.....NULLOpp. AccountManagerID's......

 

and why the huge select statement for simply retrieving the ACCOUNT NAME (and the SECCODEID).......

 

IT's a huge performance hit.....

RJ Samp
Bronze Super Contributor
Posts: 236
Registered: ‎03-30-2009

Re: HQL Gone Wild!

Interesting - did you specify the columns in the HQL datasource?  If you leave these out, I beleives it defaults to retrieving all properties.  I generally find that HQL datasources perform much better - especially on large data sets - than the old style getby datasources do.

Mike LaSpina
Professional Services Consultant

Sage
8800 N Gainey Center Dr Ste 200
Scottsdale AZ 85258
Tel: 480-383-5344
Cell: 480-321-6637
Fax: 480-556-4090
mike.laspina@sage.com

Your Business in Mind.
Silver Contributor
Posts: 835
Registered: ‎03-24-2009

Re: HQL Gone Wild!

yes. basically we asked for Opportunity.Account.AccountName......and HQL retrieved the entire Account fields (see the first post attachment).. We knew it would go after Account.SeccodeID.....never thought it would go after the entire entity.

RJ Samp
Silver Contributor
Posts: 835
Registered: ‎03-24-2009

Re: HQL Gone Wild!

Found it!

 

The HQL referenced localentity.Opportunity.Account 

 

this returns the entire Account Entity......

 

Should have been ocalentity.Opportunity.Account.AccountName

 

Now it's working!!!

RJ Samp
Bronze Super Contributor
Posts: 236
Registered: ‎03-30-2009

Re: HQL Gone Wild!

Ahh - that would definately explian it.  You probably didn't notice as the Account string expression is the name.

Mike LaSpina
Professional Services Consultant

Sage
8800 N Gainey Center Dr Ste 200
Scottsdale AZ 85258
Tel: 480-383-5344
Cell: 480-321-6637
Fax: 480-556-4090
mike.laspina@sage.com

Your Business in Mind.