Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2006
    Posts
    3

    Unanswered: Sub Select Problem

    I am trying the following pass-through query (from Access 2000) to Oracle 8i and am getting an error message (ORA-00936: missing expression). I get the same message using Oracle SQL Plus. Here is my query. What is it missing? Thanks

    SELECT OUPT_PRD.SHIPMENT.SHIPMENT_ID,
    OUPT_PRD.SHIPMENT.PLACE_ID_SHIPD_FRM,
    OUPT_PRD.SHIPMENT.SHIP_TO_KEY_ID1 AS PLACE_ID_SHIPD_TO,
    OUPT_PRD.PLACE.COUNTRY,
    OUPT_PRD.PLACE.WHOS_PLACE,
    OUPT_PRD.SHIPMENT.SHIP_DT,
    OUPT_PRD.SHIPMENT.SHIP_VIA,
    OUPT_PRD.SHIPMENT.WAY_BILL,
    OUPT_PRD.USER_DEF_CODE.UDEF14 AS DOCK_DT,
    TRUNC(SYSDATE-OUPT_PRD.SHIPMENT.SHIP_DT) AS DAYS,
    (SELECT COUNT(*)
    FROM OUPT_PRD.SHIPMENT_DETAIL
    WHERE OUPT_PRD.SHIPMENT_DETAIL.SHIPMENT_ID=OUPT_PRD.SHIP MENT.SHIPMENT_ID) AS PIECE_COUNT


    FROM OUPT_PRD.SHIPMENT,
    OUPT_PRD.PLACE,
    OUPT_PRD.USER_DEF_CODE

    WHERE ((SHIPMENT.PLACE_ID_SHIPD_FRM = 'DFWRLC') AND (SHIPMENT.SHIP_DT > TO_DATE('31-JAN-2001', 'DD-MON-YYYY')))
    AND ((((SHIPMENT.WAY_BILL Is Null) or (SHIPMENT.WAY_BILL like 'disc*')) or (USER_DEF_CODE.UDEF14 Is Null)) AND (SHIPMENT.SHIP_VIA NOT IN('TCI', 'DESIGN','HAND CARY')))
    AND SHIPMENT.SHIPMENT_ID = USER_DEF_CODE.FOREIGN_KEY_CHAR2 (+)
    AND SHIPMENT.REQUEST_ID = USER_DEF_CODE.FOREIGN_KEY_CHAR1 (+)
    AND PLACE.PLACE_ID = SHIPMENT.SHIP_TO_KEY_ID1 (+)

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool



    1) Try the statement WITHOUT the empty lines.

    2) And if that does not work, then try this:

    Code:
    SELECT OUPT_PRD.SHIPMENT.SHIPMENT_ID, 
    OUPT_PRD.SHIPMENT.PLACE_ID_SHIPD_FRM,
    OUPT_PRD.SHIPMENT.SHIP_TO_KEY_ID1 AS PLACE_ID_SHIPD_TO, 
    OUPT_PRD.PLACE.COUNTRY,
    OUPT_PRD.PLACE.WHOS_PLACE, 
    OUPT_PRD.SHIPMENT.SHIP_DT, 
    OUPT_PRD.SHIPMENT.SHIP_VIA,
    OUPT_PRD.SHIPMENT.WAY_BILL, 
    OUPT_PRD.USER_DEF_CODE.UDEF14 AS DOCK_DT, 
    TRUNC(SYSDATE-OUPT_PRD.SHIPMENT.SHIP_DT) AS DAYS,
    SHIPMENT_DETAIL.CNT AS PIECE_COUNT
    FROM OUPT_PRD.SHIPMENT, 
    OUPT_PRD.PLACE, 
    OUPT_PRD.USER_DEF_CODE,
    (SELECT COUNT(*) FROM OUPT_PRD.SHIPMENT_DETAIL) SHIPMENT_DETAIL
    WHERE SHIPMENT_DETAIL.SHIPMENT_ID=OUPT_PRD.SHIP MENT.SHIPMENT_ID
    AND ((SHIPMENT.PLACE_ID_SHIPD_FRM = 'DFWRLC') AND (SHIPMENT.SHIP_DT > TO_DATE('31-JAN-2001', 'DD-MON-YYYY')))
    AND ((((SHIPMENT.WAY_BILL Is Null) or (SHIPMENT.WAY_BILL like 'disc*')) or (USER_DEF_CODE.UDEF14 Is Null)) AND (SHIPMENT.SHIP_VIA NOT IN('TCI', 'DESIGN','HAND CARY')))
    AND SHIPMENT.SHIPMENT_ID = USER_DEF_CODE.FOREIGN_KEY_CHAR2 (+)
    AND SHIPMENT.REQUEST_ID = USER_DEF_CODE.FOREIGN_KEY_CHAR1 (+)
    AND PLACE.PLACE_ID = SHIPMENT.SHIP_TO_KEY_ID1 (+)

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Formatted or not, this query is ugly Red color shows a place which should be corrected (you have SHIP MENT instead of SHIPMENT).
    Code:
    SELECT oupt_prd.shipment.shipment_id, oupt_prd.shipment.place_id_shipd_frm,
           oupt_prd.shipment.ship_to_key_id1 AS place_id_shipd_to,
           oupt_prd.place.country, oupt_prd.place.whos_place,
           oupt_prd.shipment.ship_dt, oupt_prd.shipment.ship_via,
           oupt_prd.shipment.way_bill, oupt_prd.user_def_code.udef14 AS dock_dt,
           TRUNC (SYSDATE - oupt_prd.shipment.ship_dt) AS days,
           (SELECT COUNT (*)
              FROM oupt_prd.shipment_detail
             WHERE oupt_prd.shipment_detail.shipment_id = oupt_prd.shipment.shipment_id
           ) AS piece_count
      FROM oupt_prd.shipment, oupt_prd.place, oupt_prd.user_def_code
     WHERE (    (shipment.place_id_shipd_frm = 'DFWRLC')
            AND (shipment.ship_dt > TO_DATE ('31-JAN-2001', 'DD-MON-YYYY'))
           )
       AND (    (   (   (shipment.way_bill IS NULL)
                     OR (shipment.way_bill LIKE 'disc*')
                    )
                 OR (user_def_code.udef14 IS NULL)
                )
            AND (shipment.ship_via NOT IN ('TCI', 'DESIGN', 'HAND CARY'))
           )
       AND shipment.shipment_id = user_def_code.foreign_key_char2(+)
       AND shipment.request_id = user_def_code.foreign_key_char1(+)
       AND place.place_id = shipment.ship_to_key_id1(+)

  4. #4
    Join Date
    Jan 2006
    Posts
    3

    Invalid Column

    Thanks for the reponse. I tried the first suggestion and now get an error "invalid column". This is the query:

    SELECT OUPT_PRD.SHIPMENT.SHIPMENT_ID,
    OUPT_PRD.SHIPMENT.PLACE_ID_SHIPD_FRM,
    OUPT_PRD.SHIPMENT.SHIP_TO_KEY_ID1 AS PLACE_ID_SHIPD_TO,
    OUPT_PRD.PLACE.COUNTRY,
    OUPT_PRD.PLACE.WHOS_PLACE,
    OUPT_PRD.SHIPMENT.SHIP_DT,
    OUPT_PRD.SHIPMENT.SHIP_VIA,
    OUPT_PRD.SHIPMENT.WAY_BILL,
    OUPT_PRD.USER_DEF_CODE.UDEF14 AS DOCK_DT,
    TRUNC(SYSDATE-OUPT_PRD.SHIPMENT.SHIP_DT) AS DAYS,
    SHIPMENT_DETAIL.CNT AS PIECE_COUNT
    FROM OUPT_PRD.SHIPMENT,
    OUPT_PRD.PLACE,
    OUPT_PRD.USER_DEF_CODE,
    (SELECT COUNT(*) FROM OUPT_PRD.SHIPMENT_DETAIL) SHIPMENT_DETAIL
    WHERE SHIPMENT_DETAIL.SHIPMENT_ID=OUPT_PRD.SHIPMENT.SHIP MENT_ID
    AND ((SHIPMENT.PLACE_ID_SHIPD_FRM = 'DFWRLC') AND (SHIPMENT.SHIP_DT > TO_DATE('31-JAN-2001', 'DD-MON-YYYY')))
    AND ((((SHIPMENT.WAY_BILL Is Null) or (SHIPMENT.WAY_BILL like 'disc*')) or (USER_DEF_CODE.UDEF14 Is Null)) AND (SHIPMENT.SHIP_VIA NOT IN('TCI', 'DESIGN','HAND CARY')))
    AND SHIPMENT.SHIPMENT_ID = USER_DEF_CODE.FOREIGN_KEY_CHAR2 (+)
    AND SHIPMENT.REQUEST_ID = USER_DEF_CODE.FOREIGN_KEY_CHAR1 (+)
    AND PLACE.PLACE_ID = SHIPMENT.SHIP_TO_KEY_ID1 (+)

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Yes, there is a typo:

    1) Correct =OUPT_PRD.SHIPMENT.SHIP MENT_ID to remove the space.


    2) Change this:
    Code:
    ...
    (SELECT COUNT(*) FROM OUPT_PRD.SHIPMENT_DETAIL) SHIPMENT_DETAIL
    to this:
    Code:
    ...
    (SELECT COUNT(*) as CNT FROM OUPT_PRD.SHIPMENT_DETAIL) SHIPMENT_DETAIL



    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Jan 2006
    Posts
    3

    Same problem

    Thanks, I tried that but got the same error. I also tried adding OUPT_PRD.SHIPMENT_DETAIL in the FROM clause, but the count returned a very big number. Is there a better way to write this ugly query?

Posting Permissions

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