Click here to Skip to main content
15,944,737 members
Home / Discussions / Database
   

Database

 
QuestionSQL Linq, Row Number() Over, equiv in VB Pin
jkirkerx4-Aug-15 14:03
professionaljkirkerx4-Aug-15 14:03 
AnswerRe: SQL Linq, Row Number() Over, equiv in VB Pin
Richard Deeming5-Aug-15 1:41
mveRichard Deeming5-Aug-15 1:41 
GeneralRe: SQL Linq, Row Number() Over, equiv in VB Pin
jkirkerx5-Aug-15 6:09
professionaljkirkerx5-Aug-15 6:09 
GeneralRe: SQL Linq, Row Number() Over, equiv in VB Pin
jkirkerx5-Aug-15 7:58
professionaljkirkerx5-Aug-15 7:58 
GeneralRe: SQL Linq, Row Number() Over, equiv in VB Pin
Richard Deeming5-Aug-15 8:12
mveRichard Deeming5-Aug-15 8:12 
GeneralRe: SQL Linq, Row Number() Over, equiv in VB Pin
Richard Deeming5-Aug-15 8:18
mveRichard Deeming5-Aug-15 8:18 
GeneralRe: SQL Linq, Row Number() Over, equiv in VB Pin
jkirkerx5-Aug-15 10:39
professionaljkirkerx5-Aug-15 10:39 
QuestionIs this possible, using an old foxpro database files and oledb for DBase Pin
jkirkerx30-Jul-15 13:18
professionaljkirkerx30-Jul-15 13:18 
I have this report for sales rep margin in Account Mate for DOS, that uses FoxPro for database table file, .dbx

The item tables or DB files does not have a column for sales rep and order dates, so I can't go to the table directly to get my data.

So without knowing or even where to start to write this in pure SQL, I wrote 2 functions

1.
Get Invoices that match the sales rep and start and stop dates, and store it in a List(of invoices)
2. loop a function that targets items by invoice number, and get the cost, price, qty, etc.

When you have 500 invoices, it takes forever to run, slowing down fixing my other problems, which may be fixed now.

Do you think it's possible to merge these statements into a single statement. Plus if so, a nudge in the right direction. I hate this old Fox Pro stuff, it was before my time.
SELECT 
  h.FINVNO 
, h.FSHIPDATE 
 FROM ARINV01H.dbf h 
 WHERE 
 h.FSALESPN = @FSALESPN 
 AND 
 h.FSHIPDATE >= @startDate AND h.FSHIPDATE <= @stopDate 
UNION ALL 
SELECT 
  v.FINVNO 
, v.FSHIPDATE 
 FROM ARINV01.dbf v 
 WHERE 
 v.FSALESPN = @FSALESPN 
 AND 
 v.FSHIPDATE >= @startDate AND v.FSHIPDATE <= @stopDate 
SELECT
 DISTINCT FITEMNO
, SUM(FSHIPQTY)
, SUM(FCOST * FSHIPQTY)
, SUM(FPRICE * FSHIPQTY)
, (SELECT FDESCRIPT FROM ICITM01.dbf i WHERE i.FITEMNO = h.FITEMNO) AS FREALDESC
 FROM
(
   SELECT
      h.FITEMNO
    , h.FSHIPQTY
    , h.FCOST
    , h.FPRICE
   FROM ARTRS01H.dbf h
   WHERE
   h.FINVNO = @FINVNO
   UNION
   SELECT
      v.FITEMNO
    , v.FSHIPQTY
    , v.FCOST
    , v.FPRICE
   FROM ARTRS01.dbf v
   WHERE
   v.FINVNO = @FINVNO
)
 GROUP BY FITEMNO "

AnswerRe: Is this possible, using an old foxpro database files and oledb for DBase Pin
Mycroft Holmes30-Jul-15 14:13
professionalMycroft Holmes30-Jul-15 14:13 
GeneralRe: Is this possible, using an old foxpro database files and oledb for DBase Pin
jkirkerx31-Jul-15 6:25
professionaljkirkerx31-Jul-15 6:25 
Questionrestore database Pin
millerwilliam3529-Jul-15 2:51
millerwilliam3529-Jul-15 2:51 
AnswerRe: restore database Pin
CHill6029-Jul-15 3:56
mveCHill6029-Jul-15 3:56 
QuestionRe: restore database Pin
Eddy Vluggen29-Jul-15 5:31
professionalEddy Vluggen29-Jul-15 5:31 
AnswerRe: restore database Pin
Mycroft Holmes29-Jul-15 14:30
professionalMycroft Holmes29-Jul-15 14:30 
AnswerRe: restore database Pin
Nitzan Levi22-Aug-15 20:20
Nitzan Levi22-Aug-15 20:20 
QuestionUpdate SQL Server Table with a Spreadsheet Worksheet DataTable using CommandBuilder Pin
LHendren27-Jul-15 1:37
LHendren27-Jul-15 1:37 
AnswerRe: Update SQL Server Table with a Spreadsheet Worksheet DataTable using CommandBuilder Pin
Mycroft Holmes27-Jul-15 14:33
professionalMycroft Holmes27-Jul-15 14:33 
QuestionSQL Linq, Joins, returns 1st record over and over Pin
jkirkerx23-Jul-15 9:11
professionaljkirkerx23-Jul-15 9:11 
Answer[Solved] Pin
jkirkerx23-Jul-15 11:30
professionaljkirkerx23-Jul-15 11:30 
QuestionEntity Framework Book. Pin
Kevin Marois23-Jul-15 5:29
professionalKevin Marois23-Jul-15 5:29 
QuestionRe: Entity Framework Book. Pin
Richard MacCutchan23-Jul-15 6:36
mveRichard MacCutchan23-Jul-15 6:36 
AnswerRe: Entity Framework Book. Pin
Kevin Marois23-Jul-15 7:31
professionalKevin Marois23-Jul-15 7:31 
AnswerRe: Entity Framework Book. Pin
Mycroft Holmes23-Jul-15 14:10
professionalMycroft Holmes23-Jul-15 14:10 
QuestionSQL Linq, select from select and join Pin
jkirkerx21-Jul-15 13:27
professionaljkirkerx21-Jul-15 13:27 
SuggestionRe: SQL Linq, select from select and join Pin
Richard Deeming22-Jul-15 1:50
mveRichard Deeming22-Jul-15 1:50 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.