How about this ?
A reader, January 02, 2005 - 12:55 pm UTC
Tom,
How do you like this soln. Just a variation of yours.
SELECT NAME,
SUM (DECODE (mnth_bet, 3, sal, 0)) mnt_3,
SUM (DECODE (mnth_bet, 2, sal, 0)) mnt_2,
SUM (DECODE (mnth_bet, 1, sal, 0)) mnt_1
FROM (SELECT t.*,
MONTHS_BETWEEN (TO_DATE (TO_CHAR (SYSDATE, 'YYMM'), 'YYMM'),
TO_DATE (MONTH, 'YYMM')
) mnth_bet
FROM TEST t
WHERE MONTHS_BETWEEN (TO_DATE (TO_CHAR (SYSDATE, 'YYMM'), 'YYMM'),
TO_DATE (MONTH, 'YYMM')
) BETWEEN 1 AND 3)
GROUP BY NAME
Thanx
January 02, 2005 - 7:48 pm UTC
don't like the where clause. you have a function applied to a column, and a function that cannot be indexed, so that could be an issue.
H.A.T.S. (How about this soln) ? followup
A reader, January 02, 2005 - 8:24 pm UTC
So an FBI on TO_DATE (MONTH, 'YYMM') would help ?
But would be too much here.. isnt it ? Your soln looks easier/efficient to implement( a little more to code though).
Thanx always learning new things here.
January 02, 2005 - 10:20 pm UTC
no, it wouldn't in your case.
my predicate:
12 where month in ( to_char(add_months(sysdate,-1), 'yymm' ),
13 to_char(add_months(sysdate,-2), 'yymm' ),
14 to_char(add_months(sysdate,-3), 'yymm' ) )
needs just a normal index on month.
your predicate:
WHERE MONTHS_BETWEEN (TO_DATE (TO_CHAR (SYSDATE, 'YYMM'), 'YYMM'),
TO_DATE (MONTH, 'YYMM')
) BETWEEN 1 AND 3)
is not indexable at all. There is no index you can create that could be used
you would have to index:
MONTHS_BETWEEN (TO_DATE (TO_CHAR (SYSDATE, 'YYMM'), 'YYMM'),
TO_DATE (MONTH, 'YYMM')
but that involves SYSDATE and SYSDATE is a "here and now" thing -- you cannot index it!
H.A.T.S. : Combo
A reader, January 02, 2005 - 10:20 pm UTC
SELECT NAME,
SUM (DECODE (mnth_bet, 3, sal, 0)) mnt_3,
SUM (DECODE (mnth_bet, 2, sal, 0)) mnt_2,
SUM (DECODE (mnth_bet, 1, sal, 0)) mnt_1
FROM (SELECT t.*,
MONTHS_BETWEEN (TO_DATE (TO_CHAR (SYSDATE, 'YYMM'), 'YYMM'),
TO_DATE (MONTH, 'YYMM')
) mnth_bet
FROM TEST t
WHERE
month in ( to_char(add_months(sysdate,-1), 'yymm' ),
to_char(add_months(sysdate,-2), 'yymm' ),
to_char(add_months(sysdate,-3), 'yymm' ) )
GROUP BY NAME.
Is it Better now ?
Thanx
January 02, 2005 - 11:00 pm UTC
how about just
trunc(months_between(sysdate,to_date(month,'yymm')))
or
months_between(trunc(sysdate,'mm'),to_date(month,'yymm'))
but yes, that looks fine -- i toyed with the idea of an inline view for a while but thought the decode as is was good enough...
that yymm scares the $#@ out of me at the end of the day. everytime I see YY I just want to say "didn't we learn something..."
H.A.T.S : Excellant
A reader, January 02, 2005 - 11:26 pm UTC
Thanx. Its Always pleasing to question/reason out any solution to learn more.
Excellent Piece of code
Bhagwan, January 03, 2005 - 12:04 am UTC
Cool way to approach a problem through ORACLE!
slow execution when using trunc(edate,'MM')
TS, January 10, 2005 - 6:08 pm UTC
Hi Tom,
I am trying to run a query against a table
that has approximately a million rows.
the query looks something like this:-
select oid from ORDER_ARCHIVE
where TRUNC(enttime,'MM')=trunc(sysdate,'MM')
group by oid;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=8 Bytes=120)
1 0 SORT (GROUP BY) (Cost=3 Card=8 Bytes=120)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'ORDER_ARCHIVE' (Cost=2
Card=8 Bytes=120)
3 2 INDEX (RANGE SCAN) OF 'FNIDX_ORDARCHIVE_ENTTIMEMM' (NO
N-UNIQUE) (Cost=1 Card=3)
I created a function index for the column
trunc(enttime,'MM') and the execution plan
shows the index being used. Inspite of that
the query is very SLOW. I tried both scenarios,
with and without a function index and the
query takes about 26 secs to execute.
If I try the same query with a where condition like
trunc(enttime)=trunc(sysdate)
the query is very fast(1 second). I know that trunc(enttime) and trunc(enttime,'MM') have different meaning, but I am wondering why the latter function is slow.
Is there a way to Rewrite this query?
Any help would be appreciated.
Thanks in Advance,
TS
January 10, 2005 - 6:17 pm UTC
well, I would not have created an FBI, i would have used any "normal" index on enttime and queried:
where enttime >= trunc(sysdate,'mm')
and enttime < add_months(trunc(sysdate,'mm'),1)
but -- lets see a tkprof here. It "thinks" it is going to get 3 rows -- if so, that would be lightspeed via an index. We need to see how many rows it truly got and then figure out "why 3 is so far off" if it was.
slow execution when using trunc(enttime,'MM')
TS, January 11, 2005 - 3:42 pm UTC
Tom,
Here's the tkprof output for the query:-
select oid from order_archive
where trunc(enttime,:"SYS_B_0")=trunc(sysdate,:"SYS_B_1")
group by oid
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.01 0 0 0 0
Fetch 465 13.48 14.68 17003 17014 0 6946
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 467 13.49 14.70 17003 17014 0 6946
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 22
Rows Row Source Operation
------- ---------------------------------------------------
6946 SORT GROUP BY
6946 TABLE ACCESS FULL ORDER_ARCHIVE
Hmm. tkprof shows a full table scan on the table
whereas when I do a
set autotrace traceonly explain
this is what I see
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=248 Card=41290 Bytes
=619350)
1 0 SORT (GROUP BY) (Cost=248 Card=41290 Bytes=619350)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'ORDER_ARCHIVE' (Cost=4
Card=41290 Bytes=619350)
3 2 INDEX (RANGE SCAN) OF 'FNIDX_ORDARCHIVE_ENTTIMEMM' (NO
N-UNIQUE) (Cost=2 Card=41290)
Also, I tried to modify the where condition with:
where enttime >= trunc(sysdate,'MM')
and enttime < add_months(trunc(sysdate,'MM'),1)
even this takes the same amount of time as the original
where clause.
Thanks,
TS
January 11, 2005 - 4:06 pm UTC
autotrace lies, ESPECIALLY when cursor sharing != exact.
and you KILLED the ability to use the index!!!! curses on cursor_sharing.
autotrace doesn't "autobind", hence, it would see an index as being OK.
but the REAL query is:
select oid from order_archive
where trunc(enttime,:"SYS_B_0")=trunc(sysdate,:"SYS_B_1")
group by oid
and the optimizer says "hmm, trunc(database_column, <to something, no clue what>)"
so no index for you.
and when you did the between, you did sort of have an index in place for it to you correct? do you have the tkprof of that
(and ask yourself why you have cursor sharing not on EXACT, do you have some buggy apps that aren't binding -- and are they priority one on the list of things to get done?)
SQL Query
a reader, January 12, 2005 - 2:42 am UTC
Tom,
create table tab1(acc_no number (10), date_opened date);
insert into tab1 values (100, sysdate -70);
insert into tab1 values (101, sysdate -65);
insert into tab1 values (102, sysdate -60);
insert into tab1 values (103, sysdate -40);
insert into tab1 values (104, sysdate -30);
insert into tab1 values (105, sysdate -10);
SQL> select * from tab1;
ACC_NO DATE_OPENED
---------- ---------------
100 03-NOV-04
101 08-NOV-04
102 13-NOV-04
103 03-DEC-04
104 13-DEC-04
105 02-JAN-05
I would like to get o/p something like the below. When the query runs in jan'05 it will give all accounts that are opened in the month of Nov'04, similarly when run in Feb'05 it will o/p all accounts that are opened in Dec and so on. Could you please comment whether the below query is right for that purpose.
1* select * from tab1 where to_char(date_opened, 'YYYYMM') = to_char(add_months(sysdate, -2), 'yyyymm')
SQL> /
ACC_NO DATE_OPENED
---------- ---------------
100 03-NOV-04
101 08-NOV-04
102 13-NOV-04
January 12, 2005 - 8:30 am UTC
where date_opened >= add_months(trunc(sysdate,'mm'),-2)
and date_opened < add_months(trunc(sysdate,'mm'),-1)
would be "more index friendly" and avoid having to apply a function to each and every single row. the add_months(....) are "constants" evaluated at the beginning of the query and we have no need for the to_char().
anytime you see yourself applying a function to a database column ask yourself "is there another way"
slow execution when using trunc(enttime,'MM')
TS, January 12, 2005 - 5:30 pm UTC
Tom,
>and when you did the between, you did sort of have an >index in place for it to
>you correct?
My bad. I created the index for enttime column on ORDER_ARCHIVE table on development
and not production.
Anyway,I created the index on production and ran the rewritten query with
where enttime >= trunc(sysdate,'mm')
and enttime < add_months(trunc(sysdate,'mm'),1)
its very fast now. Thanks for all the help.
Also, before creating the index, I tried
alter session set cursor_sharing=EXACT;
and tried running the original query
and it was very very fast.
Currently, the database has CURSOR_SHARING=SIMILAR
Should the database be set to run with CURSOR_SHARING=EXACT ?
Any advice from you would be greatly appreciated.
January 12, 2005 - 6:12 pm UTC
you should ask whomever set cursor_sharing "why, what was the reason and can we test it the other way, at exact -- which is the good setting"
(keyword = TEST)
hi tom
hameed, January 12, 2005 - 6:26 pm UTC
hi tom
i have zibra printer and i have program by oracle 8i and developer 6i
how i do barcode in my program for stock's item
and printing on zibra printer
thanks tom
January 12, 2005 - 7:07 pm UTC
good question.
not.
a) seems to not have a thing at all to do with the original question
b) I've never ever heard of "zibra"
c) a barcode scanner is just an input device, like a keyboard, we have nor do we need anything "fancy" or special to work with it. You plug it in and it "types".
One more help required for SQL union
Parag Jayant Patankar, January 13, 2005 - 6:35 am UTC
Hi Tom,
I have following table
create table parag as select * from user_indexes where 1=2;
I am executing following query
select
rtrim(
'111111'||' 02 '||rtrim(tn)||'-REC.'||chr(10)||
'111112'||' 03'||rtrim(tn)||'-TCB.'||chr(10)||
'111113'||' 15 '||rtrim(tn)||'-RET PIC S9(009) COMP.'||chr(10)||
'111114'||' 15 '||rtrim(tn)||'-STREAM PIC X(008) VALUE SPACES'||chr(10)||
'111115'||' 03'||rtrim(tn)||'-REC.'||chr(10)||
'111116'||' 05'||rtrim(tn)||'-KEY.' )
from
(
select
tname tn
from tab
where tname=upper('PARAG')
)
union
select
'123456'||' '||'15 '||rtrim(tn)||'-'||rpad(rtrim(cn),10,' ')||' '||'PIC '||
decode(dtype, 'DATE', ' X(008)', 'VARCHAR2', ' X('||lpad(len,3,0)||')', 'CHAR', ' X('||lpad(len,3,0)||')',
'NUMBER', decode(nvl(scl,0), 0, 'S9('||lpad(nvl(prec,0),3,0)||') COMP-3 VALUE ZEROES.',
'S9('||lpad(nvl(prec,0)-nvl(scl,0),3,0)||')V9('||lpad(scl,2,'0')||') COMP-3 VALUE ZEROES.'))
||' '||decode(dtype, 'CHAR', 'VALUE SPACES.', 'VARCHAR2', 'VALUE SPACES.')
from
(
select
table_name tn,
column_name cn,
rtrim(substr(data_type,1,10)) dtype,
DATA_LENGTH len,
DATA_PRECISION prec,
data_scale scl,
column_id ci
from cols
where table_name=upper('PARAG')
order by ci
)
/
It is genrating following output
111111 02 PARAG-REC.
111112 03PARAG-TCB.
111113 15 PARAG-RET PIC S9(009) COMP.
111114 15 PARAG-STREAM PIC X(008) VALUE SPACES
111115 03PARAG-REC.
111116 05PARAG-KEY.
<==== ( ******************** )
123456 15 PARAG-AVG_DATA_B PIC S9(000) COMP-3 VALUE ZEROES.
123456 15 PARAG-AVG_LEAF_B PIC S9(000) COMP-3 VALUE ZEROES.
123456 15 PARAG-BLEVEL PIC S9(000) COMP-3 VALUE ZEROES.
123456 15 PARAG-BUFFER_POO PIC X(007) VALUE SPACES.
123456 15 PARAG-CLUSTERING PIC S9(000) COMP-3 VALUE ZEROES.
123456 15 PARAG-COMPRESSIO PIC X(008) VALUE SPACES.
123456 15 PARAG-DEGREE PIC X(040) VALUE SPACES.
123456 15 PARAG-DISTINCT_K PIC S9(000) COMP-3 VALUE ZEROES.
123456 15 PARAG-DOMIDX_OPS PIC X(006) VALUE SPACES.
123456 15 PARAG-DOMIDX_STA PIC X(012) VALUE SPACES.
123456 15 PARAG-DURATION PIC X(015) VALUE SPACES.
123456 15 PARAG-FREELISTS PIC S9(000) COMP-3 VALUE ZEROES.
123456 15 PARAG-FREELIST_G PIC S9(000) COMP-3 VALUE ZEROES.
123456 15 PARAG-FUNCIDX_ST PIC X(008) VALUE SPACES.
123456 15 PARAG-GENERATED PIC X(001) VALUE SPACES.
123456 15 PARAG-GLOBAL_STA PIC X(003) VALUE SPACES.
123456 15 PARAG-INCLUDE_CO PIC S9(000) COMP-3 VALUE ZEROES.
123456 15 PARAG-INDEX_NAME PIC X(030) VALUE SPACES.
123456 15 PARAG-INDEX_TYPE PIC X(027) VALUE SPACES.
123456 15 PARAG-INITIAL_EX PIC S9(000) COMP-3 VALUE ZEROES.
123456 15 PARAG-INI_TRANS PIC S9(000) COMP-3 VALUE ZEROES.
123456 15 PARAG-INSTANCES PIC X(040) VALUE SPACES.
123456 15 PARAG-ITYP_NAME PIC X(030) VALUE SPACES.
123456 15 PARAG-ITYP_OWNER PIC X(030) VALUE SPACES.
123456 15 PARAG-LAST_ANALY PIC X(008)
123456 15 PARAG-LEAF_BLOCK PIC S9(000) COMP-3 VALUE ZEROES.
123456 15 PARAG-LOGGING PIC X(003) VALUE SPACES.
123456 15 PARAG-MAX_EXTENT PIC S9(000) COMP-3 VALUE ZEROES.
123456 15 PARAG-MAX_TRANS PIC S9(000) COMP-3 VALUE ZEROES.
123456 15 PARAG-MIN_EXTENT PIC S9(000) COMP-3 VALUE ZEROES.
123456 15 PARAG-NEXT_EXTEN PIC S9(000) COMP-3 VALUE ZEROES.
123456 15 PARAG-NUM_ROWS PIC S9(000) COMP-3 VALUE ZEROES.
123456 15 PARAG-PARAMETERS PIC X(100) VALUE SPACES.
123456 15 PARAG-PARTITIONE PIC X(003) VALUE SPACES.
123456 15 PARAG-PCT_DIRECT PIC S9(000) COMP-3 VALUE ZEROES.
123456 15 PARAG-PCT_FREE PIC S9(000) COMP-3 VALUE ZEROES.
123456 15 PARAG-PCT_INCREA PIC S9(000) COMP-3 VALUE ZEROES.
123456 15 PARAG-PCT_THRESH PIC S9(000) COMP-3 VALUE ZEROES.
123456 15 PARAG-PREFIX_LEN PIC S9(000) COMP-3 VALUE ZEROES.
123456 15 PARAG-SAMPLE_SIZ PIC S9(000) COMP-3 VALUE ZEROES.
123456 15 PARAG-SECONDARY PIC X(001) VALUE SPACES.
123456 15 PARAG-STATUS PIC X(008) VALUE SPACES.
123456 15 PARAG-TABLESPACE PIC X(030) VALUE SPACES.
123456 15 PARAG-TABLE_NAME PIC X(030) VALUE SPACES.
123456 15 PARAG-TABLE_OWNE PIC X(030) VALUE SPACES.
123456 15 PARAG-TABLE_TYPE PIC X(011) VALUE SPACES.
123456 15 PARAG-TEMPORARY PIC X(001) VALUE SPACES.
123456 15 PARAG-UNIQUENESS PIC X(009) VALUE SPACES.
123456 15 PARAG-USER_STATS PIC X(003) VALUE SPACES.
50 rows selected.
Why is it creating blank line between ( indicated by ******* ) ? I have tried this sql by substr, rtrim ..etc but nothing found working on this.
Kindly guide me as usual.
regards & thanks
pjp
January 13, 2005 - 9:01 am UTC
so, how does this relate to the original question exactly?
try setting your linesize much larger, looks like a wide column that spilled.
Develope quota system for new questions according to timezones / region wise
Parag Jayant Patankar, January 14, 2005 - 5:44 am UTC
Hi Tom,
Sorry to ask not relevant question in line with orginal question in this thread.
I just put question by title as "SQL Query", I think it is in line with title.
But I think we people from Asia are having practially less chances to ask you new question because you must have an automated process to take new questions and when you answer certain no of questions we can again get a thread for a new question automatically, but due to "time difference" ( timezones ) between US ( I do not know from which part of US you are ) and Asia we are hardly getting any chance to ask you questions. When you normally answers the questions it is late evening / night here.
so we sometime put questions in other threads also when we are very egar to get an answer from you when our other sources did not work.
So I request you enhance your system to have timzone wise questions quota so give us fair chance to learn from your expertise skills. I am sure you will develope such system. ( after developing the system kindly do not forget to explain to us )
Secondly I have increased the linesize as per your suggestions but still it is not working i.e. blank line again coming between two query outputs
for e.g.
15:41:02 SQL> create table parag as select * from user_indexes where 1=2;
Table created.
15:44:01 SQL> show linesize
linesize 32767
15:44:12 SQL> l
select
'111111'||' 02 '||tn||'-REC.'||chr(10)||
'111112'||' 03 '||tn||'-TCB.'||chr(10)||
'111113'||' 15 '||tn||'-RET PIC S9(009) COMP.'||chr(10)||
'111114'||' 15 '||tn||'-STREAM PIC X(008) VALUE SPACES.'||chr(10)||
'111115'||' 03 '||tn||'-REC.'||chr(10)||
'111116'||' 05 '||tn||'-KEY.'
from
(
select
rtrim(tname) tn
from tab
where tname=upper('PARAG')
)
union all
select 'Y' from dual
15:44:17 SQL> /
111111 02 PARAG-REC.
111112 03 PARAG-TCB.
111113 15 PARAG-RET PIC S9(009) COMP.
111114 15 PARAG-STREAM PIC X(008) VALUE SPACES.
111115 03 PARAG-REC.
111116 05 PARAG-KEY.
<========== ( blank line )
Y
regards & thanks
pjp
January 14, 2005 - 8:14 am UTC
I've actually run reports showing (by ip address) that the distribution of questions worldwide is very "fair"
ops$tkyte@ORA9IR2> select rownum, txt
2 from (
3 select
4 '111111'||' 02 '||tn||'-REC.'||chr(10)||
5 '111112'||' 03 '||tn||'-TCB.'||chr(10)||
6 '111113'||' 15 '||tn||'-RET PIC S9(009) COMP.'||chr(10)||
7 '111114'||' 15 '||tn||'-STREAM PIC X(008) VALUE SPACES.'||chr(10)||
8 '111115'||' 03 '||tn||'-REC.'||chr(10)||
9 '111116'||' 05 '||tn||'-KEY.' TXT
10 from
11 (
12 select rtrim(tname) tn
13 from tab
14 where tname=upper('EMP')
15 )
16 union all
17 select 'Y' from dual
18 )
19 /
ROWNUM TXT
---------- ----------------------------------------------------------------------------------------------------
1 111111 02 EMP-REC.
111112 03 EMP-TCB.
111113 15 EMP-RET PIC S9(009) COMP.
111114 15 EMP-STREAM PIC X(008) VALUE SPACES.
111115 03 EMP-REC.
111116 05 EMP-KEY.
2 Y
you are going to want to use DBMS_OUTPUT and PLSQL (use set serveroutput on size 1000000 format word_wrapped to preserve your white space)
the use of chr(10) in the text is going to drive the simple reporting tool that is SQLLDR nuts.
Also -- do not rely on union all returning result sets in ANY ORDER, you must use an ORDER BY to get data in some order.
(set recsep off will fix the issue, but I'd suggest plsql for this AND use order by -- using ORDER BY is mandatory regardless)
zibra printer
hameed, January 25, 2005 - 7:53 pm UTC
January 25, 2005 - 7:59 pm UTC
no clue what you mean.
Giraffe Printer (competitor of zibra printer)
A reader, January 25, 2005 - 9:01 pm UTC
What is this zibra printer thing ?
Is this some kind of silent Advertisement ?
You did not even say what your problem is ?
Tom is no psychic ?
hi tom
hameed, January 26, 2005 - 5:23 pm UTC
hi tom
i have program by oracle 8i and developer 6i for stocks
and i have zibra printer
</code>
http://www.zebraexpert.com/xml_printers.html <code>
found more item in this stock
i need printer barcode for all stock items
-----------------
for example
|||||||||
22312
-----------------
please help me for this my problem
January 27, 2005 - 7:42 am UTC
I guess I would contact the printer people and ask them for the method.
zibra printer
hameed, January 26, 2005 - 5:27 pm UTC
the zibra printer user print lable for stock items
for example
------------------
||||||||
123566
|||||||
985686
||||||
12536
||||||
263359
----------------
SQL Query
A reader, January 27, 2005 - 11:06 am UTC
Hi Tom,
I've tried to find a similar solution to the problem I have but I couldn't, so I thought I'd post it here:
I have 2 tables that look like below:
A.col1 A.col2 A.col3
------ ------ ------
1.1 1.2 1.3
2.1 2.2 2.3
3.1 3.2 3.3
and
B.col1 B.col2 B.col3
------ ------ ------
2.1 2.2 2.3
I'd like the result to look like:
A.col1 A.col2 A.col3 IN table B
------ ------ ------ ----------
1.1 1.2 1.3 N
2.1 2.2 2.3 Y
3.1 3.2 3.3 N
Please advise how to have a select statement that produces the above output.
Thank you very much.
January 27, 2005 - 11:13 am UTC
select a.*, decode(b.col1,null,'N','Y') in_b
from a, b
where a.col1 = b.col1(+)
and a.col2 = b.col2(+)
and a.col3 = b.col3(+);
(if col1/col2/col3 in b are unique)
select a.*,
nvl(select 'Y' from b where b.col1=a.col1 and ... and rownum = 1),'N')
from a;
is another way.
select a.*, 'N' from a minus select b.*, 'N' from b
UNION ALL
select a.*, 'Y' from a intersect select b.*, 'Y' from b;
could be another alternative....
select a.*, 'N' from a where (col1, col2, col3) not in ( select * from b)
union all
select a.*, 'Y' from a where (col1, col2, col3) in ( select * from b );
....
SQL Query
A reader, January 27, 2005 - 3:16 pm UTC
Thank you very much Tom. All work really well except the minus/intersect pair. It only gives me the one row that exists in table B.
Thanks again for your help.
January 27, 2005 - 3:30 pm UTC
sorry, missed () for order of set operations:
ops$tkyte@ORA9IR2> select a.*, decode(b.col1,null,'N','Y') in_b
2 from a, b
3 where a.col1 = b.col1(+)
4 and a.col2 = b.col2(+)
5 and a.col3 = b.col3(+);
COL1 COL2 COL3 I
---------- ---------- ---------- -
1 2 3 N
4 5 6 Y
7 8 9 N
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> (select a.*, 'N' from a minus select b.*, 'N' from b)
2 UNION ALL
3 (select a.*, 'Y' from a intersect select b.*, 'Y' from b);
COL1 COL2 COL3 '
---------- ---------- ---------- -
1 2 3 N
7 8 9 N
4 5 6 Y
SQL Query
A reader, January 27, 2005 - 4:26 pm UTC
That works now. Your help is really appreciated.
print massage
hameed, February 01, 2005 - 3:30 am UTC
hi tom
i need to print to printer some massages from form developer 6i with out report...
how?
A reader
abarna, February 25, 2005 - 8:21 am UTC
dear tom
i want to form a query like employee number whose commision is null comm_emp,
employee number whose commision is not null non_comm_emp
FROM
emp;
the output should be as below
comm_emp non_comm_emp
-------------- ---------------------
3315 3317
3318 3319
3320 3321
but there is one condition there should not be any null values in between the records.
February 25, 2005 - 5:21 pm UTC
and what if there are 100 comm_emp's and 2 non_comm_emp's
how do they "go together"??
A reader
abarna, February 25, 2005 - 8:23 am UTC
Dear Tom
i want to form a query like employee number whose commision is null comm_emp,
employee number whose commision is not null non_comm_emp
FROM
emp;
the output should be as below
comm_emp non_comm_emp
-------------- ---------------------
3315 3317
3318 3319
3320 3321
but there is one condition there should not be any null values in between the records.
Thanks
A reader
abarna, February 27, 2005 - 10:51 pm UTC
Let us assume there are equal records for employee's comm null and not null then what will be the query for that
February 28, 2005 - 7:05 am UTC
seems to be a "naive" assumption isn't it? is this a real world problem? What are you really trying to do (i can write that query -- i have written it, rownum and two inline views joined -- but I'm not sure what the goal is?)
A reader
abarna, February 28, 2005 - 11:17 pm UTC
This is not the real problem.i got this question thru some site.if u acheived this query for equal number of records for which comm is null and not null.kindly tell
Thanks
March 01, 2005 - 8:15 am UTC
join
select ...
from ( select rownum r, x from t where comm is null ) A,
( select rownum r, x from t where comm is not null ) B
where a.r = b.r
is one approach, using a full outer join would solve the "different sizes of A and B" issue
hardware
hameed, March 16, 2005 - 5:09 pm UTC
hi tom
i want to send some data to port com1,com2,com3,LPT
plase help me............
March 17, 2005 - 8:27 am UTC
at a loss for words.
hameed, March 17, 2005 - 5:51 pm UTC
sorry ..
please .
what functions which send data to ports com1,com2,com3,LPT
sorry..
sorry..
March 17, 2005 - 6:06 pm UTC
askbill@microsoft.com?
this is a windows specific programming question -- No idea what you use to peek and poke com ports on windows these days are.
Now, under MS-DOS ;)
sorry -- not really a "sql query" question and not really something I know anything about.
OK
Raju, March 19, 2005 - 11:54 am UTC
Hi Tom,
What is the escape sequence for single blank space?
March 19, 2005 - 12:57 pm UTC
huh?
??
A reader, March 20, 2005 - 12:58 am UTC
Why Huh??
March 20, 2005 - 7:33 am UTC
apparently the words
"What is the escape sequence for single blank space?"
in that sequence have utterly no meaning or context for me....
why would a single blank space need an "escape"
where would this escape be used
so, huh?
OK
Raju, March 20, 2005 - 11:25 am UTC
Hi,
Just a sense of curiosity and nothing else.
One of our team members asked.
SQL > select 'Hello' || ' ' ||'World' from dual;
He asked "Can that quotes be replaced with any chr(???) functions?"
Since I didn't know,I asked you.
March 20, 2005 - 6:39 pm UTC
not if you want to use character string literals - quotes are the special character you use around character string literals. Period, the only character.
Now, if you want to concatenate a space in between two strings, you can:
select 'hello' || chr(32) || 'world' from dual;
OK
Emily, March 21, 2005 - 12:15 pm UTC
Hi Tom,
Regarding that empno with comm both null and not null,
Your query works fine.
How to use a full join here?
Could you please provide the query?
You say about different sizes,What do you mean by that?
SQL> create table empl(eno number,comm number default null);
Table created.
SQL> insert into empl(eno) values(2001)
2 /
1 row created.
SQL> insert into empl values(2002,300)
2 /
1 row created.
SQL> insert into empl(eno) values(2003)
2 /
1 row created.
SQL> insert into empl values(2004,400)
2 /
1 row created.
SQL> commit;
Commit complete.
SQL> select * from empl
2 /
ENO COMM
---------- ----------
2001
2002 300
2003
2004 400
SQL> select a.eno,b.eno
2 from (select rownum r,eno from empl where comm is null)a,
3 (select rownum r,eno from empl where comm is not null)b
4 where a.r = b.r
5 /
ENO ENO
---------- ----------
2001 2002
2003 2004
OK
Kumar, March 22, 2005 - 9:02 pm UTC
Hi Tom,
How a full join can be used with the above data?
March 23, 2005 - 1:12 am UTC
big page, which "above data"
OK
Kumar, March 23, 2005 - 12:02 pm UTC
Hi,
I mean from the data posted by Emily from UK
i.e) the last one.
March 23, 2005 - 6:13 pm UTC
now define "full join"
A reader, March 24, 2005 - 2:48 pm UTC
Hi Tom,
Below is a pseudocode of what I'm trying to accomplish. The main thing is I'd like to be able to construct a query based on a series of questions (prompts), execute that query, and loop thru the result set of that query. As I'm fairly new to PL/SQL, I do not know how to do that. Please help. Thanks.
accept aValue prompt 'Value: ';
declare
ll_value number;
ls_sql varchar2(2000);
begin
ll_value := to_number('&&aValue');
if ll_value is null then
ls_sql := 'select * from tableA';
else
ls_sql := 'select * from tableA where col1 = ll_value';
end if;
result_set := execute ls_sql;
for n in result_set loop
...
end loop;
end;
/
March 24, 2005 - 4:04 pm UTC
do you need to loop thru and process them or just print them out?
A reader, March 24, 2005 - 5:06 pm UTC
I need to loop thru the result set, set up some values base on what's being returned, insert into another table and then print out what I inserted into that table.
Thanks.
March 24, 2005 - 5:29 pm UTC
it could look like:
ops$tkyte@ORA9IR2> @test
ops$tkyte@ORA9IR2> accept avalue prompt 'enter a value: '
enter a value: 1
ops$tkyte@ORA9IR2> declare
2 l_cursor sys_refcursor;
3 l_rec all_users%rowtype;
4 begin
5 if (&aValue = 1)
6 then
7 open l_cursor for select * from all_users where rownum <= 5;
8 else
9 open l_cursor for select * from all_users where user_id = UID;
10 end if;
11 loop
12 fetch l_cursor into l_rec;
13 exit when l_cursor%notfound;
14 dbms_output.put_line( l_rec.username );
15 end loop;
16 close l_cursor;
17 end;
18 /
old 5: if (&aValue = 1)
new 5: if (1 = 1)
SYS
SYSTEM
OUTLN
DBSNMP
WMSYS
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @test
ops$tkyte@ORA9IR2> accept avalue prompt 'enter a value: '
enter a value: 2
ops$tkyte@ORA9IR2> declare
2 l_cursor sys_refcursor;
3 l_rec all_users%rowtype;
4 begin
5 if (&aValue = 1)
6 then
7 open l_cursor for select * from all_users where rownum <= 5;
8 else
9 open l_cursor for select * from all_users where user_id = UID;
10 end if;
11 loop
12 fetch l_cursor into l_rec;
13 exit when l_cursor%notfound;
14 dbms_output.put_line( l_rec.username );
15 end loop;
16 close l_cursor;
17 end;
18 /
old 5: if (&aValue = 1)
new 5: if (2 = 1)
OPS$TKYTE
PL/SQL procedure successfully completed.
A reader, March 24, 2005 - 5:44 pm UTC
Thanks for a quick response.
Can I not use a variable for the select statement? Something like:
if (&aValue = 1) then
ls_sql := 'select * from all_users where rownum <= 5';
else
ls_sql := 'select * from all_users where user_id = UID';
end if;
open l_cursor for ls_sql;
The reason for this is because I have to build the select statement base on a lot of prompts, a lot of different conditions and scanarios using different columns and values.
Thanks.
March 24, 2005 - 6:25 pm UTC
sure, you can do that as well.
A reader, March 24, 2005 - 10:50 pm UTC
Thank you very much. Your help is greatly appreciated.
A reader, April 21, 2005 - 3:39 pm UTC
Hi Tom,
How do I go about finding where in the entired database all columns that has a value I want to look for. For example, I want a list of all columns and their tables in the entired database that has the value 'UNKNOWN' in them.
Thank you very much.
Problem Query
A reader, July 26, 2006 - 3:50 pm UTC
Hi Tom,
I have this query as follows with the expalin plan.
It is taking like 25 mins to execute. Can you suggest a way to rewrite it or what changes to implement to make it go faster.
SELECT
A.EMPLID, B.NAME, C.COUNTRY, C.ADDRESS1, C.ADDRESS2,
C.ADDRESS3, C.ADDRESS4, C.CITY, C.POSTAL,
MAX( DECODE( D.PHONE_TYPE,'CELL', D.PHONE,null) ),
MAX( DECODE( D.PHONE_TYPE,'HOME', D.PHONE,null) ),
MAX( DECODE( D.PHONE_TYPE,'BUSN', D.PHONE,null) ),
MAX( DECODE( D.PHONE_TYPE,'CEPE', D.PHONE,null) ),
A.JOBCODE, A.EMPL_TYPE, A.REG_TEMP
FROM
MONTECLA.PS_JOB A,
MONTECLA.PS_EMPLMT_SRCH_QRY A1,
MONTECLA.PS_NAMES B,
MONTECLA.PS_PERS_SRCH_QRY B1,
MONTECLA.PS_ADDRESSES C,
MONTECLA.PS_PERSONAL_PHONE D
WHERE
A.EMPLID = A1.EMPLID
AND A.EMPL_RCD = A1.EMPL_RCD
AND A1.ROWSECCLASS = 'DPGLOBAL'
AND B.EMPLID = B1.EMPLID
AND B1.ROWSECCLASS = 'DPGLOBAL'
AND C.EMPLID = B1.EMPLID
AND D.EMPLID = B1.EMPLID
AND ( A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM MONTECLA.PS_JOB A_ED
WHERE A.EMPLID = A_ED.EMPLID
AND A.EMPL_RCD = A_ED.EMPL_RCD
AND A_ED.EFFDT <= SYSDATE)
AND A.EFFSEQ =
(SELECT MAX(A_ES.EFFSEQ) FROM MONTECLA.PS_JOB A_ES
WHERE A.EMPLID = A_ES.EMPLID
AND A.EMPL_RCD = A_ES.EMPL_RCD
AND A.EFFDT = A_ES.EFFDT)
AND A.EMPL_STATUS = 'A'
AND A.BUSINESS_UNIT = 'SINGH'
AND A.EMPLID = B.EMPLID
AND B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM MONTECLA.PS_NAMES B_ED
WHERE B.EMPLID = B_ED.EMPLID
AND B.NAME_TYPE = B_ED.NAME_TYPE
AND B_ED.EFFDT <= SYSDATE)
AND B.NAME_TYPE = 'PRI'
AND A.EMPLID = C.EMPLID
AND C.EFFDT =
(SELECT MAX(C_ED.EFFDT) FROM MONTECLA.PS_ADDRESSES C_ED
WHERE C.EMPLID = C_ED.EMPLID
AND C.ADDRESS_TYPE = C_ED.ADDRESS_TYPE
AND C_ED.EFFDT <= SYSDATE)
AND A.EMPLID = D.EMPLID
AND C.ADDRESS_TYPE = 'HOME' )
GROUP BY A.EMPLID, B.NAME, C.COUNTRY, C.ADDRESS1, C.ADDRESS2, C.ADDRESS3, C.ADDRESS4,
C.CITY, C.POSTAL, A.JOBCODE, A.EMPL_TYPE, A.REG_TEMP
ORDER BY 1 ;
Explain Plan:
-------------
ID PARENT_ID Query Plan
---- ---------- ------------------------------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=319 Card=1 Bytes=512)
1 0 SORT GROUP BY (Cost=310 Card=1 Bytes=512)
2 1 FILTER
75 2 FILTER
76 75 TABLE ACCESS BY INDEX ROWID PS_SCRTY_TBL_DEPT (Cost=3 Card=1 Bytes=26)
77 76 INDEX RANGE SCAN PS_SCRTY_TBL_DEPT (Cost=2 Card=1)
60 2 SORT AGGREGATE
61 60 FILTER
62 61 INDEX RANGE SCAN PS_NAMES (Cost=3 Card=1 Bytes=21)
63 61 SORT AGGREGATE
64 63 FIRST ROW (Cost=3 Card=1 Bytes=21)
65 64 INDEX RANGE SCAN (MIN/MAX) PS_NAMES (Cost=3 Card=323137)
66 2 SORT AGGREGATE
67 66 FIRST ROW (Cost=3 Card=1 Bytes=20)
68 67 INDEX RANGE SCAN (MIN/MAX) PSAJOB (Cost=3 Card=2438828)
69 2 SORT AGGREGATE
70 69 FIRST ROW (Cost=3 Card=1 Bytes=23)
71 70 INDEX RANGE SCAN (MIN/MAX) PSAJOB (Cost=3 Card=2438828)
72 2 FILTER
73 72 TABLE ACCESS BY INDEX ROWID PS_SCRTY_TBL_DEPT (Cost=3 Card=1 Bytes=26)
74 73 INDEX RANGE SCAN PS_SCRTY_TBL_DEPT (Cost=2 Card=1)
3 2 TABLE ACCESS BY INDEX ROWID PS_SCRTY_TBL_DEPT (Cost=2 Card=1 Bytes=36)
4 3 NESTED LOOPS (Cost=257 Card=1 Bytes=512)
59 4 INDEX RANGE SCAN PSBSCRTY_TBL_DEPT (Cost=1 Card=1)
5 4 NESTED LOOPS (Cost=255 Card=1 Bytes=476)
57 5 TABLE ACCESS BY INDEX ROWID PS_SCRTY_TBL_DEPT (Cost=2 Card=1 Bytes=36)
58 57 INDEX RANGE SCAN PSBSCRTY_TBL_DEPT (Cost=1 Card=1)
6 5 NESTED LOOPS (Cost=253 Card=1 Bytes=440)
56 6 INDEX RANGE SCAN PSAPSTREENODE (Cost=2 Card=1 Bytes=44)
7 6 NESTED LOOPS (Cost=251 Card=1 Bytes=396)
54 7 TABLE ACCESS BY INDEX ROWID PS_JOB (Cost=3 Card=1 Bytes=42)
55 54 INDEX RANGE SCAN PSAJOB (Cost=2 Card=2)
8 7 NESTED LOOPS (Cost=248 Card=1 Bytes=354)
47 8 INDEX RANGE SCAN PS_NAMES (Cost=2 Card=1 Bytes=21)
48 47 SORT AGGREGATE
49 48 FILTER
50 49 INDEX RANGE SCAN PS_NAMES (Cost=3 Card=1 Bytes=21)
51 49 SORT AGGREGATE
52 51 FIRST ROW (Cost=3 Card=1 Bytes=21)
53 52 INDEX RANGE SCAN (MIN/MAX) PS_NAMES (Cost=3 Card=323137)
9 8 NESTED LOOPS (Cost=246 Card=1 Bytes=333)
46 9 INDEX UNIQUE SCAN PS_PERSON
10 9 NESTED LOOPS (Cost=246 Card=1 Bytes=324)
45 10 INDEX RANGE SCAN PSAPSTREENODE (Cost=2 Card=1 Bytes=44)
11 10 NESTED LOOPS (Cost=244 Card=1 Bytes=280)
12 11 NESTED LOOPS (Cost=241 Card=1 Bytes=242)
13 12 NESTED LOOPS (Cost=238 Card=1 Bytes=127)
14 13 NESTED LOOPS (Cost=235 Card=1 Bytes=100)
15 14 NESTED LOOPS (Cost=223 Card=4 Bytes=236)
16 15 INDEX FAST FULL SCAN PS_NAMES (Cost=214 Card=4 Bytes=84)
17 15 TABLE ACCESS BY INDEX ROWID PS_NAMES (Cost=3 Card=1 Bytes=38)
18 17 INDEX RANGE SCAN PS_NAMES (Cost=2 Card=1)
19 18 SORT AGGREGATE
20 19 FIRST ROW (Cost=3 Card=1 Bytes=21)
21 20 INDEX RANGE SCAN (MIN/MAX) PS_NAMES (Cost=3 Card=323137)
22 14 TABLE ACCESS BY INDEX ROWID PS_JOB (Cost=3 Card=1 Bytes=41)
23 22 INDEX RANGE SCAN PSAJOB (Cost=2 Card=1)
24 23 SORT AGGREGATE
25 24 FIRST ROW (Cost=3 Card=2 Bytes=40)
26 25 INDEX RANGE SCAN (MIN/MAX) PSAJOB (Cost=3 Card=1219414)
27 23 SORT AGGREGATE
28 27 FIRST ROW (Cost=3 Card=1 Bytes=23)
29 28 INDEX RANGE SCAN (MIN/MAX) PSAJOB (Cost=3 Card=2438828)
30 13 TABLE ACCESS BY INDEX ROWID PS_PERSONAL_PHONE (Cost=3 Card=1 Bytes=27)
31 30 INDEX RANGE SCAN PS_PERSONAL_PHONE (Cost=2 Card=1)
32 12 TABLE ACCESS BY INDEX ROWID PS_ADDRESSES (Cost=3 Card=1 Bytes=115)
33 32 INDEX RANGE SCAN PS_ADDRESSES (Cost=2 Card=1)
34 33 SORT AGGREGATE
35 34 FIRST ROW (Cost=3 Card=1 Bytes=22)
36 35 INDEX RANGE SCAN (MIN/MAX) PS_ADDRESSES (Cost=3 Card=393322)
37 11 TABLE ACCESS BY INDEX ROWID PS_JOB (Cost=3 Card=1 Bytes=38)
38 37 INDEX RANGE SCAN PSAJOB (Cost=2 Card=1)
39 38 SORT AGGREGATE
40 39 FIRST ROW (Cost=3 Card=1 Bytes=20)
41 40 INDEX RANGE SCAN (MIN/MAX) PSAJOB (Cost=3 Card=2438828)
42 38 SORT AGGREGATE
43 42 FIRST ROW (Cost=3 Card=1 Bytes=23)
44 43 INDEX RANGE SCAN (MIN/MAX) PSAJOB (Cost=3 Card=2438828)
78 rows selected.
montecla.ps_job 2438828
montecla.ps_names 323137
montecla.ps_addresses 393322
montecla.ps_personal_phone 247011
The other tables:
MONTECLA.PS_EMPLMT_SRCH_QRY
MONTECLA.PS_PERS_SRCH_QRY
are views.
Oracle Version 9.2.0.7
Thanks in advance
July 26, 2006 - 4:15 pm UTC
not really, not here.
Thanks anyways
A reader, July 26, 2006 - 5:45 pm UTC
Hi Tom,
Thanks for your response of Not Here, Not Really. :)
I do understand that was a lot in the asking. Well I need to turn to someone in difficult times ;)
Well I have fine tuned the query and now it is down to 48 secs.
The developer did not provide the complete information.
Thanks as always
what did you do?
A reader, July 27, 2006 - 9:50 am UTC
Tom,
This question is to the poster yesterday.
Could you tell how you tuned it? Does it have anything to do with the nested loop?
Thanks,
Return deptno's where jobs' exist
Umesh Kasturi, August 31, 2010 - 8:27 am UTC
Hi Tom ,
I want to return the details of emp,
if both JOBS ( 'SALESMAN','CLERK') are present in the deptnno
The JOBS list is a variable one meaning I could pass
( 'SALESMAN','CLERK') or I could pass some other list of values
Thanks in advance
September 09, 2010 - 11:42 am UTC
sql query for my scenario
A reader, November 16, 2011 - 9:49 am UTC
Hi Tom,
Please help me with this scenario.Create table and insert scripts are as below..
create table test(pk1 varhcar2(10),pk2 varchar2(10),a varchar2(10),b varchar2(10),seq number(10));
insert into test values('ad1','id1','N23','S34',1);
insert into test values('ad1','id1','N34','S44',2);
insert into test values('ad1','id1','N33','S35',3);
insert into test values('ad2','id2','N53','S34',1);
insert into test values('ad2','id2','N23','S34',2);
insert into test values('ad2','id2','N63','S35',3);
insert into test values('ad3','id3','N153','S234',1);
insert into test values('ad3','id3','N231','S341',2);
insert into test values('ad3','id3','N23','S34',3);
insert into test values('ad3','id3','N63','S35',4);
Pk1 and pk2 combinely form a primary key and a,b values may be shared between the parents meaning
in the above sample data N63,S35(a,b) value is shared by parents((ad2,id2) and (ad3,id3)) similarly
N23,S34(a,b) is shared by (ad1,id1),(ad2,id2) and (ad3,id3))
I should get the output as below:
ad2,id2,ad3,id3,N63,S35
ad1,id1,ad2,id2,ad3,id3,N23,S34
what should be my approach?
Thanks
November 16, 2011 - 1:20 pm UTC
no version,
no real explanation of the output
I'll give an answer
ops$tkyte%ORA11GR2> select a, b, listagg( pk1||', ' || pk2, ' - ' ) within group (order by seq) list
2 from test
3 group by a, b
4 having count(*) > 1;
A B LIST
---------- ---------- ------------------------------
N23 S34 ad1, id1 - ad2, id2 - ad3, id3
N63 S35 ad2, id2 - ad3, id3
might not work for you - but it does produce the sort of output you asked for
NVL Alternative
A reader, November 16, 2011 - 2:38 pm UTC
hi Tom,
Just curious.. What could be the possible alternative to NVL function.
create table test(column_1 varchar2(10), column_2 varchar2(10), column_3 varchar2(10));
insert into test values('A',NULL,NULL);
insert into test values('B',NULL,'2');
insert into test values('C','3','1');
insert into test values('D','3',NULL);
Query which I am firing:
select * from test where NVL(column_2, ' ') = :1 AND NVL(column_3, ' ') = :2
Bind variable values :1 = 3 and :2 = ' '
Expected Result:
'D','3',NULL
How can we rewrite this query. Please suggest.
November 17, 2011 - 6:45 pm UTC
where (column_2 = :1 or (column_2 is null and ' ' = :1))
and (column_3 = :2 or (column_3 is null and ' ' = :1))
but, do you have a reason for wanting to rewrite the query - that would be a very useful bit of information to know.
COUNT(*) difference
Saurabh Nolakha, November 17, 2011 - 3:43 am UTC
Hi Tom,
I am using the following pl-sql block to throw an error if the query for the exists keyword succeeds(i.e where 1=1).The error report is below the code :
declare
l_fail number;
begin
select 1
into l_fail
from dual
where exists( select 1
from dual where 1=1);
raise_application_error(-20001,'The validation of the entity has failed.');
exception
when no_data_found then
null;
end;
Error report:
ORA-20001: The validation of the entity has failed.
ORA-06512: at line 10
similarly , when I alter the code to make the exists query fail(i.e where 1=0), the anonymous block successfully completes as below:
declare
l_fail number;
begin
select 1
into l_fail
from dual
where exists( select 1
from dual where 1=0);
raise_application_error(-20001,'The validation of the entity has failed.');
exception
when no_data_found then
null;
end;
anonymous block completed
Till here the results are desired only,But when I alter the code to include Count(*) it fails in both the above cases as below:
declare
l_fail number;
begin
select count(*)
into l_fail
from dual
where exists( select 1
from dual where 1=0);
raise_application_error(-20001,'The validation of the entity has failed.');
exception
when no_data_found then
null;
end;
Error report:
ORA-20001: The validation of the entity has failed.
ORA-06512: at line 10
declare
l_fail number;
begin
select count(*)
into l_fail
from dual
where exists( select 1
from dual where 1=1);
raise_application_error(-20001,'The validation of the entity has failed.');
exception
when no_data_found then
null;
end;
Error report:
ORA-20001: The validation of the entity has failed.
ORA-06512: at line 10
even though Count(*) and 1 both return number only, still the results vary.
Can you please let me know the reason behind this?
Thanks a million ton !!
November 17, 2011 - 7:01 pm UTC
an aggregate without a group by ALWAYS returns at least one row and at most one row.
select count(*) from an_empty_table;
will return 0 as the answer.
select count(*) from any_table where 1=0;
will return 0 as the answer.
aggregate without group by assures you of a single row - regardless of how many rows are to be aggregated.
NVL Alternative
A reader, November 18, 2011 - 4:38 pm UTC
Hi tom,
It works.
This logic is implemented for the application called PEGA where we define a variable but we can't implement any function based variable.
November 21, 2011 - 1:30 pm UTC
function based variable? I'm not sure what that is. You have a tool that prohibits you from using builtin SQL functions?
SQL Qery
Devesh, December 01, 2011 - 5:39 am UTC
Hi,
I am having following data in Oracle 9i
create table toto ( a number, flag varchar2(1));
insert into toto values (1, 'U');
insert into toto values (1, 'O');
insert into toto values (2, 'U');
insert into toto values (3, 'U');
insert into toto values (3, 'O');
insert into toto values (4, 'U');
insert into toto values (4, 'O');
insert into toto values (4, 'I');
insert into toto values (4, 'L');
insert into toto values (5, 'U');
insert into toto values (6, 'O');
insert into toto values (7, 'O');
insert into toto values (7, 'U');
insert into toto values (8, 'O');
insert into toto values (9, 'U');
insert into toto values (9, 'O');
insert into toto values (9, 'I');
insert into toto values (9, 'L');
insert into toto values (9, 'U');
insert into toto values (9, 'O');
insert into toto values (9, 'I');
insert into toto values (9, 'L');
insert into toto values (9, 'U');
insert into toto values (9, 'O');
insert into toto values (9, 'I');
insert into toto values (9, 'L');
I want where flag is 'U' or 'O' or 'U' and 'O' they should be mark as 'SAT' and those who are having flag 'U' and 'O', and 'I' and 'L' should come as present
my output should be
1 SAT
2 SAT
3 SAT
4 PRESENT
5 SAT
6 SAT
7 SAT
8 SAT
9 PRESENT
pl show me how to do it
thanks
devesh
December 06, 2011 - 10:11 am UTC
ops$tkyte%ORA11GR2> select a,
2 case when U='U' and O='O' and I='I' and L='L'
3 then 'present'
4 when U='U' or O='O'
5 then 'sat'
6 end tag
7 from (
8 select a,
9 max(decode(flag,'U','U')) U,
10 max(decode(flag,'O','O')) O,
11 max(decode(flag,'I','I')) I,
12 max(decode(flag,'L','L')) L
13 from t
14 group by a
15 )
16 order by a;
A TAG
---------- -------
1 sat
2 sat
3 sat
4 present
5 sat
6 sat
7 sat
8 sat
9 present
9 rows selected.
Problem with String Comparison
Karan, March 06, 2012 - 3:17 am UTC
Hi,
First of all Thank you for providing such precise explanations for all these queries.
I have a problem here (not sure whether it qualifies as a REVIEW here) -
Related To comparing 2 VARCHAR columns in a single table
Table is -
create table test_spliting
(
id number primary key
, col1 varchar2(4000 char)
, col2 varchar2(4000 char)
)
Sample Data is -
Insert into TEST_SPLITING
(ID, COL1, COL2)
Values
(1, 'a,b,c', 'b,c,a');
Insert into TEST_SPLITING
(ID, COL1, COL2)
Values
(2, 'a,b,d', 'd,a');
Insert into TEST_SPLITING
(ID, COL1, COL2)
Values
(3, 'x,y,z', 'z,y,x');
COMMIT;
Problem -
Below is my table
ID col1 col2
---- ----- -------
1 a,b,c c,b,a
2 x,y,z a,x,y
3 d,e,f e,f,d
I want to find out the rows where Col1 And Col2 do not contain same values (order can be different - that is fine). In my example above, The query should give out put as
ID
------
2
Because for the first and third rows - the (comma-separated) values contained in col1 and col2 are same (though in diffrent order).
I have already written a query which given me the IDs for which col1 contains some extra values than col2 (which is just half of what is required). But it is very RAW. Just want to know if there is a better way of doing this -
WITH transposed_tab AS
(
SELECT DISTINCT t.id
, SUBSTR(t.col1, INSTR(t.col1,',',1,t.lev)+1, INSTR(t.col1,',',1,t.lev+1)-1-INSTR(t.col1,',',1,t.lev)) as col1
, SUBSTR(t.col2, INSTR(t.col2,',',1,t.lev)+1, INSTR(t.col2,',',1,t.lev+1)-1-INSTR(t.col2,',',1,t.lev)) as col2
FROM (SELECT id
, ','||col1||',' AS col1
, ','||col2||',' AS col2
, LEVEL AS lev
FROM test_spliting b
CONNECT BY LEVEL <= CASE
WHEN NVL(LENGTH(col1),0)-NVL(LENGTH(REPLACE(col1,',')),0)+1 > NVL(LENGTH(col2),0)-NVL(LENGTH(REPLACE(col2,',')),0)+1
THEN NVL(LENGTH(col1),0)-NVL(LENGTH(REPLACE(col1,',')),0)+1
ELSE
NVL(LENGTH(col2),0)-NVL(LENGTH(REPLACE(col2,',')),0)+1
END
) t
ORDER BY t.id
)
SELECT DISTINCT id FROM
(
SELECT DISTINCT id, col1
FROM transposed_tab t1
WHERE col1 IS NOT NULL
MINUS
SELECT DISTINCT id, col2
FROM transposed_tab t2
)
March 06, 2012 - 7:07 am UTC
question for you first... And I'm serious.
How, in such a small example, could you make your sample inserts have such entirely different data from your commented example?!?!? I can forgive the missing sql terminator for the create table - but to have your inserts so clearly NOT MATCH your example? How? Why?
here is an approach:
ops$tkyte%ORA11GR2> select *
2 from (select id, ','||col1||',' txt, ','||col2||',' txt2 from t) t,
3 table( cast( multiset(
4 select
5 listagg( trim( substr (txt,
6 instr (txt, ',', 1, level ) + 1,
7 instr (txt, ',', 1, level+1)
8 - instr (txt, ',', 1, level) -1 ) ), ',' ) within group (order by trim( substr (txt,
9 instr (txt, ',', 1, level ) + 1,
10 instr (txt, ',', 1, level+1)
11 - instr (txt, ',', 1, level) -1 ) ) )
12 as token
13 from dual
14 connect by level <=
15 length(txt)-length(replace(txt,',',''))-1 ) as sys.odciVarchar2List ) ) x,
16 table( cast( multiset(
17 select
18 listagg( trim( substr (txt2,
19 instr (txt2, ',', 1, level ) + 1,
20 instr (txt2, ',', 1, level+1)
21 - instr (txt2, ',', 1, level) -1 ) ), ',' ) within group (order by trim( substr (txt2,
22 instr (txt2, ',', 1, level ) + 1,
23 instr (txt2, ',', 1, level+1)
24 - instr (txt2, ',', 1, level) -1 ) ) )
25 as token
26 from dual
27 connect by level <=
28 length(txt2)-length(replace(txt2,',',''))-1 ) as sys.odciVarchar2List ) ) y
29 where decode( x.column_value, y.column_value, 1, 0 ) = 0
30 /
ID TXT TXT2 COLUMN_VAL COLUMN_VAL
---------- ---------- ---------- ---------- ----------
2 ,a,b,d, ,d,a, a,b,d a,d
Explanation Required
Karan, March 06, 2012 - 2:20 pm UTC
Sorry, for making such stupid mistakes.
Thank you very much for this great query.
But Can you please provide some high level details on how this query works?
1. I understand that use of ListAgg Function and connect by Level has been made to arrange the values in COL1 and COL2 in dictionary order.
2. Also, I understand that for each row in table t (or test_spliting) inline view x (single column inline view) generates 1 row having same values as col1 but arranged in dictionary order.
3. similarly, y does the same job for col2
What I do not understand here is -
1. Why use of Cast and Multiset is required here?
2. how in this example are we able to access "txt" and "txt2" in x and y respectively? Also, what are the restrictions on such references of columns? Or in other words, What is the scope of reference of a table column? I tried to re-write the query which you gave by removing "TABLE(CAST(Multiset" from x and y but I got the below error -
ORA-00904: "TXT": invalid identifier
Thanks in Advance.
March 06, 2012 - 6:27 pm UTC
1) no, the connect by was used to turn the comma delimited list into a set, listagg took the entire set and put it back into a string after sorting.
2) alphabetic order, yes
3) correct
part 2:
1) because I needed a set for listagg to work on - and cast and multiset are used to do that. see
http://asktom.oracle.com/Misc/varying-in-lists.html 2) that is just how table functions work, they have access to the row in the 'real' table. Without the table clause, nothing in the above query would make 'sense'. It does all of the work.
Need help with a SQL Query
karan, June 26, 2012 - 3:48 pm UTC
I have a table -
create table USER_INCOME
(
USER_ID VARCHAR2(10)
, INCOME1 NUMBER
, INCOME2 NUMBER
, INCOME3 NUMBER
);Here is the sample data -
insert into USER_INCOME (USER_ID, INCOME1, INCOME2, INCOME3) values ('U01', 10000, 12000, 13000);
insert into USER_INCOME (USER_ID, INCOME1, INCOME2, INCOME3) values ('U02', 4000, 1000, 5000);
insert into USER_INCOME (USER_ID, INCOME1, INCOME2, INCOME3) values ('U03', 7800, 9000, 8500);
COMMIT;Here is the problem now -
For each user, there are 3 different incomes maintained in this table. I need to write a query that gives me the second highest income for each user (it can be in any of the 3 columns).
Here is something, which I have written, which is already doing this -
select USER_ID, INCOME
from (
select USER_ID, INCOME, rank() over (partition by USER_ID order by INCOME desc) rk
from (
select USER_ID, INCOME1 as INCOME from USER_INCOME
union all
select USER_ID, INCOME2 as INCOME from USER_INCOME
union all
select USER_ID, INCOME3 as INCOME from USER_INCOME
)
)
where rk = 2;My question is -
Is there any Oracle provided functionality which makes getting this information simpler than the way shown above? If yes, which way is preferable from performance point.
Note - It can be safely assumed that no 2 incomes are same for any user.
Thanks in advance.
Yours,
Karan
June 26, 2012 - 4:16 pm UTC
wow, is that a table done wrong or what. Anytime - anytime -you have columns named "xxxx1, xxxx2, xxxx3, ..." you have almost certainly done it *wrong*
What is there is no second highest (they all three have the same value)?
What if there are two second highest?
If they are supposed to be three different values, then:
ops$tkyte%ORA11GR2> select t.*,
2 case when income1 between least(income2,income3) and greatest(income2,income3) then income1
3 when income2 between least(income1,income3) and greatest(income1,income3) then income2
4 when income3 between least(income1,income2) and greatest(income1,income2) then income3
5 end "2nd"
6 from user_income t
7 /
USER_ID INCOME1 INCOME2 INCOME3 2nd
---------- ---------- ---------- ---------- ----------
U01 10000 12000 13000 12000
U02 4000 1000 5000 4000
U03 7800 9000 8500 8500
u05 1000 1000 1000 1000
u04 1000 2000 2000 2000
Thanks a lot. Great answer - as usual
Karan, June 27, 2012 - 2:48 am UTC
Thanks for your reply.
Just used a dummy table for demonstration purpose. Will take care to not to use such column names again.
Yours,
Karan.
June 27, 2012 - 9:35 am UTC
but your 'dummy table' models your real life.
Oracle data dictionary done wrong ?
Sokrates, June 27, 2012 - 5:52 am UTC
... Anytime - anytime -you have columns named "xxxx1, xxxx2, xxxx3, ..." you have almost certainly done it *wrong* ...
sokrates@11.2 > desc dba_advisor_actions
Name Null? Type
----------------------------------------- -------- ----------------
OWNER VARCHAR2(30)
TASK_ID NOT NULL NUMBER
TASK_NAME VARCHAR2(30)
EXECUTION_NAME VARCHAR2(30)
REC_ID NOT NULL NUMBER
ACTION_ID NOT NULL NUMBER
OBJECT_ID NUMBER
COMMAND VARCHAR2(64)
COMMAND_ID NOT NULL NUMBER
FLAGS NUMBER
ATTR1 VARCHAR2(4000)
ATTR2 VARCHAR2(4000)
ATTR3 VARCHAR2(4000)
ATTR4 VARCHAR2(4000)
ATTR5 CLOB
ATTR6 CLOB
NUM_ATTR1 NUMBER
NUM_ATTR2 NUMBER
NUM_ATTR3 NUMBER
NUM_ATTR4 NUMBER
NUM_ATTR5 NUMBER
MESSAGE VARCHAR2(4000)
FILTERED VARCHAR2(1)
sokrates@11.2 > desc dba_advisor_objects
Name Null? Type
----------------------------------------- -------- ----------------
OWNER VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
TYPE VARCHAR2(64)
TYPE_ID NOT NULL NUMBER
TASK_ID NOT NULL NUMBER
TASK_NAME VARCHAR2(30)
EXECUTION_NAME VARCHAR2(30)
ATTR1 VARCHAR2(4000)
ATTR2 VARCHAR2(4000)
ATTR3 VARCHAR2(4000)
ATTR4 CLOB
ATTR5 VARCHAR2(4000)
ATTR6 RAW(2000)
ATTR7 NUMBER
ATTR8 NUMBER
ATTR9 NUMBER
ATTR10 NUMBER
OTHER CLOB
sokrates@11.2 > desc dba_advisor_rationale
Name Null? Type
----------------------------------------- -------- ----------------
OWNER VARCHAR2(30)
TASK_ID NOT NULL NUMBER
TASK_NAME VARCHAR2(30)
EXECUTION_NAME VARCHAR2(30)
REC_ID NUMBER
RATIONALE_ID NOT NULL NUMBER
IMPACT_TYPE VARCHAR2(4000)
IMPACT NUMBER
MESSAGE VARCHAR2(4000)
OBJECT_ID NUMBER
TYPE VARCHAR2(30)
ATTR1 VARCHAR2(4000)
ATTR2 VARCHAR2(4000)
ATTR3 VARCHAR2(4000)
ATTR4 VARCHAR2(4000)
ATTR5 CLOB
sokrates@11.2 > desc dba_resumable
Name Null? Type
----------------------------------------- -------- ----------------
USER_ID NUMBER
SESSION_ID NUMBER
INSTANCE_ID NUMBER
COORD_INSTANCE_ID NUMBER
COORD_SESSION_ID NUMBER
STATUS VARCHAR2(9)
TIMEOUT NUMBER
START_TIME VARCHAR2(20)
SUSPEND_TIME VARCHAR2(20)
RESUME_TIME VARCHAR2(20)
NAME VARCHAR2(4000)
SQL_TEXT VARCHAR2(1000)
ERROR_NUMBER NUMBER
ERROR_PARAMETER1 VARCHAR2(80)
ERROR_PARAMETER2 VARCHAR2(80)
ERROR_PARAMETER3 VARCHAR2(80)
ERROR_PARAMETER4 VARCHAR2(80)
ERROR_PARAMETER5 VARCHAR2(80)
ERROR_MSG VARCHAR2(4000)
Is it done wrong ?
Should they fix it ?
June 27, 2012 - 9:43 am UTC
I'm not a fan of it, I've never said that we only hire data modelers here either. We have "programmers"
for dba_advisor_objects for example, I would have preferred almost an EAV (entity attribute value) model. The meaning of attr1, .. attr10 differs based on the object being managed. It would have been nice to be able to get just the three attributes *and their meaning* when the object type is a sql object
from the definition of that view, we get:
Note:
The definition of the ATTRn columns depends on the advisors that are using the object. For example, the SQL object type defines the attribute columns as follows:
ATTR1 contains the SQL hash value
ATTR2 contains the SQL address (in the cursor cache)
ATTR4 contains the SQL text
it would have been nice to have child records that had the three attrbutes - with their corresponding meaning and value instead of just having to know "attr1, attr2, attr4" mean "sql hash, sql address, sql text" wouldn't it.
@Sokrates
Michel Cadot, June 27, 2012 - 6:52 am UTC
Who say Oracle is a relational application?
Who say the Oracle catalog is a relational model?
It just exposes you the information in the dictionary in the form of views for you to be able to get it via SQL.
Other RDBMS just provides you stored procedures to get the same thing and it is far more difficult to build report from stored procedures than from views.
Now see each view as a stored procedure and columns as IN/OUT parameters and you will have your answer.
Regards
Michel
June 27, 2012 - 9:45 am UTC
but unfortunately, their underlying data model does this same thing.... physically they are using attr1, ... attrN :(
@Michel Cadot
Sokrates, June 27, 2012 - 9:08 am UTC
Allô Michel
I agree with you, but I don't agree with the proposition
Anytime - anytime -you have columns named "xxxx1, xxxx2, xxxx3, ..." you have almost certainly done it *wrong* ...
The dictionary is a good counterexample
Regards
Sokrates
June 27, 2012 - 9:49 am UTC
You have ALMOST CERTAINLY - almost.
And in this case, going through it case by case, I think some of them are in fact done wrong.
Because they did it 'wrong' in the dictionary doesn't mean you have a counterexample. A counterexample would show the statement was false. The statement is not false - anytime you have columns named xxx1, xxx2, .... you have almost certainly done it *wrong* was not proven wrong - you didn't give a counter example - in fact some would say you gave supporting evidence.
Date Range Validation Query
A reader, June 28, 2012 - 4:32 am UTC
CREATE TABLE product_detail
(
ID NUMBER(5) NOT NULL,
product_name VARCHAR2(10) NOT NULL,
start_date DATE,
end_date DATE
);
-- Product 'A' is Valid for any Date
INSERT INTO product_detail VALUES ( 1, 'A', NULL, NULL );
-- Product 'B' is valid for Year 2010 only
INSERT INTO product_detail VALUES ( 2, 'B', TO_DATE('01012010','MMDDYYYY'), TO_DATE('12312010','MMDDYYYY') );
-- Product 'C' is NOT valid for Year 2011, other then 2011 it is valid
INSERT INTO product_detail VALUES ( 3, 'C', null, TO_DATE('12312010','MMDDYYYY') );
INSERT INTO product_detail VALUES ( 4, 'C', TO_DATE('12312012','MMDDYYYY'), null );
Note : product_detail table can have more then 2 rows for same product_name with different date range ( it must not be overlapped )
CREATE TABLE purchase_detail
(
purchase_ID NUMBER(5) NOT NULL,
product_name VARCHAR2(10) NOT NULL, -- References product_detail.product_name
purchase_date DATE NOT NULL
);
I need to validate records in "purchase_detail" table againsts rows in "product_detail" based on product_name and Dates.
If 'purchase_date' does not fall into any of the 'start_date' - 'end_date' range of correspoding product_name, it should give error.
If purchase_date is earlier than start_date then Error should be 'purchase_date before start_date' ( Error1 = 100 for this error ) and
if purchase_date is later than end_date then Error should be 'purchase_date after end_date' ( Error2 = 200 for this error ).
Note :
For same purchase Id we can have both the Errors.
i.e., For Product 'C', if purchase_date = '01012011', I should get both the errors Error1 & Error2,
since '01012011' is greater then 1st row's end_date '12312010', Error2 And
is earlier then 2nd row's start_date '12312012', Error1.
Could you please provide query for this.
June 28, 2012 - 11:00 pm UTC
warning: this code was written after a long day, without a ton of testing. Please - begging you - read the logic, read the thoughts behind the code and VERIFY the code does what I say it does. Most importantly - understand why such a large volume of code is actually needed.
I would love to hear feedback from anyone that sees any holes in this or a better approach
warning #2: please re-read the warning above. I did not fully test this, this is my off the cuff answer - trust but verify. I think I thought through all of the cases - I hope I did, but please review it....
you have not thought this all of the way through, this is bigger than you think
this is a lot more than "a query". A LOT more.
first off, you are missing a table, you wrote:
CREATE TABLE product_detail
(
ID NUMBER(5) NOT NULL,
product_name VARCHAR2(10) NOT NULL,
start_date DATE,
end_date DATE
);
CREATE TABLE purchase_detail
(
purchase_ID NUMBER(5) NOT NULL,
product_name VARCHAR2(10) NOT NULL, -- References product_detail.product_name
purchase_date DATE NOT NULL
);
You are missing a PRODUCT table, that is what purchase_detail AND product_detail have foreign keys to.
also, it would make more sense to register "invalid" ranges in the table - NOT valid ranges.
By default, it could be assumed that a product is valid for all dates.
Then you put in ranges it is NOT valid for.
We can work with your model, but it seems rather backwards doesn't it? Put in "invalid ranges" rather than "valid for" ranges. (I'd change the model before you implement, seriously)
I would prefer to use a stored procedure to do the the modifications to these tables - you could use a trigger, but I'll let you do that. You'll have to move the logic into there.
But think about it. If you
a) delete or update product detail you must prevent any modifications to the product you are modifying from being touched in purchase_detail. If you insert into product detail, you don't have to worry about purchase detail at that point.
b) insert or update purchase detail, you must avoid any modifications to that product in product_detail. If you are deleting from purchase detail - you are OK, no locking is needed.
So, the first line in both stored procedures to insert/update will be:
select * from product where product_name = <that product> FOR UPDATE;
to serialize the next steps. Any insert or update of these two tables will lock that row *first*, to ensure that no one else is modifying that product information.
Then, you can safely query. To delete or update product_detail, you need to make sure the existing dates in purchase_detail do not fall into an invalid range. More specifically, you need to find any row in purchase_detail that falls into the OLD start/end date range, something like this for a delete:
ops$tkyte%ORA11GR2> create or replace procedure delete_product_detail( p_id in number )
2 as
3 l_product_rec product%rowtype;
4 l_prod_dtl_rec product_detail%rowtype;
5 l_n number;
6 begin
7 select *
8 into l_product_rec
9 from product
10 where product_name = (select product_name
11 from product_detail
12 where id = P_ID)
13 FOR UPDATE;
14
15 select *
16 into l_prod_dtl_rec
17 from product_detail
18 where id = P_ID;
19
20 select count(*)
21 into l_n
22 from purchase_detail
23 where purchase_date between nvl(l_prod_dtl_rec.start_date,to_date('01-jan-1900','dd-mon-yyyy'))
24 and nvl(l_prod_dtl_rec.end_date, sysdate ); -- or any date way in the future?
25
26 if (l_n > 0)
27 then
28 raise_application_error( -20001, 'you would make ' || l_n || ' existing records invalid' );
29 end if;
30
31 delete from product_detail where id = P_ID;
32 end;
33 /
Procedure created.
we are assuming that all modifications to product detail and purchase detail that need to serialize (the four mentioned above) will start with that select for update (yes, you can use a trigger, that would be technically OK in this case for that).
So, only one person will modify a product in either of these tables at a time. that lets us read them without any concurrency issues.
then we get the values of the existing record that we will delete.
And then we make sure that no record exists in the purchase detail table that falls in that range. If they do - fail. If not, delete it safely, nothing exists that is protected by that range...
Now, onto the update:
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace procedure update_product_detail( p_id in number, p_start_date in date, p_end_date in date )
2 as
3 l_product_rec product%rowtype;
4 l_prod_dtl_rec product_detail%rowtype;
5 l_n number;
6 begin
7 select *
8 into l_product_rec
9 from product
10 where product_name = (select product_name
11 from product_detail
12 where id = P_ID)
13 FOR UPDATE;
14
15 select *
16 into l_prod_dtl_rec
17 from product_detail
18 where id = P_ID;
19
20 select count(*)
21 into l_n
22 from purchase_detail
23 where purchase_date between nvl(l_prod_dtl_rec.start_date,to_date('01-jan-1900','dd-mon-yyyy'))
24 and nvl(l_prod_dtl_rec.end_date, sysdate )
25 and purchase_date NOT between nvl(p_start_date,to_date('01-jan-1900','dd-mon-yyyy'))
26 and nvl(p_end_date,sysdate);
27
28 if (l_n > 0)
29 then
30 raise_application_error( -20001, 'you would make ' || l_n || ' existing records invalid' );
31 end if;
32
33 update product_detail set start_date = p_start_date, end_date = p_end_date where id = p_id;
34 end;
35 /
Procedure created.
again, lock the parent record, serialize at product.
Then get the existing record.
Now, make sure that no record exists such that it was protected by the existing range AND NOT protected by the newly updated range.
If there are any - fail.
Else do the update.....
That covers the modification of the product_detail table, now onto the purchase_detail...
At this point, I really have to say I don't like your error handling. You say you want "two errors". But there are not two errors, you simply have an insert or update of a row that doesn't fall into a valid range. It is not that it is before a certain range or after, it just isn't in a valid range. We can do - sort of - what you want, but it seems beyond pedantic. Further - a single error cannot have TWO error codes - 100 and 200 - that doesn't even make sense.
So, ok, let's go...
ops$tkyte%ORA11GR2> create or replace
2 procedure ins_or_upd_purchase_detail
3 ( p_id in purchase_detail.purchase_id%type,
4 p_product_name in purchase_detail.product_name%type,
5 p_purchase_date in purchase_detail.purchase_date%type
6 )
7 as
8 l_product_rec product%rowtype;
9 l_prod_dtl_rec product_detail%rowtype;
10 l_n number;
11 l_sdate varchar2(30);
12 l_edate varchar2(30);
13 begin
14 select *
15 into l_product_rec
16 from product
17 where product_name = p_product_name
18 FOR UPDATE;
19
20 select count(*)
21 into l_n
22 from dual
23 where not exists (select null
24 from product_detail
25 where nvl(start_date,to_date('01-jan-1900','dd-mon-yyyy')) <= p_purchase_date
26 and nvl(end_date,sysdate) >= p_purchase_date );
27
28 if (l_n <> 0)
29 then
30 select to_char( min(start_date), 'dd-mon-yyyy')
31 into l_sdate
32 from product_detail
33 where start_date > p_purchase_date;
34
35 select to_char( max(end_date), 'dd-mon-yyyy' )
36 into l_edate
37 from product_detail
38 where end_date < p_purchase_date;
39
40 raise_application_error( -20001, 'Error: ' || case when l_sdate is not null then 'error 100, purchase date before ' || l_sdate || ' ' end ||
41 case when l_edate is not null then 'error 200, purchase date after ' || l_edate end );
42 end if;
43
44 merge into purchase_detail
45 using (select p_id id, p_product_name product_name, p_purchase_date purchase_date from dual) x
46 on ( x.id = purchase_detail.purchase_id )
47 when matched then update set purchase_date = x.purchase_date, product_name = x.product_name
48 when not matched then insert (purchase_id,product_name,purchase_date) values (x.id,x.product_name,x.purchase_date);
49 end;
50 /
Procedure created.
logic is:
a) start by locking parent record.
Then - for the new values of purchase date - verify that a record exists that is in the valid range. If count = 0, we are OK, if count isn't 0, we are not good.
If we discover we are "not good", there are no records in the table that cover the valid range - we'll query up the record with the minimum start date that is greater then your purchase date (your 100 error) and the max end end that is less than your purchase date (your 200 error)
and then format an error message based on it.
NOTE: if there is no range - there will just be the error "error" - since that is what you basically said you wanted. Meaning if there is no row in the product_detail table, you just get "error". it wasn't out of range of any of the records...
I really think your model needs a bit of work and you want to store INVALID ranges, not "valid" ranges...
A reader, June 28, 2012 - 4:43 am UTC
I've written following query, but is not giving desired output sometimes.
SELECT A.Purchase_Id,
A.Product_Name,
A.Purchase_Date,
--
100 * COALESCE ( ( SELECT (0)
FROM product_detail b
WHERE b.product_name = a.product_name
AND ( a.purchase_date BETWEEN NVL(b.start_date,'01-Jan-1900') AND NVL(b.end_date,'31-Dec-2999') )
AND ROWNUM = 1
),
--
( SELECT (0)
FROM product_detail b2
WHERE b2.product_name = a.product_name
AND NOT EXISTS ( SELECT 'x'
FROM product_detail b3
WHERE b3.product_name = a.product_name
AND b3.start_date IS NULL )
AND a.purchase_date >= ( SELECT MIN(b4.start_date)
FROM product_detail b4
WHERE b4.product_name = a.product_name )
AND ROWNUM = 1
),
--
( SELECT (0)
FROM product_detail b2
WHERE b2.product_name = a.product_name
AND EXISTS ( SELECT 'x'
FROM product_detail b3
WHERE b3.product_name = a.product_name
AND b3.start_date IS NULL )
AND 1 = ( SELECT COUNT(*)
FROM product_detail b4
WHERE b4.product_name = a.product_name )
AND ROWNUM = 1
),
--
( SELECT (0)
FROM product_detail b2
WHERE b2.product_name = a.product_name
AND EXISTS ( SELECT 'x'
FROM product_detail b3
WHERE b3.product_name = a.product_name
AND b3.start_date IS NULL )
AND 1 < ( SELECT COUNT(*)
FROM product_detail b4
WHERE b4.product_name = a.product_name )
AND a.purchase_date <= ( SELECT MIN(b5.end_date)
FROM product_detail b5
WHERE b5.product_name = a.product_name
AND b5.start_date IS NULL )
AND ROWNUM = 1
),
--
( SELECT (1)
FROM product_detail b1
WHERE b1.product_name = a.product_name
AND ROWNUM = 1 ),
--
0
)
Error1,
--
200 * COALESCE ( ( SELECT (0)
FROM product_detail b
WHERE b.product_name = a.product_name
AND ( a.purchase_date BETWEEN NVL(b.start_date,'01-Jan-1900') AND NVL(b.end_date,'31-Dec-2999') )
AND ROWNUM = 1
),
--
( SELECT (0)
FROM product_detail b2
WHERE b2.product_name = a.product_name
AND NOT EXISTS ( SELECT 'x'
FROM product_detail b3
WHERE b3.product_name = a.product_name
AND b3.end_date IS NULL )
AND a.purchase_date <= ( SELECT MAX(b4.end_date)
FROM product_detail b4
WHERE b4.product_name = a.product_name )
AND ROWNUM = 1
),
--
( SELECT (0)
FROM product_detail b2
WHERE b2.product_name = a.product_name
AND EXISTS ( SELECT 'x'
FROM product_detail b3
WHERE b3.product_name = a.product_name
AND b3.end_date IS NULL )
AND 1 = ( SELECT COUNT(*)
FROM product_detail b4
WHERE b4.product_name = a.product_name )
AND ROWNUM = 1
),
--
( SELECT (0)
FROM product_detail b2
WHERE b2.product_name = a.product_name
AND EXISTS ( SELECT 'x'
FROM product_detail b3
WHERE b3.product_name = a.product_name
AND b3.end_date IS NULL )
AND 1 < ( SELECT COUNT(*)
FROM product_detail b4
WHERE b4.product_name = a.product_name )
AND a.purchase_date >= ( SELECT MAX(b5.start_date)
FROM product_detail b5
WHERE b5.product_name = a.product_name
AND b5.end_date IS NULL )
AND ROWNUM = 1
),
--
( SELECT (1)
FROM product_detail b1
WHERE b1.product_name = a.product_name
AND ROWNUM = 1 ),
--
0
)
Error2
--
FROM purchase_detail a ;
June 29, 2012 - 10:39 am UTC
sorry?
I mean - and this is TOTALLY SERIOUS - what else did you expect?
I run this query and sometimes it doesn't return what I expect:
select sysdate from dual
why not? when you can answer me, I'll be able to answer you - but that only means we've evolved to be able to read each others minds.
this is about the worst formed question I've read, ever. Not the worst, but way way up there.
give
me
a
break
I have no clue what you are trying to do - or who you are. I just see a big query and "it doesn't work", no context.
Re: Date Range Validation Query
Michel Cadot, June 28, 2012 - 8:08 am UTC
Query depends on the following question:
- Is product_name unique in product_detail?
- If start_date is null then what does it mean? How to compare purchase_date agains it?
- Same question for end_date
- What is, in your question, the purpose of mentioning the date constraints on product_detail (the question is only about purchase_detail rows)? Is it related to null value in one of the date fields?
Regards
Michel
A reader, June 28, 2012 - 8:45 am UTC
Thanks Michel for your reply
Please find my answers to your Queries
- Is product_name unique in product_detail?
A :
Nope.
We can have multiple rows with same product_name with different date range of start_date & end_date in product_detail.
See the insert statemts in above post for Product 'C' which has 2 rows with different date range.
- If start_date is null then what does it mean? How to compare purchase_date agains it?
- Same question for end_date
A :
Consider the 1st row for Product 'C' in 'product_detail' table.
It has NULL Start_date & End_date = '31-Dec-2010'.
So for purchase_date <= '31-Dec-2010' in 'purchase_detail' table for Product 'C' must not give an error.
Similarly Consider the 2nd row for Product 'C' in product_detail table.
It has Start_date = '31-Dec-2012' & End_date is NULL.
So for purchase_date >= '31-Dec-2012' in 'purchase_detail' table for Product 'C' must not give an error.
That's why I have contion
a.purchase_date BETWEEN NVL(b.start_date,'01-Jan-1900') AND NVL(b.end_date,'31-Dec-2999')
in query I specified above.
- What is, in your question, the purpose of mentioning the date constraints on product_detail (the question is only about purchase_detail rows)?
Is it related to null value in one of the date fields?
A :
I'm not getting your ques.
For 'product_detail' table any of Dates can be NULL, but in 'purchase_detail' table purchase_date column can not be NULL.
Re: Date Range Validation Query
Michel Cadot, June 29, 2012 - 2:36 am UTC
Forget my last question I didn't understand the comment was about the following INSERT statement.
Note that for product C your last insert is in contradiction with the comment, the start_date should be 01012012, not 12312012.
I agree with Tom that 1) there should a PRODUCT table with product names and ids and both your tables should have an integrity constraint on it and 2) the product_detail table should contain invalid ranges and not valid ones.
Anyway, working with your model, I will first start with the easier part: checking the current rows in purchase_detail and returning the invalid ones with the error number.
Too bad you didn't provide a complete test case; here are some data for the tables:
-- Product 'A' is Valid for any Date
INSERT INTO product_detail VALUES ( 1, 'A', NULL, NULL );
-- Product 'B' is valid for Year 2010 only
INSERT INTO product_detail VALUES ( 2, 'B', TO_DATE('01012010','MMDDYYYY'), TO_DATE('12312010','MMDDYYYY') );
-- Product 'C' is NOT valid for Year 2011, other then 2011 it is valid
INSERT INTO product_detail VALUES ( 3, 'C', null, TO_DATE('12312010','MMDDYYYY') );
INSERT INTO product_detail VALUES ( 4, 'C', TO_DATE('01012012','MMDDYYYY'), null );
insert into purchase_detail values (1, 'A', sysdate);
insert into purchase_detail values (2, 'B', to_date('01/06/2009','DD/MM/YYYY'));
insert into purchase_detail values (3, 'B', to_date('01/06/2010','DD/MM/YYYY'));
insert into purchase_detail values (4, 'B', to_date('01/06/2011','DD/MM/YYYY'));
insert into purchase_detail values (5, 'C', to_date('01/06/2010','DD/MM/YYYY'));
insert into purchase_detail values (6, 'C', to_date('01/06/2011','DD/MM/YYYY'));
insert into purchase_detail values (7, 'C', to_date('01/06/2012','DD/MM/YYYY'));
insert into purchase_detail values (8, 'D', to_date('01/06/2012','DD/MM/YYYY'));
commit;With these data:
SQL> select * from product_detail
2 order by product_name, nvl(start_date,to_date('01/01/1900','DD/MM/YYYY'));
ID PRODUCT_NA START_DATE END_DATE
---------- ---------- ----------- -----------
1 A
2 B 01-JAN-2010 31-DEC-2010
3 C 31-DEC-2010
4 C 01-JAN-2012
4 rows selected.
SQL> select * from purchase_detail order by product_name, purchase_date;
PURCHASE_ID PRODUCT_NA PURCHASE_DA
----------- ---------- -----------
1 A 29-JUN-2012
2 B 01-JUN-2009
3 B 01-JUN-2010
4 B 01-JUN-2011
5 C 01-JUN-2010
6 C 01-JUN-2011
7 C 01-JUN-2012
8 D 01-JUN-2012
8 rows selected.I get the errors with the following query (note the purchase_detail contains a row for product D that does not exist in product_detail and so is never valid whatever is the purchase date):
SQL> select purchase_id, product_name, purchase_date,
2 decode(min(e100_start)||min(e100_end), null, null,
3 min(e100_start)||'->'||min(e100_end)) error_100,
4 decode(max(e200_start)||max(e200_end), null, null,
5 max(e200_start)||'->'||max(e200_end)) error_200
6 from (
7 select d.purchase_id, d.product_name, d.purchase_date,
8 p.start_date e100_start, nvl(p.end_date,sysdate) e100_end,
9 to_date(null) e200_start, to_date(null) e200_end
10 from purchase_detail d, product_detail p
11 where p.product_name = d.product_name
12 and p.start_date > d.purchase_date
13 union all
14 select d.purchase_id, d.product_name, d.purchase_date,
15 to_date(null), to_date(null),
16 nvl(p.start_date,to_date('01/01/1900','DD/MM/YYYY')), p.end_date
17 from purchase_detail d, product_detail p
18 where p.product_name = d.product_name
19 and p.end_date < d.purchase_date
20 union all
21 select d.purchase_id, d.product_name, d.purchase_date,
22 to_date(null), to_date(null),
23 to_date(null), to_date(null)
24 from purchase_detail d
25 where not exists ( select null from product_detail p
26 where p.product_name = d.product_name )
27 )
28 group by purchase_id, product_name, purchase_date
29 order by product_name, purchase_date
30 /
PURCHASE_ID PRODUCT_NA PURCHASE_DA ERROR_100 ERROR_200
----------- ---------- ----------- ------------------------ ------------------------
2 B 01-JUN-2009 01-JAN-2010->31-DEC-2010
4 B 01-JUN-2011 01-JAN-2010->31-DEC-2010
5 C 01-JUN-2010 01-JAN-2012->29-JUN-2012
6 C 01-JUN-2011 01-JAN-2012->29-JUN-2012 01-JAN-1900->31-DEC-2010
7 C 01-JUN-2012 01-JAN-1900->31-DEC-2010
8 D 01-JUN-2012
6 rows selected.The query gives in error_100 the first next valid date range and in error_200 the last preceding valid date range. When both are nulls then there is no valid range for this product.
I have currently no comment about Tom solution to avoid any invalid row. It seems to me, at first sight, correct and is the one I'd choose if I had to do it. I will come back if something more comes to me.
Regards
Michel
A reader, June 29, 2012 - 4:28 am UTC
Hi Tom,
Let me try to frame the requirement again.
1. Both the tables 'product_detail' and 'purchase_detail' are filled with data.
I do not need to fire any DML statements against them.
2. This is an existing Model and I don't have luxury to make changes in Data Model.
3. I just need to generate report on all the rows of 'purchase_detail' table using SELECT statement only. ( Requirement does not allow me to use Store Procedures )
4. This report should validate 'purchase_date' of all the records in 'purchase_detail' table against the 'Start_date' and 'End_date' of corresponding 'product_name' in the 'product_detail' table, and need to show whether Error1 ( 'purchase_date' before 'Start_date' ) Or/And Error2 ( 'purchase_date' after 'end_date' ).
Error1 and Error2 can have values either - 0 ( i.e. 'purchase_date' is in Valid Range ) Or 1 ( i.e. 'purchase_date' is out of Range )
Now let me try to provide some scenarios,
1. Both Start_date & End_date are NULL, So Purchase_Date is always valid for Product 'A'
Product Start_date End_date Purchase_Date Error1 Error2
------- ---------- ---------- ------------- ------ ------
A NULL NULL Any Date 0 0 ( Any Purchase_Date )
2. Valid Start_Date & End_Date range : We must never get both Error1 & Error1 equals to 1
Product Start_date End_date Purchase_Date Error1 Error2
------- ---------- ---------- ------------- ------ ------
B '01012010' '12312010' '01012009' 1 0 ( Any Purchase_Date before Start_date = '01012010' )
'01012010' 0 0 ( Any Purchase_Date in 2010 )
'01012011' 0 1 ( Any Purchase_Date after End_date = '12312010' )
3. Start_Date is NULL & Valid End_Date : For this scenario, we must never get Error1 = 1
Product Start_date End_date Purchase_Date Error1 Error2
------- ---------- ---------- ------------- ------ ------
C NULL '12312010' '12312009' 0 0 ( Any Purchase_Date before or same as End_date = '12312010' )
'12312011' 0 1 ( Any Purchase_Date after End_date = '12312010' )
4. Valid Start_Date & End_Date is NULL : For this scenario, we must never get Error2 = 1
Product Start_date End_date Purchase_Date Error1 Error2
------- ---------- ---------- ------------- ------ ------
D '01012010' NULL '12312009' 1 0 ( Any Purchase_Date before Start_date = '01012010' )
'12312011' 0 0 ( Any Purchase_Date after or same as Start_date = '01012010' )
5. Mutiple Valid Date range for Same Product in 'product_detail' table
Product Start_date End_date Purchase_Date Error1 Error2
------- ---------- ---------- ------------- ------ ------
D NULL '12312010'
D '01012012' NULL
'12312009' 0 0 ( Any Purchase_Date before or same as End_date = '12312010' )
'01012011' 1 1 ( For this Purchase_Date, I need both the errors )
'01012013' 0 0 ( Any Purchase_Date after or same as Start_date = '01012012' )
Product Start_date End_date Purchase_Date Error1 Error2
------- ---------- ---------- ------------- ------ ------
E NULL '12312010'
E '01012012' '12312012'
E '01012014' NULL
'12312009' 0 0 ( Any Purchase_Date before or same as End_date = '12312010' )
'01012011' 1 1 ( For Purchase_Date in 2011, I need both the errors )
'01012012' 0 0 ( For Purchase_Date in 2012, No Errors )
'01012013' 1 1 ( For Purchase_Date in 2013, I need both the errors )
'01012014' 0 0 ( Any Purchase_Date after or same as Start_date = '01012014' )
I hope I'm able to explain my requirement now.
I've to deliever this requirements and I can't change it.
Thanks for you time Tom.
June 29, 2012 - 10:47 am UTC
3. I just need to generate report on all the rows of 'purchase_detail' table
using SELECT statement only. ( Requirement does not allow me to use Store
Procedures )that does not prevent plsql - you have anonymous blocks do not forget.
but, here is a brute force way. Just find the rows that don't have a valid range - then look up the start date/end date if applicable:
ps$tkyte%ORA11GR2> select * from product_detail;
ID PRODUCT_NA START_DAT END_DATE
---------- ---------- --------- ---------
1 A
2 B 01-JAN-10 31-DEC-10
3 C 31-DEC-10
4 C 01-JAN-12
ops$tkyte%ORA11GR2> select * from purchase_detail;
PURCHASE_ID PRODUCT_NA PURCHASE_
----------- ---------- ---------
1 A 29-JUN-12
2 B 01-JUN-09
3 B 01-JUN-10
4 B 01-JUN-11
5 C 01-JUN-10
6 C 01-JUN-11
7 C 01-JUN-12
8 D 01-JUN-12
8 rows selected.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select pur_dtl.*,
2 (select max(start_date)
3 from product_detail x
4 where x.product_name = pur_dtl.product_name
5 and x.start_date < pur_dtl.purchase_date) error100,
6 (select min(end_date)
7 from product_detail x
8 where x.product_name = pur_dtl.product_name
9 and x.end_date > pur_dtl.purchase_date) error200
10 from purchase_detail pur_dtl
11 left outer join (select product_name, nvl(start_date,to_date('01-jan-1900','dd-mon-yyyy')) sdate, nvl(end_date,to_date('01-jan-3000','dd-mon-yyyy')) edate
12 from product_detail
13 ) prod_dtl on (pur_dtl.product_name=prod_dtl.product_name and pur_dtl.purchase_date between prod_dtl.sdate and prod_dtl.edate)
14 where prod_dtl.product_name is null
15 order by pur_dtl.product_name, pur_dtl.purchase_date
16 /
PURCHASE_ID PRODUCT_NA PURCHASE_ ERROR100 ERROR200
----------- ---------- --------- --------- ---------
2 B 01-JUN-09 31-DEC-10
4 B 01-JUN-11 01-JAN-10
6 C 01-JUN-11
8 D 01-JUN-12
use a case or nvl to format your error 100 and error 200 messages.
"... first off, you are missing a table ..."
Sokrates, June 29, 2012 - 4:43 am UTC
@Tom,
you wrote
first off, you are missing a tableI think, A Reader is missing much more than this:
I can't see any primary key, foreign key nor unique constraint.
So, the answer should be quite simple:
"uncomplete input - datamodel unknown, no look" ?
@A Reader
are there not any of the above constraints ?
I think, Toon Koppelaars "RuleGen" framework
(
http://www.rulegen.com/ ) is able to do what you want
in a declarative manner meaning that it writes packages/triggers for you to enable and validate your complex constraint.
You may check it out !
(Though I'm pretty sure it also needs a complete data model, which at least should contain pks and fks )
relationships and joins
Satya, June 29, 2012 - 10:56 am UTC
Hi Tom,
Could you please explain or direct me to the correct documentation? I wanted to know how to evaluate join conditions based on 1:1 1:M or M:M relationships. I am not really sure if these has something to do with the joins while writing sql queries.
July 02, 2012 - 6:44 am UTC
I don't know what you mean by "how to evaluate join conditions..."
what are you looking for exactly?
error handling
Sokrates, June 29, 2012 - 11:54 am UTC
At this point, I really have to say I don't like your error handling. You say you want "two errors". But there are not two errors, you simply have an insert or update of a row that doesn't fall into a valid range.
absolutely
without this error handling we would only query for
select
pude.*
from purchase_detail pude
where not exists(
select null
from product_detail prde
where prde.product_name = pude.product_name
and pude.purchase_date between nvl(prde.start_date, date'1900-01-01') and nvl(prde.end_date, date'3000-01-01')
)
which seems to be much cheaper than your query given as a reply to
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:31036568157622#5133874300346550802
regarding "Followup June 28, 2012 - 11pm Central time zone"
Sokrates, June 30, 2012 - 11:01 am UTC
Hallo Tom,
ok, it seemed you answered much more than "A Reader" wanted to know, but anyway.
...
But think about it. If you
a) delete or update product detail you must prevent any modifications to the product you are modifying from being touched in purchase_detail. If you insert into product detail, you don't have to worry about purchase detail at that point.
b) insert or update purchase detail, you must avoid any modifications to that product in product_detail. If you are deleting from purchase detail - you are OK, no locking is needed.
So, the first line in both stored procedures to insert/update will be:
select * from product where product_name = <that product> FOR UPDATE;
to serialize the next steps. Any insert or update of these two tables will lock that row *first*, to ensure that no one else is modifying that product information.
...couldn't we do with less locking ?
surely, a. and b. are correct, but I don't see the necessity of locking "the whole product" in both cases.
in
procedure ins_or_upd_purchase_detail
( p_id in purchase_detail.purchase_id%type,
p_product_name in purchase_detail.product_name%type,
p_purchase_date in purchase_detail.purchase_date%type
)
, wouldn't it be sufficient to
select *
from product_detail prde
where prde.product_name = p_product_name
and p_purchase_date between nvl(prde.start_date, date'1900-01-01') and nvl(prde.end_date, date'3000-01-01')
for update
( and, in case of insert, raising an exception when no_data_found ),
thus allowing more than one user at a time inserting or updating different purchases for the same product ?
July 02, 2012 - 7:06 am UTC
that would work too - you just need to make sure that the valid ranges you are reliant on are neither modified nor deleted.
is there data type only for month and day
A reader, July 02, 2012 - 4:29 am UTC
Hi Tom,
At your very first reply, you mentioned that is a 'strange' type for MONTH column, it should be a date type.
I understood your point, but once I got a question from my friend asking whether we can just store 'month' or 'day'?
I know we can use trunc or to_date to do it, just curious why not we have data type like 'month' or 'day', instead we use 'date' to represent all of them?
thanks.
July 02, 2012 - 8:17 am UTC
because we have dates and there are no more specific type than that. It is just the way it is.
relationships and joins
satya, July 02, 2012 - 9:45 pm UTC
Sorry for the confusion Tom. I am a novice developer learning how to write SQL's.
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:8764517459743 Optimize the query October 6, 2005 - 7pm Central time zone
Reviewer: A reader
In the above link, "A reader" asked you a question regarding optimizing the query for which you replied "hard to say - what is the relationship between "A" and the tables chan, dn, tsg, ckt - 1:1 or 1:m? "
Once you got the relationships you suggested the optimized query.
Not sure how to correlate relationships with the joins. Any explanation would be greatly appreciated.
Thank you very much Tom.
July 03, 2012 - 8:25 am UTC
I'm not sure what to tell you either. I don't really understand what it means to you to "correlate relationships with the joins"
since you
a) join to materialize a 1:1 relation
b) join to materialize a 1:m relation
If the relation is 1:1 you can sometimes use techniques - such as scalar subqueries - to 'optimize' the query for initial response time - but that is rare and useful only in very very specific situations (initial response time has to be paramount)
Regarding deadlock
A reader, July 09, 2012 - 6:15 am UTC
Hi Tom,
I am getting error ORA-00054:resource busy and acquire with nowait specified.
I am getting this error when I am inserting values into one table in a for loop and after closing the loop,I am trying to truncate table using execute immediate 'truncate table xyz'.
Please can you provide a solution to this problem.
Thanks In Advance.
July 11, 2012 - 1:10 pm UTC
hah, simple answer:
no
longer answer:
of course not
reason:
there is nothing here. You don't tell us what is happening in the database in other sessions. You don't tell us even what statement gets the ora-54
and ora-54 is NOT a deadlock.
I can guess though.
ora-54 is happpening on the truncate.
the truncate can only happen if no other sessions have outstanding transactions against the table
there are outstanding transactions
hence the truncate cannot work.
which would beg the question: why are you truncating a table others are modifying? it doesn't make sense.
Related to xml
A reader, July 16, 2012 - 3:52 am UTC
- <Document>
- <BkToCstmrStmt>
- <GrpHdr>
<MsgId />
<CreDtTm />
- <MsgPgntn>
<PgNb />
<LastPgInd />
</MsgPgntn>
</GrpHdr>
- <Stmt>
<Id>LT884010040300151524</Id> ----same value
<CreDtTm />
- <FrToDt>
<FrDtTm>21-03-12</FrDtTm>
<ToDtTm>21-03-12</ToDtTm>
</FrToDt>
- <Acct>
- <Id>
LT884010040300151524 ----same value(this column is not mapped but getting value)
- <Othr>
<Id>LT884010040300151524</Id> ----same value
</Othr>
</Id>
- <Ownr>
<Nm>FINPETROL</Nm>
</Ownr>
- <Svcr>
- <FinInstnId>
AAAA BANKEN
<Nm>FINPETROL</Nm>
- <PstlAdr>
<Ctry>SE</Ctry>
</PstlAdr>
</FinInstnId>
</Svcr>
</Acct>
- <Bal>
- <Tp>
- <CdOrPrtry>
<Cd>I08I08</Cd>
</CdOrPrtry>
</Tp>
<Amt>500000</Amt>
<CdtDbtInd>C</CdtDbtInd>
- <Dt>
29-11-1129-11-11
<Dt>29-11-1129-11-11</Dt>
</Dt>
</Bal>
- <Bal>
- <Tp>
- <CdOrPrtry>
<Cd>I08I08</Cd>
</CdOrPrtry>
</Tp>
<Amt>500000</Amt>
<CdtDbtInd>C</CdtDbtInd>
- <Dt>
29-11-1129-11-11
<Dt>29-11-1129-11-11</Dt>
</Dt>
</Bal>
- <Ntry>
<Amt>500000</Amt>
<CdtDbtInd>C</CdtDbtInd>
<Sts />
- <BookgDt>
<DtTm>29-11-11</DtTm>
</BookgDt>
- <ValDt>
<Dt>29-11-1129-11-11</Dt>
</ValDt>
<AcctSvcrRef>AAAASESS-FP</AcctSvcrRef>
- <BkTxCd>
- <Domn>
<Cd>I08I08</Cd>
- <Fmly>
<Cd>I08I08</Cd>
<SubFmlyCd />
</Fmly>
</Domn>
</BkTxCd>
- <NtryDtls>
- <TxDtls>
- <Refs>
<EndToEndId>LTUH280LTL 00002</EndToEndId>
</Refs>
- <RltdPties>
- <Dbtr>
<Nm>FINPETROL</Nm>
</Dbtr>
</RltdPties>
</TxDtls>
</NtryDtls>
</Ntry>
- <Ntry>
<Amt>500000</Amt>
<CdtDbtInd>C</CdtDbtInd>
<Sts />
- <BookgDt>
<DtTm>29-11-11</DtTm>
</BookgDt>
- <ValDt>
<Dt>29-11-1129-11-11</Dt>
</ValDt>
<AcctSvcrRef>AAAASESS-FP</AcctSvcrRef>
- <BkTxCd>
- <Domn>
<Cd>I08I08</Cd>
- <Fmly>
<Cd>I08I08</Cd>
<SubFmlyCd />
</Fmly>
</Domn>
</BkTxCd>
- <NtryDtls>
- <Btch>
<MsgId />
<PmtInfId />
<NbOfTxs />
</Btch>
</NtryDtls>
</Ntry>
- <Ntry>
<Amt>500000</Amt>
<CdtDbtInd>C</CdtDbtInd>
<Sts />
- <BookgDt>
<DtTm>29-11-11</DtTm>
</BookgDt>
- <ValDt>
<Dt>29-11-1129-11-11</Dt>
</ValDt>
<AcctSvcrRef>AAAASESS-FP</AcctSvcrRef>
- <BkTxCd>
- <Domn>
<Cd>I08I08</Cd>
- <Fmly>
<Cd>I08I08</Cd>
<SubFmlyCd />
</Fmly>
</Domn>
</BkTxCd>
- <NtryDtls>
- <TxDtls>
- <Refs>
<InstrId />
<EndToEndId>LTUH280LTL 00002</EndToEndId>
</Refs>
- <AmtDtls>
- <CntrValAmt>
<Amt>500000</Amt>
- <CcyXchg>
<SrcCcy>EUR</SrcCcy>
<XchgRate>.1085</XchgRate>
</CcyXchg>
</CntrValAmt>
</AmtDtls>
</TxDtls>
</NtryDtls>
</Ntry>
</Stmt>
</BkToCstmrStmt>
</Document>
i'm getting same value for xml creation,can u give solution for this please...
July 16, 2012 - 3:54 pm UTC
thanks much for the laugh
XML
Jim, July 16, 2012 - 10:43 pm UTC
When asktom 1st started came to learn, it's become so much more. Tom you have amazed me you work out what people are asking good to see a few still pull you up...
Enjoyed this one as much the Zibra
copy data from one schema to other
Viknesh, July 24, 2012 - 7:20 am UTC
copy data from one schema to other in neoview
spliting rows
A reader, September 25, 2012 - 1:32 pm UTC
In the similar line.
@OracleAskTom I have a table with following structure
start_date, end_date, total
08/01/2012, 08/20/2012, 400
I want to split the rows into 20 rows, which is one row per day and spread the total for each day. Output should look like the following.
start_date, end_date, total
08/01/2012, 08/02/2012, 20
08/02/2012, 08/03/2012, 20
..
08/18/2012, 08/19/2012, 20
08/19/2012, 08/20/2012, 20
Can you please give me an idea?
September 27, 2012 - 8:25 am UTC
I see Michel beat me too it below ;)
Splitting
Michel Cadot, September 27, 2012 - 3:04 am UTC
SQL> create table t (id integer, nb integer);
Table created.
SQL> insert into t values (1,100);
1 row created.
SQL> insert into t values (2,57);
1 row created.
SQL> select * from t;
ID NB
---------- ----------
1 100
2 57
2 rows selected.
SQL> select id, least(20,nb-20*column_value) nb
2 from t,
3 table(cast(multiset(select level-1 from dual
4 connect by level <= ceil(nb/20))
5 as sys.odcinumberlist))
6 /
ID NB
---------- ----------
1 20
1 20
1 20
1 20
1 20
2 20
2 20
2 17
8 rows selected.Regards
Michel
Splitting (bis)
Michel Cadot, September 27, 2012 - 3:13 am UTC
SQL> create table t (start_date date, end_date date, total integer);
Table created.
SQL> insert into t values (to_date('08/01/2012','MM/DD/YYYY'),to_date('08/05/2012','MM/DD/YYYY'),193);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t;
START_DAT END_DATE TOTAL
--------- --------- ----------
01-AUG-12 05-AUG-12 193
1 row selected.
SQL> select start_date+column_value start_date,
2 start_date+column_value+1 end_date,
3 least(ceil(total/(end_date-start_date)),
4 total-ceil(total/(end_date-start_date))*column_value)
5 total
6 from t,
7 table(cast(multiset(select level-1 from dual
8 connect by level <= (end_date-start_date))
9 as sys.odcinumberlist))
10 /
START_DAT END_DATE TOTAL
--------- --------- ----------
01-AUG-12 02-AUG-12 49
02-AUG-12 03-AUG-12 49
03-AUG-12 04-AUG-12 49
04-AUG-12 05-AUG-12 46
4 rows selected.Regards
Michel
RICH TEXT EDITOR
HAMEED, October 18, 2014 - 4:23 pm UTC
SELECT EDITORS FROM REP_RAY;
EDITORS
--------------------------------------------------------------------------------
l\fcharset0 Times New Roman;}}
{\colortbl ;\red0\green0\blue0;}
\viewkind4\uc1\pard\ltrpar\sb240\sa60\lang1033\ul\b\i\f0\fs30 The thin slices in
T1 and T2 weighted images sagittal, transverse and coronal shows:-
\par \pard\ltrpar\sl276\slmult1\tx180\tx1440\tx2758\cf1\ulnone\b0\i0\fs28 Normal
ventricular system with no shift midline structures.
\par No cerebral intra-axial or extra-axial mass lesion.
\par Normal sellar, para sellar and suprasellar region.
\par No extra-axial collection.
\par \pard\ltrpar\sl276\slmult1\tx180\tx1440\tx2758\tqr\tx9070 Normal appearance
of brain stem and cerebellum.\tab