Click here to Skip to main content
15,878,871 members
Home / Discussions / Database
   

Database

 
GeneralRe: Retrieve Data Using Comma Separated List Pin
Shweta N Mishra29-Oct-14 1:23
professionalShweta N Mishra29-Oct-14 1:23 
GeneralRe: Retrieve Data Using Comma Separated List Pin
Mycroft Holmes29-Oct-14 3:13
professionalMycroft Holmes29-Oct-14 3:13 
QuestionAdvice on Perfomance Turning of the Query Pin
Vimalsoft(Pty) Ltd28-Oct-14 7:27
professionalVimalsoft(Pty) Ltd28-Oct-14 7:27 
GeneralRe: Advice on Perfomance Turning of the Query Pin
PIEBALDconsult28-Oct-14 8:04
mvePIEBALDconsult28-Oct-14 8:04 
AnswerRe: Advice on Perfomance Turning of the Query Pin
Eddy Vluggen28-Oct-14 8:59
professionalEddy Vluggen28-Oct-14 8:59 
AnswerRe: Advice on Perfomance Turning of the Query Pin
Mycroft Holmes28-Oct-14 12:59
professionalMycroft Holmes28-Oct-14 12:59 
GeneralRe: Advice on Perfomance Turning of the Query Pin
Vimalsoft(Pty) Ltd4-Nov-14 21:56
professionalVimalsoft(Pty) Ltd4-Nov-14 21:56 
QuestionCombine results from 2 foxpro DBF files using union Pin
jkirkerx27-Oct-14 12:30
professionaljkirkerx27-Oct-14 12:30 
On this query, I get 2 sets of results for each part number, which is probably what I asked for.
I'm trying to get 1 set of results for each part number, I just need to figure out the proper method.

I'll try some critical thinking here, it can't be the group by clause, so perhaps I'm suppose to wrap the union in a select statement like my count in the post below? But then I have the distinct, and I don't want to mess that up.

Once I have this done, I think I'm done writing for these old account mate dos programs.


Dim queryString As String = _
" SELECT " & _
" DISTINCT h.FITEMNO " & _
", SUM(h.FSHIPQTY) " & _
", AVG(h.FCOST) " & _
", AVG(h.FPRICE) " & _
", SUM(h.FSHIPQTY * h.FPRICE) " & _
", (SELECT FDESCRIPT FROM ICITM01.dbf i WHERE i.FITEMNO = h.FITEMNO) AS FREALDESC " & _
", SUM(h.FSHIPQTY * h.FPRICE - h.FSHIPQTY * h.FCOST)" & _
" FROM ARTRS01H.dbf h " & _
" WHERE " & _
"   h.FSHIPDATE >= @startDate AND h.FSHIPDATE <= @stopDate " & _
" GROUP BY h.FITEMNO "

queryString +=
" UNION ALL "

queryString +=
" SELECT " & _
" DISTINCT v.FITEMNO " & _
", SUM(v.FSHIPQTY) " & _
", AVG(v.FCOST) " & _
", AVG(v.FPRICE) " & _
", SUM(v.FSHIPQTY * v.FPRICE) " & _
", (SELECT FDESCRIPT FROM ICITM01.dbf i WHERE i.FITEMNO = v.FITEMNO) AS FREALDESC " & _
", SUM(v.FSHIPQTY * v.FPRICE - v.FSHIPQTY * v.FCOST)" & _
" FROM ARTRS01.dbf v " & _
" WHERE " & _
"   v.FSHIPDATE >= @startDate AND v.FSHIPDATE <= @stopDate " & _
" GROUP BY v.FITEMNO "

AnswerMakes no sense how it works Pin
jkirkerx27-Oct-14 13:01
professionaljkirkerx27-Oct-14 13:01 
QuestionSelect count on 2 DBF files Pin
jkirkerx27-Oct-14 11:11
professionaljkirkerx27-Oct-14 11:11 
GeneralRe: Select count on 2 DBF files Pin
PIEBALDconsult27-Oct-14 11:25
mvePIEBALDconsult27-Oct-14 11:25 
GeneralRe: Select count on 2 DBF files Pin
jkirkerx27-Oct-14 12:23
professionaljkirkerx27-Oct-14 12:23 
QuestionRewrit a Scalar function to a table valued function Pin
Ambertje21-Oct-14 0:00
Ambertje21-Oct-14 0:00 
AnswerRe: Rewrit a Scalar function to a table valued function Pin
Mycroft Holmes21-Oct-14 0:25
professionalMycroft Holmes21-Oct-14 0:25 
GeneralRe: Rewrit a Scalar function to a table valued function Pin
Ambertje21-Oct-14 1:40
Ambertje21-Oct-14 1:40 
GeneralRe: Rewrit a Scalar function to a table valued function Pin
Richard Deeming21-Oct-14 2:00
mveRichard Deeming21-Oct-14 2:00 
GeneralRe: Rewrit a Scalar function to a table valued function Pin
Ambertje21-Oct-14 2:25
Ambertje21-Oct-14 2:25 
GeneralRe: Rewrit a Scalar function to a table valued function Pin
Mycroft Holmes21-Oct-14 12:52
professionalMycroft Holmes21-Oct-14 12:52 
SuggestionRe: Rewrit a Scalar function to a table valued function Pin
Richard Deeming21-Oct-14 1:55
mveRichard Deeming21-Oct-14 1:55 
AnswerRe: Rewrit a Scalar function to a table valued function Pin
Eddy Vluggen21-Oct-14 2:33
professionalEddy Vluggen21-Oct-14 2:33 
QuestionStored Procedure for Item Allotment Pin
avisharma@sharma19-Oct-14 20:01
avisharma@sharma19-Oct-14 20:01 
QuestionRe: Stored Procedure for Item Allotment Pin
Jörgen Andersson19-Oct-14 21:22
professionalJörgen Andersson19-Oct-14 21:22 
AnswerRe: Stored Procedure for Item Allotment Pin
avisharma@sharma19-Oct-14 23:04
avisharma@sharma19-Oct-14 23:04 
GeneralRe: Stored Procedure for Item Allotment Pin
Shweta N Mishra20-Oct-14 2:24
professionalShweta N Mishra20-Oct-14 2:24 
GeneralRe: Stored Procedure for Item Allotment Pin
avisharma@sharma27-Oct-14 2:42
avisharma@sharma27-Oct-14 2:42 

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.