Error in SQL syntax - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Error in SQL syntax

Author

Topic

eflying
Starting Member

3 Posts

Posted - 2012-06-08 : 10:09:03

Hi at all,

Unfortunately I am not a specialist and now am not getting on anywhere.
Can someone help me? I wish that the query works in this sense.
Thank's a lot for your help!

select ps_product.id_product,ps_manufacturer.name,ps_product.reference,ps_product_lang.name,

ps_specific_price.reduction_type, (CASE WHEN ps_specific_price.reduction_type = amount THEN
round(ps_product.price-ps_specific_price.reduction,2),
ELSE
round(ps_product.price-(ps_product.price*ps_specific_price.reduction/100,2))END,
available_now,ean13
FROM ps_product,ps_product_lang,ps_manufacturer,ps_specific_price
WHERE ps_product_lang.id_product = ps_product.id_product
AND id_lang = 3 AND
ps_product.id_manufacturer = ps_manufacturer.id_manufacturer
AND ps_specific_price.id_product = ps_product.id_product
AND ps_product.active = 1

Error:
MySQL meldet: Dokumentation
#1064 - 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 '
ELSE
round(ps_product.price-(ps_product.price*ps_specific_price.reduction/100,2' at line 4

Ida Hoe
Starting Member

3 Posts

Posted - 2012-06-08 : 10:28:38

quote:
Originally posted by eflying

Hi at all,

Unfortunately I am not a specialist and now am not getting on anywhere.
Can someone help me? I wish that the query works in this sense.
Thank's a lot for your help!

select ps_product.id_product,ps_manufacturer.name,ps_product.reference,ps_product_lang.name,

ps_specific_price.reduction_type, (CASE WHEN ps_specific_price.reduction_type = amount THEN
round(ps_product.price-ps_specific_price.reduction,2),
ELSE
round(ps_product.price-(ps_product.price*ps_specific_price.reduction/100,2))END,
available_now,ean13
FROM ps_product,ps_product_lang,ps_manufacturer,ps_specific_price
WHERE ps_product_lang.id_product = ps_product.id_product
AND id_lang = 3 AND
ps_product.id_manufacturer = ps_manufacturer.id_manufacturer
AND ps_specific_price.id_product = ps_product.id_product
AND ps_product.active = 1

Error:
MySQL meldet: Dokumentation
#1064 - 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 '
ELSE
round(ps_product.price-(ps_product.price*ps_specific_price.reduction/100,2' at line 4

First off it looks like you're missing a right paren ..

 )))END,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-08 : 12:01:42

error message shows MySQL which indicates you're using MySQL. Please keep in mind that this is MS SQL Server forum and we deal mostly with transact sql. So though we might be able to suggest you on generic approaches we wont be able to suggest you much on MySQL specific syntax. So you may be better off trying you luck at MySQL forums like www.dbforums.com

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

eflying
Starting Member

3 Posts

Posted - 2012-06-08 : 12:04:47

Thank you!

Now it is the Error on the 2nd last line:

This is the comlete Script:

<?php
header('Content-type:text/plain');
//tiny script to generate an export for Domain....
include('config/......php');
$dbprefix=_DB_PREFIX_;
$myshopUrl=".......";
$myDbConnect = mysql_connect(_DB_SERVER_,_DB_USER_,_DB_PASSWD_);
if (!$myDbConnect) {
die('keine Verbindung zur DB möglich: ' . mysql_error());
}
$dbSelected = mysql_select_db(_DB_NAME_);
if (!$dbSelected) {
die('DB nicht gefunden');
}
$result = mysql_query("select ps_product.id_product,ps_manufacturer.name,ps_product.reference,ps_product_lang.name,

ps_specific_price.reduction_type, (CASE WHEN ps_specific_price.reduction_type = amount THEN
round(ps_product.price-ps_specific_price.reduction,2),
ELSE
round(ps_product.price-(ps_product.price*ps_specific_price.reduction/100,2))END,
available_now,ean13
FROM ps_product,ps_product_lang,ps_manufacturer,ps_specific_price
WHERE ps_product_lang.id_product = ps_product.id_product
AND id_lang = 3 AND
ps_product.id_manufacturer = ps_manufacturer.id_manufacturer
AND ps_specific_price.id_product = ps_product.id_product
AND ps_product.active = 1;");
if (!$result) {
die('Ungültige Abfrage: ' .mysql_error());
}
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
printf ("$myshopUrl/product.php?id_product=%s~~%s~~%s~~%s~~%s~~%s~~%s\n", $row[0], $row[1],$row[2],$row[3],$row[4],$row[5],$row[6]);
}
mysql_close($myDbConnect);
)))END,

?>

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-08 : 12:07:48

i think braces should be outside END to complete CASE WHEN

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

eflying
Starting Member

3 Posts

Posted - 2012-06-08 : 12:10:41

Thank you very much, so I switch over to the MySQL-Forum. Best regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources