limit or border for the sql code?

Applications SQL Builder
#1

hi,

i have the evaluationversion and a question for the sql code:

can i do that ( its only the end of the code):

FROM
auftraege
LEFT JOIN export_einzug ON auftraege.lfnr=export_einzug.auftrag_lfnr
INNER JOIN artikel ON auftraege.produkt_kl = artikel.id
INNER JOIN artikelgruppe ON artikel.gruppe = artikelgruppe.id
LEFT JOIN anrede ON auftraege.c_anrede = anrede.id
INNER JOIN artikel_zw ON auftraege.zahlungsweise=artikel_zw.lfnr
WHERE
(((DATE_FORMAT(storniert_zu,"%Y%m")>=DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL 1 MONTH),"%Y%m"))) OR (storniert_zu IS NULL)) AND /* storno in zukunft oder nicht storniert /
DATE_FORMAT(export_einzug.exp_datum,"%Y%m")<>DATE_FORMAT(NOW(),"%Y%m") AND
(DATE_FORMAT(spielbegin,"%Y%m") <= DATE_FORMAT(NOW(),"%Y%m"))AND
/auftraege.spielbegin <= “2009-09-03” AND/
(auftraege.status_kontrolle = “2” OR auftraege.status_kontrolle = “8”) AND
auftraege.status_export = “1” AND
auftraege.EZwann=“15” AND
/ if((:EZwann=1),auftraege.EZwann=:EZwann, 1=1) and*/
artikel.gruppe=“32” AND
(
((auftraege.zahlungsweise= “2” OR “8”) AND /* quartalsweise o. halbjaehrlich */
(auftraege.spielbegin <= DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL 1 MONTH),"%Y-%m-01"))AND
(MONTH(DATE_ADD(auftraege.spielbegin, INTERVAL 3 MONTH))= MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH))OR
MONTH(DATE_ADD(auftraege.spielbegin, INTERVAL 6 MONTH))= MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH))OR
MONTH(DATE_ADD(auftraege.spielbegin, INTERVAL 9 MONTH))= MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH))OR
MONTH(DATE_ADD(auftraege.spielbegin, INTERVAL 12 MONTH))= MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH))))OR
(auftraege.zahlungsweise=“1”)/zahlungsweise mtl./ OR
(artikel.gruppe IN (2,3,4,5))
)

GROUP BY auftraege.lfnr
ORDER BY auftraege.spielbegin,
auftraege.zahlungsweise,
auftraege.produkt_kl,
auftraege.export_lfnr,
auftraege.lfnr

i have tried that but i get an error: syntax error in the sqlstatement check the code.

is there a solution?

greating

sam

#2

Re: limit or border for the sql code?

quite a lot to check here in the forum.

May be I would suggest to check the SQL phrase step by step in the database enviroment. (So wuerde ich es machen)
May be there is just a char missing or to much.

Best regards

Eric

#3

Re: limit or border for the sql code?

Sam,
It’s very difficult to pick out unless I have all the information.

Can you send me a sqldump of the database schema, and the full sql statement you’re trying to execute?
I don’t need data.

I should be able to figure it out for you.

Alan

#4

Re: limit or border for the sql code?

Sam
I would need the schema (table definition) of all the tables that are in this query.

You can send via private message, that would be fine.

Alan

#5

Re: limit or border for the sql code?

hi,

thankyou for the answers. the code is running on SQLyog. so there is no syntaxerror. but give me time to learn more about scriptcase. in the moment i make the course V4. give me time to answer. i think to the end off the week.

greatings from spain

sam

#6

Re: limit or border for the sql code?

ok, here i am again,

15 days left for evaluation. And here is the error.
in the sql builder i became a result. see the pictures. please help.
i could not bay a program with a bug. or is that a capacity problem. (sql to complex).

  1. Parse error: parse error in D:wwwscriptcaseappank_exp_testgridbankexportgridbankexport.php on line 1149,

line1149:
WHERE (((DATE_FORMAT(storniert_zu,"%Y%m")>=DATE_FORMAT(@NextM,"%Y%m"))) OR (storniert_zu IS NULL)) AND /* storno in zukunft oder nicht storniert */

[edit]

i hope that helps.
i only have 5 days to try if we can work in the future with scriptcase.

greeting

sam

#7

Re: limit or border for the sql code?

hi to all,

sorry was my fault. a syntax problem.

if you read the script you will see " instead of '. i chanced that and every thing runs. but i do not understand, that sql prog runs with that, including the sql builder from scriptcase

sam

#8

Re: limit or border for the sql code?

Just a suggestion… when you are working with longs selects, try to use view.

Just thinking in maintenance.

Cauze if you need to change de tables or where … you just change in the database, you dont need to change in the application.

ANd your application will only have a simple sql like

SELECT
field1,
field2,
field3
FROM
VIEW

#9

Hello man: i think that your erorr is the parse of the SQL query.

Remember that scripcase is generating code wich means that he will commonly use " (double quotes) to set query strings to the database if you use those kind of chars you need to use something like

 Select *
From table
Where field = \ " 8 \ "

or in anycase use single quotes. that is the base of your problem.

Hope this helps you.

Regards.