- 01
- 02
- 03
- 04
- 05
- 06
- 07
- 08
- 09
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
sSQLTitle := 'SELECT  CASE WHEN (VTSS.WRKORDNO IS NOT NULL)'
    +' THEN VTSS.WRKORDNO ELSE VTW.WRKORDNO END AS '''+RSNumDok+''','
    +' CASE WHEN (VTSS.GRECNO IS NOT NULL) THEN VTSS.GRECNO ELSE VTW.GRECNO END AS '''+RSSchet+''','
    +' CASE WHEN (VTSS.STYPE IS NOT NULL) THEN VTSS.STYPE ELSE VTW.STYPE END AS '''+RSDocType+''','
    +' CASE WHEN (VTSS.ServD IS NOT NULL) THEN VTSS.ServD ELSE VTW.ServD END AS '''+RSOpen+''','
    +' CASE WHEN (VTSS.BILLD IS NOT NULL) THEN VTSS.BILLD ELSE VTW.BILLD  END AS '''+RSClose+''','
    +' CASE WHEN (VTSS.FULLNAME IS NOT NULL) THEN VTSS.FULLNAME ELSE VTW.FULLNAME  END AS '''+RSClient+''','
    +' CASE WHEN (VTSS.PAYNAME IS NOT NULL) THEN VTSS.PAYNAME ELSE VTW.PAYNAME  END AS '''+RSPlatelwik+''','
    +' CASE WHEN (VTSS.VIN IS NOT NULL) THEN VTSS.VIN ELSE VTW.VIN  END AS '''+RSVIN+''','
    +' CASE WHEN (VTSS.GNUMER IS NOT NULL) THEN VTSS.GNUMER ELSE VTW.GNUMER  END AS '''+RSGosNomer+''','
    +' CASE WHEN (VTSS.HSMANID IS NOT NULL) THEN VTSS.HSMANID ELSE VTW.HSMANID  END AS '''+RSOtvetstv+''','
    +' CASE WHEN (VTSS.LBACK IS NOT NULL) THEN VTSS.LBACK ELSE VTW.LBACK  END AS '''+RSReturn+''','
    +' VTSS.FULLSUMMA AS '''+RSSumItems+''','
    +' VTW.SUMMA AS '''+RSSumWork+'''';
with DataMdl.adsADODataSet do
  begin
    Active := false;
    if rbAllDate.Checked then
    {$REGION 'Поиск по всем датам'}
      case rgZakazType.ItemIndex of
        0: //поиск по открытым заказ-заявкам
        CommandText:=sSQLTitle
          +' FROM'
          +' (SELECT  VTS.WRKORDNO,'
       (...)
          +' SUM(SUMMA) AS FULLSUMMA'
          +' FROM'
          +' (SELECT GS.WRKORDNO,'
          +' GB.GRECNO,'
          + sSQLCase
          +' CONVERT(varchar(10), GS.ServD, 104) AS ServD,'
          +' CONVERT(varchar(10), GB.BILLD,104) AS BILLD,'
          +' CASE'
          +'	WHEN C.FNAME IS NULL THEN C.LNAME'
          +'	WHEN C.LNAME IS NULL THEN C.FNAME'
          +' ELSE C.FNAME+'' ''+C.LNAME'
          +'	END AS FULLNAME,'
//          +' C.LNAME + '' '' + C.FNAME AS FULLNAME,'
          +' CS.PNAME AS PAYNAME,'
          +' V.SERIALNO AS VIN,'
          +' V.LicNo AS GNUMER,'
          +' GB.HSMANID,'
          +' CASE WHEN (GS.CREDIT IS NULL)OR (GS.CREDIT <> 1) THEN NULL ELSE '''+RSReturn+''' END AS LBACK,'
          +' GW.NAME,'
          +' SUM(GW.ORDNUM) AS '''+RSQuantity+''','
          +' SUM(GW.ORDNUM)*GW.UNITPR AS SUMMA'
          +' FROM GSAL'+sPostfix+' GS LEFT OUTER JOIN'
          +' GROW'+sPostfix+' GW ON (GS.GSALID = GW.GSALID)  LEFT OUTER JOIN'
          +' GBIL'+sPostfix+' GB ON (GS.GSALID = GB.GSALID) LEFT OUTER JOIN'
          +' VEHI V ON (GS.VEHIID = V.VEHIID) LEFT OUTER JOIN'
          +' CUST C ON (GS.CUSTNO = C.CUSTNO) RIGHT OUTER JOIN'
          +' (SELECT     CustNo, Lname + '' '' + FNAME AS PNAME'
          +' FROM CUST WITH (NOLOCK))'
          +' CS ON (GB.CUSTNO = CS.CUSTNO)'
          +' WHERE (GS.STATUS = ''A'') AND (GW.RTYPE IN (1,2))'  +sSQLWhere
          +' GROUP BY  GW.ITEM, GW.NAME, GW.UNITPR,'
          +' GS.WRKORDNO, GB.Grecno, GS.ServD, GB.BILLD, C.LNAME, C.FNAME,'
          +' CS.PNAME, V.SERIALNO, V.LicNo, GB.HSMANID, GS.CREDIT, GW.RECMTIME, GS.STYPE)'
          +' VTS'
          +' CASE WHEN (GS.CREDIT IS NULL)OR (GS.CREDIT <> 1) THEN NULL ELSE '''+RSReturn+''' END AS LBACK,'
          +' GW.NAME,'
          +' SUM(GW.ORDNUM) AS '''+RSQuantity+''','
          +' SUM(GW.ORDNUM)*GW.UNITPR AS SUMMA'
          +' FROM GSAL'+sPostfix+' GS LEFT OUTER JOIN'
          +' GROW'+sPostfix+' GW ON (GS.GSALID = GW.GSALID)  LEFT OUTER JOIN'
          +' GBIL'+sPostfix+' GB ON (GS.GSALID = GB.GSALID) LEFT OUTER JOIN'
          +' VEHI V ON (GS.VEHIID = V.VEHIID) LEFT OUTER JOIN'
          +' CUST C ON (GS.CUSTNO = C.CUSTNO) RIGHT OUTER JOIN'
          +' (SELECT     CustNo, Lname + '' '' + FNAME AS PNAME'
       
     ...
                                     
        
            ... и так далее. НАПИСАНО ВРУЧНУЮ! Всего 420 строк, 4 подзапроса, связанных через full outer join. 16 тысяч записей 4-x ядерный mssql server обрабатывал таким образом минуты две. Я уж промолчу, как он по строкам  в Delphi разбит...
Два дня врубался, как оно работает, переписал за два часа. 58 строк, включая каждое поле из select с новой строки :)