Filter Records

[alisaif]

Hi,

I want to filter multiple records in a same field (pono) like;

m_pono = "PO100982,PO101032,PO101144,PO101158,PO101214,PO101239,PO101254"

Select pono, ;
   podate, ;
   pcode, ;
   full_name, ;
   refno,;
   SUM(Original) As original,;
   SUM(delivered) As delivered, ;
   remarks,;
   reqby,;
   enteredby ;
   FROM ViewPo ;
   GROUP By pono,podate,pcode,full_name,refno,remarks,reqby,enteredby ;
   ORDER By podate ;
   INTO Cursor poview Readwrite

I want to use "where" like (where pono in(&m_pono)) in above code for the purpose of filter only those records which contains these pono no.

Thanks

Saif

 

[Mike Lewis]

Have a look at INLIST():

[tt]SELECT .... ;
WHERE INLIST(pono, "PO100982","PO101032","PO101144","PO101158","PO101214","PO101239",PO101254")[/tt]

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[EzLogic]

Mike gave you an answer.

Remember inlist() can only use up to 27 parameters I believe.


Ez Logic
Michigan

 

[alisaif]

SELECT .... ;
WHERE INLIST(pono, "PO100982","PO101032","PO101144","PO101158","PO101214","PO101239",PO101254")

Is it correct

SELECT .... ;
WHERE INLIST(pono, &m_pono)

Saif

 

[Mike Lewis]

That would only work if you included the semi-colons and commas in the macro string:

[tt]m_pono = ["PO100982","PO101032","PO101144","PO101158","PO101214","PO101239",PO101254"]

SELECT .... ;
WHERE INLIST(pono, &m_pono)
[/tt]

Another way of doing it would be like this:

[tt]m_pono = "PO100982,PO101032,PO101144,PO101158,PO101214,PO101239,PO101254"
SELECT .... ;
WHERE AT(pono, m_pono) > 0[/tt]

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[EzLogic]

Also Assuming the PONO field is 8 characters long,

otherwise, you would need to ALLTRIM() IT.



Ez Logic
Michigan

 

[TamarGranor]

You don't need INLIST() here. The SQL IN clause can handle this, but you have the same issue that you need each item in the list to be a separate string:

m_pono = ["PO100982","PO101032","PO101144","PO101158","PO101214","PO101239",PO101254"]
Select pono, ;
   podate, ;
   pcode, ;
   full_name, ;
   refno,;
   SUM(Original) As original,;
   SUM(delivered) As delivered, ;
   remarks,;
   reqby,;
   enteredby ;
   FROM ViewPo ;
   WHERE pono IN (&m_pono)
   GROUP By pono,podate,pcode,full_name,refno,remarks,reqby,enteredby ;
   ORDER By podate ;
   INTO Cursor poview Readwrite

Tamar

 

[alisaif]

It is just an ordinary question that what is the ideal way to filter records from a large table using SQL-Select.

dt1 = Thisform.contmaster.datefromto1._datepicker1.Value
dt2 = Thisform.contmaster.datefromto1._datepicker2.Value

Large Cursor
------------

Select rawpom.pono, rawpom.podate, rawpom.pcode, supplier.full_name,;
   rawpom.enteredby, rawpom.reqby, rawpom.refno,  rawpom.discamount, ;
   rawpom.Curr, rawpom.isocode, rawpom.Currency, rawpom.Sign, rawpom.currrate,;
   rawpom.discrem, rawpom.remarks, rawpo.rawcode, ;
   rawpo.quantity,  rawpo.qtyavail, rawpo.unit, ;
   rawpo.rate, Cast((quantity*rate) As N(10,2)) As original, ;
   CAST((qtyavail*rate) As N(10,2)) As delivered ;
   FROM ;
   village!rawpom ;
   inner Join village!rawpo ;
   ON  rawpom.pono = rawpo.pono ;
   inner Join village!supplier ;
   ON  rawpom.pcode = supplier.pcode;
   WHERE  rawpom.podate Between dt1 And dt2 ;
   INTO Cursor ViewPo Readwrite
Index On pono Tag pono Additive

Output contains 70-80 records approximately in the following SQL-Select
Here I want to use filter or SQL-select to get the specific records

Select pono, ;
   podate, ;
   pcode, ;
   full_name, ;
   refno,;
   SUM(original) As original,;
   SUM(delivered) As delivered, ;
   remarks,;
   reqby,;
   enteredby ;
   FROM ViewPo ;
   GROUP By pono,podate,pcode,full_name,refno,remarks,reqby,enteredby ;
   INTO Cursor poview Readwrite
Alter Table poview Add Column balance N(10,2)
Replace All balance With original-delivered
Sum original,delivered,balance To moriginal,mdelivered,mbalance

Saif

 

[vgulielmus]

Alternatively, you can create a cursor and populate it with the desired pono's, then use this cursor in your query.


Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Mike Lewis]

It is just an ordinary question that what is the ideal way to filter records from a large table using SQL-Select.

I thought we had answered your question. But now you are bringing datepicker values and an ALTER TABLE into the picture. You're just confusing the issue.

Also, there is no point in generating a cursor from a SELECT, and then using ALTER TABLE to add a new column to it. It is much easier to include the new column in the original field list:

SELECT ;
   pono, ;
   podate, ;
   ....
   OriginalDelivered AS Balance, ;
   ....

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[alisaif]

Thanks

Saif

 

[Mike Lewis]

On another point, do all of your PO numbers always start with the letters PO? If so, there would be no need to store those letters in the PO field. It would be slightly more efficient to only store the actual numbers, but to include the letters when displaying the values in forms and reports.

Of course, if you already have a live application with the data already set up, it probably wouldn't be worth the effort of changing it now, but it is something to keep in mind for another occasion.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[alisaif]

Thanks I will try that also by eliminating PO.

Saif