OraFAQ Forum: Forms » populating record in multi recor d block

Home » Developer & Programmer » Forms » populating record in multi recor d block (oracle forms 6i,windows)

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

populating record in multi recor d block [message #381826]

Tue, 20 January 2009 00:56

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

azamkhan
Messages: 557
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

Report message to a moderator

Re: populating record in multi recor d block [message #381841 is a reply to message #381829]

Tue, 20 January 2009 01:34

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

Report message to a moderator

Re: populating record in multi recor d block [message #381854 is a reply to message #381841]

Tue, 20 January 2009 02:31

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 ;

Report message to a moderator

Re: populating record in multi recor d block [message #381869 is a reply to message #381854]

Tue, 20 January 2009 02:55

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

Report message to a moderator

Previous Topic:	Is there any way to get the source of Program Units from forms? (merged)
Next Topic:	Capture Delete_record

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Sep 24 21:08:49 CDT 2025