Laurent Schneider

Hi Laurent,
I am not sure whether I should be asking this question to you but
I would like to know how can I buy your book in India?
Is there any Indian print planned by your Publisher?

Thanks.

Hello Laurent,

How are you doing?

I am just starting with databases so I think that your website is really good. Is there any better way to contact you?

I hope to hear from you.

Regards,

Ray Silva

Hi,

Can you give me a hint for this?

SQL> conn /as sysoper;
Connected.
SQL> conn /
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

Thank you,
SIA

Hi Laurent,
How can I import data from an excel file into a table?
Any link in your site regarding this would be of great help.

Your link for spooling table values into excel was of great great help.

Hi Laurent,
I wonder if you can help me to solve a problem in sql. I need to be able to display the last (max) entry for each group in the column. Here is the setup code:
——————-
create table test_tb
(scale_code VARCHAR2(4));

insert into test_tb values (’B01′);
insert into test_tb values (’B02′);
insert into test_tb values (’B03′);
insert into test_tb values (’B04′);
insert into test_tb values (’B05′);
insert into test_tb values (’BP1′);
insert into test_tb values (’BP2′);
insert into test_tb values (’BP3′);
insert into test_tb values (’BR3′);
insert into test_tb values (’BU1′);
——————

So, I wonder if I can get:
B05,
BP3,
BR3,
BU1
using “select” in sql.

Thanks,
Eugene

Hi Laurent…

I met today your site.

Your articles are really very goods.

Your OCM certification is evidenced by the excellent quality of the material.

I hope one day to reach the OCM, still studying for this.

With your permission, I would like to add your site as one of the addresses listed on my blog.

Best regards!!!

Wagner Pinheiro

Hi Laurent,

I wud like to move to oracle DBA…I have just gone thru ur site…its really holding fabulous contents…Can I connect u thru mail ids mentioned above…

Best Rgds
Dushyant

Hi Laurent,
I am trying to come up with pivot query for our packaged app audit table.
Any change to any column is written as a new row. I came up with smaller scale example of it.
Here is the test data:
————————————–
create table ep_pivot
(sales_order_prefix varchar2(3),
sales_order_number number(10),
audit_timestamp date,
customer_purchase_order varchar2(25),
ship_address_id number(10),
ship_address_type_id varchar2(4)
);

Insert into ep_pivot
(BUSINESS_UNIT_ID, SALES_ORDER_PREFIX, SALES_ORDER_NUMBER, AUDIT_TIMESTAMP, SHIP_ADDRESS_ID,
SHIP_ADDRESS_TYPE_ID, CUSTOMER_PURCHASE_ORDER)
Values
(’81′, ‘J’, 64850, TO_DATE(’02/27/2001 08:19:12′, ‘MM/DD/YYYY HH24:MI:SS’), 13469,
‘BILL’, ‘3/23 AD BACKUP’);
Insert into ep_pivot
(BUSINESS_UNIT_ID, SALES_ORDER_PREFIX, SALES_ORDER_NUMBER, AUDIT_TIMESTAMP, SHIP_ADDRESS_ID,
SHIP_ADDRESS_TYPE_ID, CUSTOMER_PURCHASE_ORDER)
Values
(’81′, ‘J’, 64850, TO_DATE(’02/27/2001 08:19:39′, ‘MM/DD/YYYY HH24:MI:SS’), 13469,
‘BILL’, ‘645265/669601′);
Insert into ep_pivot
(BUSINESS_UNIT_ID, SALES_ORDER_PREFIX, SALES_ORDER_NUMBER, AUDIT_TIMESTAMP, SHIP_ADDRESS_ID,
SHIP_ADDRESS_TYPE_ID, CUSTOMER_PURCHASE_ORDER)
Values
(’81′, ‘J’, 64850, TO_DATE(’02/27/2001 08:20:38′, ‘MM/DD/YYYY HH24:MI:SS’), 13469,
‘BILL’, ‘645265/669601′);
Insert into ep_pivot
(BUSINESS_UNIT_ID, SALES_ORDER_PREFIX, SALES_ORDER_NUMBER, AUDIT_TIMESTAMP, SHIP_ADDRESS_ID,
SHIP_ADDRESS_TYPE_ID, CUSTOMER_PURCHASE_ORDER)
Values
(’81′, ‘J’, 64850, TO_DATE(’02/27/2001 08:20:53′, ‘MM/DD/YYYY HH24:MI:SS’), 13469,
‘BILL’, ‘645265/669601′);
Insert into ep_pivot
(BUSINESS_UNIT_ID, SALES_ORDER_PREFIX, SALES_ORDER_NUMBER, AUDIT_TIMESTAMP, SHIP_ADDRESS_ID,
SHIP_ADDRESS_TYPE_ID, CUSTOMER_PURCHASE_ORDER)
Values
(’81′, ‘J’, 64850, TO_DATE(’02/27/2001 08:21:18′, ‘MM/DD/YYYY HH24:MI:SS’), 13469,
‘BILL’, ‘645265′);
Insert into ep_pivot
(BUSINESS_UNIT_ID, SALES_ORDER_PREFIX, SALES_ORDER_NUMBER, AUDIT_TIMESTAMP, SHIP_ADDRESS_ID,
SHIP_ADDRESS_TYPE_ID, CUSTOMER_PURCHASE_ORDER)
Values
(’81′, ‘J’, 64850, TO_DATE(’03/14/2001 22:53:31′, ‘MM/DD/YYYY HH24:MI:SS’), 21321,
‘DC’, ‘645265′);
COMMIT;

Here is my query:
———————
Select business_unit_id, sales_order_prefix, sales_order_number, ts,
— PO
max( decode( rn, 1, customer_purchase_order, null )) original_cust_po
,max( decode( rn, 2, customer_purchase_order, null )) changed_cust_po
,max( decode( rn, 3, customer_purchase_order, null )) changed_cust_po
,max( decode( rn, 4, customer_purchase_order, null )) changed_cust_po
,max( decode( rn, 5, customer_purchase_order, null )) changed_cust_po
,max( decode( rn, 6, customer_purchase_order, null )) changed_cust_po
— Address Id
,max( decode( rn, 1, ship_address_id, null )) original_ship_address_id
,max( decode( rn, 2, ship_address_id, null )) changed_ship_address_id
,max( decode( rn, 3, ship_address_id, null )) changed_ship_address_id
,max( decode( rn, 4, ship_address_id, null )) changed_ship_address_id
,max( decode( rn, 5, ship_address_id, null )) changed_ship_address_id
,max( decode( rn, 6, ship_address_id, null )) changed_ship_address_id
— Address Type
,max( decode( rn, 1, ship_address_type_id, null )) original_ship_address_type_id
,max( decode( rn, 2, ship_address_type_id, null )) changed_ship_address_type_id
,max( decode( rn, 3, ship_address_type_id, null )) changed_ship_address_type_id
,max( decode( rn, 4, ship_address_type_id, null )) changed_ship_address_type_id
,max( decode( rn, 5, ship_address_type_id, null )) changed_ship_address_type_id
,max( decode( rn, 6, ship_address_type_id, null )) changed_ship_address_type_id
from (
select business_unit_id,sales_order_prefix,sales_order_number, audit_timestamp ts, customer_purchase_order,ship_address_id,ship_address_type_id,
row_number() over (partition by sales_order_prefix, sales_order_number order by audit_timestamp) rn
from ep_pivot
order by audit_timestamp
)
group by business_unit_id,sales_order_prefix, sales_order_number,ts,ship_address_id,ship_address_type_id

I wonder if it is possible (using sql) to show a value ONLY if it is changed. In my example “customer_purchase_order” didn’t change during 4 inserts.

Thanks,
Eugene

Hi Laurent,
Re. your very interesting entry on sftp, I have a similar brief at the moment, in that I’ve been tasked with altering a PL/SQL package to use sftp(or scp) rather than http://ftp.
Could you point me at an example of how this is achieved in PL/SQL?
All the example I can Google are ftp only.
Thanks in advance
James

Hoppy Bird Day!

Hi Laurent,
I wonder whether you may be able to give me any hint how to approach the problem
I’m struggling with

I have a historical table with 1B+ rows
like

DT C1 C2
07/01/2010 Null 1
07/03/2010 NULL 2
07/04/2010 NULL 4
07/05/2010 4 5
07/06/2010 NULL 6
07/07/2010 1 8 (Sample record beeing processed)
07/09/2010 4 2

For each row in this table, I need to extract following:

DT, C1, C2, and average of C2 for 2 records meeting criteria:
with DT prior to date on the record being processed and not earlier than 5 days before that date,
(so search window is between current - 1day and current- 5 days)
and having C1 NULL.
If there are more then 2 records with C1 = NULL in the window CURRENT -1 and CURRENT -5 days,
I need to use 2 records with dates closest to DT on the record beeing processed to calculate the average.
There may be gaps in the dates.

So for example, if we are processing record of 07/07/2010, the search window for 2 days with C1=NULL is back to 07/02/2010 (5 days).
There are 3 records meeting this criteriia wthin search window, and I need for average calculation, only 2 of them,
having dt closest to the DT on the record beeing processed.

So the output for this particular row should be:
07/07/2010 1 8 5 (as average is (6+4)/2 because rows 07/04/2010 and 07/06/2010 are used to calc average)

I’m working with 10g so I can’t use nth_value() with IGNORE NULLS to address the problem within single statement.
So my question is:

- is there a nice way to formulate single select to extract table columns along with average I need?
- what will be the best approach to process the whole 1B+ rows?

Thank you very much for any hints,

Marek