Re: [sqr-users] RE: Order by the values obtained in the amount

[Donald Mellen <donm@ontko.com>]

Actually, to make it a little simpler, you don't need to match the items 
selected with the "order by" statement.  The decode can just be put in the 
"order by" statement, like...

select cust_name , item_amt , billable_flg 
from ps_xxx
where ....
order by billable_flg desc ,decode(item_amt,0,9,0), cust_name


White . Denise wrote:
> "Two great minds..." and all that.  I was thinking along the same lines
> - decode() - before I saw your reply.  But I still think that may be the
> way to go.
>
> Why wouldn't the following work?
>
> select cust_name , item_amt , billable_flg ,decode(item_amt,0,9,0)
> from ps_xxx
> where ....
> order by billable_flg desc , 4, cust_name
>
> Of course, in a straight SQL select, you will see the decoded value (0
> or 9) as a fourth column, but in SQR just don't print it out.
>
> Denise M. White
> EBS Programmer/Analyst III
> Dynamics Research Corporation
> ------------------------------
>
> Message: 5
> Date: Tue, 06 Mar 2007 18:28:08 -0600
> From: "the dragon" <ceprn@hotmail.com>
> Subject: RE: [sqr-users] Order by the values obtained in the amount
>       columns
> To: sqr-users@sqrug.org
> Message-ID: <BAY103-F28A014B364A3E8D6B7A094A57A0@phx.gbl>
> Content-Type: text/plain; format=flowed
>
> I was going to suggest putting a decode on the item_amt field, and
> setting the value for 0 equal to 99999999999, for sorting only, but then
> you'd need to use that in your order by.  The problem is, order by will
> either sort on the name *OR* the item_amt as a secondary sort, and then
> you won't end up with what you really want, because your "requirements"
> are mutually exclusive.
>
> Sort of performing a select, moving it into an array and processing it
> that way, I don't see how to make this work.  Well, a kludgey way would
> be to have 3 or 4 selects, with Y positive numbers, Y zeros, N positive
> numbers and N zeros... but that would suck.  Or, 4 selects in a union
> would work too.  Once again, a sucky solution.
>
> You could also do something like this (this is what I would do)....
> order by the billable_fg and cust_name...  if you encounter a 0 amount
> in the item_amt column, don't print that record now, but put it (the
> entire returned record) into an array, then, when the billable_flg
> changes from Y to N, dump the array and print them, which will be all
> zeroes and pre-sorted by cust_name, and then continue processing and
> printing the N values.  If you have to separate N zero values, you could
> do the same thing with another array.
>
> Or you can take the easy way out and just tell your l-users that it
> can't be done without a lot of work and slow processing.
>
> peace,
> clark 'the dragon' willis
>
> PSA: Salary <> Slavery. If you earn a salary, your employer is renting
> your services for 40 hours a week, not purchasing your soul. Your time
> is the only real finite asset that you have, and once used it can never
> be recovered, so don't waste it by giving it away.
>
> I work to live; I don't live to work.
>
> "Time is the coin of your life. It is the only coin you have, and only
> you can determine how it will be spent. Be careful lest you let other
> people spend it for you." -- Carl Sandburg (1878 - 1967)
>
> It is impossible to defeat an ignorant man in argument. -- William G.
> McAdoo
>
> Religion is regarded by the common people as true, by the wise as false,
> and by the rulers as useful. -- Seneca
>
> "I distrust those people who know so well what God wants them to do
> because I notice it always coincides with their own desires." - Susan B.
> Anthony
>
>
>
>
> ----Original Message Follows----
>
> try adding the item_amt to the order by as a descending field before the
> cust_name.  This should order by billable_fg descending, then the amt,
> then alphabetize the cust_name when the amounts are the same
>
> Order by billable_fg desc, item_amt desc, cust_name
>
> Christine
>
> -----Original Message-----
> To: sqr-users@sqrug.org
> Subject: [sqr-users] Order by the values obtained in the amount columns
>
> I am having a small problem in figurin this out maybe someone has done
> this before
>
>    Here is my problem ..... here is the sql i have written
>
>    select cust_name , item_amt , billable_flg
>    from ps_xxx
>    where ....
>    order by billable_flg desc , cust_name
>
>    The output will order by billable_flg and customer name .... here is
> the result of the output
>
>    billable_flg   name   item_amt
>         Y            A.....   123.22
>         Y            B....    0.00
>         Y            C....    21.00
>         N            A....    213.22
>         N            B....    321.22
>
>    what i am finally supposed to do is ....to put all the item_amt which
> are equal to zero at the end but just before the billable_flg is changed
> to 'N' from 'Y'
>
>    final output required
>
>    billable_flg   name   item_amt
>         Y            A.....   123.22
>         Y            C....    21.00
>         Y            B....    0.00
>         N            A....    213.22
>         N            B....    321.22
>
>    any ideas on how to do it ...even if u know some order by syntaxes
> that i can try let me know i can test it on my query
>
>    Thanks,
>    Ashwin
>
>
> _______________________________________________
> sqr-users mailing list
> sqr-users@sqrug.org
> http://www.sqrug.org/mailman/listinfo/sqr-users
>
>   


-- 
-----------------------------------------------------------------------
Donald Mellen  | Ray Ontko & Co. - Richmond, IN - http://www.ontko.com/
donm@ontko.com |  "In the beginning, there was nothing, which exploded"


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users