OraFAQ Forum: Forms » create table from query with passing parameters

Home » Developer & Programmer » Forms » create table from query with passing parameters (9i, form6i)

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

create table from query with passing parameters [message #527733]

Thu, 20 October 2011 00:07

kame
Messages: 69
Registered: July 2009

Member

I want to create table from query with passing parameters

create table as temp
select * from emp
where hiredate between :sdate and :edate
and deptno = :dpt

when I tried in Toad after passing parameters it gives me error

ORA-01036: illegal variable name/number

Report message to a moderator

Re: create table from query with passing parameters [message #527735 is a reply to message #527733]

Thu, 20 October 2011 00:48

Littlefoot
Messages: 21817
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Invalid syntax. Should be CREATE TABLE TEMP AS (not "as temp")
Bind variables aren't allowed for data definition operations (so this would result in ORA-01027). You should have used & instead of : (i.e. substitution instead of bind variables)

HIREDATE requires DATE datatype, DEPTNO expects NUMBER datatype. What did you provide? Did you enter something that looks like a DATE to you (but Oracle disagreed)?

Please, post your SQL*Plus session so that we could see what you did. It *should* look like this:

SQL> create table temp as
  2  select * from emp
  3  where hiredate between &sdate and &edate
  4  and deptno = &dpt;
Enter value for sdate: to_date('25.08.1978', 'dd.mm.yyyy')
Enter value for edate: to_date('13.03.1986', 'dd.mm.yyyy')
Enter value for dpt: 10

Table created.

SQL> select * from temp;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09.06.1981 00:00:00       3250                    10
      7839 KING       PRESIDENT            17.11.1981 00:00:00       5800                    10
      7934 MILLER     CLERK           7782 23.12.1982 00:00:00       2100                    10

SQL>

Report message to a moderator

Re: create table from query with passing parameters [message #527745 is a reply to message #527735]

Thu, 20 October 2011 01:16

kame
Messages: 69
Registered: July 2009

Member

thank you for your reply. Actual I tried in TOAD. because it is easy to pass parameters there. And I want to create table for using Form 6i.
I want to create table in form

PROCEDURE TableView IS
BEGIN
  
FORMS_DDL('drop table test');
declare
v_com varchar2(4000);
BEGIN
 
V_COM := 'Create table test as 
  	select * from emp
where hiredate between :sdate and :edate
and deptno = :dpt
';
 
FORMS_DDL(V_COM);
end;

[Updated on: Thu, 20 October 2011 01:17]

Report message to a moderator

Re: create table from query with passing parameters [message #527748 is a reply to message #527745]

Thu, 20 October 2011 01:30

Littlefoot
Messages: 21817
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

This is most probably VERY wrong. Create table once, in SQL*Plus. Use it as many times as you want from Forms, just don't drop & create tables on fly - that's not how Oracle works.

So, why do you think you have to create tables that way?

Report message to a moderator

Re: create table from query with passing parameters [message #527750 is a reply to message #527748]

Thu, 20 October 2011 01:43

kame
Messages: 69
Registered: July 2009

Member

Actual I want selected data according to user input. and after that I will create report on the basis of that data.
this is my query, after save result in table of this query I will create some cursor in form for making report, because I am facing some problem to make report "moving weighted average"

select ddate,fnd_code,fund,co_code,com,dist,rem,units,dp_uprice price,nvl(dp_uprice,0)*units Amount,stat 
		 from  
(
select :sdate-1 dDate ,inv_fnd_code Fnd_code,null Fund, inv_co_code Co_code, null Com,null Dist ,null Rem, 
   nvl(Qry1.Iunit,0) - nvl(qry2.runit,0) Units, null Price
		  ,null Amount,null Stat from (   
		  select inv_co_code,inv_fnd_code,sum(nvl(inv_nofu,0))IUnit, sum(inv_uprice),sum(inv_amount), null from investment 
		  where inv_date < :sdate 
		  and inv_fnd_code = :fnd
		  and inv_co_code = :co_code
		  group by inv_fnd_code,inv_co_code
		  ) qry1,
		  (select red_co_code,red_fnd_code,sum(nvl(red_nofu,0))RUnit, sum(red_uprice),sum(red_amount), null from redemption 
		  where red_date < :sdate 
		  and red_fnd_code = :fnd
		  and red_co_code = :co_code group by red_co_code,red_fnd_code
		  ) qry2 
		  where qry1.inv_co_code = qry2.red_co_code (+)
		  and qry1.inv_fnd_code = qry2.red_fnd_code (+) 
	) Opn1, 	  
---------------  Getting Dp_Uprice = :dd ----------------
(SELECT dp_fnd_code, 
       max(dp_date) keep (dense_rank last order by dp_date) Dp_date, 
       max(dp_uprice) keep (dense_rank last order by dp_date) dp_uprice
  FROM dailyprice 
  where dp_date <= :sdate
  and dp_uprice is not null
  group by dp_fnd_code 
 ) opn2
  ------------------------------------
where opn1.fnd_code = opn2.dp_fnd_code (+)	   
------------------------------------ -------------- End Opening Balance --------------------
		  union all
		  ----------------------- Investment -----------------
		  select inv_date ddate,inv_fnd_code,inv_fnd_name fund,inv_co_code,inv_co_name com,inv_dist_name dist, inv_remarks rem, inv_nofu units, inv_uprice price,
		  inv_amount Amount, 'I' STAT from investment
		   WHERE inv_date BETWEEN :SDATE AND :EDATE
		   and inv_fnd_code = :fnd
		   and inv_co_code = :co_code
		  union all
		  -------------------Redeumption --------------------
		  select red_date,red_fnd_code,red_fnd_name,red_co_code,red_co_name,red_dist_name, red_remarks, 
		  red_nofu - red_nofu - red_nofu red_nofu , 
		  red_uprice,
		  null red_amount, 'R'  from redemption
		  WHERE red_date BETWEEN :SDATE AND :EDATE
		  and red_fnd_code = :fnd
		  and red_co_code = :co_code

Report message to a moderator

Re: create table from query with passing parameters [message #527761 is a reply to message #527750]

Thu, 20 October 2011 02:04

Littlefoot
Messages: 21817
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Perhaps you'd rather create a view (as UNION of all these SELECTs), as well as a report (whose query would SELECT FROM VIEW) that accepts parameters (these DATE values, DEPTNO, whatever).

Report message to a moderator

Re: create table from query with passing parameters [message #527774 is a reply to message #527761]

Thu, 20 October 2011 02:41

kame
Messages: 69
Registered: July 2009

Member

Actual It is a sub query and I have to use parameters in this query if I will not use it will collect data according to current date . that will be false.

SELECT dp_fnd_code,
max(dp_date) keep (dense_rank last order by dp_date) Dp_date,
max(dp_uprice) keep (dense_rank last order by dp_date) dp_uprice
FROM dailyprice
where dp_date <= :sdate
and dp_uprice is not null
group by dp_fnd_code

Report message to a moderator

Previous Topic:	travel from 10g to 11g problems
Next Topic:	tree_node

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Aug 11 12:18:25 CDT 2024