[OT] είναι η SQL γλώσσα προγραμματισμού?

[Nick Demou]

[ανέκδοτη ιστορία μόνο για προγραμματιστές και SQLόπληκτους]

πριν μια βδομάδα κάποιος μου μίλησε για την SQL αναφέροντας την ως
γλώσσα προγραμματισμού. Του εξήγησα τότε ότι ο χαρακτηρισμός αυτός
είναι κάπως οριακός αν και ίσως όχι λάθος. Αυτό που σκεφτόμουν ήταν
πως δεν έχω δει ποτέ μου sql που να μοιάζει έστω και λίγο με ένα μικρό
πρόγραμμα... Η μοίρα όμως είναι περίεργος διάβολος και σήμερα, λίγες
μέρες μετά, τελείωσα το παρακάτω sql... μικρό "πρόγραμμα":

SELECT s.sID, s.sCategory, Left(s.[sGroup],3) AS grp, s.sName,
s.sDescr, sys_tmp_StockGroups.aTypicalRole,

       ap.Qty0, iv.[2001cost],

       iv.FirstOfkCost as [First buy cost],

       iv.MaxOfkCost   as [MAX of cost],

       iv.AvgOfkCost   as [Avg of cost],

       iv.MinOfkCost   as [min of cost],

       iv.LastOfkCost  as [Last cost],

       iv.vc.LastkDate as [Last buy date],

       iv.FirstOfValue as [First sales price],

       iv.MaxOfValue   as [MAX price],

       iv.AvgOfValue   as [Avg price],

       iv.MinOfValue   as [min price],

       iv.LastOfValue  as [Last price],

       iv.vs.LastkDate as [Last sales date],

       iv.EndCost,

       cQcur.LastcQ         as [dQty 2007]   ,

       cQcur.dec_min_major  as [min major1]     ,

       cQcur.dec_major_days as [min major1 days],

       cQnxt.jan_min_major  as [min major2]     ,

       cQcur.dec_min_minor  as [min minor1]     ,

       cQcur.dec_minor_days as [min minor1 days],

       cQnxt.jan_min_minor  as [min minor2]     ,

       cQnxt.jan_minor_days as [min minor2 days],

       cQcur.TotalActions   as [Total Actions]  ,

       EndAp.qty            as [apogr Lhxhs],

       ap.Qty0+cQcur.LastcQ-EndAp.qty as [qty lianiki]



FROM ((((((stock as s

      LEFT JOIN (SELECT stock.sID, stock.sCategory, Left([sGroup],3)
AS ItemGroup, stock.sName

       ,a2001.cost AS 2001cost

       ,vc.FirstOfkCost, vc.MinOfkCost, vc.AvgOfkCost, vc.MaxOfkCost,
vc.LastOfkCost, vc.LastkDate, vc.CntBuy, vc.Buy_dQty

       ,vs.FirstOfValue, vs.MinOfValue, vs.AvgOfValue, vs.MaxOfValue,
vs.LastOfValue, vs.LastkDate, vs.CntSales, vs.Sell_dQty

       , IIf( IsNull(vc.cntBuy) ,

              iif( isnull(a2001.cost),

                   null,

                   a2001.cost

                 )

              ,

              iif( abs(vc.LastOfKcost/vc.FirstOfKcost-1)<0.5 ,

                   vc.LastOfKcost ,

                   null

                 )

            )

         as EndCost

FROM ((stock LEFT JOIN (SELECT ItemCat, ItemGroup, ItemName, ItemID,

    First(kCost) AS FirstOfValue, Min(kCost) AS MinOfValue,

    Avg(kCost)   AS AvgOfValue,   Max(kCost) AS MaxOfValue,

    Last(kCost)  AS LastOfValue,  Last(kDate) AS LastkDate

    ,Count(a1.kID) AS CntSales,  Sum(a.kQTY) AS Sell_dQty

FROM (SELECT s.sID as ItemID, s.sCategory as ItemCat, Left([sgroup],3)
AS ItemGroup, s.sName as ItemName,

       a1.kDate, a1.kid, a.kCost,  a.kQty

FROM (

      (action1 as a1 LEFT JOIN TBLKtypes as t ON a1.kType = t.code)

        LEFT JOIN Actions as a ON a1.kID = a.kIDptr

     )

     LEFT JOIN stock as s ON a.ksID = s.sID

WHERE ( true



   AND (t.InlcudeInCleanList)

   AND (t.category="ÐÙ" Or t.category="(ÐÙ)")

   AND (a1.cReasonText Is Null)

   AND (a1.NetTotal<>0)

   AND (t.HasActionsRecords) AND (a.ksID<>295 And (a.ksID)<>184)

   AND ( a1.kDate>=#1/1/2007# And a1.kDate<=#31/12/2007# )

   AND (a.kcost<>0) AND (a.kQty<>0)

)

ORDER BY a1.kDate)

GROUP BY ItemCat, ItemGroup, ItemName, ItemID) AS vs ON stock.sID =
vs.ItemID) LEFT JOIN (SELECT ItemCat, ItemGroup, ItemName, ItemID,

    First(kCost) AS FirstOfkCost, Min(kCost) AS MinOfkCost,

    Avg(kCost)   AS AvgOfkCost,   Max(kCost) AS MaxOfkCost,

    Last(kCost)  AS LastOfkCost,  Last(kDate) AS LastkDate

    ,Count(a1.kID) AS CntBuy,  Sum(a.kQTY) AS Buy_dQty

FROM (SELECT s.sID as ItemID, s.sCategory as ItemCat, Left([sgroup],3)
AS ItemGroup, s.sName as ItemName,

       a1.kDate, a1.kid, a.kCost,  a.kQty

FROM (

      (action1 as a1 LEFT JOIN TBLKtypes as t ON a1.kType = t.code)

        LEFT JOIN Actions as a ON a1.kID = a.kIDptr

     )

     LEFT JOIN stock as s ON a.ksID = s.sID

WHERE ( true



   AND (t.InlcudeInCleanList)

   AND (t.category="ÁÃ" Or t.category="(ÁÃ)" Or t.code=13  )

   AND (a1.cReasonText Is Null OR a1.cReasonText="ÅÉÓÁÃÙÃÇ/ÅÎÁÃÙÃÇ" Or
t.code=13 )



   AND (t.HasActionsRecords) AND (a.ksID<>295 And (a.ksID)<>184)

   AND ( a1.kDate>=#1/1/2007# And a1.kDate<=#31/12/2007# )

   AND (a.kcost<>0) AND (a.kQty<>0)

)

ORDER BY a1.kDate)

GROUP BY ItemCat, ItemGroup, ItemName, ItemID) AS vc ON stock.sID =
vc.ItemID) LEFT JOIN Apogr_Start as a2001 ON stock.sID = a2001.sID

WHERE ( (a2001.cost is not null) OR (vc.CntBuy is not null) OR
(vs.CntSales is not null) )

) AS iv ON s.sID = iv.sID)

       LEFT JOIN (SELECT stock.sID, stock.sCategory, Left([sGroup],3)
AS ItemGroup, stock.sName

       , a2001.Qty0

       , dL.dQty

       , ( IIf( IsNull(a2001.Qty0) ,0,a2001.Qty0  ) + IIf(
IsNull(dL.dQty) ,0,dL.dQty  )  ) as NewQty





FROM (stock  LEFT JOIN (SELECT ItemCat, ItemGroup, ItemName, ItemID

    ,Count(a1.kID) AS CntActs,  Sum(a.kQTY) AS dQty

FROM (SELECT s.sID as ItemID, s.sCategory as ItemCat, Left([sgroup],3)
AS ItemGroup, s.sName as ItemName,

       a1.kDate, a1.kid, a.kCost,  a.kQty

FROM (

      (action1 as a1 LEFT JOIN TBLKtypes as t ON a1.kType = t.code)

        LEFT JOIN Actions as a ON a1.kID = a.kIDptr

     )

     LEFT JOIN stock as s ON a.ksID = s.sID

WHERE ( true

   AND (t.Affects_L_Qty<>0) AND NOT(t.ServicesOnly)

   AND (a1.cReasonText Is Null OR (a1.cReasonText<>"ÁÊÕÑÙÌÅÍÇ" AND
a1.cReasonText<>"ÁÊÕÑÙÓÇ ÊÉÍÇÓÇÓ") )

   AND (a1.kDate>=#1/1/2007# AND a1.kDate<=#31/12/2007# )

   AND (a.ksID<>295 And (a.ksID)<>184

        AND ksID<>499

        AND ksid<>8780

        AND ksid<>8781

       )

   AND (a.kQty<>0)

)

ORDER BY s.sID, a1.kDate, a.kQTY DESC)

GROUP BY ItemCat, ItemGroup, ItemName, ItemID) AS dL ON stock.sID =
dL.ItemID) LEFT JOIN Apogr_Start as a2001 ON stock.sID = a2001.sID

WHERE (

        (a2001.cost is not null) OR (dL.dQty is not null)

        AND sgroup<>"SERVICES"

      )

) AS ap ON s.sID = ap.sID)

        LEFT JOIN sys_tmp_StockGroups ON s.sGroup = sys_tmp_StockGroups.sGroup)

         LEFT JOIN [SELECT Actions.ksID as id FROM Actions UNION
SELECT Apogr_Start.sID as id FROM Apogr_Start]. AS usedsids ON s.sID =
usedsids.id)

          LEFT JOIN (SELECT *,sid,

       iif( LastcQ-mincQd<0            ,  0 , LastcQ-mincQd   ) as
dec_min_major,

       iif( LastcQ-mincQd<0            , 365,   minCQd_days   ) as
dec_major_days,

       iif( LastcQ-mincQy<LastcQ-mincQd,  0 , LastcQ-mincQy   ) as
dec_min_minor,

       (   minCQy_days   ) as dec_minor_days,

       iif( -mincQj<0                  ,  0 ,    -mincQj      ) as
jan_min_major,

       iif( -mincQy<-mincQj            ,  0 ,    -mincQy      ) as
jan_min_minor,

       iif( -mincQy>-mincQj            , 365, 365-minCQy_days ) as
jan_minor_days,

       actions                                                  as TotalActions



FROM sys_cQ_stats

WHERE cYear= 2007) as cQcur ON s.sID = cQcur.sID)

           LEFT JOIN (SELECT *,sid,

       iif( LastcQ-mincQd<0            ,  0 , LastcQ-mincQd   ) as
dec_min_major,

       iif( LastcQ-mincQd<0            , 365,   minCQd_days   ) as
dec_major_days,

       iif( LastcQ-mincQy<LastcQ-mincQd,  0 , LastcQ-mincQy   ) as
dec_min_minor,

       (   minCQy_days   ) as dec_minor_days,

       iif( -mincQj<0                  ,  0 ,    -mincQj      ) as
jan_min_major,

       iif( -mincQy<-mincQj            ,  0 ,    -mincQy      ) as
jan_min_minor,

       iif( -mincQy>-mincQj            , 365, 365-minCQy_days ) as
jan_minor_days,

       actions                                                  as TotalActions



FROM sys_cQ_stats

WHERE cYear= 2007+1) as cQnxt ON s.sID = cQnxt.sID)

            LEFT JOIN [Apogr_End]              as EndAp    ON s.sID = EndAp.id



WHERE (((usedsids.id) Is Not Null) AND s.sGroup<>"UPRODUCT" AND
s.sGroup<>"TOOL")



ORDER BY s.sCategory, s.[sGroup], s.sName, s.sID;

________________________________
ΥΓ όχι δεν είμαι High Functioning Autistic. Χρειάστηκε πρώτα να γράψω
ένα "πραγματικό" πρόγραμμα για να με βοηθήσει να κατασκευάσω κομμάτι -
κομμάτι το παραπάνω τερατάκι