Pull data from multiple tables

Forum for DBF Commander discussing.

Pull data from multiple tables

Postby TLWP » Tue Apr 18, 2017 4:41 pm

Here is the raw FoxPro SQL but it doesn't seem that "USE" is a valid function in DBF Commander. I need all these columns and need them in the specific order they show up. Any way to do this?

USE 'c:\deha.dbf' IN 0 ALIAS DEHA
USE 'c:\DEDA.dbf' IN 0 ALIAS DEDA
USE 'c:\inite.DBF' IN 0 ALIAS INITE

SELECT
DEHA.NUMBER,
DEHA.NAME,
DEHA.INVNUM,
DEHA.INVDATE,
DEHA.CUSTPONUM,
DEDA.ITEMNUM
DEDA.DESCRIPT,
DEDA.QTYSHIP,
INITE.UMSALES,
DEDA.PRICE,
DEDA.EXTENSION,
DEHA.FRTCOST
FROM DEHA
INNER JOIN "DEDA"
LEFT OUTER JOIN "INITE";
ON DEDA.ITEMNUM = INITE.ITEMNUM ;
ON deha.Ordnum = deda.Ordnum ;
WHERE (DEHA.SLP1 like '%GOL%' AND DEHA.INVNUM BETWEEN {^2017-03-18} AND {^2017-03-28})
TLWP
 
Posts: 8
Joined: Thu Feb 02, 2017 5:51 pm

Re: Pull data from multiple tables

Postby Admin » Thu Apr 20, 2017 12:39 pm

Yeah, that's right. Just delete all USE clauses and replace aliase names with full paths to the corresponding DBF file, setting an aliase with AS clause. Also, remove all semicolons:
Code: Select all
SELECT
DEHA.NUMBER,
DEHA.NAME,
DEHA.INVNUM,
DEHA.INVDATE,
DEHA.CUSTPONUM,
DEDA.ITEMNUM
DEDA.DESCRIPT,
DEDA.QTYSHIP,
INITE.UMSALES,
DEDA.PRICE,
DEDA.EXTENSION,
DEHA.FRTCOST
FROM 'C:\path\DEHA.dbf' AS DEHA
INNER JOIN 'C:\path\DEDA.dbf' AS DEDA
LEFT OUTER JOIN 'C:\path\INITE.dbf' AS INITE
ON DEDA.ITEMNUM = INITE.ITEMNUM
ON deha.Ordnum = deda.Ordnum
WHERE (DEHA.SLP1 like '%GOL%' AND DEHA.INVNUM BETWEEN {^2017-03-18} AND {^2017-03-28})
Best regards,
Oleg Zhechkov
User avatar
Admin
Site Admin
 
Posts: 124
Joined: Wed Apr 21, 2010 7:27 pm


Return to DBF Commander Professional

Who is online

Users browsing this forum: No registered users and 3 guests

cron