OraFAQ Forum: SQL & PL/SQL » sorting order need to be done when the columns in the order by are given in input parameter

Home » SQL & PL/SQL » SQL & PL/SQL » sorting order need to be done when the columns in the order by are given in input parameter (11 g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sorting order need to be done when the columns in the order by are given in input parameter [message #653766]

Mon, 18 July 2016 08:28

saipavan.plsql
Messages: 17
Registered: February 2015
Location: chennai

Junior Member

hi all,

i had a small issue in fetching the information with the order by condition.when the columns in the order by is an input variable
the order by condition is not working as expected.

create table dummy_table
(
  javasession_id             VARCHAR2(255),
  search_criteria            VARCHAR2(4000),
  executive_uid              NUMBER(38),
  plans_uid                  NUMBER(38),
  participant_uid            NUMBER(38),
  transaction_date           DATE,
  transaction_type           VARCHAR2(50),
  event_name                 VARCHAR2(50),
  event_code                 VARCHAR2(255),
  reallocation_period_uid    NUMBER(38),
  distribution_code          VARCHAR2(255),
  award_code                 VARCHAR2(50),
  sai_trade_event_code       VARCHAR2(50),
  request_date               DATE,
  account_name               VARCHAR2(255),
  offering_uid               VARCHAR2(38),
  offering_name              VARCHAR2(120),
  offering_currency_iso_code CHAR(3),
  number_shares              NUMBER(24,8),
  share_price_scr_curr       NUMBER(24,8),
  share_price_off_curr       NUMBER(24,8),
  tx_amount_scr_curr         NUMBER(24,8),
  tx_amount_off_cur          NUMBER(24,8),
  tx_est_amt_scr_curr        NUMBER(24,8),
  tx_est_amt_off_curr        NUMBER(24,8),
  from_currency_iso_code     CHAR(3),
  to_currency_iso_code       CHAR(3),
  rate                       NUMBER(24,8),
  from_amount                NUMBER(24,8),
  to_amount                  NUMBER(24,8),
  status                     VARCHAR2(50),
  grouping_code              VARCHAR2(255),
  row_number                 NUMBER(38),
  reference_number           VARCHAR2(50),
  award_name                 VARCHAR2(256),
  show_sai_hist_flag         CHAR(1) default 'Y' not null,
  eqty_dist_req_hdr_uid      NUMBER(38),
  event_action_type          VARCHAR2(225)
);



insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('17-03-2016', 'dd-mm-yyyy'), 'S', 'TRADE', 'Automated_Fee_Trade', 'SETTLED', 'SECAP US', 'Health Care Select Sector SPDR', 'USD', -1.73000000, 66.30000000, null, null, -114.64000000, null, null, null, null, null, null, '1899', null, null, null, null, null, null, 'NO GROUP', 14, 8360, 'Y', null, null, null, 'Automated_Fee_Trade');

insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('17-03-2016', 'dd-mm-yyyy'), 'S', 'TRADE', 'Automated_Fee_Trade', 'SETTLED', 'SECAP US', 'iShares Dow Jones US Technology', 'USD', -1.13000000, 106.85000000, null, null, -120.64000000, null, null, null, null, null, null, '1902', null, null, null, null, null, null, 'NO GROUP', 17, 8360, 'Y', null, null, null, 'Automated_Fee_Trade');

insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('17-03-2016', 'dd-mm-yyyy'), 'S', 'TRADE', 'Automated_Fee_Trade', 'SETTLED', 'SECAP US', 'MSIF Growth - I', 'USD', -4.02000000, 37.01000000, null, null, -148.94000000, null, null, null, null, null, null, '255', null, null, null, null, null, null, 'NO GROUP', 16, 8360, 'Y', null, null, null, 'Automated_Fee_Trade');

insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('21-03-2016', 'dd-mm-yyyy'), 'S', 'FEE', 'Fee_Sweep', 'SETTLED', 'SECAP US', 'MM MSILF Prime Portfolio - IN', 'USD', -788.56000000, 1.00000000, null, null, -788.56000000, null, null, null, null, null, null, '536', null, null, null, null, null, null, 'NO GROUP', 7, 8360, 'Y', null, null, null, 'Fee_Sweep');

insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('20-04-2016', 'dd-mm-yyyy'), 'S', 'FEE', 'Fee_Sweep', 'SETTLED', 'SECAP US', 'MM MSILF Prime Portfolio - IN', 'USD', -4.34000000, 1.00000000, null, null, -4.34000000, null, null, null, null, null, null, '536', null, null, null, null, null, null, 'NO GROUP', 4, 8360, 'Y', null, null, null, 'Fee_Sweep');

insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('17-05-2016', 'dd-mm-yyyy'), 'B', 'TRADE', 'trade', 'SETTLED', 'SECAP US', 'iShares Dow Jones US Telecommunications', 'USD', 1263.79000000, 30.33000000, null, null, 38330.85000000, null, null, 5578, null, null, null, '1905', to_date('04-05-2016 15:59:33', 'dd-mm-yyyy hh24:mi:ss'), null, null, null, null, null, 'NO GROUP', 2, 8360, 'Y', null, null, null, 'REALLOCATION');

insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('17-05-2016', 'dd-mm-yyyy'), 'B', 'TRADE', 'trade', 'SETTLED', 'SECAP US', 'Consumer Staples Select Sector SPDR', 'USD', 359.40000000, 52.53000000, null, null, 18879.37000000, null, null, 5578, null, null, null, '1879', to_date('04-05-2016 15:59:33', 'dd-mm-yyyy hh24:mi:ss'), null, null, null, null, null, 'NO GROUP', 1, 8360, 'Y', null, null, null, 'REALLOCATION');

insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('17-03-2016', 'dd-mm-yyyy'), 'S', 'TRADE', 'Automated_Fee_Trade', 'SETTLED', 'SECAP US', 'MSIF Global Franchise', 'USD', -7.79000000, 20.94000000, null, null, -163.03000000, null, null, null, null, null, null, '251', null, null, null, null, null, null, 'NO GROUP', 15, 8360, 'Y', null, null, null, 'Automated_Fee_Trade');

insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('22-01-2016', 'dd-mm-yyyy'), 'S', 'FEE', 'Fee_Sweep', 'SETTLED', 'SECAP US', 'MM MSILF Prime Portfolio - IN', 'USD', -0.09000000, 1.00000000, null, null, -0.09000000, null, null, null, null, null, null, '536', null, null, null, null, null, null, 'NO GROUP', 18, 8360, 'Y', null, null, null, 'Fee_Sweep');

insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('17-03-2016', 'dd-mm-yyyy'), 'S', 'TRADE', 'Automated_Fee_Trade', 'SETTLED', 'SECAP US', 'Consumer Discretionary Select Sector SPDR', 'USD', -2.03000000, 78.31000000, null, null, -159.32000000, null, null, null, null, null, null, '1878', null, null, null, null, null, null, 'NO GROUP', 12, 8360, 'Y', null, null, null, 'Automated_Fee_Trade');

insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('17-05-2016', 'dd-mm-yyyy'), 'S', 'TRADE', 'trade', 'SETTLED', 'SECAP US', 'iShares Dow Jones US Technology', 'USD', -558.20000000, 102.49000000, null, null, -57210.22000000, null, null, 5578, null, null, null, '1902', to_date('04-05-2016 15:59:33', 'dd-mm-yyyy hh24:mi:ss'), null, null, null, null, null, 'NO GROUP', 3, 8360, 'Y', null, null, null, 'REALLOCATION');

insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('23-03-2016', 'dd-mm-yyyy'), 'B', 'DIVIDEND', 'dividend', 'SETTLED', 'SECAP US', 'iShares Dow Jones US Technology', 'USD', 2.09000000, 106.35000000, null, null, 221.98000000, null, null, null, null, null, null, '1902', null, null, null, null, null, null, 'NO GROUP', 6, 8360, 'Y', null, null, null, 'dividend');

insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('24-12-2015', 'dd-mm-yyyy'), 'B', 'DIVIDEND', 'dividend', 'SETTLED', 'SECAP US', 'iShares Dow Jones US Technology', 'USD', 1.78000000, 107.89000000, null, null, 192.34000000, null, null, null, null, null, null, '1902', null, null, null, null, null, null, 'NO GROUP', 20, 8360, 'Y', null, null, null, 'dividend');

insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('18-03-2016', 'dd-mm-yyyy'), 'B', 'DIVIDEND', 'dividend', 'SETTLED', 'SECAP US', 'Consumer Staples Select Sector SPDR', 'USD', 4.13000000, 52.65000000, null, null, 217.36000000, null, null, null, null, null, null, '1879', null, null, null, null, null, null, 'NO GROUP', 10, 8360, 'Y', null, null, null, 'dividend');

insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('18-03-2016', 'dd-mm-yyyy'), 'B', 'TRADE', 'Automated_Fee_Trade', 'SETTLED', 'SECAP US', 'MM MSILF Prime Portfolio - IN', 'USD', 792.87000000, 1.00000000, null, null, 792.87000000, null, null, null, null, null, null, '536', null, null, null, null, null, null, 'NO GROUP', 8, 8360, 'Y', null, null, null, 'Automated_Fee_Trade');

insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('18-03-2016', 'dd-mm-yyyy'), 'B', 'DIVIDEND', 'dividend', 'SETTLED', 'SECAP US', 'Consumer Discretionary Select Sector SPDR', 'USD', 4.12000000, 78.09000000, null, null, 321.70000000, null, null, null, null, null, null, '1878', null, null, null, null, null, null, 'NO GROUP', 9, 8360, 'Y', null, null, null, 'dividend');

insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('17-03-2016', 'dd-mm-yyyy'), 'S', 'TRADE', 'Automated_Fee_Trade', 'SETTLED', 'SECAP US', 'Consumer Staples Select Sector SPDR', 'USD', -1.63000000, 52.99000000, null, null, -86.31000000, null, null, null, null, null, null, '1879', null, null, null, null, null, null, 'NO GROUP', 13, 8360, 'Y', null, null, null, 'Automated_Fee_Trade');

insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('18-03-2016', 'dd-mm-yyyy'), 'B', 'DIVIDEND', 'dividend', 'SETTLED', 'SECAP US', 'Health Care Select Sector SPDR', 'USD', 3.02000000, 66.84000000, null, null, 201.58000000, null, null, null, null, null, null, '1899', null, null, null, null, null, null, 'NO GROUP', 11, 8360, 'Y', null, null, null, 'dividend');

insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('01-04-2016', 'dd-mm-yyyy'), 'B', 'DIVIDEND', 'dividend', 'SETTLED', 'SECAP US', 'MM MSILF Prime Portfolio - IN', 'USD', 0.03000000, 1.00000000, null, null, 0.03000000, null, null, null, null, null, null, '536', null, null, null, null, null, null, 'NO GROUP', 5, 8360, 'Y', null, null, null, 'dividend');

insert into dummy_table (TRANSACTION_DATE, TRANSACTION_TYPE, EVENT_NAME, EVENT_CODE, STATUS, ACCOUNT_NAME, OFFERING_NAME, OFFERING_CURRENCY_ISO_CODE, NUMBER_SHARES, SHARE_PRICE_OFF_CURR, SHARE_PRICE_SCR_CURR, TX_AMOUNT_SCR_CURR, TX_AMOUNT_OFF_CUR, TX_EST_AMT_OFF_CURR, TX_EST_AMT_SCR_CURR, REALLOCATION_PERIOD_UID, DISTRIBUTION_CODE, AWARD_CODE, SAI_TRADE_EVENT_CODE, OFFERING_UID, REQUEST_DATE, FROM_CURRENCY_ISO_CODE, TO_CURRENCY_ISO_CODE, RATE, FROM_AMOUNT, TO_AMOUNT, GROUPING_CODE, ROW_NUMBER, PARTICIPANT_UID, SHOW_SAI_HIST_FLAG, EQTY_DIST_REQ_HDR_UID, REFERENCE_NUMBER, AWARD_NAME, EVENT_ACTION_TYPE)
values (to_date('01-01-2016', 'dd-mm-yyyy'), 'B', 'DIVIDEND', 'dividend', 'SETTLED', 'SECAP US', 'MM MSILF Prime Portfolio - IN', 'USD', 0.09000000, 1.00000000, null, null, 0.09000000, null, null, null, null, null, null, '536', null, null, null, null, null, null, 'NO GROUP', 19, 8360, 'Y', null, null, null, 'dividend');

consider p_sort_order :='transaction_date DESC,transaction_type'

create or replace procedure  Get_Sort_Action(p_sort_order in varchar2
                                ,p_compliance_actions_cur OUT SYS_REFCURSOR) IS

begin

 OPEN p_compliance_actions_cur FOR
select DISTINCT tth.transaction_date
               ,tth.transaction_type
               ,tth.event_name
               ,tth.event_code
               ,tth.status
               ,tth.account_name
               ,tth.offering_name
               ,tth.offering_currency_iso_code
               ,tth.number_shares             
               ,tth.share_price_off_curr      
               ,tth.share_price_scr_curr      
               ,tth.tx_amount_scr_curr        
               ,tth.tx_amount_off_cur         
               ,tth.tx_est_amt_off_curr       
               ,tth.tx_est_amt_scr_curr       
               ,tth.reallocation_period_uid
               ,tth.distribution_code
               ,tth.award_code
               ,tth.sai_trade_event_code
               ,tth.offering_uid
               ,tth.request_date
               ,tth.from_currency_iso_code
               ,tth.to_currency_iso_code
               ,tth.rate                      
               ,tth.from_amount               
               ,tth.to_amount                 
               ,tth.grouping_code
               ,tth.row_number
               ,tth.participant_uid
               ,tth.show_sai_hist_flag
               ,tth.eqty_dist_req_hdr_uid
               ,tth.reference_number 
               ,tth.award_name
               ,tth.event_action_type
  FROM DUMMY_TABLE tth
ORDER BY p_sort_order;

end;

i am fetching the information out of refcursor but the order by condition is not working .

could anyone suggest me how to handle this senoria.

Report message to a moderator

Re: sorting order need to be done when the columns in the order by are given in input parameter [message #653767 is a reply to message #653766]

Mon, 18 July 2016 08:35

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

(ab)use EXECUTE IMMEDIATE

Report message to a moderator

Re: sorting order need to be done when the columns in the order by are given in input parameter [message #653768 is a reply to message #653767]

Mon, 18 July 2016 09:04

Bill B
Messages: 1971
Registered: December 2004

Senior Member

What you have is sorting by the literal string 'transaction_date DESC,transaction_type', NOT the columns

Report message to a moderator

Re: sorting order need to be done when the columns in the order by are given in input parameter [message #653769 is a reply to message #653768]

Mon, 18 July 2016 09:14

Bill B
Messages: 1971
Registered: December 2004

Senior Member

Just curious. If the procedure is so simple, why not create a view and then simply use the sort order on the select from the view?
like

select * from dummy_table_v
order by transaction_date DESC,transaction_type;

Report message to a moderator

Re: sorting order need to be done when the columns in the order by are given in input parameter [message #653770 is a reply to message #653766]

Mon, 18 July 2016 09:59

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Simplest way:

create or replace procedure  Get_Sort_Action(p_sort_order in varchar2
                                ,p_compliance_actions_cur OUT SYS_REFCURSOR) IS

begin

 OPEN p_compliance_actions_cur FOR
'select DISTINCT tth.transaction_date
...
  FROM DUMMY_TABLE tth
ORDER BY' || p_sort_order;

end;

Example:

SQL> var o varchar2(100)
SQL> var s refcursor
SQL> exec :o := 'deptno, sal desc'

PL/SQL procedure successfully completed.

SQL> exec open :s for 'select * from emp order by '||:o;

PL/SQL procedure successfully completed.

SQL> print :s
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17/11/1981 00:00:00       5000                    10
      7782 CLARK      MANAGER         7839 09/06/1981 00:00:00       2450                    10
      7934 MILLER     CLERK           7782 23/01/1982 00:00:00       1300                    10
      7788 SCOTT      ANALYST         7566 19/04/1987 00:00:00       3000                    20
      7902 FORD       ANALYST         7566 03/12/1981 00:00:00       3000                    20
      7566 JONES      MANAGER         7839 02/04/1981 00:00:00       2975                    20
      7876 ADAMS      CLERK           7788 23/05/1987 00:00:00       1100                    20
      7369 SMITH      CLERK           7902 17/12/1980 00:00:00        800                    20
      7698 BLAKE      MANAGER         7839 01/05/1981 00:00:00       2850                    30
      7499 ALLEN      SALESMAN        7698 20/02/1981 00:00:00       1600        300         30
      7844 TURNER     SALESMAN        7698 08/09/1981 00:00:00       1500          0         30
      7654 MARTIN     SALESMAN        7698 28/09/1981 00:00:00       1250       1400         30
      7521 WARD       SALESMAN        7698 22/02/1981 00:00:00       1250        500         30
      7900 JAMES      CLERK           7698 03/12/1981 00:00:00        950                    30

14 rows selected.

Report message to a moderator

Re: sorting order need to be done when the columns in the order by are given in input parameter [message #653813 is a reply to message #653770]

Tue, 19 July 2016 07:31

saipavan.plsql
Messages: 17
Registered: February 2015
Location: chennai

Junior Member

@Michel : thx Michel its working as expected.

@Bill : sorry bill i can't use it as a view because i should not hard code the order by columns
the requirement is sorting order needs to be done from the input parameter the order may changes as per client requirement.
and the query is not as simple as it looks to create a view i had provided a portion of the query for the better understanding

Tnx for Your suggestions bill.

Report message to a moderator

Previous Topic:	transposing of data in oracle
Next Topic:	Merging BLOB

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 06:13:27 CDT 2024