Get data from sql To foxpro9 using date range.

[Niki_S]

I have a VIEW in sql server and I need to get data to foxpro using date range. Below is my codes that I used,
SQL SERVER viwe vInvFinal,

cSggNo     cInvNo    cProdFty    cHTSCode    nInvQty    dXFactory
SGG0945    S1490     FTM         10.14       234        2021-04-24 00:00:00.000
SGG0945    S1490     FTM         10.14       12         2021-04-24 00:00:00.000
SGG0945    S1490     FTM         10.14       743        2021-04-24 00:00:00.000
SGG0941    S1486     SFC         601         100        2021-04-15 00:00:00.000
SGG0941    S1486     SFC         601         25         2021-04-15 00:00:00.000
SGG1050    S1694     GML         345.23      456        2021-04-28 00:00:00.000
SGG1050    S1694     GML         345.23      874        2021-04-28 00:00:00.000
.NULL      S1854     GML         31          12         2021-04-10 00:00:00.000
.NULL      S1854     GML         31          12         2021-04-10 00:00:00.000

And I have a foxpro form which has two texboxes to insert start date and end date. I used below code to get data from sql to foxpro.

stra="SELECT cSggNo,cInvNo,cProdFty,cGMTName,cHTSCode,SUM(nInvQty),dXFactory FROM MIS.dbo.vInvFinal WHERE "
stra=stra+"dXFactory>=?thisform.txtFromDate.Value AND dXFactory<=?thisform.txtToDate.Value and cSggNo='isnull' group by cInvNo"
SQLEXEC(hndOps,stra,'SGG')

I need my cursor like this,

cSggNo     cInvNo    cProdFty    cHTSCode    nInvQty    dXFactory
SGG0945    S1490     FTM         10.14       989        2021-04-24 00:00:00.000
SGG0941    S1486     SFC         601         125        2021-04-15 00:00:00.000
SGG1050    S1694     GML         345.23      1330       2021-04-28 00:00:00.000

Here I don't want to get data from cSggNo which is .NULL

How can I make my code to do this?
Thank you.

 

[Mike Lewis]

SQL Server won't like your code, for two reasons.

First, when you have a GROUP BY clause, every expression in the column list (that is, the list of fields immediately following the word SELECT) must either be aggregate functions (such as your [tt]SUM(nInvQty)[/tt]) or fields that take part in the grouping ([tt]cInvNo[/tt] in this case).

As it happens, each of the other fields in your column list is unique within each invoice group. That means that you can work round the problem by turning each of them into an aggregate function. And the way to do that is with the MAX()function. Thus:

SELECT MAX(cSggNo), cInvNo, MAX(cProdFty), MAX(cGMTName), MAX(cHTSCode) ,SUM(nInvQty),dXFactory  ... etc

.


The other problem is in your test for null values. The correct syntax is:

... WHERE cSggNo IS NOT NULL

I'd also point out that there seems to be lot of redundancy in your date. But that's a separate issue.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[bborissov]

TEXT TO lcSQL NOSHOW TEXTMERGE
     SELECT cSggNo
           ,cInvNo
           ,cProdFty
           ,cGMTName
           ,cHTSCode
           ,SUM(nInvQty) AS nInvQty
           ,dXFactory 
     FROM MIS.dbo.vInvFinal 
     WHERE  dXFactory >= ?m.ldDate1 
        AND dXFactory <= ?m.ldDate2
        AND cSggNo IS NOT NULL
     GROUP BY cSggNo
             ,cInvNo
             ,cProdFty
             ,cGMTName
             ,cHTSCode
             ,dXFactory 
ENDTEXT
ldDate1 = thisform.txtFromDate.Value
ldDate2 = thisform.txtToDate.Value
SQLEXEC(hndOps, m.lcSQL ,'SGG')

Borislav Borissov
VFP9 SP2, SQL Server

 

[Niki_S]

Thank you all for your support !