Results 1 to 9 of 9

Thread: End of my rope

  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: End of my rope

    I've been getting strange results out of a query, and I'm either too involved to see the easy answer - or something strange is going on.
    Code:
    SQL> SELECT home_id
      2  FROM LOIS.LOS_SITE_OF_RECORD_V SITUS
      3  WHERE home_id IN (299996,167430);
    
       HOME_ID
    ----------
        167430
        167430
        299996
        299996
    
    SQL> CREATE TABLE SITUS AS 
      2  SELECT home_id
      3  FROM LOIS.LOS_SITE_OF_RECORD_V SITUS
      4  WHERE home_id IN (299996,167430);
    
    Table created.
    
    SQL> select * from situs;
    
       HOME_ID
    ----------
        299996
        167430
    Why are only 2 rows appearing in the CTAS table?

    This originated from the strange results I got running the following
    Code:
    SQL> SELECT home_id
      2  FROM LOIS.LOS_SITE_OF_RECORD_V SITUS
      3  WHERE home_id IN (299996,167430);
    
       HOME_ID
    ----------
        167430
        167430
        299996
        299996
    
    SQL> SELECT TEMP.HOME_ID, SITUS.HOME_ID
      2  FROM (SELECT 299996 AS HOME_ID FROM DUAL
      3        UNION ALL
      4        SELECT 299996 AS HOME_ID FROM DUAL
      5        UNION ALL
      6        SELECT 167430 AS HOME_ID FROM DUAL
      7        UNION ALL
      8        SELECT 167430 AS HOME_ID FROM DUAL) TEMP,
      9       LOIS.LOS_SITE_OF_RECORD_V SITUS
     10  WHERE TEMP.HOME_ID = SITUS.HOME_ID (+);
    
       HOME_ID    HOME_ID
    ---------- ----------
        299996     299996
        299996     299996
        167430     167430
        167430     167430
    I expected 8 rows in the output. Please help me from going nutso on this one.
    Thanks,
    Chuck

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Something tells me that LOIS.LOS_SITE_OF_RECORD_V is a view. Care to post the definition?

  3. #3
    Join Date
    Oct 2002
    Location
    greenwich.ct.us
    Posts
    279
    hmm. Something is going on here... Lets see the output from:

    Code:
       SELECT home_id, count(*)
       FROM LOIS.LOS_SITE_OF_RECORD_V SITUS
       WHERE home_id IN (299996,167430)
       GROUP BY home_id
    and

    Code:
    SELECT home_id FROM (
       SELECT home_id
       FROM LOIS.LOS_SITE_OF_RECORD_V SITUS
       WHERE home_id IN (299996,167430)
       )

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    It is a view, and I'm trying to rewrite a report which incorporates this view, as I don't think the view is necessary=
    Code:
    CREATE OR REPLACE VIEW LOIS.LOS_SITE_OF_RECORD_V
    AS 
    SELECT
           A.ID HOME_ID,
           C.ID SITE_ID,
           B.BUSINESS_NAME PARK_NAME,
          C.PARK_LOT_NBR,
          C.PARK_SPACE_NBR,
          C.MAP_TAX_LOT,
          C.ADDRESS1,
          C.ADDRESS2,
          C.CITY,
          C.STATE,
          C.ZIP_CODE,
          C.ZIP4,
          C.DELIVERY_DATE,
          C.REMOVAL_DATE,
          C.CCON_CODE CONTACT_CODE,
          C.SITING_TYPE,
          D.NAME  CONTACT_NAME,
          E.MESSAGE
    FROM
           LOIS.LOS_HOME A,
           LOIS.LOS_BUSINESS B,
           LOIS.LOS_SITUS C,
           LOIS.LOS_COUNTY_CONTACT D,
          (SELECT DISTINCT LOIS.LOS_HOME_ISSUE.HOME_ID,
                                       'Contact BCD for Details' MESSAGE
            FROM LOIS.LOS_HOME_ISSUE
            WHERE (LOIS.LOS_HOME_ISSUE.END_DATE IS NULL OR LOIS.LOS_HOME_ISSUE.END_DATE >= SYSDATE)
            AND LOIS.LOS_HOME_ISSUE.BLOCK_VISIBILITY_FLAG = 'Y') E,
           (SELECT LOIS.LOS_SITUS.ID SITE_KEY,
                          LOIS.LOS_SITUS.HOME_ID
            FROM LOIS.LOS_SITUS
            WHERE LOIS.LOS_SITUS.DELIVERY_DATE IS NULL
            AND LOIS.LOS_SITUS.REMOVAL_DATE IS NULL
            UNION ALL
            SELECT LOIS.LOS_SITUS.ID SITE_KEY,
                         LOIS.LOS_SITUS.HOME_ID
            FROM LOIS.LOS_SITUS
            WHERE LOIS.LOS_SITUS.DELIVERY_DATE IS NOT NULL
            AND LOIS.LOS_SITUS.REMOVAL_DATE IS NULL
            AND LOIS.LOS_SITUS.HOME_ID NOT IN (SELECT LOIS.LOS_SITUS.HOME_ID
                                                                              FROM LOIS.LOS_SITUS
                                                                              WHERE LOIS.LOS_SITUS.DELIVERY_DATE  IS NULL
                                                                             AND LOIS.LOS_SITUS.REMOVAL_DATE IS NULL)) F
    WHERE
            A.ID = C.HOME_ID
            AND C.HOME_ID = F.HOME_ID
            AND C.ID = F.SITE_KEY (+)
            AND A.ID = E.HOME_ID (+)
            AND C.BUS_ID = B.ID (+)
            AND C.CCON_CODE = D.CODE (+)
    and as for the query results
    Code:
    SQL> SELECT home_id, count(*)
      2     FROM LOIS.LOS_SITE_OF_RECORD_V SITUS
      3     WHERE home_id IN (299996,167430)
      4     GROUP BY home_id;
    
       HOME_ID   COUNT(*)
    ---------- ----------
        167430          2
        299996          2
    
    SQL> SELECT home_id FROM (
      2     SELECT home_id
      3     FROM LOIS.LOS_SITE_OF_RECORD_V SITUS
      4     WHERE home_id IN (299996,167430)
      5     );
    
       HOME_ID
    ----------
        167430
        167430
        299996
        299996
    Thanks for your help,
    Chuck

  5. #5
    Join Date
    Oct 2002
    Location
    greenwich.ct.us
    Posts
    279
    Hmmm. So your query is definitely returning two rows (I suspect UNION ALL is the culprit), but CTAS doesn't. I wonder what would happen if you created the table empty and then filled it with a INSERT INTO SELECT ...

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    Am I ready for METALINK, or the insane asylum?

    Code:
    SQL> CREATE TABLE SITUS AS 
      2  SELECT home_id
      3  FROM LOIS.LOS_SITE_OF_RECORD_V 
      4  WHERE 1 = 2;
    
    Table created.
    
    SQL> insert into SITUS
      2  SELECT home_id
      3  FROM LOIS.LOS_SITE_OF_RECORD_V
      4  WHERE home_id IN (299996,167430);
    
    2 rows created.
    
    SQL> SELECT home_id
      2  FROM LOIS.LOS_SITE_OF_RECORD_V
      3  WHERE home_id IN (299996,167430);
    
       HOME_ID
    ----------
        167430
        167430
        299996
        299996
    -cf

  7. #7
    Join Date
    Oct 2002
    Location
    greenwich.ct.us
    Posts
    279
    WTF?? Yeah, looks like a visit to Metalink (The insane asylum) is in order.

  8. #8
    Join Date
    Dec 2003
    Posts
    1,074
    I'll report back with an update, I have a TAR started already.

    This time I'm confident the response will not suggest an upgrade ... right

    -Chuck

  9. #9
    Join Date
    Oct 2002
    Location
    greenwich.ct.us
    Posts
    279
    Quote Originally Posted by chuck_forbes
    This time I'm confident the response will not suggest an upgrade ... right
    Yeah, good luck on that

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •