| |
| |
SQL Statement zu lang- #23740 in section [741231] |
| Sec: |
(1) Allgemeines |
| Von: |
Ulf Neugebauer |
| An: |
Harro Schippan |
| Am/Um: |
23.04.2018 09:50:54 gelesen |
Hallo Harro,
das Problem hatte ich auch mal.
* - Error 1812 - SQL: Anweisung zu lang - verhindern
da hilft z.B. SYS(3055,1000)
Was mir auch geholfen hast ist das Zusammenbauen per Textmerge und die Ausführung per. Execscript().
Anbei ein Beispiel, das man leicht Nachvollziehen kann.
Ist eine Kategorie nicht ausgewählt (lcGes = "") fliegt sie raus, ansonsten je nach Bedingung:
DO Case
CASE This.cGes = "w"
lcGes = " AND Cdbw1.Geschlecht = 2"
CASE This.cGes = "m"
lcGes = " AND Cdbw1.Geschlecht <> 2"
ENDCASE
wird sie eingefügt bzw. evaluiert << lcGes >>.
LOCAL lcScript AS String ,;
lcBetrieb AS String ,;
lcZeitraum AS String ,;
lcAnw AS String ,;
lcTyp AS String ,;
lcDia AS String ,;
lcBeh AS String ,;
lcMed AS String ,;
lcGes AS String ,;
lcSuche AS String ,;
lcTier AS String ,;
lcHgList AS String ,;
lcLaList AS String ,;
lcBeleg AS String ,;
lcCharge AS String
lcZeitraum = " Farm_09.Datum >= {" + TRANSFORM( This.dVon ) + "} AND Farm_09.Datum <= {" + TRANSFORM( This.dBis ) + "}"
lcBetrieb = " AND Farm_09.Bnrc = '" + gcBnrc + "' AND Cdbw1.Bnrc = '" + gcBnrc + "'"
IF EMPTY( This.cAeList )
This.cAeList = PADL( TRANSFORM( gnAe ) ,2 ,"0" )
ENDIF
lcBetrieb = lcBetrieb + " AND Farm_09.Ae IN (" + This.cAeList + ") AND Cdbw1.Ae IN (" + This.cAeList + ") "
lcAnw = IIF(This.cAnw == "-" ,"" ," AND Farm_09.Name = '" + This.cAnw + "'" )
lcTyp = IIF(This.cTyp == "-" ,"" ," AND Farm_09.Typ = '" + This.cTyp + "'" )
lcBeh = IIF(This.cBeh == "-" ,"" ," AND Farm_09.B_Bez = '" + This.cBeh + "'" )
lcMed = IIF(This.cMed == "-" ,"" ," AND Farm_09.Medikament = '" + This.cMed + "'" )
lcDia = IIF(This.cDia == "-" ,"" ," AND Farm_09.D_Bez = '" + This.cDia + "'" )
lcGes = ""
DO Case
CASE This.cGes = "w"
lcGes = " AND Cdbw1.Geschlecht = 2"
CASE This.cGes = "m"
lcGes = " AND Cdbw1.Geschlecht <> 2"
ENDCASE
lcHgList = ""
*-- Haltungsgruppe aktuell
IF !EMPTY( This.cHgList )
lcHgList = lcHgList + " AND Cdbw1.Hg IN (" + This.cHgList + ") "
ENDIF
*-- Haltungsgruppe zur Untersuchung
IF !EMPTY( This.cHgListAlt )
lcHgList = lcHgList + " AND Farm_09.Hg IN (" + This.cHgListAlt + ") "
ENDIF
lcLaList = ""
*-- Laktation aktuell
IF !EMPTY( This.cLaList )
lcLaList = lcLaList + " AND Cdbw1.Laktation IN (" + This.cLaList + ") "
ENDIF
*-- Laktation zur Untersuchung
IF !EMPTY( This.cLaListAlt )
lcLaList = lcLaList + " AND Farm_09.Laktation IN (" + This.cLaListAlt + ") "
ENDIF
lcBeleg = ""
IF !EMPTY( ALLTRIM(This.cBeleg) )
lcBeleg = " AND Farm_09.Beleg = '" + ALLTRIM(This.cBeleg) + "'"
ENDIF
lcCharge = ""
IF !EMPTY( ALLTRIM(This.cCharge) )
lcCharge = " AND Farm_09.Charge = '" + ALLTRIM(This.cCharge) + "'"
ENDIF
*-- Tierfilter
lcSuche = CHRTRAN( This.cSuch ,CHRTRAN( This.cSuch ,"1234567890" ,"" ) ,"" )
lcTier = ""
IF !EMPTY( lcSuche )
IF This.nSuch = 1
*-- StNr
lcTier = " AND Cdbw1.StNr = " + TRANSFORM( lcSuche )
ELSE
*-- Ohrnummer
lcTier = " AND Farm_09.Ohrnummer LIKE '" + PADL( lcSuche ,15 ,"_" ) + "'"
ENDIF
ENDIF
TEXT TO lcScript NOSHOW TEXTMERGE PRETEXT 7
SELECT Cdbw1.ohrnummer ,;
Cdbw1.Stnr ,Farm_09.stnr AS StNrAlt ,;
Cdbw1.hg ,Farm_09.hg AS HgAlt ,;
Cdbw1.fg ,Farm_09.fg AS FgAlt ,;
Cdbw1.Laktation ,Farm_09.laktation AS LaktAlt ,;
Cdbw1.Geburt ,;
Cdbw1.Abgang ,;
Cdbw1.Zugang ,Cdbw1.Rasse ,;
Cdbw1.Geschlecht ,Cdbw1.mast ,;
Cdbw1.Archiv ,;
Farm_09.datum ,;
Farm_09.typ ,;
Farm_09.diagnose AS d_code ,;
Farm_09.b_bez ,;
Farm_09.behandlung AS b_code ,;
Farm_09.d_bez ,;
Farm_09.m_code ,;
Farm_09.medikament ,;
Farm_09.medikament AS m_bez ,;
Farm_09.charge ,;
Farm_09.charge AS m_charge,;
Farm_09.beleg ,;
Farm_09.beleg AS m_beleg ,;
Farm_09.menge ,;
Farm_09.menge AS m_menge,;
Farm_09.einheit ,;
Farm_09.einheit AS m_einheit ,;
Farm_09.nomelk_dat AS ms_datum,;
Farm_09.m_ms ,;
Farm_09.fleischspe AS fs_datum ,;
Farm_09.m_fs ,;
Farm_09.kont_datum AS ko_datum ,;
Farm_09.m_ko ,;
Farm_09.a_code ,;
Farm_09.name AS a_bez ,;
Farm_09.erledigt ,;
Farm_09.kosten,;
Farm_09.standort ,;
Farm_09.erfassung ,;
Farm_09.bnrc ,;
Farm_09.ae ,;
Farm_03.Ae_Name ,;
Farm_09.status ,;
Farm_09.ew_status ;
FROM Farm32!Farm_09 ;
INNER JOIN Farm32!Cdbw1 ;
ON (Farm_09.ohrnummer = Cdbw1.ohrnummer ;
AND Farm_09.Ae = Cdbw1.Ae ;
AND Farm_09.Bnrc = Cdbw1.Bnrc) ;
INNER JOIN Farm32!Farm_03 ;
ON (Farm_09.Bnrc = Farm_03.Bnrc AND Farm_09.Ae = Farm_03.Ae) ;
WHERE << lcZeitraum >> << lcBetrieb >> << lcAnw >> << lcTyp >> ;
<< lcDia >> << lcBeh >> << lcMed >> << lcHgList >> << lcTier >> ;
<< lcGes >> << lcLaList >> ;
<< lcCharge >> << lcBeleg >> ;
ORDER BY Farm_09.datum, Farm_09.stnr, Cdbw1.ohrnummer ;
INTO CURSOR vmBBuch READWRITE
ENDTEXT
EXECSCRIPT(lcScript)
mfg
Ulf Neugebauer
Fox on The Run (The Sweet)
|
|
|
|
