Avatar billede nicklasw Nybegynder
26. juli 2011 - 11:30 Der er 2 kommentarer og
1 løsning

mssql query, kan ikke finde fejlen (Incorrect syntax near ',')

Hej eksperter.
Jeg har en mssql query jeg prøve at sende igennem php (ikke fordi det er specielt relevant). Men jeg har åbenbart lavet en syntax fejl et eller andet sted.
Queryen skal lave en INSERT i en tabel der hedder invoice ud fra nogle andre tabeler og nogle få konstanter som jeg smider ind via php, men jeg får fejlen Incorrect syntax near ','.

Jeg kan simpelthen ikke finde fejlen.
sql'en ser således ud:

INSERT INTO invoice
(case_id, invoice_name, invoice_address, invoice_housenumber, invoice_postalcode, invoice_floor, invoice_city,
cust_name, cust_address, cust_housenumber, cust_floor, cust_postalcode, cust_city,
del_name, del_address, del_housenumber, del_floor, del_city, del_postalcode,
text, date, payment_date, type, ref, rek, customer_number, made_by, amount, vat, amount_vat, fi_number,
billed_electronic, electronic_date)

SELECT '$caseId',
CASE WHEN (cust.invoice_account = '' OR cust.invoice_account = NULL) THEN
    cust.invoice_name
ELSE
    (SELECT c.c.invoice_name FROM cust_customer AS c WHERE c.customer_number = cust.invoice_account)
END,

CASE WHEN (cust.invoice_account = '' OR cust.invoice_account = NULL) THEN
    cust.invoice_roadname
ELSE
    (SELECT c.c.invoice_roadname FROM cust_customer AS c WHERE c.customer_number = cust.invoice_account)
END,

CASE WHEN (cust.invoice_account = '' OR cust.invoice_account = NULL) THEN
    cust.invoice_housenumber
ELSE
    (SELECT c.invoice_housenumber FROM cust_customer AS c WHERE c.customer_number = cust.invoice_account)
END,

CASE WHEN (cust.invoice_account = '' OR cust.invoice_account = NULL) THEN
    cust.invoice_postalcode
ELSE
    (SELECT c.invoice_postalcode FROM cust_customer AS c WHERE c.customer_number = cust.invoice_account)
END,

CASE WHEN (cust.invoice_account = '' OR cust.invoice_account = NULL) THEN
    cust.invoice_sal
ELSE
    (SELECT c.invoice_sal FROM cust_customer AS c WHERE c.customer_number = cust.invoice_account)
END,

CASE WHEN (cust.invoice_account = '' OR cust.invoice_account = NULL) THEN
    cust.invoice_city
ELSE
    (SELECT c.invoice_city FROM cust_customer AS c WHERE c.customer_number = cust.invoice_account)
END,


cust.customer_name,

cust.customer_roadname,

cust.customer_housenumber,

cust.customer_sal,

cust.customer_postalcode,

cust.customer_city,

cust.customer_name,


del.name,

del.road_name,

del.road_number,

del.floor,

del.city,

del.postalcode,


'$text',

'$date',

'$paymentDate',

'1',


case.yourref,

case.rek,

cust.customer_number,

'$madeBy',

'$amount',

'$vat',

'$amountVat',

base.fi_number,

'0',

NULL

FROM cases AS case, baseData AS base LEFT OUTER JOIN cust_customer AS cust ON cust.id = case.customer_id LEFT OUTER JOIN cust_delivery_addresses AS del ON del.id = case.delivery_address WHERE case.id = '$id'

Håber meget i kan hjælpe mig.
Tak på forhånd
Avatar billede nicklasw Nybegynder
26. juli 2011 - 11:36 #1
Fandt ud af at der stod c.c.columnNavn nogle steder... Dette har jeg nu rettet men det hjalp desværre ikke på problemet
Avatar billede nicklasw Nybegynder
26. juli 2011 - 11:53 #2
Fandt ud af at cust.customer_name var nævnt to gange. dette er nu fjernet og koden ser derved således ud:

INSERT INTO invoice
(case_id, invoice_name, invoice_address, invoice_housenumber, invoice_postalcode, invoice_floor, invoice_city,
cust_name, cust_address, cust_housenumber, cust_floor, cust_postalcode, cust_city,
del_name, del_address, del_housenumber, del_floor, del_city, del_postalcode,
text, date, payment_date, type, ref, rek, customer_number, made_by, amount, vat, amount_vat, fi_number,
billed_electronic, electronic_date)

SELECT '$caseId',
CASE WHEN (cust.invoice_account = '' OR cust.invoice_account = NULL) THEN
    cust.invoice_name
ELSE
    (SELECT c.invoice_name FROM cust_customer AS c WHERE c.customer_number = cust.invoice_account)
END,

CASE WHEN (cust.invoice_account = '' OR cust.invoice_account = NULL) THEN
    cust.invoice_roadname
ELSE
    (SELECT c.invoice_roadname FROM cust_customer AS c WHERE c.customer_number = cust.invoice_account)
END,

CASE WHEN (cust.invoice_account = '' OR cust.invoice_account = NULL) THEN
    cust.invoice_housenumber
ELSE
    (SELECT c.invoice_housenumber FROM cust_customer AS c WHERE c.customer_number = cust.invoice_account)
END,

CASE WHEN (cust.invoice_account = '' OR cust.invoice_account = NULL) THEN
    cust.invoice_postalcode
ELSE
    (SELECT c.invoice_postalcode FROM cust_customer AS c WHERE c.customer_number = cust.invoice_account)
END,

CASE WHEN (cust.invoice_account = '' OR cust.invoice_account = NULL) THEN
    cust.invoice_sal
ELSE
    (SELECT c.invoice_sal FROM cust_customer AS c WHERE c.customer_number = cust.invoice_account)
END,

CASE WHEN (cust.invoice_account = '' OR cust.invoice_account = NULL) THEN
    cust.invoice_city
ELSE
    (SELECT c.invoice_city FROM cust_customer AS c WHERE c.customer_number = cust.invoice_account)
END,


cust.customer_name,

cust.customer_roadname,

cust.customer_housenumber,

cust.customer_sal,

cust.customer_postalcode,

cust.customer_city,


del.name,

del.road_name,

del.road_number,

del.floor,

del.city,

del.postalcode,


'$text',

'$date',

'$paymentDate',

'1',


case.yourref,

case.rek,

cust.customer_number,

'$madeBy',

'$amount',

'$vat',

'$amountVat',

base.fi_number,

'0',

NULL

FROM cases AS case, baseData AS base LEFT OUTER JOIN cust_customer AS cust ON cust.id = case.customer_id LEFT OUTER JOIN cust_delivery_addresses AS del ON del.id = case.delivery_address WHERE case.id = '$id'
Avatar billede nicklasw Nybegynder
26. juli 2011 - 12:34 #3
Jeg har fundet ud af det... Jeg kunne self ikke lave et alias der hedder CASE da det er en sql funtion.
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Kurser inden for grundlæggende programmering

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester