[Solved] Boolean logic and string manipulation in a Query

[bobban]

What I am trying to do is form the letter head on my invoice forms of my database. There is a 'Customer Information' table that has the kind of information I need for this, but instead of just directly connecting the relevant fields of those tables to my form, I would like to do some operations on them to make them look nicer. I have done a kind of similar thing on the same form in regards to the actual invoice numbers (coming from the 'Invoice' table and sharing a common field of 'Customer ID' with the 'Customer Information table'), whereby a query was used to add a few subtotals, and invoice total to the form.

But now what I am wanting to do is a string manipulation exercise. At the top of the invoice is a block of information about the customer:

** addressee name **
address

The addressee name is tricky because it could be the 'company name' of the customer, or a combination of the 'first name' and 'last name' of the customer, if there is no company name in that case. These are three separate fields in the 'Customer Information' table.

Am I asking too much of a query to do this? Would it better be done in a macro? Or another way? Don't know a lot about queries tbh..

[squenson]

The COALESCE or CASEWHEN functions may be a solution. Look at this post for an example and more information.

[bobban]

The COALESCE or CASEWHEN functions may be a solution. Look at this post for an example and more information.

Thanks a lot for that link to that thread which had all the information in it to solve my problem. CASEWHEN was the solution I needed to get 'If Then Else' logic, and CONCAT allowed me to add to string together . Time to learn some more SQL.

Here was the code which I needed (char(32) to add a space between the names):

Code: Select all

CASEWHEN("CompanyName" IS NULL OR "CompanyName" = 'n/a',CONCAT(CONCAT("FirstName",CHAR(32)),"LastName"),"CompanyName") as "InvoiceAddresseeName"

I am marking the thread solved, butif anyone knows if there is a facility for local variables or not I would like to know. For example I tried to make a "NewFullName" variable to temporarily hold the value for code clarity but it threw an error saying it did not know about that variable.

Code: Select all

CONCAT(CONCAT("FirstName",CHAR(32)),"LastName") as "NewFullName",
CASEWHEN("CompanyName" IS NULL,"NewFullName","CompanyName") as "InvoiceAddresseeName",

[bobban]

I have taken off the solved mark from this thread because a new problem has arisen in regards to this.

As mentioned in the thread you referenced, the 'Direct SQL' mode must be turned on to allow the CASEWHEN statement to work. It does that, but the purpose of this for me is to use this query in my form and using 'Direct SQL' appears to break that. Basically my form is based on the query, and it has a subform based on a table, and the two are linked by a common field. I am noticing that once the query uses 'Direct SQL' mode the linkage between the two forms is broken, so that as I change records of the main form the subform will not update (I remove the line with CASEWHEN and turn off 'Direct SQL' and the form performs well again). Just when I thought I had the functionality I need a new obstacle.

[RPG]

Hello

The caswhen is usable in OOo3.1.1 in queries not in Direct mode.
I a little test I can use query with caswhen in a form.
When you update then concat is also more easy then you can use ||

http://wiki.services.openoffice.org/wik ... _Functions

Romke

[bobban]

Ok, so perhaps I have my 'direct mode' terminology confused. Not sure when you press the green 'SQL' button whether that is direct mode or not, but it needs to be pressed for CASEWHEN to work.

The main point is that my form is based on that query, and has an embedded subform based on a table, with the two forms linked by a common field, but once I use CASEWHEN in my query (and change the SQL mode to make it work), my form no longer works properly. When I advance a record for the main form the subform appears frozen. Can anyone indictae to me whether this would be a logical result of changing the SQL mode of the query, or is it more likely to be a bug? I though I had jumped all the hurdles Base had thrown at me to get this relatively simple database working.

Now I guess I will just try one big form based only on a query which has all the data of the table and the original query incorporated.

[bobban]

Now I guess I will just try one big form based only on a query which has all the data of the table and the original query incorporated.

Another ooBase whammy here and yet again more time down the drain.

Apparently once you have a query with fields from two separate tables the form can only display data and not write back to the tables. This has turned out to be a bit of farce tbh as what I thought would be a fairly regular kind of database situation keeps turning out to be a bridge too far.

I want two tables linked by common fields. I want a form with elements of both tables, that is editable (editable in terms of being able to write back to the tables), and has some extra fields with calculated values in it. I was painfully close on this until I needed to press the 'SQL' button to make my query be able to use CASEWHEN, which for unknown reasons, led to my linked form/subform mysteriously no longer working.

[RPG]

Hello

It seemes you do not understand my post.
Now clear ugrade to OOo3.1.1

You can only edit a subform based on a real table or query with a PK
Fields who are calc-culated, you cannot change. And this is maybe the same for the casewhen in the way how you use it.

Romke

[bobban]

It seeme you do not understand my post.

I am sorry but I do not understand you exactly. It seems you are saying that CASEWHEN work in non-direct mode v3.1.1 (I am calling "direct mode" when the SQL button is on, and "non-direct mode" when the SQL button is off just to clarify). I can only get CASEWHEN to work in my queries when the SQL button is on ("direct mode"), and when it is not I get a SQL syntax error.

Code: Select all

SQL Status: HY000
Error code: 1000

syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE

By the way I am using v3.1.1 for a while now (I just updated my signature, but had been using this version since before starting work on this database). Sorry if I misled you on this.

If you have a simple test of CASEWHEN working in non-direct SQL mode could you please attach the file so I look at what you have done.

Cheers
bobban

[RPG]

Hello

Code say more

How do you use it ?
When you use only casewhen in a form outside a SQL that is not working.
I think you try to use it complete wrong.

Romke

[bobban]

From my earlier post:

Code: Select all

CASEWHEN("CompanyName" IS NULL OR "CompanyName" = 'n/a',CONCAT(CONCAT("FirstName",CHAR(32)),"LastName"),"CompanyName") as "InvoiceAddresseeName"

This code works fine once I enter direct SQL mode.

[RPG]

Hello

I must make thing maybe more clear.

CASWHEN can be used normal inside OOo 3.1.1 without problems.
When you use this in a form or query you can use this for displaying data. But you can not change this field not in a form and maybe not in a query I did not test it.
That is not an error in your code. OOo3.1.1 does not implement this.
On this moment I donot have an example. But you can find a lot of examples of Sliderule.

I hope this is more clear

Romke

[RPG]

Hello

I have now not much time
I have change it a little

but I think the test part must be better


CASEWHEN(
"CompanyName" IS NULL OR "CompanyName" = 'n/a'
,"FirstName" || ' ' || "LastName" , "CompanyName"
as "InvoiceAddresseeName"

I have not test it

Romke

[bobban]

Thanks for your input, I will try what you mentioned. If I cannot get this to work I will upload a odb file that demonstrates my problems and what I am trying to do, and perhaps this will make things easier to understand.

[RPG]

Hello

You can rewrite your code to

Code: Select all

SELECT 
"ID", "Naam", 
CASEWHEN("Naam" = 'n/a' ,"plaats" || ' ' || "plaats" ,
	casewhen(COALESCE("Naam",'')='',"plaats" || ' ' || "plaats", "plaats"))
as "InvoiceAddresseeName","plaats"
,"wildit", "datum" FROM "client" AS "client

"

I did only test the code first on the caswhen. But you can use casewhen but not with the or. Then you have to rewrite it in a way I did.
I assume you rewrite your own code.
This code is working for me.

Romke

[bobban]

This code is working for me.

Hi. I am 90% sure my syntax is not the problem (for my example below I have made the CASEWHEN line very very simple).

I have attached a file that I think demonstrates the problems I am experiencing.

It's a very simple database, with two tables are related by a common field (CustomerID). I have made two queries, one using non direct SQL mode(Query_NoDirSQL), and the other using direct SQL mode (Query_DirSQL). Query_DirSQL uses CASEWHEN, and you will notice that if you try to change the SQL mode of this query and then either run/save it you will get an error.

There is also 5 forms whose names indicate their type:
1) Form1-Tb+Tb - a form from table plus subform from table.
Very simple.
2) Form2-Tb+Qry_NoDirSQL - a form from table plus subform from the Query_NoDirSQL.
Again very simple and all works as it should. The query part of the form can be used to edit data in the table the query is based up fine, with the expected exception of the "InvoiceAddresseeName" field as it is a construct of the query (FirstName and LastName fields concatenated).
3) Form3-Tb+Qry_DirSQL - a form a table and subform from the Query_DirSQL.
Here is where trouble strikes. The subform part now does not change records as the main form changes and is completely un-editable.
4) Form4-Qry_DirSQL - a form bases on the Query_DirSQL (no subform).
This one is quite interesting as in this case the form allows for the table it is based upon to be edited (except of course in the constructed fields which have no corresponding field in the table).
5) Form5-Qry_DirSQL+Tb - a form based on the Query_DirSQL plus a subform based on a table.
Again similar problems to before. The main form cannot be used edit data in the table it is based upon.

So it seems that once a form is itself based on a direct SQL mode query or has any subforms based on a direct SQL mode query that the form fails to function in terms of either being able to navigate records and the two parts of the form change record according to their linkage, or being able to edit the fields of the part of the form based on the direct mode SQL query. The exception to this was Form (4) where the form was solely based on a direct SQL mode query.

What am I aiming to achieve is to create a form which can:
1) display elements of two separate tables (all of which can be edited and the data saved back to the tables).
2) perform calculations on numbers from one of the tables for display purposes on the form (these do not have to be edited in any way)
3) perform string manipulation on some fields of the other table for display purposes (would be nice if these fields could be edited because this form is used to print/export a copy for the customer, but obviously this editing does not imply any data being written back to any table).

bobban

[RPG]

Hello

This query gives a problem in the mode of direct SQL

Code: Select all

SELECT 
	CASEWHEN("CompanyName" IS NULL,"FirstName","CompanyName") as "InvoiceAddresseeName",
	"CustomerID" AS "CustomerID", 
	"CompanyName" AS "CompanyName", 
	"FirstName" AS "FirstName", 
	"LastName" AS "LastName", 
	"Address" AS "Address", 
	"PostCode" AS "PostCode", 
	"PhoneNumber" AS "PhoneNumber", 
	"MobileNumber" AS "MobileNumber", 
	"FaxNumber" AS "FaxNumber", 
	"EmailAddress" AS "EmailAddress", 
	"Notes" AS "Notes", 
	"Suburb" AS "Suburb", 
	"State" AS "State" FROM "CustomerInformation" AS "CustomerInformation"

It would be nice if it can work in the OOo mode. I think it must can do, but it does not. Why ?
I 'm a person that is satified when it works and the problems of OOo I accept. But we have to search for the best solution.

But this query seemes to work

Code: Select all

SELECT coalesce( "CompanyName", "FirstName") AS "InvoiceAddresseeName", "CustomerID" AS "CustomerID", "CompanyName" AS "CompanyName", "FirstName" AS "FirstName", "LastName" AS "LastName", "Address" AS "Address", "PostCode" AS "PostCode", "PhoneNumber" AS "PhoneNumber", "MobileNumber" AS "MobileNumber", "FaxNumber" AS "FaxNumber", "EmailAddress" AS "EmailAddress", "Notes" AS "Notes", "Suburb" AS "Suburb", "State" AS "State" FROM "CustomerInformation" AS "CustomerInformation"

The only thing I have modiied is to this :

Code: Select all

coalesce( "CompanyName", "FirstName") AS "InvoiceAddresseeName"

I think it does the same. I hope this is a solution for your problem. I did test it for input and I think it was working.

Also I think it is not a workaround but also a better solution read the explanation from the hsqldb Manual

if expr1 is not null then it is returned else, expr2 is evaluated and if not null it is returned and so on

Romke

[bobban]

I think it does the same. I hope this is a solution for your problem. I did test it for input and I think it was working.

Yes it is all working now that I am using COALESCE, so thank you very much indeed for that clever solution to the problem.

Also I think it is not a workaround but also a better solution read the explanation from the hsqldb Manual

I agree it is a better solution in terms of the SQL statements used. CASEWHEN was overkill in this task, because I was mainly searching for a NULL entry and the simpler COALESCE statement can substitute for CASEWHEN in this situation. However, it is still very much a workaround in general for the shortcoming of ooBase here.

It would be nice if it can work in the OOo mode. I think it must can do, but it does not. Why ?
I 'm a person that is satified when it works and the problems of OOo I accept. But we have to search for the best solution.

Me too, and I thank you again Romke for your help on this, but I still think I need to once more point out the root problem here as I see it. It all simply boils down to the fact that when you have a query which contains SQL that requires the "run SQL command directly" switch to be turned on (ie. if CASEWHEN was used for example) then any form which contains linked subforms with one of the forms being based on that query, then the linkage is broken, so that when you change records on one part of the form it does not change the other. Your solution bought me under the need to have a direct mode SQL query which was brilliant thinking.

A funny endnote. I just had a quick look through the Issue Tracker to see if this bug was registered and I am not the first. Issue 96703 is for this bug (and I encourage everyone who reads and understands this thread to vote for it), and ironically it was submitted by our good friend and OOCF hero Monsieur Villeroy.

[RPG]

Hello

I'm glad you have succeed.

Only one thing In your post you wrote that the problem is in the casewhen. But I think the problem is in "is null"
The reason why I wrote this is that other people maybe think they cannot use casewhen.

This code works only in native SQL or Direct SQL

Code: Select all

 CASEWHEN("CompanyName" IS NULL,"FirstName","CompanyName") as "InvoiceAddresseeName",

This code is working in OOo-base SQL

Code: Select all

CASEWHEN(COALESCE("CompanyName",'')='',"FirstName","CompanyName") as "InvoiceAddresseeName",

So there is no problem in casewhen but with IS NULL. Why?
I think you can use casewhen in a subform without problems.

Romke

[eremmel]

An other workaround as described in the issue mentioned above is to make a OOo-Base query based on the Direct-SQL query (acts as a view for HSQLDB) that retrieves just the data. When one makes e.g. a query Query_DirSQL2 with

Code: Select all

SELECT "InvoiceAddresseeName" AS "InvoiceAddresseeName", "CustomerID" AS "CustomerID", "CompanyName" AS "CompanyName", "FirstName" AS "FirstName", "LastName" AS "LastName", "Address" AS "Address", "PostCode" AS "PostCode", "PhoneNumber" AS "PhoneNumber", "MobileNumber" AS "MobileNumber", "FaxNumber" AS "FaxNumber", "EmailAddress" AS "EmailAddress", "Notes" AS "Notes", "Suburb" AS "Suburb", "State" AS "State" FROM "Query_DirSQL" AS "CustomerInformation"

and use this as the source of the sub-form in Form3-Tb+Qry_DirSQL all works as well.

[bobban]

Only one thing In your post you wrote that the problem is in the casewhen. But I think the problem is in "is null"
The reason why I wrote this is that other people maybe think they cannot use casewhen.

Very good point and thanks for clarifying this detail.

An other workaround as described in the issue mentioned above is to make a OOo-Base query based on the Direct-SQL query (acts as a view for HSQLDB) that retrieves just the data. When one makes e.g. a query Query_DirSQL2 with

Thanks for including that in this thread. I noticed that mentioned on the Issue Tracker, but it is good it is here too so this thread is a more complete reference of the issue. Will this allow data to be written back to the tables (for the fields of the query which have a field in the source table), or this for a read only form?