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

[ashwin reddy <ashwin443@yahoo.co.uk>]

The ones given by Denise & george work ..... But i already implemented it with 
2 select statements as i needed some subtotatlin and stuff .....
   
  Anyways the one given by George is the most efficient way of doin it ...
   
  Even the one give By Denise Works ....but it needs some addition sqr logic to 
say that 0 means the one which have item amts as 0 and 9 means the ones which 
have item amounts not 0.....
   
   
  Thanks a lot ... appreciate it ....
  

George Jansen <GJANSEN@aflcio.org> wrote:
  Or

SELECT cust_name, item_amt, billable_fig
FROM ps_xxx
WHERE ...
ORDER BY billable_fig desc, abs(sign(item_amt)) desc [, other fields as desired]

sign gives you 1 for positive, 0 for 0, -1 for negative, abs renders everything 
positive...

>>> Donald Mellen 3/7/2007 10:36 AM >>>
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" 
> Subject: RE: [sqr-users] Order by the values obtained in the amount
> columns
> To: sqr-users@sqrug.org 
> Message-ID: 
> 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

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


                
---------------------------------
 New Yahoo! Mail is the ultimate force in competitive emailing. Find out more 
at the Yahoo! Mail Championships. Plus: play games and win prizes.

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