No question at this time
DBA Top 10
1 B. Vroman 14500
2 M. Cadot 14200
3 A. Kavsek 6900
4 T. Boles 6100
5 J. Schnackenberg 5800
6 M. Hidayathullah ... 3600
7 G. Lambregts 1100
7 T. P 1100
7 P. Wisse 1100
10 B. Derous 500
10 R. Wauben 500
10 H. Steijntjes 500
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48226
Total active users1641
Act. users last 24h5
Act. users last hour0
Registered user hits last week344
Registered user hits last month1464
Go up

Help with query
Next thread: Memory Max Target Vs Memory target.
Prev thread: MMON Process

Message Score Author Date
<pre> Hi people. I need your help. SQL> desc sd_t...... Mario Rodriguez Apr 16, 2018, 18:41
Please provide CREATE TABLE statement and I'll s...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Michel Cadot Apr 16, 2018, 19:01
<pre> Here you are Mr.Cadot --> create table s...... Mario Rodriguez Apr 16, 2018, 19:05
<pre>SQL> col stmt format a80 SQL> select 'UPDATE...... Score: 900 PtsScore: 900 PtsScore: 900 PtsScore: 900 PtsScore: 900 Pts Michel Cadot Apr 16, 2018, 19:45
Thank you sir... Mario Rodriguez Apr 16, 2018, 21:09
Michel, You are always a Gem when it comes to t...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Tim Boles Apr 17, 2018, 14:51
Thanks Tim I really appreciate your assessment. ...... Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts Michel Cadot Apr 17, 2018, 16:54

Follow up by mail Click here


Subject: Help with query
Author: Mario Rodriguez, Venezuela
Date: Apr 16, 2018, 18:41, 96 days ago
Os info: any
Oracle info: any
Message:
 Hi people. I need your help.

SQL> desc sd_tcodo
Name Null? Type
----------------------------
CODO_ORDC VARCHAR2(9)
CODO_PROD VARCHAR2(8)
CODO_FELAB DATE
CODO_COSTBS NUMBER(12,2)
CODO_COSTDOL NUMBER(12,2)
CODO_COSTANT NUMBER(12,2)
CODO_COSTADOL NUMBER(12,2)
CODO_COSTPEN NUMBER(12,2)
CODO_COSTPDOL NUMBER(12,2)
CODO_REMP NUMBER(8,2)

By a requirement, I need to divide all the numerical columns by 1000 which match this criteria (
data_precision =12 and data_type='NUMBER' ).

So, I did:
select 'UPDATE '||table_name||' SET '||COLUMN_NAME||' ='||COLUMN_NAME||'/1000;'
from user_tab_columns
where data_precision=12 and data_type='NUMBER'
and table_name in ('SD_TCODO');


And I got:
UPDATE SD_TCODO SET CODO_COSTBS =CODO_COSTBS/1000;
UPDATE SD_TCODO SET CODO_COSTDOL =CODO_COSTDOL/1000;
UPDATE SD_TCODO SET CODO_COSTANT =CODO_COSTANT/1000;
UPDATE SD_TCODO SET CODO_COSTADOL =CODO_COSTADOL/1000;
UPDATE SD_TCODO SET CODO_COSTPEN =CODO_COSTPEN/1000;
UPDATE SD_TCODO SET CODO_COSTPDOL =CODO_COSTPDOL/1000;
But I would like:

UPDATE SD_TCODO SET CODO_COSTBS =CODO_COSTBS/1000,CODO_COSTDOL =CODO_COSTDOL/1000,CODO_COSTANT =CODO_COSTANT/1000...
and so on , all in one simple query , is it possible?

TIA

Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Help with query
Author: Michel Cadot, France
Date: Apr 16, 2018, 19:01, 96 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message:
Please provide CREATE TABLE statement and I'll show you.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Help with query
Author: Mario Rodriguez, Venezuela
Date: Apr 16, 2018, 19:05, 96 days ago
Message:

Here you are Mr.Cadot -->

create table sd_texample (
CODO_ORDC VARCHAR2(9),
CODO_PROD VARCHAR2(8),
CODO_FELAB DATE,
CODO_COSTBS NUMBER(12,2),
CODO_COSTDOL NUMBER(12,2),
CODO_COSTANT NUMBER(12,2),
CODO_COSTADOL NUMBER(12,2),
CODO_COSTPEN NUMBER(12,2),
CODO_COSTPDOL NUMBER(12,2),
CODO_REMP NUMBER(12,2));

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Help with query
Author: Michel Cadot, France
Date: Apr 16, 2018, 19:45, 96 days ago
Score:   Score: 900 PtsScore: 900 PtsScore: 900 PtsScore: 900 PtsScore: 900 Pts
Message:
SQL> col stmt format a80

SQL> select 'UPDATE '||table_name||' SET '||
2 listagg(COLUMN_NAME||'='||COLUMN_NAME||'/1000',', ')
3 within group (order by column_id)||';'
4 stmt
5 from user_tab_columns
6 where data_precision=12 and data_type='NUMBER'
7 and table_name in ('SD_TEXAMPLE')
8 group by table_name
9 order by table_name;
STMT
--------------------------------------------------------------------------------
UPDATE SD_TEXAMPLE SET CODO_COSTBS=CODO_COSTBS/1000, CODO_COSTDOL=CODO_COSTDOL/1
000, CODO_COSTANT=CODO_COSTANT/1000, CODO_COSTADOL=CODO_COSTADOL/1000, CODO_COST
PEN=CODO_COSTPEN/1000, CODO_COSTPDOL=CODO_COSTPDOL/1000, CODO_REMP=CODO_REMP/100
0;


Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Help with query
Author: Mario Rodriguez, Venezuela
Date: Apr 16, 2018, 21:09, 96 days ago
Message: Thank you sir
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Help with query
Author: Tim Boles, United States
Date: Apr 17, 2018, 14:51, 95 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Michel,

You are always a Gem when it comes to these types of queries. I really wish I had my head around these SQL capabilities but it never seems to stick.

Tim
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Help with query
Author: Michel Cadot, France
Date: Apr 17, 2018, 16:54, 95 days ago
Score:   Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts
Message:
Thanks Tim I really appreciate your assessment.
The drawbacks are the 30 years of work on Oracle to acquire these skills and so the age that goes with. :(

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here