I need to create a IN clause on a temp table:

IN (Select id from TempTable).

Is there a way to do that with FieldCompareSetPredicate? The temp table clearly doesn't have an Entity class associated with.

Do you create the temp table using LLBLGen code? Or is it created by an external mean?

It's created by an external mean, unfortunately.

Walaa wrote:

Do you create the temp table using LLBLGen code? Or is it created by an external mean?

Is it an actual sql temp table (ie is the name prefixed with # (connection scope) or ## (global scope)) ?

Or is it an actual physical table which is created for a temporary purpose...?

Matt

Sorry I didn't make myself clear. It's a physical table created for a temporary purpose.

MTrinder wrote:

Is it an actual sql temp table (ie is the name prefixed with # (connection scope) or ## (global scope)) ?

Or is it an actual physical table which is created for a temporary purpose...?

Matt

jia wrote:

Sorry I didn't make myself clear. It's a physical table created for a temporary purpose.

If it's a physical table created by you, then you can add it to your LLBLGen project so you would be able to use it when constructing LLBLGen queries. Am I right?

If you don't do that, I don't see other option than use your own stored procedure. Then you can project the stored procedure result into an entity collection.

The problem is that the physical table is generated by the code, and the table name varies on the running condition. I shouldn't be able to add it to my LLBLGen project, right?

I wanted to generate the following query to "SELECT" everything but only "GROUP BY" on VISITOR_ID column so the HAVING condition is calculated on VISITOR_ID level. I guess I need a subquery or a temporal physical table (the IN clause):

SELECT [SESSION].[VISITOR_ID] AS [VISITOR_ID], [SESSION].[ID] AS [SESSION_ID], ** Count([EVENT_OCCURRENCE].ID) OVER(PARTITION BY [SESSION].[VISITOR_ID]) AS [MATCHES] ** FROM ( [SESSION] INNER JOIN [EVENT_OCCURRENCE]
ON [SESSION].[ID]=[EVENT_OCCURRENCE].[SESSION_ID])

  WHERE ( ( [EVENT_OCCURRENCE].[EVENT_ID] = 50) 
  AND ( [SESSION].[END] >= '11/01/2010 12:00:00 AM' 
  AND [SESSION].[END] <= '11/01/2010 11:59:59 PM') 
  AND ( [SESSION].[VISITOR_ID] <> 0)
  AND [SESSION].[VISITOR_ID] IN (**SELECT [SESSION].[VISITOR_ID] AS [VISITOR_ID] 
  FROM ( [SESSION]  INNER JOIN [EVENT_OCCURRENCE]  
  ON  [SESSION].[ID]=[EVENT_OCCURRENCE].[SESSION_ID]) 
  WHERE ( ( [EVENT_OCCURRENCE].[EVENT_ID] = 50) 
  AND ( [SESSION].[END] >= '11/01/2010 12:00:00 AM' 
  AND [SESSION].[END] <= '11/01/2010 11:59:59 PM') 
  AND ( [SESSION].[VISITOR_ID] <> 0)) 
  GROUP BY [SESSION].[VISITOR_ID]
  HAVING ( COUNT([EVENT_OCCURRENCE].[ID]) >= 2))**)

This is for SQL Server 2008 R2. SESSION and EVENT_OCCURENCE are both in the LLBLGEN project.

I intend to make the subquery result into a temporal table, then either use the IN clause or JOIN.

Could derived table be used if I use JOIN instead of IN?

daelmo wrote:

jia wrote:

Sorry I didn't make myself clear. It's a physical table created for a temporary purpose.

If it's a physical table created by you, then you can add it to your LLBLGen project so you would be able to use it when constructing LLBLGen queries. Am I right?

If you don't do that, I don't see other option than use your own stored procedure. Then you can project the stored procedure result into an entity collection.

You can reproduce that query with some subqueries using FieldCompareSetPredicate, without temp tables. Do you have problems to reproduce the above query in LLBLGen?

I don't know how to do that. Can FieldCompareSetPredicate be defined to generate such a complex query?

daelmo wrote:

You can reproduce that query with some subqueries using FieldCompareSetPredicate, without temp tables. Do you have problems to reproduce the above query in LLBLGen?

What are you actually trying to achieve with the OVER (PARTITION BY...) section of the query...?

Matt

Do the count calculation as if GROUP BY by VISITOR_ID. Essentially I want a SQL statement that selects VISITOR_ID, SESSION_ID, COUNT(EVENT_ID) with a filter condition on COUNT that is only grouped on VISITOR level, not on both VISITOR and SESSION. A visitor can have multiple sessions and a session can have multiple event occurrences.

The window function is not a big problem ,as I can hardcode it into generated SQL. My main challenge is on how to generate the subquery, as I don't want to hardcode the IN clause.

If there are other means without using subquery or multiple querys, it would be great too.

Thanks.

MTrinder wrote:

What are you actually trying to achieve with the OVER (PARTITION BY...) section of the query...?

Matt

How many records should the subQuery return? In which magnitude.

One solution I'd take is fetch the resultSet instead of saving them in a temp table, and then use the FieldCompareRangePredicate and pass these values as the range.

Another solution: If the temp table's struture is well known beforehand, then you might want to consider fixing it as a permenant table, and so you can use it in LLBLGen, these will make yourlife simpler afterwards.

And most probably you might need to add an extra column (Token) to differentiate between different resultsets being saved in the table at runtime. And you might consider clearing the specified resultSet once you have finished using them.

Thanks for all the input. I will give them a try.

Walaa wrote:

How many records should the subQuery return? In which magnitude.

One solution I'd take is fetch the resultSet instead of saving them in a temp table, and then use the FieldCompareRangePredicate and pass these values as the range.

Another solution: If the temp table's struture is well known beforehand, then you might want to consider fixing it as a permenant table, and so you can use it in LLBLGen, these will make yourlife simpler afterwards.

And most probably you might need to add an extra column (Token) to differentiate between different resultsets being saved in the table at runtime. And you might consider clearing the specified resultSet once you have finished using them.