s******y
发帖数: 352 | 1 %let somedt1=21APR2011;
%let somedt2=01Jun2011;
%put current session date: &sysdate;
%put &somedt1. > &sysdate.? %eval(%sysfunc(inputN(&somedt1,date9))>%sysfunc
(inputN(&sysdate.,date9)));
%put &somedt2. > &sysdate.? %eval(%sysfunc(inputN(&somedt2,date9))>%sysfunc
(inputN(&sysdate.,date9)));
BAOZI please!! |
|
c***c
发帖数: 6234 | 2 谢谢。google了pivot table
其实就是用case when then else end 语句。还是query不熟。最后我写成
select count(case when (nvl(t.resolved_time, nvl(t.close_time,sysdate))- t.open_time) <= 4 then t.id else null end) as "<4",
count(case when (nvl(t.resolved_time, nvl(t.close_time,sysdate))- t.open_time) >4 and (nvl(t.resolved_time, nvl(t.close_time,sysdate))- t.open_time) <7 then t.id else null end) as "4-7",
count(case when (nvl(t.resolved_time, nvl(t.close_time,sysdate))- t.open_time) >=7 and (nvl(t.resolved_time, nvl(t.c |
|
y****9
发帖数: 144 | 3 Sorry, don't understand your reply.Don't know how to do it without upper
limit, but if to find next 10 biz days in 2012, I can do this as follows:
SQL>select mydate
2 from
3 (
4 select to_char(sysdate + level, 'YYYY-Mon-DD') mydate, level
5 from dual
6 where mod(to_number(to_char(sysdate + level, 'YYYYMMDD')),3) = 0 --
equivalent to isBIzDay(sysdate+level) =1
7 connect by level < to_date('2012-12-31','YYYY-MM-DD') - sysdate
8 )
9 where rownum <=10;
MYDATE
-----------
2... 阅读全帖 |
|
x****g
发帖数: 4 | 4 1. Oracle 里单引号怎么输入? (这个问题好象以前有人问过, 我找不到了)
2. 怎么让 to_char(sysdate,...) 打印出这样的格式: Thu Dec 14 19:03:24 SGT 2000
就是 'DY Mon dd HH24:MI:SS ??? YYYY', Oracle 有时区的支持吗?
3. 如果 Oracle 没有时区支持, 我直接把 SGT,GMT 等字打上去, 怎么打?
文档里写单引号就可以了, 可我怎么也试不出来.
SQL> select to_char(sysdate,'DY Mon dd HH24:MI:SS ''''SGT'''' YYYY') from dual;
select to_char(sysdate,'DY Mon dd HH24:MI:SS ''''SGT'''' YYYY') from dual
*
ERROR at line 1:
ORA-01821: date format not recognized
只有这个能用:
select concat(concat(to_cha |
|
o**********a
发帖数: 330 | 5 select *
from mytable
where case when select to_char(sysdat,'day')='sunday' then mydate > sysdate-
5 else mydate > sysdate-3 end;
i tried this, but it doesn't work. Do you know what happened here |
|
y****9
发帖数: 144 | 6
Thanks. Learned sth new today.
SQL>select to_char(sysdate + level, 'YYYY-Mon-DD') mydate
2 from dual
3 where mod(to_number(to_char(sysdate + level, 'YYYYMMDD')),3) = 0 --
equivalent to isBIzday(sysdate+level) =1
4 connect by rownum <=15
5 ;
MYDATE
-----------
2012-Jan-09
2012-Jan-12
2012-Jan-15
2012-Jan-18
2012-Jan-21
2012-Jan-24
2012-Jan-27
2012-Jan-30
2012-Feb-02
2012-Feb-05
2012-Feb-08
2012-Feb-11
2012-Feb-14
2012-Feb-17
2012-Feb-20
15 rows selected. |
|
B*****g
发帖数: 34098 | 7 主要是处理不是number的比较烦，oracle 11g写了一个，纯属娱乐
***只有一列***
WITH tmp AS
(SELECT XMLTYPE (
DBMS_XMLGEN.getxml ('SELECT 1, ''2'', ''3.0'', '' 4.2 '',
sysdate, ''abcd'' FROM DUAL'
)
) myxml
FROM DUAL)
SELECT SUM(v)
FROM tmp a,
XMLTABLE ('/ROWSET/ROW/*' PASSING myxml
COLUMNS v VARCHAR2(1000) PATH '.')
WHERE REGEXP_LIKE(TRIM(v), '^[0-9]\d{0,2}(\.\d{1,2})?%?$')
GROUP BY 1
***整个table***
WITH tmp AS
(SELECT ROWNUM rn,
... 阅读全帖 |
|
p****s
发帖数: 3184 | 8
This is actually a general SQL question. Just repeat ' once.
It seems no.
use || operator may be better
to_char(sysdate,'DY Mon dd HH24:MI:SS ')||'SGT'||to_char(sysdate,' YYYY')
or use DECODE expression to convert timezone numbers to names, and vice versa. |
|
m****d
发帖数: 372 | 9 嘿嘿，有点问题，v$session
如果1秒采样没有遗漏任何sql的话
SELECT sql_text
FROM v$sqlarea
where sql_id
in (select distinct sql_id
from v$active_session_history
where sample_time > sysdate-1/24);
下面这个sql, 不知道我对 LAST_ACTIVE_TIME理解有没有错
SELECT sql_text
FROM v$sqlarea
where LAST_ACTIVE_TIME > sysdate-1/24; |
|
o**********a
发帖数: 330 | 10 select *
from mytable
where mydate > sysdate-3
现在的问题是, 这个query是要过去三天的data. 我需要改进成要过去三个business
day的data. for example
如果今天是星期一
我的query应该是
select *
from mytable
where mydate > sysdate-5
问题是我如何能知道今天是星期几?
多谢 |
|
g*******r
发帖数: 1414 | 11 数据库新手，请轻拍
想给现在的几个scheduling的模块设计几个Oracle Materialized View，没什么具体的
要求，因为现在的也工作正常，但是觉得不优化（因为数据直接写入table，不是很必
要）
现在想把table中的一些经常需要scheduler去写入的field去掉，抓出来做view。但是
几乎每20秒要IO一次，所以觉得用普通的view不是很好的design。这个view基本是如下
数据表大小做inner join
100*500*3000*30*50
然后aggregation去SUM，还要用到DECODE，加上where 后面四个 and condition
研究Materialized View，现在有一吨问题：
1. 如何判断是否需要QUERY REWRITE
2. 如果想每三分钟refresh一次，这么写有何不妥？
BUILD IMMEDIATE REFRESH FAST START WITH (sysdate) NEXT (sysdate+3*60/(60*60*
24)) WITH rowid AS
3. 建Materialized View... 阅读全帖 |
|
c*****d
发帖数: 6045 | 12 对mview了解不多，一起讨论
1. 如何判断是否需要QUERY REWRITE
[ ] 你是要判断SQL是否REWRITE吗？如果是这个意思，用explain plan或者DBMS_MVIEW
.EXPLAIN_REWRITE就可以看到
2. 如果想每三分钟refresh一次，这么写有何不妥？
BUILD IMMEDIATE REFRESH FAST START WITH (sysdate) NEXT (sysdate+3*60/(60*60*
24)) WITH rowid AS
[ ] 语法没问题，就是refresh太频繁了，除非你们业务有这个需要，我们是一天
refresh一次，oltp交易流水给DW作分析
3. 建Materialized View，加不加primary key有什么区别？如果原table的field就有
了index，在MV里就不存在了？
[ ] 你理解错了，这个PK不是mview上的PK，用来判断数据变化
是基于table上的PK
mview要么基于PK，要么基于rowid
4. 当缺乏大数据库做测试的情况下，使用Materialized View TUN... 阅读全帖 |
|
m******u
发帖数: 12400 | 13 个人觉得这个题目出的不好（比如第二题），answer key 也不算怎样，第一题就可见
一斑。
。。。。。。。。。。。。。。
Table 1 : DEPT
DEPTNO (NOT NULL , NUMBER(2)), DNAME (VARCHAR2(14)),
LOC (VARCHAR2(13)
Table 2 : EMP
EMPNO (NOT NULL , NUMBER(4)), ENAME (VARCHAR2(10)),
JOB (VARCHAR2(9)), MGR (NUMBER(4)), HIREDATE (DATE),
SAL (NUMBER(7,2)), COMM (NUMBER(7,2)), DEPTNO (NUMBER(2))
MGR is the empno of the employee whom the employee reports to. DEPTNO is a
foreign key.
QUERIES
1. List all the employees who have at least one person reporting to them.
2. L... 阅读全帖 |
|
w**********g
发帖数: 48 | 14 我觉得A对啊，为什么不是A,B 而是D呢
我电脑跑出来是这样的，是不是我电脑用的破解版出问题啊
561 %put "today is %sysfunc(today(),worddate.)";
"today is August 7, 1993"
562 %put "today is: &sysdate.";
"today is: 07AUG10"
563 %put "today is: &sysdate9.";
"today is: 07AUG2010"
Item 12 of 63 Mark item for review
Which title statement would always
display the current date?
A.
title "Today is: &sysdate.";
B.
title "Today is: &sysdate9.";
C.
title "Today is: &today.";
D.
title "Today is: %sysfu |
|
t********1
发帖数: 799 | 15 I want to select the file in which the date is a part of name, for example "
rain_08feb11", from "filename" column everyday, now i use
where filename like '%_0_&sysdate';
it doesn't work, &sysdate can not be converted 08feb11, Then how can I do
this?
Thanks. |
|
w*****e
发帖数: 806 | 16 macro processor doesn't resolve macros inside single quotation makrs...
please use double quotation marks.
'%_0_&sysdate'----------"%_0_&sysdate" |
|
e*****3
发帖数: 610 | 17 实际上还有另外一个COLUMN来HANDLE这个CASE的。不过为了简化问题，你可以认为
LOGOUTTIME为空并且LOGINTIME - SYSDATE 》 8 HOURS的SESSION不计为一个
SESSION。 |
|
|
B*****n
发帖数: 135 | 19 select old_date+(1/24/60)*10 from dual;
the above sql will add 10 minutes to 'old_date'. For instance:
SQL> select to_char(sysdate, 'MM-DD-YYYY HH24:MI:SS') "current time" from dual;
current time |
|
w*********n
发帖数: 84 | 20 Or, simply use SYSDate instead of 'login'.
I think it can also works. |
|
s*k
发帖数: 144 | 21
SELECT TO_CHAR(SYSDATE,'DD/MON/YYYY') FROM sys.dual;
hope that works |
|
G***o
发帖数: 5158 | 22 i connect using the client using internal/oracle
upon login i got
"Connected to an idle instance."
after i tryped
"select sysdate from dual"
ORA-01034: ORACLE not available
i checked the helpd document about this error code,
it says:
ORA-01034 ORACLE not available
Cause: Oracle was not started. Possible causes include the following:
The SGA requires more space than was allocated for it.
The operating-system variable pointing to the instance is improperly |
|
b****e
发帖数: 1275 | 23 why don't you just create a column in the archive table that has
default set to sysdate? |
|
b****e
发帖数: 1275 | 24 alter table borrowerarchive add (delete_date date default sysdate) |
|
g*******l
发帖数: 6 | 25 I am exporting a subset of records in two tables into a file. The command i
use
is:
exp usr/pass@db file=test.dat log=test.log tables=\(table1, table2\)
query=\"where the_time\
The command was running but it gave follow error:
"
Export: Release 9.2.0.4.0 - Production on Tue Sep 14 07:10:52 2004
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Expor |
|
B*****g
发帖数: 34098 | 26 first time see c++ connect database, haha.
for 3. There is no empty string in oracle, or you can say empty string is
null.
You can confrim this by:
SELECT CASE WHEN '' IS NULL THEN 'empty string is NULL'
ELSE 'empty string is NOT NULL'
END AS RESULT
FROM DUAL
Also try
SELECT ROUND ((SYSDATE - TO_DATE ('01/01/1970', 'mm/dd/yyyy')) * 3600 * 24)
FROM DUAL; |
|
B*****g
发帖数: 34098 | 27 SELECT h.user_id,
s2.username,
s1.sql_text,
FROM v$active_session_history h,
v$sqlarea s1,
v$session s2
WHERE h.sample_time > sysdate-1/24
AND h.sql_id = s1.sql_id
AND h.user_id = s2.user_id |
|
B*********L
发帖数: 700 | 28 我用openquery从oracle取数据
SELECT * FROM OPENQUERY(LINKSERVER,'select SYSDATE from tableA')
这个query在ssms里可以执行
放到stored procedure里
exec sp_test
在ssms里也可以执行
现在我把这个stored procedure放到job里面schedule就要如下的error：
Message
Executed as user: YYYY\XXX. Access to the remote server is denied because
the current security context is not trusted. [SQLSTATE 42000] (Error 15274).
The step failed.
谁知道怎么办？谢谢！ |
|
c**t
发帖数: 2744 | 29
procedure Foo(pResult OUT type_return_cursor)
as
type MyRow is object (
f1 varchar2(10),
f2 NUMBER,
f3 Date);
type MyTbl is VARRAY(10) of MyRow;
myTbl MyTbl;
stmt varchar2(5000) := '';
begin
myTbl := MyTbl();
for i IN 1..10 loop
myTbl(i).f1 := 'string of ' || to_char(i);
myTbl(i).f2 := i;
myTbl(i).f3 := trunc(sysdate, 'HH24') + i / 12;
end loop;
for i in myTbl.First..myTbl.Last
Loop
if i > 1 then stmt := stmt || CHR(10) || 'UNION' || CHR(10); e |
|
t********a
发帖数: 2402 | 30 select (sysdate-to_date('1970-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) *
86400 from dual
软件是mybasev 5.5。 后面日期帮忙弄成2020年12月12日好了。谢谢 |
|
|
B*****g
发帖数: 34098 | 32 when后面没有select
sysdate- |
|
B*****g
发帖数: 34098 | 33 我一开始就这么写，结果oracle啃爹了，大家看看下面为啥不work？我简化了一下
with t(d,n) as
(
select sysdate, 1
from dual
union all
select t.d+1, t.n +1
from t
where t.n < 10
)
select * from t
下面这个work，bug？
with t(d,n) as
(
select 1, 1
from dual
union all
select t.d+1, t.n +1
from t
where t.n < 10
)
select * from t
then
then |
|
y****9
发帖数: 144 | 34
I asked the below question in Oracle-L, let's if any useful info can be
obtained during weekend.
---
Don't understand why the below query does not give me the next 10 days
instead get past 10 days. ( also noticed without the cast it does not work
at all)
SQL> select * from v$version
2 ;
BANNER
----------------------------------------------------------------------------
----
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
COR... 阅读全帖 |
|
y****9
发帖数: 144 | 35 Got a reply from Oracle-L
------------------------
Looks like you are hitting Bug 11840579 :-) It looks weird, but if you
reference the date column twice it returns the expected data:
SQL> with t(d,n) as
2 (
3 select cast ( sysdate as date) , 1
4 from dual
5 union all
6 select decode(d,null,d,d +1), n+1
7 from t
8 where n<10
9 )
10 select * from t;
D N
--------- ----------
07-JAN-12 1
08-JAN-12 2
09-JAN-12 3
10-JAN-12 4
11-JAN-12 5
12-JAN-12 6
13-JAN-12 7
14-JA... 阅读全帖 |
|
i*****9
发帖数: 293 | 36 有下面这四个表和下面这几个要求，怎么写比较好能得到我想要的结果? 多谢
1，先选出USERACC_FOR_DOM_REP的所有的值：username, account_status, target_db.
2，再从上面的结果中除掉所有EXCEPTION_ACC的username(条件是EXCEPTION_ACC里的
username和target_db是USERACC_FOR_DOM_REP的)。
3, 再从上面的结果中除掉所有MAX_USER_LOGIN的username(条件是MAX_USER_LOGIN里的
username和target_db是USERACC_FOR_DOM_REP的，外加LAST_LOGIN < sysdate -90)。
4, 再从上面的结果中除掉所有MYTABLE的username(条件是MYTABLE里的username和
target_db是USERACC_FOR_DOM_REP的
SQL> desc USERACC_FOR_DOM_REP
Name Null? Type
... 阅读全帖 |
|
y****9
发帖数: 144 | 37 Googled and tested for a while, comp up with this method:
select cast(datepart(yy, getdate()) as varchar) + '-' +
cast(datepart(mm, getdate()) as varchar) + '-' +
cast(datepart(dd, getdate()) as varchar) + ' ' +
cast(datepart(hh, getdate()) as varchar)
Not sure this is the best idea in SQL Server. in Oracle it is easy:
select to_char('YY-MM-DD HH24', sysdate) from dual;
any suggestion? I am working on a query which need this conversion
Thanks, |
|
r****5
发帖数: 618 | 38 Given the car number and the original purchase price (values that will have
to be passed as parameters to the function), current market value will be
determined by taking the original purchase price, subtracting 5% of the
original price for each year old that the car is (based on
car_year in the CARS table) and subtracting an extra 1% of the original
price for each time the car needed a repair.
大家帮我看看我的出了什么问题? 谢谢
CREATE OR REPLACE FUNCTION Current_car_value(carnum IN cars.car_num%type,
pur_price... 阅读全帖 |
|
d****n
发帖数: 12461 | 39 Oracle写法
with t0 as
(select
acct_number,status,day,
row_number() over (partition by acct_number order by day) rn
from t)
select
t3.acct_number,t3.status,t3.day+t4.level
from
(select
t1.acct_number,t1.status,t1.day,
nvl(t2.day,trunc(sysdate)+1)-t1.day day_between
from
t0 t1,
t0 t2
where
t1.acct_number=t2.acct_number(+)
and t1.rn+1=t2.rn(+)
) t3
(select level-1 day_delta from dual connect by level<36500) t4
where
t4.day_delta |
|
|
w****n
发帖数: 266 | 41 select custid
from yourtable
where sysdate-orderdate》m
group by custid
having count(custid)》k
tested in oracle |
|
w****n
发帖数: 266 | 42 select custid
from yourtable
where sysdate-orderdate》m
group by custid
having count(custid)》k
tested in oracle |
|
n****f
发帖数: 905 | 43 In Oracle, it will be:
1. Create Index:
CREATE INDEX INDX_TIMESTAMP ON POSITIONS
(TO_CHAR (DATETIME_STAMP,'YYYY_MM_DD'));
2. Query Data:
SELECT *
FROM POSITIONS
WHERE ID =
(SELECT MIN (LOAD_ID)
FROM POSITIONS
WHERE TO_CHAR (DATETIME_STAMP, 'YYYY_MM_DD') =
TO_CHAR (SYSDATE, 'YYYY_MM_DD')) |
|
g*****y
发帖数: 36 | 44 Further optimization
1. create composite index on timestamp and load_id.
create index idx_pos on (DATETIME_STAMP,load_id);
2. Remove functional filter against indexed column.
select * from positions where load_id =
(select min(load_id) from positions where datetime_stamp >=trunc(sysdate)); |
|
a********g
发帖数: 651 | 45 昨天终于考完ADV了，前后复习了1个多月，从本版得到了很大的帮助。前面的几个总结
性的帖子很管用，特别是pricematch 的帖子提到的，我基本上都考到了。
下面具体的说说我考的题目。
SQL：
各种的join都有涉及，和merge，set a, set b 的情况比较。
SQL insert 的格式
IC的使用
SQL中index 和view的基本用法。
Dictionary.table
MACRO:
很多%在一起的结果，有2题
Automacro 中sysday and sysdate的区别
特别要注意的一个考点，这个是我没有在online tutor 中见到的：
Call symput 的用法，其输出什么情况下在local，什么情况在global。
Symput and symputx 的区别，这个自己瞎蒙的。
％let在什么地方，是否影响symput
Efficient:
前人已经说得很全面了，我只想补充几点。
Transpose 的用法
By groupformat
By x notsorted
Array的基本用法
If and select比较
hash object |
|
w**h
发帖数: 10 | 46 刚才发现我的sas里today()返回的日期不对，&sysdate返回是对的。不知道怎么更改？ |
|
L*******d
发帖数: 38 | 47 One more question: Q12
Item 12 of 63 Mark item for review
Which title statement would always
display the current date?
A.
title "Today is: &sysdate.";
B.
title "Today is: &sysdate9.";
C.
title "Today is: &today.";
D.
title "Today is: %sysfunc(today(),worddate.)"; |
|
p****8
发帖数: 50 | 48 第12题答案是D,为啥A，B不对？
Item 12 of 63 Mark item for review
Which title statement would always
display the current date?
A.
title "Today is: &sysdate.";
B.
title "Today is: &sysdate9.";
C.
title "Today is: &today.";
D.
title "Today is: %sysfunc(today(),worddate.)"; |
|
h*e
发帖数: 10233 | 49 thanks thanks. this works.
再问一个问题。怎么把sysdate systime变成一个yymmddhhmm的string.thanks. |
|
s******r
发帖数: 1524 | 50 compress(cat(put("&sysdate"d,yymmdd10.),"&systime"),'-:'); |
|
