Crystal报表无法从数据库检索数据 [英] Crystal report failed to retrieve data from database

问题描述

Crystal报表中是否存在错误，问题在于自oracle以来他无法恢复数据，我在oracle上进行了查询，但是当我将其发布到命令中时，起初它运行良好，但稍后向我显示此错误，请有人可以帮助我 这是查询

Is there an error in crystal report the problem is that he can not recover the data since oracle, I made a query that is workable on oracle but when I post it on command, at first it works well but after a moment he shows me this error, please someone can help me and this is the query

select to_char(to_date('1970-01-01 00','yyyy-mm-dd hh24') + (time)/60/60/24 
, 'YYYY-MM-DD') datestr,
L.LOGINID,
L.STATUS,
L.TIME,
O.PRESENTATION_NAME,

 N_CALLSANSWERED_
 from "REP_STAT_DB"."LOGIN"  L
           join "GCTI_DMART"."O_AGENT_DAY"  O on 
  L.AGENTDBID=O.CONFSERVER_OBJ_ID
           join "GCTI_DMART"."R_AGENT_DAY"  R on O.OBJECT_ID=R.OBJECT_ID
        join "GCTI_DMART"."T_AGENT_DAY" T on T.TIME_KEY=R.TIME_KEY AND 
    T.BEGIN_TIME=to_char(to_date('1970-01-01 00','yyyy-mm-dd hh24') + 
  (time)/60/60/24 , 'DD/MM/YY')
    where   O.DELETE_TIME IS NULL

无法检索数据:

推荐答案

将日期和时间戳记不存储在适当的数据类型中，而是存储在数字中是一个不好的主意.这正好导致您面临的问题.

It is a bad idea to store dates and timestamps not in appropriate data types, but in numbers instead. Such leads exactly to the problems you are facing.

您的错误在这里:

T.BEGIN_TIME = to_char(to_date('1970-01-01 00','yyyy-mm-dd hh24') +
               (time)/60/60/24 , 'DD/MM/YY')

T.BEGIN_TIME的类型为DATE. TO_CHAR(...)，但是是字符串.因此，Oracle会转换您的数据以便比较两者.它将您的字符串转换为DATE，以便比较两个日期.您的字符串包含格式为"DD/MM/YY"的日期. Oracle会根据会话设置尝试以某种方式解释它.您收到错误，因为此操作失败.我想您的会话设置建议日期以月份开头，因此"13/02/17"被解释为"2017-13-02"，其中包含无效的月份.

T.BEGIN_TIME is of type DATE. TO_CHAR(...), however is a string. So Oracle converts your data in order to compare the two. It converts your string to DATE so as to compare two dates. Your string contains a date in the format 'DD/MM/YY'. Depending on session settings Oracle tries to interpret it somehow. You are getting the error because this fails. I suppose your session setting suggests that a date starts with the month, so '13/02/17' gets interpreted as '2017-13-02', which contains an invalid month.

要使用日期时间时不要使用字符串.为了获得时间戳记的日期部分，请改用TRUNC.

Don't use strings when you want to work with datetimes. In order to get the date part of a timestamp, use TRUNC instead.

查询已更正:

select 
  to_char(date '1970-01-01' + interval '1' second * time, 'yyyy-mm-dd') datestr,
  l.loginid,
  l.status,
  l.time,
  o.presentation_name,
  n_callsanswered_
from rep_stat_db.login  l
join gcti_dmart.o_agent_day o on l.agentdbid = o.confserver_obj_id
join gcti_dmart.r_agent_day r on o.object_id = r.object_id
join gcti_dmart.t_agent_day t
       on t.time_key = r.time_key
       and t.begin_time = trunc(date '1970-01-01' + interval '1' second * time)
where o.delete_time is null;

如果您将时间戳记存储在DATE中而不是自"1970-01-01 00:00:00"以来的秒数中，则查询甚至会更加简单.如前所述，不使用适当的数据类型(在这种情况下为DATE，这是Oracle的datetime数据类型，即TIMESTAMP)是个坏主意.

If you stored the timestamp in a DATE instead of seconds since '1970-01-01 00:00:00', the query would even be simpler. As mentioned, it's a bad idea, not to use the appropriate datatype (DATE in this case, which is Oracle's datetime data type, or TIMESTAMP).

这篇关于Crystal报表无法从数据库检索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！