No question at this time
DBA Top 10
1 B. Vroman 15100
2 M. Cadot 14200
3 A. Kavsek 9300
4 J. Schnackenberg 6100
5 T. Boles 5500
6 M. Hidayathullah ... 5400
7 P. Wisse 2500
8 T. P 900
9 R. Wauben 500
9 H. Steijntjes 500
About
DBA-Village
Download PLATO
The free tool for auditing and tuning your database
Version 55 now available
Sep 02, 2016
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48178
Total active users1670
Act. users last 24h2
Act. users last hour0
Registered user hits last week257
Registered user hits last month1287
Go up

Help with query
Next thread:
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: 700 PtsScore: 700 PtsScore: 700 PtsScore: 700 PtsScore: 700 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...... Tim Boles Apr 17, 2018, 14:51
Thanks Tim I really appreciate your assessment. ...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 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, 2 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, 2 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, 2 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, 2 days ago
Score:   Score: 700 PtsScore: 700 PtsScore: 700 PtsScore: 700 PtsScore: 700 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, 2 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, 1 days ago
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, 1 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 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