3 Replies Latest reply: Apr 22, 2013 4:21 PM by Frank Kulash RSS

    query returning zero results for yesterdays same hour

    k1ng87
      I have the following query:

      SELECT
      d_dtm,

      BTS_ID,
      CASE WHEN D_DTM = (D_DTM-24/24)
      THEN sum(V_ATT_CNT)

      END AS "LASTATT",

      sum(V_ATT_CNT) as "V_ATT_CNT",

      CASE WHEN D_DTM = D_DTM
      THEN sum(V_ATT_CNT)

      END AS "ATT"

      FROM
      DMSN.DS3R_FH_1XRTT_FA_LVL_KPI

      WHERE
      to_date(D_DTM, 'DD/MM/yyyy') >= (SELECT TO_DATE(max(D_DTM),'DD/MM/YYYY') FROM DMSN.DS3R_FH_1XRTT_FA_LVL_KPI)-2

      GROUP BY
      d_dtm,

      BTS_ID

      having
      CASE WHEN D_DTM = (D_DTM-24/24)
      THEN sum(V_ATT_CNT)

      END > 0

      But it is not returning any results because of the "having" clause. I know it should return results because all I want it to do is in one column have the V_ATT for the current time period, and in the 2nd column, have the V_ATT 24 hours ago. I've checked the data and I should get results back but can't seem to figure out why this is not working...

      Edited by: k1ng87 on Apr 22, 2013 1:03 PM
        • 1. Re: query returning zero results for yesterdays same hour
          sb92075
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: query returning zero results for yesterdays same hour
            k1ng87
            confused on what I'm missing here...I'm using version 11g, not sure if that matters for this ? though....
            • 3. Re: query returning zero results for yesterdays same hour
              Frank Kulash
              Hi,
              k1ng87 wrote:
              I have the following query:

              SELECT
              d_dtm,

              BTS_ID,
              CASE WHEN D_DTM = (D_DTM-24/24)
              THEN sum(V_ATT_CNT)

              END AS "LASTATT",
              ...
              You may have noticed that this site normally doesn't display multiple spaces in a row.
              Whenever you post formatted text (including, but not limited to, code) on this site, type these 6 characters:

              \
              (small letters only, inside curly brackets) before and after each section of formatted text, to preserve spacing.
              That's just one of many useful things found in the forum FAQ {message:id=9360002}   
              
              But it is not returning any results because of the "having" clause.
              You're right:
              HAVING CASE
                   WHEN d_dtm = (d_dtm - 24/24)
                   THEN SUM (v_att_cnt)
                   END > 0
              Whatever d_dtm is, it's not d_dtm - 24/24, so the WHEN condition will never be TRUE.  That means the CASE expression will always return NULL, and NULL is not greater than 0, so the HAVING condition will never be TRUE.
              
              k1ng87 wrote:
              confused on what I'm missing here...I'm using version 11g, not sure if that matters for this ? though....
              There is no version 11f or 11h, so it's kind of silly to say you're using 11g.  Why not give your actual version number, like 11.2.0.2.0?  Sometimes, the part after 11 makes a huge difference. But probably not in this case, as you said.   What's more important is for you to post CREATE TABLE and INSERT statements for a little sample data, and the results you want from that sample data. No kidding; see the forum FAQ {message:id=9360002}.