Home » Developer & Programmer » Forms » populating record in multi recor d block (oracle forms 6i,windows)
populating record in multi recor d block [message #381826] Tue, 20 January 2009 00:56 Go to next message
dutt49
Messages: 3
Registered: January 2009
Junior Member

Hi,

I have a requirement for filling data in the multirecord block in a form.

The requirement is like that first record should be populated with a Opening balance (means the balance that was there on that day)
And after that from the second record on wards i need to fill the
daily transaction can any one tell me how to do this,

In the matter of acheiving , i have writtten the following query

SELECT 
     'Opening' day,
      NULL period,
     --:p_prev_mon_jobs_open jobs_open,
     --:p_prev_mon_jobs_close jobs_close,   
     SUM(NVL(xdsi.veh_ca,0) + NVL(xdsi.veh_cr,0)) veh_tot,       
     SUM(xdsi.veh_sold) veh_sold,
     --:p_prev_mon_veh_onhand veh_onhand,   
     SUM(xdsi.veh_cancl_qty) veh_cancl_qty,
     SUM(xdsi.veh_arrd_qty) veh_arrd_qty,
     TO_NUMBER(:GLOBAL.VEH_ON_HAND) veh_on_hand
   FROM 
    xxar_daily_sales_income xdsi
   WHERE 
    xdsi.day BETWEEN to_date('01-JAN-'||to_char(sysdate-1,'YY'),'DD-MON-YY') AND LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE),-1))--to_date('01-'||to_char(sysdate-1,'MON-YY'),'DD-MON-YY')
   UNION ALL
   SELECT * FROM 
   (SELECT
    TO_CHAR(xdsi.day),
    xdsi.period period,
    NVL(xdsi.veh_ca,0) + NVL(xdsi.veh_cr,0) veh_tot,
    xdsi.veh_sold veh_sold,
    xdsi.veh_cancl_qty veh_cancl_qty,
    xdsi.veh_arrd_qty veh_arrd_qty ,
    xdsi.veh_onhand veh_on_hand
    FROM 
    xxar_daily_sales_income xdsi
    WHERE
    xdsi.day >= to_date('01-'||to_char(sysdate-1,'MON-YY'),'DD-MON-YY'))
    order by xdsi.day ; 


but the problem it is not acceepting the column in the order by clause.

The error is like this
ORDER BY item must be a number in the select expression ,

My column in the order by clause is a date column and i need the date in the order of the date .

This error is coming when i compile the program unit in the form

This is an urgent issue , can anyone respond on this asap

Thanks and Regards
Sunil Dutt.S



[EDITED by LF: applied [code] tags]

[Updated on: Tue, 20 January 2009 01:38] by Moderator

Report message to a moderator

Re: populating record in multi recor d block [message #381829 is a reply to message #381826] Tue, 20 January 2009 01:06 Go to previous messageGo to next message
azamkhan
Messages: 548
Registered: August 2005
Senior Member
Assuming that all columns and tables and condition are correct. Inorder to use order the query shouldn't be using union. Why dont you use your query in FROM clause and then put order by clause in the main / outer query.

SELECT * FROM (YOUR_UNION_QUERY)
ORDER BY COLMUN_NAME

Regards,
Azam Khan
Re: populating record in multi recor d block [message #381841 is a reply to message #381829] Tue, 20 January 2009 01:34 Go to previous messageGo to next message
dutt49
Messages: 3
Registered: January 2009
Junior Member

Hi Azim khan,

Thanks for your Quick reply ,

I am pleased with your reply , But still i doubt that the performance will be degraded

What do you say abt this
Re: populating record in multi recor d block [message #381854 is a reply to message #381841] Tue, 20 January 2009 02:31 Go to previous messageGo to next message
sinida1984
Messages: 83
Registered: September 2007
Location: India
Member
Hi..
Please try

SELECT
sysdate day,
'Opening' date_label,
NULL period,
--:p_prev_mon_jobs_open jobs_open,
--:p_prev_mon_jobs_close jobs_close,
SUM(NVL(xdsi.veh_ca,0) + NVL(xdsi.veh_cr,0)) veh_tot,
SUM(xdsi.veh_sold) veh_sold,
--:p_prev_mon_veh_onhand veh_onhand,
SUM(xdsi.veh_cancl_qty) veh_cancl_qty,
SUM(xdsi.veh_arrd_qty) veh_arrd_qty,
TO_NUMBER(:GLOBAL.VEH_ON_HAND) veh_on_hand
FROM
xxar_daily_sales_income xdsi
WHERE
xdsi.day BETWEEN to_date('01-JAN-'||to_char(sysdate-1,'YY'),'DD-MON-YY') AND LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE),-1))--to_date('01-'||to_char(sysdate-1,'MON-YY'),'DD-MON-YY')
UNION ALL
SELECT * FROM
(SELECT
xdsi.day,
xdsi.day day_label,
xdsi.period period,
NVL(xdsi.veh_ca,0) + NVL(xdsi.veh_cr,0) veh_tot,
xdsi.veh_sold veh_sold,
xdsi.veh_cancl_qty veh_cancl_qty,
xdsi.veh_arrd_qty veh_arrd_qty ,
xdsi.veh_onhand veh_on_hand
FROM
xxar_daily_sales_income xdsi
WHERE
xdsi.day >= to_date('01-'||to_char(sysdate-1,'MON-YY'),'DD-MON-YY'))
order by 1 ;
Re: populating record in multi recor d block [message #381869 is a reply to message #381854] Tue, 20 January 2009 02:55 Go to previous message
dutt49
Messages: 3
Registered: January 2009
Junior Member

Thanks MR sindia1984,

This has solved my problem,

I too thought abt this way, still doubted whether this could work ,
Now my problem is solved
Previous Topic: Is there any way to get the source of Program Units from forms? (merged)
Next Topic: Capture Delete_record
Goto Forum:
  


Current Time: Sun Dec 04 08:51:36 CST 2016

Total time taken to generate the page: 0.08254 seconds