Hi Nicolas,

what do you mean with "enter queries" and UPPER method?
You can enter the function directly into the field "field" or do you
mean that we should extend the function listbox where at the moment
are only aggregate functions and the group flag allowed.

Best regards,
Ocke

Let's say you've got the following table:

---------
  User
---------
[PK] id
firstname
country
---------

with the following data:

-------------------------
id  | firstname | country
----+-----------+--------
1   | Ocke      | France
2   | ocke      | Germany
3   | John      | USA
-------------------------

I'd like to create a query corresponding to the following sql statement:

SELECT
  *
FROM
  User
WHERE
  UPPER(firstname)='OCKE'

If there's a way to acheve this, i didn't see it.
- by entering sql statement directly, the parser refuses the 'UPPER'
function
- in the query design view, there's a limited set of functions in the
listbox
  you're refering to, and AFAIK you can't enter other functions by hand

This would be of the *highest* interest for me ; the ability to use common
sql functions in the SELECT part of the statement -- that is to say to
enter
functions into the field "field" in the query design view -- would also be
very useful, but i understand it could be harder to implement :-/

Cheers,
Nicolas

I see.
The problem is inside the parser. I don't know where exactly at this
moment, but I'll investigate it asap. As a work around untill I fix
this you may run the query in native mode.

I'll have a look at it.

Hi Nicolas,

I just had a look into our parser. It is normally correct that he
reject a statement like "select UPPER("ColumnName") from table". Upper
normally accepts only strings not column names. If you want to upper
the content of column fields, you have to use UCASE(column name) which
should do the task as well.

Best regards,
Ocke

Hi Ocke,        
        
I'm *not* sure to follow your mind. Your last comment is very       
interesting, because it makes me realize that there are a lot of       
things unclear to me. As i think i'm a representative user, i'll try       
to explain my views.       
       
I'm recently began considering the possibility that "my users" use      
OOo data access to *quickly* and *easily* query an Oracle database      
as their needs change. It's impossible to foresee all possible      
needs, so i don't know exactly what queries they'll need, but i      
already know these could be complex.      
      
So OOo could be the killer app for us, and i started to examine the     
query design view.     
     
I knew nothing about the "native mode" before your comment about it.     
I looked for it in the docs (OOo user help ; user manual ; api docs    
; developer docs) and i found nothing, nor in the OOo glossary page   
(http://l10n.openoffice.org/localization/OpenOffice_Glossary.html). 
I only saw this term in the page    
http://dba.openoffice.org/papers/dbfuture.html. OK, there's the   
related icon and it's description in the OOo help browser, but, at   
least for me, one can't find a text which clearly explains the   
difference between the "native mode" and the presumably named   
"normal mode". Maybe it should be an idea to document their   
differences and their meaning ?   
   
And by the way, here's another unclear point to me: in which   
"dialect" should queries be written, both in normal and native mode   
?   
  
At first sight, i thought i had to speak "SAL ANSI-92" because it's   
the most frequently (supposely) adopted standard. And I think i'm   
not the only one : that's why people (e.g.  
http://www.openoffice.org/issues/show_bug.cgi?id=5798) ask why  
statements generated by the query design view (also in 'normal mode'  
if i understand well) are not pure SQL.  
  
In this approach, the use of UCASE is not acceptable, since it isn't  
an SQL ANSI-92 reserved word (see for instance  
http://www.netaktive.com/biblio/sql/SQL98/sql2bnf.aug92.txt) ; you  
definitely have to use the UPPER function.  
  
But your comment makes me suspect the following:  
* In 'normal' mode, e.g. when using the query design view, queries  
  are intended to the 2nd layer of your db-access stuff (i know  
  nothing about it, sorry), in a dialect specific to it (tastes  
  like ANSI92 but it isn't)  
* In 'native' mode, the queries are intended to the appropriate  
  driver, and then you've got to be as respectful of the ANSI-92  
  dialect as the driver is  
  
If this is true, let me tell you that it is far from obvious for the  
end user !  
  
So in your side, i wonder if you intend to respect the ANSI-92  
standard -- maybe not, after all, since you told me about the UCASE  
function, but in this case, what is your intent ? Is there a  
document which explains the possibilities allowed, the functions  
which can be used ?  
  
And in my side, my need is to be able to enter standard ANSI-92  
queries, because it is a universal starting point to work -- i can  
reuse queries from one tool/client to another; i can easily discuss  
around it with other people, etc.  
  
I saw in the dev@dba.openoffice.org mailing list that you were  
considering the use of a users mailing list : i  think this would be  
definitely useful, since these kind of discussions would take their  
place in it...  
  
I'm not a member ov the dev@dba mailing list --since i'm not an OOo  
developer, but i would be glad to register if you think it is a  
better place, even if temporary, to continue the discussion.  
Let me know...  
  
Cheers,  
Nicolas  
  

Hi Nicolas,

through the case that the medium is not usable as news or mailings
are,  I inserted some paragraphs.

Yes you are right UCASE is not part of SQL92. It comes from the
ODBC2-3 functions so most database should support them.

Native mode: When you switch this mode on, the statement you entered
into the text view will be executed untouched by the dbaccess
components. This may be useful when for example our parser doesn't
accept your statement, or you use some special dialect. When you run
the query in normal we get a chance to extract some information about
the columns you want to fetch and the table so that we might give you
an accessible RowSet where you modify, insert or delete rows.

Yes, this should be documented.

No, you have to write the query only once. But when you use a dialect
other than SQL92 you must be sure that the other database driver you
are using understand what you want to. As I mentioned before, our
parser may have a bug with UPPER, I just found out that we only accept
strings for UPPER and not column names, that may be wrong I guess. My
fault was to think that UPPER should only accpet strings, or is this
wrong?

To discuss this on the mailing list would be the best I think.

Best regards,
Ocke 

Created attachment 2651 [details]
issues.sxc

Created attachment 2652 [details]
solved_issues.png

Created attachment 2653 [details]
solved_issues_2.png

Oops... i'm sorry, the last 3 attachments were intended to another 
issue and should be ignored / deleted. 
 
Sorry again. 

I fixed this in cws dba02.

Reopen task to send to QA

Please verify in dba02

fixed in cws dba02

verified in cws dba02

ok in m4s4

As mentioned on the qa dev list on March 5th I will close all resolved
<wontfix/duplicate/worksforme/invalid> issues. Please see this posting for details. 

change subcomponent to 'none'