Query Compexity in Design Mode

[Keith Clark-2]

Why is the following query too complex to be viewed in the design mode?

SELECT `Date`, weekday( `Date` ) AS `Weekday`,
`Precipitation` AS `Precip`
FROM `kwbo4418_WaterlooWeather2000`.`2005` AS `2005`
WHERE ( weekday( `Date` ) = '4'
OR weekday( `Date` ) = '5'
OR weekday( `Date` ) = '6' )
AND `Precipitation` > 0


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[drewjensen]

Keith Clark wrote:

> Why is the following query too complex to be viewed in the design mode?
>
> SELECT `Date`, weekday( `Date` ) AS `Weekday`,
> `Precipitation` AS `Precip`
> FROM `kwbo4418_WaterlooWeather2000`.`2005` AS `2005`
> WHERE ( weekday( `Date` ) = '4'
> OR weekday( `Date` ) = '5'
> OR weekday( `Date` ) = '6' )
> AND `Precipitation` > 0
>
>  

Without trying it - change the criteria to 4, 5 and 6 vs `4`, etc.


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Keith Clark-2]

On Sat, 2009-08-08 at 19:07 -0400, Drew Jensen wrote:

> Keith Clark wrote:
> > Why is the following query too complex to be viewed in the design mode?
> >
> > SELECT `Date`, weekday( `Date` ) AS `Weekday`,
> > `Precipitation` AS `Precip`
> > FROM `kwbo4418_WaterlooWeather2000`.`2005` AS `2005`
> > WHERE ( weekday( `Date` ) = '4'
> > OR weekday( `Date` ) = '5'
> > OR weekday( `Date` ) = '6' )
> > AND `Precipitation` > 0
> >
> >  
>
> Without trying it - change the criteria to 4, 5 and 6 vs `4`, etc.
>
>

Hmm, still says Query Too Complex.


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[drewjensen]

Keith Clark wrote:

> On Sat, 2009-08-08 at 19:07 -0400, Drew Jensen wrote:
>  
>> Keith Clark wrote:
>>    
>>> Why is the following query too complex to be viewed in the design mode?
>>>
>>> SELECT `Date`, weekday( `Date` ) AS `Weekday`,
>>> `Precipitation` AS `Precip`
>>> FROM `kwbo4418_WaterlooWeather2000`.`2005` AS `2005`
>>> WHERE ( weekday( `Date` ) = '4'
>>> OR weekday( `Date` ) = '5'
>>> OR weekday( `Date` ) = '6' )
>>> AND `Precipitation` > 0
>>>
>>>  
>>>      
>> Without trying it - change the criteria to 4, 5 and 6 vs `4`, etc.
>>
>>
>>    
>
> Hmm, still says Query Too Complex.
>
>  

Are you using the Native connector?  I just constructed something
similar and it takes it - but - and I didn't notice this before (how did
I miss this) it wants to use double quotes... so the SQL turns into
SELECT "Date", weekday( "Date" ), "precipt" FROM "MSIS"."precipitation"
AS "precipitation" WHERE ( "precipt" > 0 AND weekday( "Date" ) = '4' OR
weekday( "Date" ) = '5' OR weekday( "Date" ) = '6' )





---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[drewjensen]

Ok there is a problem.

This time I am using JDBC 5.1.6

If I construct my query this way:

SELECT `Date`, weekday( `Date` )

FROM `MSIS`.`precipitation` AS `precipitation`

WHERE `precipt` > 0

    AND   ( weekday( `Date` ) = '4'

                 OR weekday( `Date` ) = '5'

                 OR weekday( `Date` ) = '6'

               )


No problem

But as soon as I flip it aroung the way you have it

SELECT `Date`, weekday( `Date` )

FROM `MSIS`.`precipitation` AS `precipitation`

WHERE ( weekday( `Date` ) = '4'

        OR weekday( `Date` ) = '5'

        OR weekday( `Date` ) = '6'

       )
AND `precipt` > 0


Then I get a syntax error bringing it into the designer...

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Keith Clark-2]

On Sat, 2009-08-08 at 19:24 -0400, Drew Jensen wrote:

> Keith Clark wrote:
> > On Sat, 2009-08-08 at 19:07 -0400, Drew Jensen wrote:
> >  
> >> Keith Clark wrote:
> >>    
> >>> Why is the following query too complex to be viewed in the design mode?
> >>>
> >>> SELECT `Date`, weekday( `Date` ) AS `Weekday`,
> >>> `Precipitation` AS `Precip`
> >>> FROM `kwbo4418_WaterlooWeather2000`.`2005` AS `2005`
> >>> WHERE ( weekday( `Date` ) = '4'
> >>> OR weekday( `Date` ) = '5'
> >>> OR weekday( `Date` ) = '6' )
> >>> AND `Precipitation` > 0
> >>>
> >>>  
> >>>      
> >> Without trying it - change the criteria to 4, 5 and 6 vs `4`, etc.
> >>
> >>
> >>    
> >
> > Hmm, still says Query Too Complex.
> >
> >  
> Are you using the Native connector?  I just constructed something
> similar and it takes it - but - and I didn't notice this before (how did
> I miss this) it wants to use double quotes... so the SQL turns into
> SELECT "Date", weekday( "Date" ), "precipt" FROM "MSIS"."precipitation"
> AS "precipitation" WHERE ( "precipt" > 0 AND weekday( "Date" ) = '4' OR
> weekday( "Date" ) = '5' OR weekday( "Date" ) = '6' )
>

I am using the jdbc connector to a MySQL remote database



---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Keith Clark-2]

On Sat, 2009-08-08 at 19:37 -0400, Drew Jensen wrote:

> Ok there is a problem.
>
> This time I am using JDBC 5.1.6
>
> If I construct my query this way:
>
> SELECT `Date`, weekday( `Date` )
>
> FROM `MSIS`.`precipitation` AS `precipitation`
>
> WHERE `precipt` > 0
>
>     AND   ( weekday( `Date` ) = '4'
>
>                  OR weekday( `Date` ) = '5'
>
>                  OR weekday( `Date` ) = '6'
>
>                )
>
>
> No problem
>
> But as soon as I flip it aroung the way you have it
>
> SELECT `Date`, weekday( `Date` )
>
> FROM `MSIS`.`precipitation` AS `precipitation`
>
> WHERE ( weekday( `Date` ) = '4'
>
>         OR weekday( `Date` ) = '5'
>
>         OR weekday( `Date` ) = '6'
>
>        )
> AND `precipt` > 0
>
>
> Then I get a syntax error bringing it into the designer...

Interesting....

So it matters what order the "ands" and "ors" are in?

Keith



---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Drew Jensen]

> Interesting....
>
> So it matters what order the "ands" and "ors" are in?
>
> Keith
>  

Well the GUI designer trys to put ANDs before ORs..that I've seen before.
However - I just ran all this with an earlier 3.1.1 development build
and not the 3.1.1 RC1 build.
I will be installing the RC1 package later tonight and will see if
anything changes .

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[drewjensen]

> Interesting....
>
> So it matters what order the "ands" and "ors" are in?
>
> Keith
>  

Well the GUI designer trys to put ANDs before ORs..that I've seen before.
However - I just ran all this with an earlier 3.1.1 development build
and not the 3.1.1 RC1 build.
I will be installing the RC1 package later tonight and will see if
anything changes .

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[drewjensen]

then again you could change the query to this:

SELECT `Date`, weekday( `Date` ) FROM `MSIS`.`precipitation` AS `precipitation`

WHERE

    weekday( `Date` ) in ( '4', '5', '6' )

AND

    `precipt` > 0


The designer likes this construct.




---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Keith Clark-2]

On Sat, 2009-08-08 at 19:56 -0400, Drew Jensen wrote:

> > Interesting....
> >
> > So it matters what order the "ands" and "ors" are in?
> >
> > Keith
> >  
>
> Well the GUI designer trys to put ANDs before ORs..that I've seen before.
> However - I just ran all this with an earlier 3.1.1 development build
> and not the 3.1.1 RC1 build.
> I will be installing the RC1 package later tonight and will see if
> anything changes .

Actually Drew, I just flipped it and I still get a Query Too Complex

SELECT `Date`, weekday( `Date` ) AS `Weekday`,
`Precipitation` AS `Precip`
FROM `kwbo4418_WaterlooWeather2000`.`2005` AS `2005`
WHERE `Precipitation` > 0
AND ( weekday( `Date` ) = 4
        OR weekday( `Date` ) = 5
        OR weekday( `Date` ) = 6 )


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Keith Clark-2]

On Sat, 2009-08-08 at 20:00 -0400, Drew Jensen wrote:

> then again you could change the query to this:
>
> SELECT `Date`, weekday( `Date` ) FROM `MSIS`.`precipitation` AS `precipitation`
>
> WHERE
>
>     weekday( `Date` ) in ( '4', '5', '6' )
>
> AND
>
>     `precipt` > 0
>
>
> The designer likes this construct.
>

Perfectamundo.  Thanks.



---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Andreas Saeger]

Keith Clark wrote:
> Why is the following query too complex to be viewed in the design mode?
>
> SELECT `Date`, weekday( `Date` ) AS `Weekday`,
> `Precipitation` AS `Precip`
> FROM `kwbo4418_WaterlooWeather2000`.`2005` AS `2005`
> WHERE ( weekday( `Date` ) = '4'
> OR weekday( `Date` ) = '5'
> OR weekday( `Date` ) = '6' )
> AND `Precipitation` > 0

Since the "query designer" does not handle many valid queries, I
consider  this tool as obsolete, experimental or waste of time.
I use it to collect the names of tables and fields before I compose the
query in text mode or in a text editor.

Today I found that it can not even group by calculated fields:
SELECT "Forname"||','||"Surname" AS "Patient Info",
[...]
GROUP BY "Patient Info"


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[drewjensen]

Andreas Saeger wrote:

>
> Since the "query designer" does not handle many valid queries, I
> consider  this tool as obsolete, experimental or waste of time.
> I use it to collect the names of tables and fields before I compose
> the query in text mode or in a text editor.
>
> Today I found that it can not even group by calculated fields:
> SELECT "Forname"||','||"Surname" AS "Patient Info",
> [...]
> GROUP BY "Patient Info"
>

Works for me using the 3.2 pre-release build
*http://tinyurl.com/kp5yrf

Drew
*

[Keith Clark-2]

On Mon, 2009-08-10 at 08:23 -0400, Drew Jensen wrote:

> Andreas Saeger wrote:
> >
> > Since the "query designer" does not handle many valid queries, I
> > consider  this tool as obsolete, experimental or waste of time.
> > I use it to collect the names of tables and fields before I compose
> > the query in text mode or in a text editor.
> >
> > Today I found that it can not even group by calculated fields:
> > SELECT "Forname"||','||"Surname" AS "Patient Info",
> > [...]
> > GROUP BY "Patient Info"
> >
>

I'm using 3.0 and can confirm this in that version.  I ran into it over
the weekend as well.


> Works for me using the 3.2 pre-release build
> *http://tinyurl.com/kp5yrf
>
> Drew
> *
>


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[drewjensen]

Keith Clark wrote:

> On Mon, 2009-08-10 at 08:23 -0400, Drew Jensen wrote:
>  
>> Andreas Saeger wrote:
>>    
>>> Since the "query designer" does not handle many valid queries, I
>>> consider  this tool as obsolete, experimental or waste of time.
>>> I use it to collect the names of tables and fields before I compose
>>> the query in text mode or in a text editor.
>>>
>>> Today I found that it can not even group by calculated fields:
>>> SELECT "Forname"||','||"Surname" AS "Patient Info",
>>> [...]
>>> GROUP BY "Patient Info"
>>>
>>>      
>
> I'm using 3.0 and can confirm this in that version.  I ran into it over
> the weekend as well.
>  

3.1 and the upcoming 3.1.1 do not support it either , support for this
is added in the 3.2 release.

- 3.2 is scheduled for sometime in November from what I've seen in lists.


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Keith Clark-2]

On Mon, 2009-08-10 at 08:50 -0400, Drew Jensen wrote:

> Keith Clark wrote:
> > On Mon, 2009-08-10 at 08:23 -0400, Drew Jensen wrote:
> >  
> >> Andreas Saeger wrote:
> >>    
> >>> Since the "query designer" does not handle many valid queries, I
> >>> consider  this tool as obsolete, experimental or waste of time.
> >>> I use it to collect the names of tables and fields before I compose
> >>> the query in text mode or in a text editor.
> >>>
> >>> Today I found that it can not even group by calculated fields:
> >>> SELECT "Forname"||','||"Surname" AS "Patient Info",
> >>> [...]
> >>> GROUP BY "Patient Info"
> >>>
> >>>      
> >
> > I'm using 3.0 and can confirm this in that version.  I ran into it over
> > the weekend as well.
> >  
>
> 3.1 and the upcoming 3.1.1 do not support it either , support for this
> is added in the 3.2 release.
>
> - 3.2 is scheduled for sometime in November from what I've seen in lists.
>
>

Good stuff.  Continuous Improvement!



---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Ariel Constenla-Haile]

Hello Drew,

On Monday 10 August 2009, 09:50, Drew Jensen wrote:
> 3.1 and the upcoming 3.1.1 do not support it either , support for this
> is added in the 3.2 release.
>
> - 3.2 is scheduled for sometime in November from what I've seen in lists.

to follow release dates, you can track
http://wiki.services.openoffice.org/wiki/OOoRelease32

by now 3.2 is targeted to end of November

Regards
--
Ariel Constenla-Haile
La Plata, Argentina

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Andreas Saeger]

Drew Jensen wrote:

> Andreas Saeger wrote:
>> [...]
>> Today I found that it can not even group by calculated fields:
>> SELECT "Forname"||','||"Surname" AS "Patient Info",
>> [...]
>> GROUP BY "Patient Info"
>>
>
> Works for me using the 3.2 pre-release build
> *http://tinyurl.com/kp5yrf
>
> Drew
> *
>
>

Next one converting 2-digit years into 4 digit years (1909-2008):
SELECT (CASE WHEN ("P"."Jahr"+2000)>=YEAR(CURRENT_DATE) THEN 1900 ELSE
2000 END)+ "P"."Jahr" AS "Four Digit Year"

Yields an SQL syntax error. Does works in direct mode. However, direct
mode is read-only and fails in subforms (relation to parent form fails
silently).


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Andreas Saeger]

Andreas Saeger wrote:

> Next one converting 2-digit years into 4 digit years (1909-2008):
> SELECT (CASE WHEN ("P"."Jahr"+2000)>=YEAR(CURRENT_DATE) THEN 1900 ELSE
> 2000 END)+ "P"."Jahr" AS "Four Digit Year"
>
> Yields an SQL syntax error. Does works in direct mode. However, direct
> mode is read-only and fails in subforms (relation to parent form fails
> silently).


And another one merging tables and queries:
SELECT ... WHERE ("qryA"."XID"="tblA"."ID")AND("tblB"."X"="tblC"."Y")...
Fails with unknown column, strips "quryA" when loaded in design mode,
but runs well while testing in parsed SQL mode.


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...