Visual FoxPro SQL Queries - SQL help needed

Author

SQL help needed

bpanders71@hotmail.com

2005-09-21, 11:25 am

I'm trying to extract some data from a table, and can't figure out the
proper SELECT statement to extract the data I need. Hopefully this is
an obvious and easy fix for those of you with more experience than I
(not hard...)

I have an invoice transaction table that has line item detail. I need
to extract all invoice data for invoices which contain a certain item
code, say, "RETURN", however, I also need to get information from a
previous line from the same invoice which will contain another item
code and a serial number. So it might be like this:

invoice|lineno| itemcode | serialno
1000 |1 |bstrho1602 | 123456
1000 |2 |RETURN | 1

Problem is the second line also has data in the serialno field and I
need to reference bstrho1602 and 123456 for the RETURN (but don't need
to show "RETURN" in the final print, just the bstrhol1602 itemcode and
serialno). Is there a reasonable SELECT statement for this scenario?
I'm using VFP5 if that makes a difference.

Thanks-
Brian

Cindy Winegarden

2005-09-21, 8:25 pm

Hi Brian,

The following code assumes that the "RETURN" line item immediately follows
(next LineNo) the item being returned, and that the combinations of Invoice
and LineNo are unique.

Create Cursor InvTrans (Invoice I, LineNo I, ItemCode C(10), SerialNo C(10))
Insert Into InvTrans Values (1, 1, "bstrho1602", "123456")
Insert Into InvTrans Values (1, 2, "RETURN", "1")
Insert Into InvTrans Values (2, 1, "bstrho1602", "123456")
Insert Into InvTrans Values (2, 2, "bstrho1234", "654321")
Insert Into InvTrans Values (2, 3, "RETURN", "1")
Insert Into InvTrans Values (2, 4, "bstrho5678", "567890")

*-- Use a "self join"
Select ;
InvTrans.* ;
From ;
InvTrans ;
Inner Join InvTrans As Return On ;
InvTrans.Invoice = Return.Invoice And ;
InvTrans.LineNo = Return.LineNo - 1 ;
Where ;
Return.ItemCode = "RETURN"

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn
.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden

<bpanders71@hotmail.com> wrote in message
news:1127319533.577144.53790@z14g2000cwz.googlegroups.com...
> I'm trying to extract some data from a table, and can't figure out the
> proper SELECT statement to extract the data I need. Hopefully this is
> an obvious and easy fix for those of you with more experience than I
> (not hard...)
>
> I have an invoice transaction table that has line item detail. I need
> to extract all invoice data for invoices which contain a certain item
> code, say, "RETURN", however, I also need to get information from a
> previous line from the same invoice which will contain another item
> code and a serial number. So it might be like this:
>
> invoice|lineno| itemcode | serialno
> 1000 |1 |bstrho1602 | 123456
> 1000 |2 |RETURN | 1
>
> Problem is the second line also has data in the serialno field and I
> need to reference bstrho1602 and 123456 for the RETURN (but don't need
> to show "RETURN" in the final print, just the bstrhol1602 itemcode and
> serialno). Is there a reasonable SELECT statement for this scenario?
> I'm using VFP5 if that makes a difference.
>
> Thanks-
> Brian
>

bpanders71@hotmail.com

2005-09-22, 1:24 pm

Thanks Cindy! I was looking into it further and the Self Join was what
I was thinking I was going to need.

Best-
Brian

Sponsored Links