Announcement

Collapse
No announcement yet.

limit or border for the sql code?

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • limit or border for the sql code?

    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(D ATE_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

    Comment


    • #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

      Comment


      • #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

        Comment


        • #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

          Comment


          • #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_testgridbankexportgridb ankexport.php on line 1149,

            line1149:
            WHERE (((DATE_FORMAT(storniert_zu,"%Y%m")>=DATE_FORMAT(@ NextM,"%Y%m"))) OR (storniert_zu IS NULL)) AND&#160; /* 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

            Comment


            • #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

              Comment


              • #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

                Comment


                • #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

                  PHP Code:
                   Select *
                  From table
                  Where field 
                  = \ " 8 \ " 

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

                  Hope this helps you.

                  Regards.
                  Last edited by kafecadm; 06-08-2016, 04:32 PM.

                  Comment

                  Working...
                  X