Hi,
A little background. The problem i am solving is a bit similar to this
simplified version.
As an admin of a mooc site you want to track how many people have
completed, started in enrolled in various course enrollments over the past
1 year and plot them in a single graph
for each user, course a record is present in a course status table which
has user id,course id,enrolled date,started date, completed date etc
SelectConditionStep<Record> selectStmnt =
create.select(COURSE_STATUS.USER_ID,COURSE_STATUS.COURSE_ID,COURSE_STATUS.STARTED_DATE,COURSE_STATUS.COMPLETED_DATE,COURSE_STATUS.ENROLLED_DATE)
.from(COURSE_STATUS)
.where(extract(COURSE_STATUS.ENROLLED_DATE,DatePart.YEAR).eq(2014));
dslContext.execute("CREATE TEMP TABLE TempTable as {0}", selectStmnt);
dslContext.select(
field(COURSE_STATUS.COURSE_ID.getName()), DSL.count())
.from("TempTable")
.where(field(COURSE_STATUS.COMPLETION_DATE.getName()).isNotNull())
.groupBy(field(COURSE_STATUS.COURSE_ID.getName()))
.fetch();
So on for started, enrolled etc.
The System has lot more filteration params like location,sex,age group etc
and more data points can be viewed as passed the course,failed it, etc
Thanks,
Gokul
On Wednesday, July 9, 2014 4:43:34 PM UTC+5:30, Lukas Eder wrote:
>
> Hi Gokul,
>
> It's hard to say from your description. Maybe, share a little code to see
> where you're going and to see whether that's a good idea?
>
> Cheers
> Lukas
>
> 2014-07-09 12:57 GMT+02:00 <[email protected] <javascript:>>:
>
>> Hi,
>>
>> I figured out a way in which i call getName on the original generated
>> column and do a select on the new table. That seems to work well for post
>> gres.
>> I am very new to sql so i am not sure if this is a best practice of using
>> only the column name without the schema and table names.
>>
>> Thanks,
>> Gokul
>>
>>
>> On Wednesday, July 9, 2014 4:10:10 PM UTC+5:30, [email protected] wrote:
>>>
>>> Thanks Lukas.
>>> That fills the bill nicely.
>>>
>>> Is there any way i can use the earlier jooq generated column names to
>>> refer to the columns names in the table or should i use sql builder with
>>> strings to query this table.
>>>
>>> Thanks,
>>> Gokul
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> On Wednesday, July 9, 2014 3:26:41 PM UTC+5:30, Lukas Eder wrote:
>>>>
>>>> Hello,
>>>>
>>>> This is currently not supported by jOOQ and we probably won't support
>>>> it as PostgreSQL documentation recommends using the CREATE TABLE AS syntax
>>>> instead of SELECT INTO.
>>>>
>>>> Note that other databases including the SQL standard specify SELECT
>>>> INTO as follows:
>>>>
>>>> <select statement: single row> ::=
>>>> SELECT [ <set quantifier> ] <select list>
>>>> INTO <select target list>
>>>> <table expression>
>>>>
>>>> <select target list> ::=
>>>> <target specification> [ { <comma> <target specification> }... ]
>>>>
>>>>
>>>> So, INTO expects column references or parameters, for instance, not
>>>> table references.
>>>>
>>>> Now, CREATE TABLE AS can be used with jOOQ using plain SQL as follows:
>>>>
>>>> DSL.using(configuration)
>>>>
>>>> .execute("CREATE TABLE xx AS {0}", select);
>>>>
>>>>
>>>> Where select is your jOOQ SELECT statement.
>>>>
>>>> In any case, the CREATE TABLE AS statement would be a useful addition
>>>> to the newly introduced set of DDL statements in jOOQ. I have added it to
>>>> the roadmap for jOOQ 3.5:
>>>> https://github.com/jOOQ/jOOQ/issues/3381
>>>>
>>>> Cheers
>>>> Lukas
>>>>
>>>> 2014-07-09 11:16 GMT+02:00 <[email protected]>:
>>>>
>>>>> Hi,
>>>>>
>>>>> postgres has a select into which allows defining a new table from the
>>>>> results of a query. Is there any way i can do this in jooq?
>>>>>
>>>>> http://www.postgresql.org/docs/9.3/static/sql-selectinto.html
>>>>>
>>>>> i have a use case where i create a complex query containing a number
>>>>> of joins and a number of conditions to get a result set.
>>>>> On this result set i need to compute different aggregate values which
>>>>> are then used for plotting a graph.
>>>>>
>>>>> I thought it would be more efficent to push the results of the first
>>>>> query into a temp table and then do the later computation on the
>>>>> temporary
>>>>> table.
>>>>>
>>>>> Thanks,
>>>>> Gokul
>>>>>
>>>>> --
>>>>> You received this message because you are subscribed to the Google
>>>>> Groups "jOOQ User Group" group.
>>>>> To unsubscribe from this group and stop receiving emails from it, send
>>>>> an email to [email protected].
>>>>> For more options, visit https://groups.google.com/d/optout.
>>>>>
>>>>
>>>> --
>> You received this message because you are subscribed to the Google Groups
>> "jOOQ User Group" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to [email protected] <javascript:>.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
