SQLitening Support Forum

SQLitening Support => You've got Questions? We've got Answers! => Topic started by: Robert Sarrazin on August 01, 2009, 12:15:56 am

Title: array sqlitening sort
Post by: Robert Sarrazin on August 01, 2009, 12:15:56 am
Hi, this my problem.
I got client.sld with table cust

        REDIM lsaColsRows(0) AS STRING
        sSQL = bla bla bla
        slOpen sDatabase, "C"
        slSelAry (sSQL, lsaColsRows(), "N4")
        slClose
        y = UBOUND(lsaColsRows(2))

now I got exemple:
(1,x)  ;    (2,x)    ;      (3,x)         ;       (4,x)   ;   (......)
RowId;   comp    ;       buy x 1k    ;     contact ;   etc.

the lsaColsRows() 'work very well.

    1,3m,  12,  James, ......more data
    2,Philip,  4,  Frank, ......
    3,Advard,  21, Peter, .....

I want this result

    2,Philip,  4,  Frank, ......more data
    1,3m,  12,  James, ......
    3,Advard,  21, Peter, .....

I try all kind of sort,  to sort  the colum 3

ARRAY SORT lsaColsRows(3)
ARRAY SORT lsaColsRows(3) for 1
I build lsaBuy()   second array fill with 12,4,21  by the colum 3
ARRAY SORT lsaColsRows(), TAGARRAY lsaBuy()
ARRAY SORT lsaColsRows(0), TAGARRAY lsaBuy()
ARRAY SORT lsaColsRows(3), TAGARRAY lsaBuy()
no way all different result never good.

I try to resolved the problem by try
to sort with  slSelAry by option [,order by] .....(no result)

I find "JoeByrne" talking about:
You can (a) create an index on the column or
(b) use the 'SORT ON' command in your slSEL
but no exp. or detail. :-\
Just the "Actually, with Indexing, SORT ON, and the WHERE clause"
that all. ( I can't figure how is working)

but now someone
find a solution to sort a array by the column.
Thank hope the exp. is enough clear.
thank again. ::)
Title: Re: array sqlitening sort
Post by: Rolf Brandt on August 01, 2009, 02:14:57 am
Hello Robert,

QuotesSQL = bla bla bla


What does your SQL string actually look like? If you have defined the SQL query properly then there is no need for an array sort at all. You will get the data in the right order.

Rolf

Title: Re: array sqlitening sort
Post by: Rolf Brandt on August 01, 2009, 12:33:06 pm
Hello Robert,

I assume that the database fields you want to display are:

RowId, comp, buy x 1k, contact, etc.

You want to sort by "buy x 1k", right?

Then your SQL query should be:

"SELECT RowId, * FROM Cust ORDER BY [buy x 1k]"

Like this you would have the data in the proper order and there is no need to sort.

Rolf
Title: Re: array sqlitening sort
Post by: Robert Sarrazin on August 01, 2009, 10:23:58 pm
Thanks.
the sSql = "SELECT RowId,  * FROM cust WHERE " + Seltxt + " = '0'" + "order by buy"

That is the real sSql the Seltxt is
Month=Month-1
or may look like this:
the sSql = "SELECT RowId,  * FROM cust WHERE " + "July" + " = '0'" + "order by buy"
the table contain the 12 month for the customer purchaces:
and to check all promotion we have to check if the customer is buying before or
buy this month due the promotion.

if july > 0  the customer is not select for the new promotion and the select
work great. (the problem is the sort the bying current month).

The real expression is:
if the customer last month buy nothing and how much is buying this month and
who is the best (DESCEND order).
???

After try I figure out how to use order by it is working(not the way we planing)

to follow my sample exp.
I got this: with the sSql select

    7,Kodak,  0,  Diane, ......more data
    4,Yahoo,  0,  Tom, ......
    6,Mash,  0,  Robert, .....
    5,Micro,  0,  Mark, ......
    1,3m,  12,  James, ......
    2,Philip,  4,  Frank, ......
    3,Advard,  21, Peter, .....

I got all the customer with no buying this month and at the end all
the one is buying this month.
The one with 0 is not a broblem to eleminated but
the 12, 4, 21 still not in sort

Kind a progress but still not the answer.
the Order By seem not to bad it bring all the 0 to the top.
All is there, just to figure out how to use 'array sort' the 3th column.
Thanks again.
Title: Re: array sqlitening sort
Post by: Rolf Brandt on August 02, 2009, 05:12:57 am
Let me see if I understood you properly:

Your table has fields January, February ... December. In these fields you save the sales of the customers - either in $, ?, or number of sales. The table has some other customer specific data like name address etc.

You want to find out which customers bought nothing in the previous month (July) but did sales in the current month (August).

This data should be sorted on the sales of the current month in descending order.

Am I right so far?

Let us assume the current month is August which makes the previous one July.
Then the following query should give you the desired results in the proper order:

sSql = "SELECT RowId,  * FROM cust WHERE July = '0' ORDER BY August [DESC]"

This should give you the following result:

    3,Advard,  21, Peter, .....more data
    1,3m,  12,  James, ......
    2,Philip,  4,  Frank, ......
    7,Kodak,  0,  Diane, ......
    4,Yahoo,  0,  Tom, ......
    6,Mash,  0,  Robert, .....
    5,Micro,  0,  Mark, ......


Does that meet your desired results?
Title: Re: array sqlitening sort
Post by: Robert Sarrazin on August 02, 2009, 06:41:18 pm
Work very well:
I still in shock after many hour follow the PB9 manual
instruction I am using wrong word.
this is your code
sSql = "SELECT RowId,  * FROM cust WHERE July = '0' ORDER BY August DESC"
compare with I use.
sSql = "SELECT RowId,  * FROM cust WHERE July = '0' ORDER BY August DESCEND"
I got no error compiling but when execute I got
1= near "DESCEND": syntax error
Now I realize the DESC is the code from sqlitening
compare to DESCEND is the code from PB9 for array sort.
  :) THANKS Rolf Thank's's's  :)  I never find this one. I was  realy stick with DESCEND
Title: Re: array sqlitening sort
Post by: Rolf Brandt on August 03, 2009, 02:32:27 am
PB9 will never check SQL syntax since the SQL query is just a string you are handing over to the SQLite engine. The problem is that you are basically dealing with two different programming environments or two different languages which are sometimes similar in syntax.

Back in the 80ies and early 90ies I wrote programs in QuickBasic and dBase/Clipper. I had a similar problem because there syntax was similar, and sometimes I got mixed Basic with Clipper syntax.

Glad that I was able to help you.

Rolf