SQL Question

[bebbo]

I am trying to create a cursor called tmp1 which has all the fields from sellline and an additional field called vat. Basically the sellline.plu could be in one of three tables, stock, pack or opendept. Stock and Opendept are linked to the vat table by Stock.VAT or OpenDept.Vat = Vat.Sysref. Pack is linked to Stock by Pack.Unitplu = stock.plu.

The new table I require will have all the original sellline fields with the additional vat field with the appropriate vat percentage in that field. Is the SQL statement below close? it doesn't appear to work. Just takes ages as though it is in a loop.

SELECT Sellline.*, ;
VAT.Rate AS VAT ;
INTO cursor Tmp1 ;
FROM Sellline, Vat, Stock, PACK, OpenDept ;
WHERE (Stock.PLU = SELLLine.PLU ;
AND Stock.VAT = VAT.SYSREF) ;
OR (OpenDept.PLU = SELLLine.PLU ;
AND OpenDept.VAT = VAT.SYSREF)
OR (Pack.PLU = SELLLine.PLU ;
AND PACK.UnitPLU = STOCK.PLU ;
AND Stock.VAT = VAT.SYSREF )

 

[rgbean]

Bebbo,
What version of VFP are you running? When you have these more complex relationships, you really need to consider using the JOIN syntax so that VFP has a better idea on what you are trying to do.

Rick

 

[bebbo]

Hi

I'm running VF 6. I am getting a bit closer with the code below. However still requires one or two mods.

SELECT Sellline.*, ;
IIF(ISNULL(VAT.Rate), 0.00, VAT.RATE) AS VAT ;
INTO cursor Tmp1 ;
FROM Sellline ;
LEFT outer join STOCK ;
ON SELLLINE.PLU = STOCK.PLU ;
FULL JOIN VAT ;
ON STOCK.VAT = VAT.SYSREF ;
WHERE SELLLINE.PLU = STOCK.PLU ;
UNION ALL ;
SELECT SELLLINE.* , ;
IIF(ISNULL(VAT.Rate), 0.00, VAT.RATE) AS VAT ;
FROM Sellline ;
LEFT outer join Opendept ;
ON SELLLINE.PLU = OPENDEPT.PLU ;
FULL JOIN VAT ;
ON OPENDEPT.VAT = VAT.SYSREF ;
WHERE SELLLINE.PLU = OPENDEPT.PLU ;
ORDER By 1, 2

SELECT TMP1.* ;
Into Cursor TMP ;
From TMP1 ;
UNION ALL ;
SELECT sellline.*, ;
IIF(ISNULL(VAT.Rate), 0.00, VAT.RATE) AS VAT ;
FROM Sellline ;
LEFT outer join Pack ;
ON SELLLINE.PLU = PACK.PLU ;
LEFT outer JOIN STOCK ;
ON PACK.UNITPLU = STOCK.PLU ;
LEFT outer JOIN VAT ;
ON STOCK.VAT = VAT.SYSREF ;
WHERE SELLLINE.PLU = PACK.PLU ;
ORDER By 1, 2

 

[bebbo]

I did it as below in the end. However I will be looing at the SQL statement, if anyone has any further input. Thanks

SELECT SELLLINE
ALTER TABLE SELLLINE ADD VAT N(6,2)
GO TOP
DO WHILE !EOF()
DO CASE
CASE SEEK(SELLLINE.PLU, "STOCK&quot
IF SEEK(STOCK.VAT, "VAT&quot
REPLACE SELLLINE.VAT WITH VAT.RATE
ELSE
REPLACE SELLLINE.VAT WITH 0.00
ENDIF
CASE SEEK(SELLLINE.PLU, "OPENDEPT&quot
IF SEEK(OPENDEPT.VAT, "VAT&quot
REPLACE OPENDEPT.VAT WITH VAT.RATE
ELSE
REPLACE SELLLINE.VAT WITH 0.00
ENDIF
CASE SEEK(SELLLINE.PLU, "PACK&quot
IF SEEK(PACK.UNITPLU, "STOCK&quot
IF SEEK(STOCK.VAT, "VAT&quot
REPLACE SELLLINE.VAT WITH VAT.RATE
ELSE
REPLACE SELLLINE.VAT WITH 0.00
ENDIF
ENDIF
ENDCASE
SKIP 1 IN SELLLINE
ENDDO

 

[Stella740pl]

bebbo,

This is based on your last code, just shorter.

ALTER TABLE SELLLINE ADD VAT N(6,2)
SELECT SELLLINE
SCAN
DO CASE
CASE SEEK(SELLLINE.PLU, "STOCK&quot
ls=SEEK(STOCK.VAT, "VAT&quot
REPLACE REPLACE SELLLINE.VAT WITH IIF(ls, VAT.RATE, 0.00)
CASE SEEK(SELLLINE.PLU, "OPENDEPT&quot
IF SEEK(OPENDEPT.VAT, "VAT&quot
REPLACE OPENDEPT.VAT WITH VAT.RATE
ELSE
REPLACE SELLLINE.VAT WITH 0.00
ENDIF
CASE SEEK(SELLLINE.PLU, "PACK&quot
IF SEEK(PACK.UNITPLU, "STOCK&quot
ls=SEEK(STOCK.VAT, "VAT&quot
REPLACE SELLLINE.VAT WITH IIF(ls, VAT.RATE, 0.00)
ENDIF
ENDCASE
ENDSCAN

Stella

 

[craigsboyd]

I am a little confused...looking at the code I don't understand the SEEKs needing to be nested like that...perhaps you could provide a small example of what a couple of records from each table would look like and what the result is that you are looking for and I am quite sure that a very workable SQL solution could be found for this, the way it is being done looks terribly innefficient, but no way to tell without knowing what the data looks like and what the desired result is. Thanks.

Slighthaze = NULL
_{craig1442@mchsi.com}

"Whom computers would destroy, they must first drive mad." - Anon​