Tip:
Highlight text to annotate it
X
I'm using the
odb17 intro to queries database
for this tutorial in this video we're going to take another look
creating a query in SQL and look at some other common mistakes and errors people
make
some typical error messages they might get
and discuss a few things you can do to try and figure out
what went wrong in how to fix the errors We're going to create
a query in SQL View. when the
SQL screen opens up it's blank
there's nothing on it. this can be a little intimidating
but let's try and remember from our last video what we need to create
some SQL code we need select
some field names then from
and the table name easy enough, let's type that in
everything looks good
I'm going to go up and ran it by clicking the run query button
and we get an error message. the error message says
the data content could not be loaded column that found
last name in statement select last name first name department from
table employees so this is telling us that
it did find the table but it did not
find the fields. if we look at our fields again
last name and first name Department
on our table we started our fields with a capital letter
so let's type that in
now it looks good. let's give it a try I'm hitting
F5 and it runs
now let's see what would happen if we'd left out a comma
if we leave out this first-comma
and try to run it, again I'm hitting
F5 to run it shows us that it found
last name and it found Department
but it left out first name but it still ran
I'm going to add that one back - actually what happens
if we
are missing to commas I try and run it now
F5 we get a syntax error
let's see what happens if we have
two commas together, if I try and run this
I'm hitting f5 - a syntax
error and if we have an
extra camera here at the end again I'm hitting f5
and get another syntex error; this syntex here doesn't really tell us too much
but one thing we might want to check is are other appropriate number of commas
and
are they in the right place. I'm going to select
all the code delete it and paste some new code
here we've got our select some field names
from and our table name that all looks good I'm going to try and run this by hitting f5
and I get syntex error in SQL statement will check tablename
that spelled correctly last name spelled correctly first name we've got
proper case everything looks good but we can't figure out the problem
let's try and go over to design view
clicking that, tells us that
when we have an SQL syntax error
it cannot move is over into design view
one thing we can do is
going to cut out this code and
paste it here into this text file and because we don't even have a table in here
we can't go over to design view
I'm going to exit out I don't want to save the
changes. I'm going to open up design view add our table
add the last name
add the first name and run it
so we see it's working
now and going to go back over into SQL View
I'm going to copy and paste
our code from a text file and we can see that
we only had single quotes around the field names
and table name we needed double quotes so if we
can't figure out what's wrong with can recreate
the query in design view and then compare the two
so we don't need this code anymore
and let's reformat this so we can read it a little easier
the select and the from
are always capitalized do we need these capitalized
I'm going to try making this lower case
and this one
I'm going to start lower case and then go to upper case
and try and run it hitting f5
shows that it runs so it doesn't really matter
if our select in from are lower case
uppercase or any combination but if we go over to design view
and then back, it converts those
back to uppercase since uppercase is the standard that LIbreOffice Base likes
we're going to stick with that I'm going to reformat this again
these double quotes... do we need these double quotes
I'm going to take them off table
I'm going to hide the result set hitting f5
shows we do not need double quotes
on our table name how about on our field names
If I take them off a fieldname
and try and run it
there we get an error
is it the field names or is it because we have
a space in the field name. the way to test this
is add in Department without the double quotes
I'm going to hide the result set and hit f5
so we do not need the double quotes unless we have
a space in the field name
and I believe the same for the table name we do not need the double quotes
unless
there is a space in their again if we swap
over to design view
and then come back we see that it added in
those double quotes again since Libre office base ads in those double quotes
we're going to keep their is a standard and put all our fields
in double quotes the last thing we're going to look at
is right here I typed this in to a Libra office
writer document. I'm going to select and copy
going to select our entire query
control+v to paste. looks very good
I'm going to get f5 in trying and run this
and we get an error - data content could not be loaded
table not found in statement select
so it couldn't even find the table to look to see if the fields are good
how can this be when I typed this into a Writer document
exactly how would was in a working
query so we checked the spelling
and the case of the table name that all looks correct
looks like we have proper quotes
but if I run this again
Let's take a closer look at from the
table looks like we've get to different quotes here
to sets of double quotes
if I and a double quote outside
and another one on this side we see
our table name turns orange and if we remember
earlier orange seem to be the color that was working for us
we can also see these are definitely
two different types of quotes so I'm going to take out
the old ones and we've got the same
for our entire query
now if I run this, it runs. so if I select this all
and copy, and I come back over to our writer document
I'm going to paste that in
and definitely we can see that the quotes that we need are not
in this top part but the proper quotes are
down below I'm going to delete the one that is bad
and keep the one that is good
i'm talking about this now because in my previous video and probably in my future videos
my code is just going to work because I tested it out
and we want to keep the video moving but if the viewer is typing along at home
and especially someone new to SQL I want to give them some basic starting
points and how to go about debugging on their own
the SQL code all all cool
all all