SQL Error 22 "Table Not Found" but table exists

[BournvilleBob]

Hello,
I am trying to learn Base by working through the Base Guide (7.3) and in Chapter 8 “Database Tasks” there is an example that has me completely stumped. It is the very first exercise in the chapter where the aim is to create a data filtering query using a simple single lin table - “Filter”. When I try to run the query
SELECT * FROM “Media”
WHERE “Title” = IFNULL( ( SELECT “Filter_1” FROM “Filter” ), ‘Title’ )
I get the SQL Error -22 which I believe means ‘Table not found’

I then down loaded the sample database (“Media without Macros”) from the Wiki thinking it was an error with my table structure and because there was a table called “Filter” already existing, I created new table (“FilterEx”) and amended my query thus
SELECT * FROM “Media”
WHERE “Title” = IFNULL( ( SELECT “Filter_1” FROM “FilterEx” ), ‘Title’ )
Same Error!
I registered the database in both cases.
I realise that it is going to be something really simple, but how do I tell the application that the table is there?
Robert

[RobertG]

@BournvilleBob

SELECT * FROM “Media”
WHERE “Title” = IFNULL( ( SELECT “Filter_1” FROM “Filter” ), ‘Title’ )

Code is wrong. There must be double quotes around the last “Title”.

SELECT * FROM "Media"
WHERE "Title" = IFNULL( ( SELECT "Filter_1" FROM "Filter" ), "Title" )

Hope the tables “Media” and “Filter” and also the fields have been created. It should show all rows from “Media”, if there isn’t an entry in field “Filter_1” in table “Filter”. Then there is “Title” = “Title”.
But there must be also other errors, because you could set “Title” = ‘Title’. Then the content of field “Title” should be ‘Title’. Have a look at the right double quotes. Add a screenshot from the error please.

Also Robert (who created the original as “Base Handbuch” in German)

[BournvilleBob]

Hello Robert
Yes, changing to double quotes for the last “Title” allowed the query to run. Thank you.
However, the query only showed an empty line (see screen shot 1)

As I understand it, the query should return all the items in “Media” table including the one that I have deleted the title from.

This is the table FilterEx

Is there a way I can see if the the field ‘Filter_1’ is NULL?
Many thanks
Robert L

[cpb]

I downloaded the example database “Media_without_Macros.odb”.
.
the SQL you initially posted is valid.
.
the table “Filter” does not have a field called “Filter_1”.
the table “Filter” does however have a field called “Filter”.
the field “Filter” is empty i.e. NULL.
.
‘Title’ is the default title which will be used when/if “Filter”.“Filter” is null (which it is).
replace ‘Title’ with an existing “Title” contained in the table called “Media”.
.
e.g. replaced ‘Title’ with ‘Der kleine Hobbit’.

SELECT * FROM "Media"
WHERE "Title" = IFNULL( ( SELECT "Filter" FROM "Filter" ), 'Der kleine Hobbit' )

having had a quick look I don’t see a form which enables you to choose and save a “Media”.“Title” to the table “Filter”.
.
if you open the tables “Media” and “Filter” then copy and paste a “Media”.“Title” into “Filter”.“Filter” then the default title will be ignored.

[RobertG]

@BournvilleBob : Fields without content will be saved as NULL. So “Filter_1” in your second screenshot is NULL.
The query would run with

SELECT * FROM "Media" AS "T" WHERE "Title" = "Title"

Don’t know why you created an alias “T”. “Title” = “Title” will only show the content if “Title” isn’t NULL. So try this:

SELECT "Media".*, 
IFNULL("Title",'') AS "T" 
FROM "Media" 
WHERE "T" = IFNULL((SELECT "Filter_1" FROM "FilterEx"),"T")

Should also be shown in Base Guide. Here, in German Base Handbuch 7.4, it’s in chapter “Filter Data”.

@cpb : Code in example database may differ. Don’t know the moment I have changed the table “Filter”.

[BournvilleBob]

Good morning gents.
Thank you for your help. I cannot make tests at the moment as I am at work. Will try tonight and report back.
Thanks again
Robert

[BournvilleBob]

Thank you, it worked!
I have a lot to learn about SQL

Robert