[Solved] Trying to use CASE WHEN ELSE END

[dreamquartz]

Hi All,

I am trying to figure out the use of CASE WHEN ELSE END.
I have 1 table (Service) that uses a Foreign Key (FKTypeOfService).
In some cases there is not TypeOfService, and therefore no entry.
I would like to show both possibilities in one Query.

Outcome required:

Service1, FKTypeOfService1
Service2, FKTypeOfService1
Service3, FKTypeOfService2
Service4, .......
........
Servicex
Servicey
Servicez
........

I am trying to use the following principle:
In CASE there is no FKTypeOfService, THEN only show Service, ELSE show Service, FKTypeOfService

The outcome does not make sense to me, so I am doing something wrong, but do not know what.

When using the following SQL:

Code: Select all

SELECT
CASE WHEN "Service"."FKTypeOfServiceID" = NULL THEN "Service"."Service" ELSE "Service"."UnitPrice" END AS "Service"
FROM
"Service",
"TypeOfService"

I receive the following message:

SQL Status: S1000
Error code: -216

Unresolved parameter type as output of CASE when operand types are VARCHAR and NUMERIC in statement [SELECT CASE WHEN "Service"."FKTypeOfServiceID" = NULL THEN "Service"."Service" ELSE "Service"."UnitPrice" END AS "Service" FROM "Service", "TypeOfService"]

The tables are:

Service
ServiceID Integer [INTEGER] (Primary Key)
FKTypeOfServiceID Integer [INTEGER] (Foreign Key)
Service Text [VARCHAR]
UnitPrice Number [NUMERIC]

TypeOfService
TypeOfServiceID Integer [INTEGER] (Primary Key)
TypeOfService Text [VARCHAR]

Where am I going wrong?

Dream

[Sliderule]

I see ( at least ) three issues / items of concern with the Query as you have written it.

1. In the comparison, using NULL, you should NOT use an equal sign in combination with NULL . . . instead . . . use IS NULL
2. Since, in the 'new / calculated' column, per your CASE WHEN statement . . . sometimes it will be returning TEXT value ( a string Service Text [VARCHAR] ) . . . and . . . other times it will be returning a NUMERIC value ( UnitPrice Number [NUMERIC] ). . . but . . . it can only return ONE type ( content must be of the same type ).
   
   So, the solution is to use the function CAST to change the NUMERIC to a TEXT STRING .
3. You did not include a WHERE clause, showing the relationship between the two tables.

Code: Select all

SELECT 
   CASE WHEN "Service"."FKTypeOfServiceID" IS NULL THEN "Service"."Service" ELSE CAST("Service"."UnitPrice" as VARCHAR(50)) END AS "Service"

FROM "Service",
     "TypeOfService"

WHERE "Service"."FKTypeOfServiceID" = "TypeOfService"."TypeOfServiceID"

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to ad [Solved] in your 1st post Subject (edit buton top right) if this isue has ben resolved.

[dreamquartz]

I see ( at least ) three issues / items of concern with the Query as you have written it.

1. In the comparison, using NULL, you should NOT use an equal sign in combination with NULL . . . instead . . . use IS NULL
2. Since, in the 'new / calculated' column, per your CASE WHEN statement . . . sometimes it will be returning TEXT value ( a string Service Text [VARCHAR] ) . . . and . . . other times it will be returning a NUMERIC value ( UnitPrice Number [NUMERIC] ). . . but . . . it can only return ONE type ( content must be of the same type ).
   
   So, the solution is to use the function CAST to change the NUMERIC to a TEXT STRING .
3. You did not include a WHERE clause, showing the relationship between the two tables.

Code: Select all

SELECT 
   CASE WHEN "Service"."FKTypeOfServiceID" IS NULL THEN "Service"."Service" ELSE CAST("Service"."UnitPrice" as VARCHAR(50)) END AS "Service"

FROM "Service",
     "TypeOfService"

WHERE "Service"."FKTypeOfServiceID" = "TypeOfService"."TypeOfServiceID"

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to ad [Solved] in your 1st post Subject (edit buton top right) if this isue has ben resolved.

Hi Sliderule,

Thanks for your response, but me...................
BIG MISTAKE FROM MY PART!!!!!!!!!!!!!!!
Gave the wrong info.

Code: Select all

SELECT
	 CASE WHEN "Service"."FKTypeOfServiceID" <> NULL
	 THEN "Service"."Service" || ', ' || "TypeOfService"."TypeOfService"
	 ELSE "Service"."Service" END
	 AS "Service"
 FROM
	 "Service",
	 "TypeOfService"
 WHERE
	 "Service"."FKTypeOfServiceID" = "TypeOfService"."TypeOfServiceID"
 ORDER BY
	 "Service" ASC

Of course the previous Code gave the error. I can easily spot that.
Wow, what a blunder is that one.

OK.
What I have is indeed the tables, Service and TypeOfService.
The lay out is indeed as mentioned above. I attached a small example of the layout of the DataBase.
There is no error.
The outcome is not as what is the required outcome.
The moment this NEW Query is run, only those Services that do have a Foreign Key (FK) in the FKTypeOfServiceID column are listed..
I have no idea to extract a Service where there is no FKTypeOfServiceID filled in for a Service.
I can go the simple way, being entering something like a space as a TypeOfService for the use in the Service table to referenced as a FKTypeOfServeID, but that is not really elegant.
There should be a way to prevent the Query to strictly look at "Service"."FKTypeOfServiceID" = "TypeOfService"."TypeOfServiceID", and therefore ruling the empty FKTypeOfServiceID references out.
I think I need the "Service"."FKTypeOfServiceID" = "TypeOfService"."TypeOfServiceID" to select the correct reference when there is a FKTypeOfServiceID.

Any further thoughts?

Dream

[Sliderule]

I assume, with the SQL ( Structured Query Language ) that you posted above, you are still receiving an error.

If so, please try the following SQL:

Code: Select all

SELECT 
    CASE WHEN "Service"."FKTypeOfServiceID" IS NULL
    THEN "Service"."Service" || ', ' || "TypeOfService"."TypeOfService"
    ELSE "Service"."Service" END
    AS "Service"
FROM
    "Service",
    "TypeOfService"
WHERE
    "Service"."FKTypeOfServiceID" = "TypeOfService"."TypeOfServiceID"
ORDER BY
    "Service"."Service" ASC

Explanation: The above, is just like you entered it, EXCEPT . . . in the ORDER BY part . . . I changed "Service" to "Service"."Service" . Also, I changed your code: = NULL to IS NULL .

Since, you have BOTH a table by the name of "Service" and a column by the name of "Service" . . . and . . . you want / need to tell the database which you really want to use, in the ORDER BY . . . ( no confusion this way ) . . . specify the full table and column name.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to ad [Solved] in your 1st post Subject (edit buton top right) if this isue has ben resolved.

[dreamquartz]

Hi Sliderule,

Sadly that does not work.
The attached ods-format file shows 2 columns.
The left one is a View of the Query, and the right one is the one I am looking for as the outcome.

It appears that "Service"."FKTypeOfServiceID" = "TypeOfService"."TypeOfServiceID" is one cause, and that CASE is not taking other tables into considderarion.

Any thoughts?

Dream

[Sliderule]

Your use of NULL ( undefined values ) has 'complicated' issues. In my opinion, having NULL ( undefined ) values means . . . the database does not know what to do, since, you have not adequately told it what to do.

Use the follow SQL ( Structured Query Language ) . . . BUT . . . most important . . . within OpenOffice Base / Libre Office Base, the Query below can only be issued ( because of the use of UNION ) when it is run directly ( sans the OpenOffice / LibreOffice Base parser ).

Run directly means, in the window where you construct your Query . . . either, on the toolbar, press the icon with SQL . . . or . . . from the Menu: Edit -> Run SQL command directly must be checked.

Code: Select all

SELECT 
   "Service"."Service" || ', ' || "TypeOfService"."TypeOfService" as "Service"
FROM
    "Service",
    "TypeOfService"
WHERE
    "Service"."FKTypeOfServiceID" = "TypeOfService"."TypeOfServiceID"
ORDER BY
    "Service"."Service" ASC

   UNION 

Select 
   "Service"."Service" as "Service"
FROM "Service"
Where "Service"."FKTypeOfServiceID" IS NULL

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit buton top right) if this isue has ben resolved.

[Sliderule]

Another alternative way to enter your query ( thanks to user F3K Total ) . . . and . . . the SQL ( Structured Query Language ) below may be run with or without the OpenOffice / LibreOffice parser . . . is:

Code: Select all

SELECT 
    CASE WHEN "Service"."FKTypeOfServiceID" IS NULL 
         THEN "Service"."Service" 
         ELSE "Service"."Service" || ', ' || "TypeOfService"."TypeOfService" 
    END AS "Service"
FROM
    "Service" 
    LEFT OUTER JOIN "TypeOfService" ON "Service"."FKTypeOfServiceID" = "TypeOfService"."TypeOfServiceID"
ORDER BY "Service"."Service"

I have included a 'copy' of your database below, with the two Queries included:

1. Service_Left_Outer_Join
2. Service_Union

so you can run and test them.

ServiceTypeOfService.odb

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to ad [Solved] in your 1st post Subject (edit buton top right) if this isue has ben resolved.

[dreamquartz]

Another alternative way to enter your query ( thanks to user F3K Total ) . . . and . . . the SQL ( Structured Query Language ) below may be run with or without the OpenOffice / LibreOffice parser . . . is:

Code: Select all

SELECT 
    CASE WHEN "Service"."FKTypeOfServiceID" IS NULL 
         THEN "Service"."Service" 
         ELSE "Service"."Service" || ', ' || "TypeOfService"."TypeOfService" 
    END AS "Service"
FROM
    "Service" 
    LEFT OUTER JOIN "TypeOfService" ON "Service"."FKTypeOfServiceID" = "TypeOfService"."TypeOfServiceID"
ORDER BY "Service"."Service"

I have included a 'copy' of your database below, with the two Queries included:

1. Service_Left_Outer_Join
2. Service_Union

so you can run and test them.

ServiceTypeOfService.odb

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to ad [Solved] in your 1st post Subject (edit buton top right) if this isue has ben resolved.

You made my DAY
I never thought of JOIN.
I do not quite understand it, so I did not think of it.
I will do research on it, but thank you very much. It looks like this nagging issue has been solved for now.

Keep you posted. Will mark as [SOLVED]

Dream

[dreamquartz]

Sadly not quite done yet.
Realized that the requested table has a flaw.
Was hoping to do everything right the first time.
Attached you will find an updated table of what I am looking for.
I need to be able to show all combinations, which works with the suggested JOIN. So thank you for that one.

However, I need to be able to filter on i.e. Service5 or Service11 or Service2, TypeOfServiceD or Service2, TypeOfServiceA. The same is applicable for Service8.
I am using "Service"."Service" LIKE '%' || :Service || '%' to select the Service, and I was thinking about using a "TypeOfService"."TypeOfService" LIKE '%' || :Type || '%', but that does not work.
If I remove this last statement, all is shown, but it is not specific enough.

I also realized that I would like to be able to enter a Service without thinking about case or not. Some people enter only lowercase, while others combine upper and lower case, whilst others use only uppercase. I do not want to restrict users in any form, and at this point I can not seem to get around it. is there a way to avoid that problem?

Dream

[Sliderule]

I answered your original question, you marked it as [Solved] and now you have changed the question and changed the [Solved].

Therefore, since you have now changed your original question, and, added other question, I will simply say, this is already solved.

Sliderule

[dreamquartz]

I answered your original question, you marked it as [Solved] and now you have changed the question and changed the [Solved].

Therefore, since you have now changed your original question, and, added other question, I will simply say, this is already solved.

Sliderule

Hi Sliderule,

I totally accept your critique.

Do I need to start a new thread to continue?
I am not quite sure how to proceed.

Dream

[Sliderule]

Please review Survival Guide for the forum:

viewtopic.php?f=5&t=166#p718

Also, always in the Base forum, include the database back-end you are using ( as displayed on the status bar at the bottom ). The reason is, the answer might depend the syntax supported by various database back-ends. For example, is it:

1. HSQL embedded
2. HSQL external 1.8.0.10
3. HSQL external 2.3.X
4. MySQL
5. H2
6. DB2
7. PostGre
8. SQLite
9. dBase
10. Firebird
11. other etc.

And, when say,

I am using "Service"."Service" LIKE '%' || :Service || '%' to select the Service, and I was thinking about using a "TypeOfService"."TypeOfService" LIKE '%' || :Type || '%', but that does not work.

withOUT giving the entire SQL ( Structured Query Language ) you have written, it is impossible to answer. You canNOT just enter what you wrote, withOUT the words, SELECT and FROM and optionally, WHERE, ORDER etc.

Sliderule

[F3K Total]

@ dreamquartz
an additional wish:
Could you please try to use the tag buttons above the Edit-Window, especially the code tag button, which makes it much easier to read your codes:

tags.png (5.89 KiB) Viewed 13333 times

An example:

Code: Select all

SELECT
    CASE WHEN "Service"."FKTypeOfServiceID" IS NULL
         THEN "Service"."Service"
         ELSE "Service"."Service" || ', ' || "TypeOfService"."TypeOfService"
    END AS "Service" ...

R

[dreamquartz]

Decided to pursue a different route.
Don't know if it works, but will see.

Dream

[dreamquartz]

Please review Survival Guide for the forum:

viewtopic.php?f=5&t=166#p718

Also, always in the Base forum, include the database back-end you are using ( as displayed on the status bar at the bottom ). The reason is, the answer might depend the syntax supported by various database back-ends. For example, is it:

1. HSQL embedded
2. HSQL external 1.8.0.10
3. HSQL external 2.3.X
4. MySQL
5. H2
6. DB2
7. PostGre
8. SQLite
9. dBase
10. Firebird
11. other etc.

And, when say,

I am using "Service"."Service" LIKE '%' || :Service || '%' to select the Service, and I was thinking about using a "TypeOfService"."TypeOfService" LIKE '%' || :Type || '%', but that does not work.

withOUT giving the entire SQL ( Structured Query Language ) you have written, it is impossible to answer. You canNOT just enter what you wrote, withOUT the words, SELECT and FROM and optionally, WHERE, ORDER etc.

Sliderule

Thanks for pointing that out.
Much appreciated.
I run embedded HSQLDB, by the way.

Dream

[dreamquartz]

@ dreamquartz
an additional wish:
Could you please try to use the tag buttons above the Edit-Window, especially the code tag button, which makes it much easier to read your codes:

tags.png

An example:

Code: Select all

SELECT
    CASE WHEN "Service"."FKTypeOfServiceID" IS NULL
         THEN "Service"."Service"
         ELSE "Service"."Service" || ', ' || "TypeOfService"."TypeOfService"
    END AS "Service" ...

R

Makes sense.
Will do.

Dream