[sqlite] Collate

[Aviad Harell]

Hi,

is there any implications on performance of using collate i(NOCASE for
example)  in the column definition when creating a table? doesn't it
effect the indexes use of those columns?
when using collate NOCASE on some column definition, how does a specific
element is chose to be returned, the upper case, the lower case or the first
one appears?

Re: [sqlite] Re: Collate

[Aviad Harell]

tnx for the quick replay.

 when using collate NOCASE on some column definition, how does a
 specific element is chose to be returned, the upper case, the lower
 case or the first one appears?

Collation doesn't affect how the data is stored, just how it's compared.
You will get your strings out in exactly the same form you put them in.

lets say i have the following table:

create table t1 (col1 text collate nocase)

insert t1 values {a}
 insert t1 values {A}

and then i execute the following:

select col1
from t1
group by col1


the result will be: a or A?





On 11/7/07, Igor Tandetnik [EMAIL PROTECTED] wrote:

 Aviad Harell [EMAIL PROTECTED]
 wrote:
  is there any implications on performance of using collate i(NOCASE for
  example)  in the column definition when creating a table? doesn't it
  effect the indexes use of those columns?

 If you also create an index on this column, and don't explicitly specify
 what collation the index should use, it would use the collation declared
 on the column. If no collation is specified anywhere, the default is
 COLLATE BINARY.

  when using collate NOCASE on some column definition, how does a
  specific element is chose to be returned, the upper case, the lower
  case or the first one appears?

 Collation doesn't affect how the data is stored, just how it's compared.
 You will get your strings out in exactly the same form you put them in.

 Igor Tandetnik



 -
 To unsubscribe, send email to [EMAIL PROTECTED]

 -

Re: [sqlite] Re: user defined function

[Aviad Harell]

Hey Igor,

Thanks for your replay. I tried not to use user defined function to do it.
This code is working on SqlServer but on SQLite I get misuse of aggregate:
(SUM(SALES)).





SELECT   CUSTOMER,

 PRODUCT,

 [RANK]

FROM (SELECT CUSTOMER,

 PRODUCT,

 [SUM_SALES],

 (SELECT COUNT(T2.SUM_SALES)

  FROM   (SELECT   CUSTOMER,

   PRODUCT,

   (SUM(SALES)) AS [SUM_SALES]

  FROM Test1MX1000Multi AS T1

  GROUP BY CUSTOMER,

   PRODUCT) AS [T2]

  WHERE  T1.CUSTOMER = T2.CUSTOMER

 AND T2.SUM_SALES  T1.SUM_SALES) AS [RANK]

  FROM   (SELECT   CUSTOMER,

   PRODUCT,

   (SUM(SALES)) AS [SUM_SALES]

  FROM Test1MX1000Multi AS T1

  GROUP BY CUSTOMER,

   PRODUCT) AS [T1]) AS [TEMP]

WHERERANK = 4

ORDER BY CUSTOMER,

 RANK


On 10/8/07, Igor Tandetnik [EMAIL PROTECTED] wrote:

 Aviad Harell [EMAIL PROTECTED]
 wrote:
  I tried to create user defined function called Rank. which gets
  partition columns and order by column. this function should create
  rank according to its parameters. i.e.:
 
  select customer, product, sales, Rank(customer,  sales)
  from table
 
  should return:
 
  customerA productA 100 0
  customerA productB 300 2
  customerA productC 200 1
  customerA productD 400 3
  customerB productB 300 1
  customerB productA 400 2
  customerB productC 100 0
 
  how can i do it? should i use FunctionType scalar or aggregate?

 Scalar. Aggregate function looks at multiple rows and produces a single
 value (see SUM, MAX). A scalar function produces one value per row,
 which is what you want here.

 Note also that you don't really need a custom function here. This query
 should produce the same result:

 select customer, product, sales,
 (select count(*) from tableName t2
   where t2.customer = t1.customer and t2.sales  t1.sales)
 from tableName t1;

 Igor Tandetnik



 -
 To unsubscribe, send email to [EMAIL PROTECTED]

 -

[sqlite] user defined function

[Aviad Harell]

Hey,

I tried to create user defined function called Rank. which gets partition
columns and order by column. this function should create rank according to
its parameters. i.e.:

select customer, product, sales, Rank(customer,  sales)
from table

should return:

customerA productA 100 0
customerA productB 300 2
customerA productC 200 1
customerA productD 400 3
customerB productB 300 1
customerB productA 400 2
customerB productC 100 0

how can i do it? should i use FunctionType scalar or aggregate?

[sqlite] multiple connections

[Aviad Harell]

Hi,



Regarding only to select statements, is there a known limit of number of
concurrent connections to Sqlite DB?

What about concurrent connections to the same table in the sqlite DB?



Regards,
Aviad

[sqlite] Query Help

[Aviad Harell]

Hi,

I Have a problem with the following query executed on sqlite

SELECT   CUSTOMER,
 PRODUCT,
 [RANK]
FROM (SELECT CUSTOMER,
 PRODUCT,
 [SUM_SALES],
 (SELECT COUNT(T2.SUM_SALES)
  FROM   (SELECT   CUSTOMER,
   PRODUCT,
   (SUM(SALES)) AS [SUM_SALES]
  FROM Test1MX1000Multi AS T1
  GROUP BY CUSTOMER,
   PRODUCT) AS [T2]
  WHERE  T1.CUSTOMER = T2.CUSTOMER
 AND T2.SUM_SALES  T1.SUM_SALES) AS [RANK]
  FROM   (SELECT   CUSTOMER,
   PRODUCT,
   (SUM(SALES)) AS [SUM_SALES]
  FROM Test1MX1000Multi AS T1
  GROUP BY CUSTOMER,
   PRODUCT) AS [T1]) AS [TEMP]
WHERERANK = 4
ORDER BY CUSTOMER,
 RANK

 i get:

misuse of aggregate: (SUM(SALES)). this query is cool under sql server

any ideas?



tnx