SQL Command help

[krisoradmin]

Hoping someone could help me with this.


I have a file with case numbers (numeric field)
I have another file, a comment file, with multiple records for each case number in it.

I'm trying to see how I can run a sql command to create a file from the comments file for all the different case numbers contained in the case file.

 

[Olaf Doschke]

Wouldn't the comment file already be that file? Or do you want a file for each case? There is no SQL creating many results, so you have to scan through all cases and do a simple left join with comments.

Bye, Olaf.

 

[Mike Lewis]

Krisoradmin,

Not sure what you are trying to achieve. Do you want the output file to contain one record for each combination of comments and case numbers? If so, then Olaf has given you an answer.

Or maybe you want the output file to contain the comments whose case numbers are present in the first file, and only those comments?

If so, you can do this:

SELECT Comment FROM SecondFile ;
  WHERE CaseNum IN (SELECT CaseNum FROM FirstFile)

But somehow I don't think that's what you want.

Perhaps you could clarify your requirements.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[cbasoz]

If what you mean is to concatenate comments per case number than as far as I know you can't do that by simply using an SQL as you can with SQL server. VFP's SQL support is not that much evolved.

Instead you can either use a custom aggregation UDF or XBase updating of a cursor. ie:

Create Cursor result (Names m, caseNumber c(20))

Select Distinct Title As caseNumber ;
  FROM (_samples+'data\employee') ;
  ORDER By 1 ;
  INTO Array laCases
Select employee

For ix = 1 To Alen(laCases,1)
  lcNames = ''
  Scan For Title == laCases[m.ix]
    lcNames = m.lcNames + ;
      IIF( lcNames == '', '', Chr(13)+ Chr(10)) + ;
      RTRIM(first_Name)+' '+Rtrim(last_Name)
  Endscan
  Insert Into result (Names, caseNumber) Values (m.lcNames, laCases[m.ix])
Endfor

Select result
Browse

Cetin Basoz
MS Foxpro MVP, MCP