Home » SQL & PL/SQL » SQL & PL/SQL » SQL query help
SQL query help [message #204440] Mon, 20 November 2006 15:33 Go to next message
mailtopjha
Messages: 4
Registered: November 2006
Junior Member
Hello

I have got very little knowledge of SQL query.Could someone please help me in writing a sql Query for the following scenario,


I have to create a copy of a Table OLD_T having column A,B,C and D using

CREATE TABLE ..AS ..

During the copy the value of Attribute 'C' will be searched in another table C_REF and the corresponding value will be used in the new table ,And for the Attribute D the new value should be K times D( K*D).
Here is pictorial representation of the query.

TABLE - OLD_T
----------------
A B C D
- - - -
A1 B1 C1 D1
A2 B2 C2 D2
A3 B3 C3 D3

TABLE C-REF
------------------
C E
- -
C1 E1
C2 E2
C3 E3

Final Table should look as follows.
---------------------
A B C D
- - - -
A1 B1 E1 D1*K
A2 B2 E2 D2*K
A3 B3 E3 D3*K


Thanks a lot in Advance
Thanks
Prabj


Re: SQL query help [message #204444 is a reply to message #204440] Mon, 20 November 2006 16:03 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Where will you find a value for 'K'?
CREATE TABLE final AS
SELECT o.col_a, o.col_b, c.col_e, o.col_d * K
FROM old_t o, c_ref c
WHERE o.col_c = c.col_c;
Re: SQL query help [message #204624 is a reply to message #204444] Tue, 21 November 2006 07:24 Go to previous messageGo to next message
mailtopjha
Messages: 4
Registered: November 2006
Junior Member

Hello
Thank you very much for your response.
I tried the SQL command, it is Working fine apart from a minor problem. which is as follows.

In the Query the value of K can be any thing like 3.14.

When I ran the query

CREATE TABLE T4 as
SELECT ALT1.CARD_NBR,ALT2.SCN ,ALT3.SRN ,ALT1.CR * 2.3456 as CR
FROM T1 ALT1, T2 ALT2, T3 ALT3
WHERE ALT1.SC = ALT2.SCO AND ALT1.SR = ALT3.SRO ;

The Table has been created exactly in the same manner( in terms of data) as I wanted but when I ran
desc T4;
it is bit different. Here is the description of all the Tables

SQL> desc T1;
Name Type
----------------- ------------
CARD_NBR VARCHAR2(24)
SC NUMBER(18)
SR NUMBER(18)
CR NUMBER(18)

SQL> desc T2;
Name Type
----------------- ------------
SCO NUMBER(18)
SCN NUMBER(18)

SQL> desc T3;
Name Type
----------------- ------------
SRO NUMBER(18)
SRN NUMBER(18)

SQL> desc T4;
Name Type
----------------- ------------
CARD_NBR VARCHAR2(24)
SCN NUMBER(18)
SRN NUMBER(18)
CR NUMBER
I wanted to have the CR field in the TABLE T4 AS NUMBER(18) as we have in the TABLE T1.
Please let me know what I can do.

Thanks a lot in advance
Prabj

Re: SQL query help [message #204634 is a reply to message #204624] Tue, 21 November 2006 07:54 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I *think* it can not be done in a simple way. ALTER TABLE won't work as column must be empty in order to decrease its precision.

However, you could FIRST create an empty table (using desired precision) and then insert data into it using SELECT statement which, as you've said, works OK.

[Updated on: Tue, 21 November 2006 07:54]

Report message to a moderator

Re: SQL query help [message #204637 is a reply to message #204634] Tue, 21 November 2006 08:18 Go to previous messageGo to next message
mailtopjha
Messages: 4
Registered: November 2006
Junior Member

Hello

Thanks again for your quick response.

I just wanted to know that is there any significance performance difference between the two method

Method 1
--------
a)CREATE TABLE ( empty table with desired schema)
b) Instert the values by SELECT (using the query as you suggested)

Method 2
--------
a) CREATE TABLE ..AS SELECT ( query)

Assuming there are around 5 million tuples in the TABLE.

Thank you very much for your time.
Prabj
Re: SQL query help [message #204638 is a reply to message #204637] Tue, 21 November 2006 08:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There should be no real difference between the two methods.

You can specify the exacy size of the numeric field in the CREATE option by using CAST
CREATE TABLE T4 as 
SELECT ALT1.CARD_NBR
      ,ALT2.SCN 
      ,ALT3.SRN 
      ,CAST(ALT1.CR * 2.3456 as NUMBER(18) CR 
FROM   T1 ALT1
      ,T2 ALT2
      ,T3 ALT3
WHERE  ALT1.SC = ALT2.SCO 
AND    ALT1.SR = ALT3.SRO;
Re: SQL query help [message #204661 is a reply to message #204638] Tue, 21 November 2006 10:06 Go to previous messageGo to next message
mailtopjha
Messages: 4
Registered: November 2006
Junior Member
Thank you very much for your reply

I tried the command which you have spcefied but the result is still the same.

CREATE TABLE T4 as
SELECT ALT1.CARD_NBR
,ALT2.SCN
,ALT3.SRN
,CAST(ALT1.CR * 2.3456 as NUMBER(18)) CR
FROM T1 ALT1
,T2 ALT2
,T3 ALT3
WHERE ALT1.SC = ALT2.SCO
AND ALT1.SR = ALT3.SRO;

SQL> CREATE TABLE T4 as
SELECT ALT1.CARD_NBR
,ALT2.SCN
,ALT3.SRN
,CAST(ALT1.CR * 2.3456 as NUMBER(18)) CR
FROM T1 ALT1
,T2 ALT2
,T3 ALT3
WHERE ALT1.SC = ALT2.SCO
AND ALT1.SR = ALT3.SRO;

Table created.

SQL> desc T4;
Name Type
------------ ----------------------------
CARD_NBR VARCHAR2(24)
SCN NUMBER(18)
SRN NUMBER(18)
CR NUMBER

Thanks
Prabj
Re: SQL query help [message #204781 is a reply to message #204661] Wed, 22 November 2006 02:01 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Works for me. What version of Oracle are you using.
SQL> drop table cast_Test;
Table dropped.

SQL> create table cast_test as select lvl,cast(lvl as number(18)) lvl_cast
  2  from (select level lvl from dual connect by level <= 12);

Table created.

SQL> desc cast_test;
 Name                              Null?    Type
 --------------------------------- -------- --------------------
 LVL                                        NUMBER
 LVL_CAST                                   NUMBER(18)
Previous Topic: unable to create a c function to use in plsql
Next Topic: composite primary key
Goto Forum:
  


Current Time: Thu Dec 08 18:28:25 CST 2016

Total time taken to generate the page: 0.12421 seconds