Hi Tariq,
MSSQL doesn't know .T.
MartinaJ
>Dear Sir,
>
>I have following working code in VFP
>
>
>create CURSOR mixed (open_qt integer,open_wt integer)
>INSERT INTO mixed VALUES (20,30)
>
>create cursor arrival (date date,qty integer,weight integer)
>INSERT INTO arrival VALUES (DATE(2016,7,15),11,3)
>INSERT INTO arrival VALUES (DATE(2016,7,16),47,4)
>INSERT INTO arrival VALUES (DATE(2016,7,17),300,5)
>INSERT INTO arrival VALUES (DATE(2016,7,18),12345,2)
>INSERT INTO arrival VALUES (DATE(2016,7,20),50,7)
>INSERT INTO arrival VALUES (DATE(2016,7,21),15,4)
>INSERT INTO arrival VALUES (DATE(2016,8,17),300,30)
>INSERT INTO arrival VALUES (DATE(2016,8,20),50,5)
>
>
>create cursor pouring (date date, qty integer,weight integer)
>INSERT INTO pouring VALUES (DATE(2016,7,16),49,2)
>INSERT INTO pouring VALUES (DATE(2016,7,17),500,3)
>INSERT INTO pouring VALUES (DATE(2016,7,18),13,2)
>INSERT INTO pouring VALUES (DATE(2016,7,20),70,4)
>INSERT INTO pouring VALUES (DATE(2016,7,21),23,5)
>INSERT INTO pouring VALUES (DATE(2016,7,22),23,3)
>INSERT INTO pouring VALUES (DATE(2016,8,17),500,50)
>INSERT INTO pouring VALUES (DATE(2016,8,20),70,7)
>
>SELECT INT(Ymonths.ymonth / 12) AS rYear, Ymonths.ymonth % 12 AS rMonth, ;
> op.open_qt + (NVL(par.qty, 0) - NVL(ppo.qty, 0)) AS pQty, ;
> op.open_wt + (NVL(par.weight, 0) - NVL(ppo.weight, 0)) AS pWeight, ;
> NVL(ar.qty, 0) AS arrival_qty, NVL(ar.weight, 0) AS arrival_weight, ;
> NVL(po.qty, 0) AS pouring_qty, NVL(po.weight, 0) AS pouring_weight, ;
> op.open_qt + (NVL(par.qty, 0) + NVL(ar.qty,0) - NVL(ppo.qty, 0) - NVL(po.qty, 0)) AS rQty, ;
> op.open_wt + (NVL(par.weight, 0) + NVL(ar.weight, 0) - NVL(ppo.weight, 0) - NVL(po.weight, 0)) AS rWeight ;
> FROM (SELECT DISTINCT(YEAR(date) * 12 + MONTH(date)) AS ymonth FROM arrival ;
> UNION ;
> SELECT DISTINCT(YEAR(date) * 12 + MONTH(date)) AS ymonth FROM pouring) AS YMonths ;
> INNER JOIN mixed op ON .T. ;
> LEFT JOIN ;
> (SELECT YEAR(date) * 12 + MONTH(date) AS YMonth, SUM(qty) AS qty, SUM(weight) AS weight FROM arrival GROUP BY ymonth) AS ar ;
> ON ar.YMonth = YMonths.ymonth ;
> LEFT JOIN ;
> (SELECT YEAR(date) * 12 + MONTH(date) AS ymonth, SUM(qty) AS qty, SUM(weight) AS weight FROM pouring GROUP BY ymonth) AS po ;
> ON po.YMonth = YMonths.ymonth ;
> LEFT JOIN ;
> (SELECT YEAR(date) * 12 + MONTH(date) AS ymonth, SUM(qty) AS qty, SUM(weight) AS weight FROM arrival GROUP BY ymonth) AS par ;
> ON par.ymonth < YMonths.ymonth ;
> LEFT JOIN ;
> (SELECT YEAR(date) * 12 + MONTH(date) AS ymonth, SUM(qty) AS qty, SUM(weight) AS weight FROM pouring GROUP BY ymonth) AS ppo ;
> ON ppo.ymonth < YMonths.ymonth ;
> ORDER BY YMonths.ymonth ;
> INTO CURSOR RunningReport
>
>BROWSE
>
>
>
>The codes work fine, now I am trying to convert the codes to run in SQLSERVER management studio
>
>I modified as
>
>
>
>
>SELECT Ymonths.ymonth/12 AS rYear, Ymonths.ymonth % 12 AS rMonth,
> op.open_qt + (isnull(par.qty, 0) - isnull(ppo.qty, 0)) AS pQty,
> op.open_wt + (isnull(par.weight, 0) - isnull(ppo.weight, 0)) AS pWeight,
> isnull(ar.qty, 0) AS crprp_qty, isnull(ar.weight, 0) AS crprp_weight,
> isnull(po.qty, 0) AS pouring_qty, isnull(po.weight, 0) AS pouring_weight,
> op.open_qt + (isnull(par.qty, 0) + isnull(ar.qty,0) - isnull(ppo.qty, 0) - isnull(po.qty, 0)) AS rQty,
> op.open_wt + (isnull(par.weight, 0) + isnull(ar.weight, 0) - isnull(ppo.weight, 0) -isnull(po.weight, 0)) AS rWeight
> FROM (SELECT DISTINCT(YEAR(date) * 12 + MONTH(date)) AS ymonth FROM crprp
> UNION
> SELECT DISTINCT(YEAR(date) * 12 + MONTH(date)) AS ymonth FROM pouring) AS YMonths
> INNER JOIN master op ON .T.
> LEFT JOIN
> (SELECT YEAR(date) * 12 + MONTH(date) AS YMonth, SUM(qty) AS qty, SUM(weight) AS weight FROM crprp GROUP BY ymonth) AS ar
> ON ar.YMonth = YMonths.ymonth
> LEFT JOIN
> (SELECT YEAR(date) * 12 + MONTH(date) AS ymonth, SUM(qty) AS qty, SUM(weight) AS weight FROM pouring GROUP BY ymonth) AS po
> ON po.YMonth = YMonths.ymonth
> LEFT JOIN
> (SELECT YEAR(date) * 12 + MONTH(date) AS ymonth, SUM(qty) AS qty, SUM(weight) AS weight FROM crprp GROUP BY ymonth) AS par
> ON par.ymonth < YMonths.ymonth
> LEFT JOIN
> (SELECT YEAR(date) * 12 + MONTH(date) AS ymonth, SUM(qty) AS qty, SUM(weight) AS weight FROM pouring GROUP BY ymonth) AS ppo
> ON ppo.ymonth < YMonths.ymonth
> ORDER BY YMonths.ymonth
>
>
>
>
>But is shows this error message
>
>Msg 156, Level 15, State 1, Line 12
>Incorrect syntax near the keyword 'LEFT'.
>Msg 156, Level 15, State 1, Line 13
>Incorrect syntax near the keyword 'AS'.
>Msg 156, Level 15, State 1, Line 16
>Incorrect syntax near the keyword 'AS'.
>Msg 156, Level 15, State 1, Line 19
>Incorrect syntax near the keyword 'AS'.
>Msg 156, Level 15, State 1, Line 22
>Incorrect syntax near the keyword 'AS'.
>
>and line 12 is highlighted in attachment.
>
>Please
