SQL Syntax Error

[flash gordon]

Apparently I have a syntax error. Can anyone tell me what it is?

IF TRUE
THEN
  SELECT * FROM contact_chat
ELSE
  SELECT * FROM contact_phone
END IF

 

MySQL Version: MySQL 5.0.75-0ubuntu10.2 via TCP/IP

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF TRUE

THEN

  SELECT * FROM contact_chat

ELSE

  SELECT * FROM contact_phone' at line 1

[neller]

Think you need to add ; to the end of your SQL statements

[flash gordon]

IF TRUE
THEN
  SELECT * FROM contact_chat;
ELSE
  SELECT * FROM contact_phone;
END IF

still says there is an error

[rajivgonsalves]

I think your IF syntax is wrong mostly, what is TRUE ? you have to compare it to something

[flash gordon]

IF (10 > 5)
THEN
  SELECT * FROM contact_chat
ELSE
  SELECT * FROM contact_phone
END IF

 

still an error

[Philip]

Instead of IF, use CASE.

CASE
WHEN (10 > 5)
THEN
  SELECT * FROM contact_chat
ELSE
  SELECT * FROM contact_phone
END

 

If is used like IF(CONDITION, DO_WHEN_TRUE, DO_WHEN_FALSE)

 

The mysql doc will show you this

[flash gordon]

thank you for the reply. This is the doc page on "IF". http://dev.mysql.com/doc/refman/5.0/en/if-statement.html. How do you interpret that to mean IF(CONDITION, DO_WHEN_TRUE, DO_WHEN_FALSE)?

 

Also, copying and pasting your code still gives me a syntax error.

 

thanks again.

[PFMaBiSmAd]

The syntax you are attempting is for stored procedures and functions -

This section describes the syntax for the BEGIN ... END  compound statement and other statements that can be used in the body of stored programs

 

What is your whole query and it would really help if you post the exact error message.

[flash gordon]

that really is my whole query. I'm using MySQL Query Browser to test to get the syntax correct. Here's a screenshot. I'm not trying to make a stored procedure or anything, just a plain ol' query that's a conditional.

 

[attachment deleted by admin]

[flash gordon]

anymore help this morning?

[premiso]

I do not think it is possible to do what you want in MySQL, at least in a query. I have tried many combination's, referred to the manual etc and I do not think you can dynamically define which table to pull the data from as shown. However, if you can figure out a WHERE statement in which you can use the case's to filter the data from the tables, you may be able to achieve this.

[flash gordon]

everything seems to give me an error

SELECT CASE WHEN (10>5)
  THEN (SELECT (5+1))
  ELSE (SELECT (1+1))
END

works

 

but this gives an error

CASE WHEN (10>5)
  THEN (SELECT (5+1))
  ELSE (SELECT (1+1))
END

[Philip]

Because

CASE WHEN (10>5)
  THEN (SELECT (5+1))
  ELSE (SELECT (1+1))
END

Would be suited for a stored procedure/function, and not a query.

 

Try to explain why you want this, because perhaps there is a better way.

[flash gordon]

I'm trying to make a update or insert query. It's insert/updates 4 tables of data. However, not all tables must have data. So I could be updating some and inserting into others.

 

but why would

SELECT CASE WHEN (10>5)
  THEN (SELECT (5+1))
  ELSE (SELECT (1+1))
END

work and not the second one?

[Philip]

Because you're missing "SELECT" from the beginning. CASE is a control flow function, not a data manipulator function like SELECT is. (If I'm wrong, somebody please correct that statement)

 

You'd probably be better off doing this on the other side (PHP or whatever) or make a stored procedure/function to do this.

[flash gordon]

Looks like I'm going to have to do it on the php side, my SQL isn't good enough

But how do I know that IF and CASE is ONLY for stored procedures. This link http://dev.mysql.com/doc/refman/5.0/en/if-statement.html doesn't seem to tell me that.

 

Thanks for all the help.

[Philip]

On that page, after the first paragraph:

Note

There is also an IF() function, which differs from the IF statement described here. See Section 11.3, “Control Flow Functions”.

 

If you look at the example you'll see the one you were looking at was for functions/procedures:

DELIMITER //

CREATE FUNCTION SimpleCompare(n INT, m INT)
  RETURNS VARCHAR(20)

  BEGIN
    DECLARE s VARCHAR(20);

    IF n > m THEN SET s = '>';
    ELSEIF n = m THEN SET s = '=';
    ELSE SET s = '<';
    END IF;

    SET s = CONCAT(n, ' ', s, ' ', m);

    RETURN s;
  END //

DELIMITER ;

[flash gordon]

ok. I thought it was for 2 different things: regular sql and for stored procedures.

 

Thanks for all the help.

 

[RESOLVED]