Using IF statement to create View

[d0nny]

Hi
I am trying to create a View in my DB using an IF statement in my SELECT section of the CREATE VIEW statement, but the results I achieve seem to be very random.

I have a table with football teams in it with scores for each team. I want to now create some views so that the league table will automatically update based on info in the MATCHES table. I thought I would do this using a VIEW to create the points, then another view to add the points, etc.
Here's my CREATE VIEW statement:

CREATE VIEW homeresults AS
SELECT `home`,`htot`,`away`,`atot`,
if(`htot`=`atot`,1,if(`htot`>`atot`,3,0)) AS `Pts`
FROM `matches` WHERE `date` < NOW();

When I view the `homeresults` VIEW, I would expect that any team which has a higher `htot` would have 3 points, a draw (`htot`=`atot`) would have 1 point, otherwise 0 points.
But the points allocated in the VIEW seems very random. Some are correct, but on the whole the VIEW seems not to be following my logic.

Any help?

 

[d0nny]

I've been doing some further testing and still have problems with my conditions relating to how the VIEW is built.
Here's another example of another VIEW I created using the same data:

CREATE VIEW home AS
SELECT `round`,`home`,`htot`,`away`,`atot`
FROM `results` WHERE `round` = 1 and `htot`>`atot`

So, I'm hoping this creates a VIEW with only results from round 1 and only where the home total (htot) is greater than the away total (atot).
But I still get random results. I get 3 records in the new VIEW - 2 are where the `atot` are greater than the `htot` and 1 which is correct. But there should also be other records on this view based on the conditions I have set to create the view.

Any ideas/help?

 

[r937]

CREATE VIEW homeresults 
AS
SELECT home
     , htot
     , away
     , atot
     , CASE WHEN htot=atot THEN 1
            WHEN htot>atot THEN 3
            ELSE 0 END AS Pts
  FROM matches 
 WHERE `date` < CURRENT_DATE

r937.com | rudy.ca

 

[d0nny]

Nope. That doesn't work either. Still get random results.
It seems the 1 point works fine (htot=atot) but the other case statement is very random (htot>atot). I get a combination of 3 points and 0 points - some 3 points when atot>htot and some 0 points when htot>atot.

Could this be something to do with how the data is stored (character sets)?

 

[r937]

please show a few sample rows of real data

then show the query that you are using to produce your weird results

r937.com | rudy.ca

 

[d0nny]

Is it possible to create a VIEW using an ORDER BY statement of the CREATE VIEW statement? If so, the order is all out of sync as well.

But here we go:
Sample data (RESULTS table):

date | round | div | home | htot | away | atot
2008-05-10 | 1 | South | Team A | 10 | Team B | 4 
2008-05-10 | 1 | North | Team C | 4 | Team D | 2 
2008-05-17 | 2 | South | Team B | 3 | Team A | 3
2008-05-17 | 2 | North | Team D | 3 | Team C | 4

The CREATE VIEW query:

CREATE VIEW homeresults AS
SELECT `round`,`div`,`home`,`htot`,`away`,`atot`,
CASE WHEN `htot`=`atot` THEN 1
WHEN `htot`>`atot` THEN 3
ELSE 0 END AS Pts
FROM `results`
WHERE `date` < NOW()

But then the following statement:

SELECT * from homeresults

produces weird results. From the above data, I would expect the Pts column to contain 3 for the first entry, 3 for the second entry, 1 for the third entry and then 0 for the last entry.

 

[r937]

gee, that's funny

here's what i get when i tested it --

home   htot  away   atot  Pts
Team A   10  Team B    4    3
Team C    4  Team D    2    3
Team B    3  Team A    3    1
Team D    3  Team C    4    0

r937.com | rudy.ca

 

[d0nny]

I tested it using the sample data about within my RESULTS table and got this:

home   htot  away   atot  Pts
Team A   10  Team B    4    0
Team C    4  Team D    2    3
Team B    3  Team A    3    1
Team D    3  Team C    4    0

As I say, could it be something to do with the way the data is stored in the RESULTS table?
The htot and atot columns are VARCHAR data types.

 

[d0nny]

I think I've just fixed it.
I changed the data types in the RESULTS table to be INT and then queried the VIEW again and hey presto! It works!

My dumb fault ...
Thanks for your help!

;-(