PREFIX-change valid values was ([ic] sort option)

[Ron Phipps]

> From: Paul Jordan
> 
> 
> > > However, I am having one last issue in the conversion. This
> > query finds all
> > > trips in a given year - no problem. I have been trying to
> > figure out how to
> > > do an [sql-change] on the just the month porion of the date:
yyyy-mm-
> dd
> > >
> > >     [query
> > >         type=list
> > >         st=sql
> > >         sql="
> > >             SELECT  *
> > >             FROM    products
> > >             WHERE   date LIKE '[if !cgi mv_arg]2002[else][cgi
> > > mv_arg][/else][/if]%'
> > >             AND     searched BETWEEN '1' AND '2'
> > >             ORDER BY date
> > >         "]
> > >
> > > [sql-change 1][condition]
> > >
> > > [calc]
> > > my $date = q{[sql-param date]};
> > > my $month = substr($date,5,2);
> > > return $month;
> > > [/calc]
> > >
> > > [/condition]
> > >
> > > ......code...... [/sql-change 1]
> > >
> > > This calc, and many other hacks, have not been successful. Any
> > hints guys?
> > >
> > Surround your [sql-*] tag set with [list] ... [/list], as you have
> > specified "type=list" mode.
> >
> > You should also change your [calc] to [sql-calc] and the same for
the
> > calc's end-tag.
> >
> > One other minor thing.  You should try to select the columns you
need
> > rather than "select *".  Just a good habit to get in to.
> 
> 
> Thanks Kevin!
> 
> I open up my options with * when I am trying to get something to work
:)
> 
> I don't think what I want to do will be possible. I have been
> experimenting
> with date_convert and trying all sorts of (sorts). I found this in the
> docs:
> 
> -------
> "The repeating value must be a tag interpolated in the search process,
> such
> as [item-field field] or [item-data database field]. "
> ---------
> 
> I am not sure how stringent this statement actually is, but it seems
> pretty
> solid.
> 
> I can output a value by date_convert OR my [sql-calc] method above, to
be
> consistent with how I want to sort. But it will not accept it. I can
sort
> by
> any field, but not a munged value from a field. (I am sorting my the
MONTH
> ONLY from => yyyy-mm-dd)

What is date_convert, a IC function or a database built in function?
Have you tried doing something like this if you are using MySql:

SELECT field_1, field_2, date, substr(date, 5, 2) AS month FROM ....

Then use [sql-param month] as your change condition?

This will use the built in mysql substr function and return only the
month part of the date field which should be consistent with the
requirements for sql-change since it should be seen in the resultset as
a returned field.

If this does not work then set your own scratch variable to the first
value returned, on each iteration compared the current value of month to
the scratch value, if it's the same don't do anything, if it is
different do what you want to do on change then set the scratch variable
to the changed value of month.  This will simulate sql-change, but will
not have the restriction you have found.

> 
> I don't know what to do now, less an additional column just for
sorting.
> date_convert... same story, it does an accurate conversion but I
believe
> it
> is just for "output" and is not considered (by PREFIX-change) to be a
> valid
> value for it's operation.
> 
> Please, if anybody has forced [PREFIX-change] to activate off a known
> working sequence, without it being an actual field in a table, PLEASE
let
> me
> know... FOR THE LOVE OF ALL THAT IS GOOD!!
> 
> 
> HAND  :)
> 
> Paul
> 

Good luck!
-Ron