sql - mysql procedure order by clause conditional -


good day,

how make sql code below conditional 1 changing asc or desc value:

order   inventory_quantity.product_color_name desc

i've tried case statement got error

order   case product_color_name_sort when product_color_name_sort = 'asc' inventory_quantity.product_color_name end asc

also tried if statement still error

order   inventory_quantity.product_color_name if(product_color_name_sort = 'asc', 'asc', 'desc')

is possible?

procedure code

delimiter //    create definer=`root`@`localhost`    procedure `stocks_quantities`(     in `depot_id` int,      in `qa` int,      in `product_dimension_id` int,      in `product_color_id` int,      in `product_unit_name` int,      in `product_status_id` int,      in `product_name_sort` varchar(10) charset utf8,      in `product_color_name_sort` varchar(10) charset utf8,      in `product_dimension_name_sort` varchar(10) charset utf8,      in `i_limit` int,      in `i_offset` int     )        no sql       begin        declare i_limit_temp int default 0;       set i_limit_temp = coalesce(nullif(i_limit, ''));        if i_limit = 0 or i_limit = '' or i_limit null           set i_limit_temp = 1000;       end if;        select         *               (           select             products.id 'product_id',             products.name 'product_name',             products.model 'product_model',             products.qty_low_alert 'product_qty_low_alert',             coalesce(tbl_included_inventories.product_total,0) 'included_inventory_total',              coalesce(tbl_received_pos.product_total,0) 'received_po_total',              coalesce(tbl_received_tos.product_total,0) 'received_to_total',              coalesce(tbl_excluded_inventories.product_total,0) 'excluded_inventory_total',              coalesce(tbl_transfer_orders.product_total,0) 'transfer_order_total',              coalesce(tbl_official_receipts.product_total,0) 'official_receipt_total',              coalesce(tbl_missed_pos.product_total,0) 'missed_purchased_order_total',              coalesce(tbl_purchase_orders.product_total,0) 'purchased_order_total',              coalesce(tbl_wrong_send_pos.product_total,0) 'wrong_send_purchased_order_total',              coalesce(tbl_sales_returns.product_total,0) 'sales_return_total',              (               coalesce(tbl_purchase_orders.product_total,0)               -               (                 coalesce(tbl_received_pos.product_total,0)                 +                 coalesce(tbl_missed_pos.product_total,0)               )             ) 'af',             (               coalesce(tbl_excluded_inventories.product_total,0) +               coalesce(tbl_transfer_orders.product_total,0) +               coalesce(tbl_official_receipts.product_total,0)             ) 'tr',              (               coalesce(tbl_included_inventories.product_total,0) +               coalesce(tbl_received_pos.product_total,0) +               coalesce(tbl_received_tos.product_total,0) +               coalesce(tbl_sales_returns.product_total,0)             ) -             (               coalesce(tbl_excluded_inventories.product_total,0) +               coalesce(tbl_transfer_orders.product_total,0) +               coalesce(tbl_official_receipts.product_total,0)             )             + coalesce(tbl_wrong_send_pos.product_total,0)             'qa',             tbl_product_colors.id product_color_id,             tbl_product_dimensions.id product_dimension_id,             products.product_status_id product_status_id,             tbl_product_colors.name product_color_name,             tbl_product_dimensions.name product_dimension_name,             tbl_product_units.name product_unit_name                        products           left join             (               select                 included_inventory_details.product_id,                 sum(included_inventory_details.quantity_included) product_total                                 included_inventories               left join                   included_inventory_details                   on                   included_inventories.id = included_inventory_details.included_inventory_id                                  included_inventories.depot_id = coalesce(nullif(depot_id, ''), included_inventories.depot_id)               group                 included_inventory_details.product_id             )             tbl_included_inventories on tbl_included_inventories.product_id = products.id           left join             (               select                 received_po_details.product_id,                 sum(received_po_details.quantity_received) product_total                               received_pos               left join                 received_po_details                 on                 received_pos.id = received_po_details.received_po_id                                    received_pos.depot_id = coalesce(nullif(depot_id, ''), received_pos.depot_id)               group                 received_po_details.product_id               )             tbl_received_pos on tbl_received_pos.product_id = products.id           left join             (               select                 received_to_details.product_id,                 sum(received_to_details.quantity_received) product_total                               received_tos               left join                 received_to_details                 on                 received_tos.id = received_to_details.received_to_id                                    received_tos.depot_id = coalesce(nullif(depot_id, ''), received_tos.depot_id)               group                 received_to_details.product_id             )             tbl_received_tos on tbl_received_tos.product_id = products.id           left join             (               select                 excluded_inventory_details.product_id,                 sum(excluded_inventory_details.quantity_excluded) product_total                               excluded_inventories               left join                 excluded_inventory_details                 on                 excluded_inventories.id = excluded_inventory_details.excluded_inventory_id                                    excluded_inventories.depot_id = coalesce(nullif(depot_id, ''), excluded_inventories.depot_id)               group                 excluded_inventory_details.product_id               )             tbl_excluded_inventories on tbl_excluded_inventories.product_id = products.id           left join             (               select                 transfer_order_details.product_id,                 sum(transfer_order_details.quantity_transfering) product_total                               transfer_orders               left join                 transfer_order_details                 on                 transfer_orders.id = transfer_order_details.transfer_order_id                                    transfer_orders.releasing_depot_id = coalesce(nullif(depot_id, ''), transfer_orders.releasing_depot_id)               group                 transfer_order_details.product_id             )             tbl_transfer_orders on tbl_transfer_orders.product_id = products.id           left join             (               select                 official_receipt_details.product_id,                 sum(official_receipt_details.quantity_released) product_total                               official_receipts               left join                 official_receipt_details                 on                 official_receipts.id = official_receipt_details.official_receipt_id                                    official_receipts.depot_id = coalesce(nullif(depot_id, ''), official_receipts.depot_id)               group                 official_receipt_details.product_id               )             tbl_official_receipts on tbl_official_receipts.product_id = products.id           left join             (               select                 missed_po_details.product_id,                 sum(missed_po_details.quantity_missed) product_total                               missed_pos               left join                 missed_po_details                 on                 missed_pos.id = missed_po_details.missed_po_id                                    missed_pos.depot_id = coalesce(nullif(depot_id, ''), missed_pos.depot_id)               group                 missed_po_details.product_id               )             tbl_missed_pos on tbl_missed_pos.product_id = products.id           left join             (               select                 purchase_order_details.product_id,                 sum(purchase_order_details.quantity_ordered) product_total                               purchase_orders               left join                 purchase_order_details                 on                 purchase_orders.id = purchase_order_details.purchase_order_id                                    purchase_orders.depot_id = coalesce(nullif(depot_id, ''), purchase_orders.depot_id)               group                 purchase_order_details.product_id               )             tbl_purchase_orders on tbl_purchase_orders.product_id = products.id           left join             (               select                 wrong_send_po_details.product_id,                 sum(wrong_send_po_details.quantity_wrong_send) product_total                               wrong_send_pos               left join                 wrong_send_po_details                 on                 wrong_send_pos.id = wrong_send_po_details.wrong_send_po_id                                    wrong_send_pos.depot_id = coalesce(nullif(depot_id, ''), wrong_send_pos.depot_id)               group                 wrong_send_po_details.product_id               )             tbl_wrong_send_pos on tbl_wrong_send_pos.product_id = products.id           left join             (               select                 sales_return_details.product_id,                 sum(sales_return_details.quantity_received) product_total                               sales_returns               left join                 sales_return_details                 on                 sales_returns.id = sales_return_details.sales_return_id                                    sales_returns.depot_id = coalesce(nullif(depot_id, ''), sales_returns.depot_id)               group                 sales_return_details.product_id               )             tbl_sales_returns on tbl_sales_returns.product_id = products.id           left join             (               select                 product_colors.id,                 product_colors.name                               product_colors             )             tbl_product_colors on tbl_product_colors.id = products.product_color_id           left join             (               select                 product_dimensions.id,                 product_dimensions.name                               product_dimensions             )             tbl_product_dimensions on tbl_product_dimensions.id = products.product_dimension_id           left join             (               select                 product_units.id,                 product_units.name                               product_units             )             tbl_product_units on tbl_product_units.id = products.product_unit_id         ) inventory_quantity               inventory_quantity.qa >= coalesce(nullif(qa, ''), 0)         ,         inventory_quantity.product_dimension_id = coalesce(nullif(product_dimension_id, ''), inventory_quantity.product_dimension_id)         ,         inventory_quantity.product_color_id = coalesce(nullif(product_color_id, ''), inventory_quantity.product_color_id)         ,         inventory_quantity.product_unit_name = coalesce(nullif(product_unit_name, ''), inventory_quantity.product_unit_name)           ,         inventory_quantity.product_status_id = coalesce(nullif(product_status_id, ''), inventory_quantity.product_status_id)       order          case product_name_sort when product_name_sort = 'asc' inventory_quantity.product_name end asc,         case product_name_sort when product_name_sort = 'desc' inventory_quantity.product_name end desc,          case product_color_name_sort when product_color_name_sort = 'asc' inventory_quantity.product_color_name end asc,         case product_color_name_sort when product_color_name_sort = 'desc' inventory_quantity.product_color_name end desc,          case product_dimension_name_sort when product_dimension_name_sort = 'asc' inventory_quantity.product_dimension_name end asc,         case product_dimension_name_sort when product_dimension_name_sort = 'desc' inventory_quantity.product_dimension_name end desc        limit          i_limit_temp       offset         i_offset;         end; // delimiter ;

thanks

according mysql documentation not possible.

[order {col_name | expr | position} [asc | desc], ...]

reference: http://dev.mysql.com/doc/refman/5.7/en/select.html

reference2: http://dev.mysql.com/doc/refman/5.7/en/expressions.html

you can try move logic in select clause , order new created column


-

Comments

Post a Comment

Popular posts from this blog

PySide and Qt Properties: Connecting signals from Python to QML -

i trying run simple pyside appliccation design made in qml. call function in qml python in separated thread. i have following qml view: import qt 4.7 rectangle { visible: true width: 100; height: 100 color: "lightgrey" text { id: datestr objectname: "datestr" x: 10 y: 10 text: "init text" font.pixelsize: 12 function updatedata(text) { datestr.text = qstr(text) console.log("you said: " + text) } } } here application: some imports.... #!/usr/bin/python import sys import time pyside import qtdeclarative, qtcore pyside.qtgui import qapplication pyside.qtdeclarative import qdeclarativeview the qthread worker.... #worker thread class worker(qtcore.qthread): updateprogress = qtcore.signal(int) def __init__(self, child): qtcore.qthread.__init__(self) self.child = child def run(self):
Read more

c# - DevExpress.Wpf.Grid.InfiniteGridSizeException was unhandled -

Image
when using devexpress, see error: devexpress.wpf.grid.infinitegridsizeexception unhandled message="by default, infinite grid height not allowed since grid rows rendered , hence grid work slowly. fix issue, should place grid container give finite height grid, or should manually specify grid's height or maxheight. note can avoid exception setting gridcontrol.allowinfinitegridsize static property true, in case grid run slowly." the problem dxgrid has infinite height. to fix, set height non-infinite. snoop absolutely invaluable this: if "height" xaml element infinite (i.e. 0 or nan ), can set using one of following: option 1: height="{binding path=actualheight, relativesource={relativesource mode=findancestor, ancestortype=uielement}}" option 2: verticalalignment="stretch" option 3: height="auto" hint: use verticalalignment="stretch" if child of grid <rowdefinition height=
Read more

scala - 'wrong top statement declaration' when using slick in IntelliJ -

Image
i'm porting project scala 2.9 2.10, therefore have use slick instead of scalaquery. i'm using slick 2.1.0 since supports ms access. according this tutorial , upgrade guide changed robs object class , added val robs : why error message wrong top statement declaration , how rid of it? edit: i'm new scala... according this question seems can't put val outside of methods or classes, right? code above directly in packge. right approach slick then? should move code in class or trait? according this changed val robs = tablequery[robs] to object robs extends tablequery(new robs(_)) {} . no warnings or errors anymore. :)
Read more