Re: [sqlite] Getting an Array or list in a select statement...

[jose isaias cabrera]

Thanks.  Yes, that is nice, but I am using D. :-)  This is a good idea, 
though.  Thanks for sharing.  I could easily implement this.

Ok, so this is not possible using SQLite select, correct?


----- Original Message ----- 
From: "Samuel R. Neff" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Thursday, October 11, 2007 2:28 PM
Subject: RE: [sqlite] Getting an Array or list in a select statement...



We use a custom aggregate function called DisplayList to do exactly what
you're talking about.  C# code follows.


SELECT
 U.UserName,
 DisplayList(R.RoleName)
FROM
 Users U
     INNER JOIN
 Xref_Users_Roles X
     ON
 U.UserID = X.UserID
     INNER JOIN
 Roles R
     ON
   X.RoleID = R.RoleID
GROUP BY
 U.UserName
ORDER BY
 U.UserName,
 R.RoleName

HTH,

Sam


-------------------------------------------
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]



using System;
using System.Collections.Generic;
using System.Data.SQLite;
using Cube.MasterService.Common;

namespace Cube.MasterService.Storage
{
 /// <summary>
 /// Creates a list of data from the aggregate source.
 /// </summary>
 [SQLiteFunction(Name = "DisplayList", Arguments = -1, FuncType =
FunctionType.Aggregate)]
 public class SQLiteDisplayList : SQLiteFunction
 {
   public override void Step(object[] args, int stepNumber, ref object
contextData)
   {
     if (args.Length == 0)
     {
       if (contextData == null)
       {
         contextData = new ArgumentException("DisplayList requires at 
least
one argument.");
       }
       return;
     }

     DisplayListContext displayListContext;

     if (contextData == null)
     {
       contextData = displayListContext = new
DisplayListContext(args.Length > 1 ? DbConvert.ToString(args[1]) : ",",

args.Length > 2 ? DbConvert.ToBoolean(args[2]) : false);
     }
     else
     {
       displayListContext = (DisplayListContext) contextData;
     }

     displayListContext.Add(DbConvert.ToString(args[0]));
   }

   public override object Final(object contextData)
   {
     if (contextData == null)
     {
       return null;
     }

     if (contextData is Exception)
     {
       return contextData;
     }

     return contextData.ToString();
   }

   private class DisplayListContext
   {
     private readonly string _separator;
     private readonly bool _sort;
     private readonly List<string> _data = new List<string>();

     public DisplayListContext(string separator, bool sort)
     {
       _separator = separator;
       _sort = sort;
     }

     public void Add(string value)
     {
       _data.Add(value);
     }

     public override string ToString()
     {
       if (_sort)
       {
         _data.Sort();
       }
       return String.Join(_separator, _data.ToArray());
     }
   }
 }
}



-----Original Message-----
From: jose isaias cabrera [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 11, 2007 1:48 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Getting an Array or list in a select statement...


Greetings.

So, I know that I can do a single select and get a list of the different
items found on a column, but what I would like to do is to get this same
list on another select statement.  Let me explain;

I have this select statement,

"select ProjID,
      cust,
      proj,
      PClass,
      PSubClass,
      min(bdate),
      max(ddate),
      max(edate),
      lang,
      vendor,
      sum(invoice),
      sum(ProjFund),
      sum(PMTime),
      A_No from LSOpenJobs
where ProjID = 24
AND
 (
   (bdate BETWEEN '2007-09-01' AND '2007-09-01') AND
   (ddate BETWEEN '2007-09-01' AND '2007-09-01') AND
   (edate BETWEEN '2007-09-01' AND '2007-09-01')
 )
AND
 PClass!='Quote' group by ProjID;";

And this is working fine.  However, I would like to get a list of all the
different PSubClass items found on this select statement.  Can this be 
done
on this select or do I have to do the single select to get this item also?

thanks,

josé


----------------------------------------------------------------------------
-
To unsubscribe, send email to [EMAIL PROTECTED]
----------------------------------------------------------------------------
-


-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------




-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------