#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2015
    Posts
    4
    Rep Power
    0

    Question can Left Outer Join works in subquery?


    Hi all,
    I ran this query and have error message:SQL Error: ORA-01799: a column may not be outer-joined to a subquery
    01799. 00000 - "a column may not be outer-joined to a subquery"
    *Cause: <expression>(+) <relop> (<subquery>) is not allowed.
    *Action: Either remove the (+) or make a view out of the subquery.
    In V6 and before, the (+) was just ignored in this case.

    Here is my query:

    select d.description, count(*) as frequency
    ,to_char(RATIO_TO_REPORT(count(*)) OVER (),'9.999')*100 as PERCENT
    from dpv_status_codes d FULL OUTER JOIN name_address_stage n ON
    n.partition_id = (select partition_id from dw_prod.partition_map
    where file_id =(select file_id from member_files where member_file_group_id =(276398) and last_modified_by =('CleanAddress')))
    and d.status_cd = n.dpv_status_cd
    group by d.description
  2. #2
  3. Lord of the Dance
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Oct 2003
    Posts
    4,204
    Rep Power
    2012
    You should add the subquery as its own join and then "link" them together. Something like this:
    sql Code:
     
    SELECT d.description
    	, COUNT(*) AS frequency
    	,to_char(RATIO_TO_REPORT(COUNT(*)) OVER (),'9.999')*100 AS PERCENT
    FROM dpv_status_codes d 
    FULL OUTER JOIN name_address_stage n ON d.status_cd = n.dpv_status_cd
    INNER JOIN (SELECT partition_id 
                 FROM dw_prod.partition_map
                 WHERE file_id = (SELECT file_id 
                                  FROM member_files 
                                  WHERE member_file_group_id =(276398) AND last_modified_by =('CleanAddress')
                                 )
                ) AS Subquery ON n.partition_id = Subquery.partition_id
    GROUP BY d.description
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2015
    Posts
    4
    Rep Power
    0
    somehow this query not working properly;;; I compared with my original query got more rows returned:

    select d.description as description, count(*) as frequency
    ,to_char(RATIO_TO_REPORT(count(*)) OVER (),'9.999')*100 as PERCENT
    from dpv_status_codes d LEFT OUTER JOIN name_address_stage n ON
    n.partition_id = 2291
    and d.status_cd = n.dpv_status_cd
    group by d.description

    DESCRIPTION FREQUENCY PERCENT
    ---------------------------------------------------------------- ---------- ----------
    Input Adress Matched to DPV (all components) 18997 92.6
    Suite/Apt Missing 1398 6.8
    Address Was Coded to a Military Address 1 0
    Input Address Not Matched to the ZIP + 4 file 1 0
    Missing PO, RR, or HC Box number 1 0
    Address Was Coded to a Unique ZIP Code 1 0
    Address Was Coded to a General Delivery Address 1 0
    Input Address Matched to CMRA but Suite/Apt Missing 5 0
    Input Address Matched to the ZIP + 4 file 8 0
    Input Address Matched to CMRA 1 0
    Postal or Rural Route invalid 2 0
    Input Address Primary Number Missing 1 0
    Street Number Invalid 90 0.4
    Suite/Apt present but invalid 1 0

    14 rows selected
    *************************************************
    SELECT d.description
    , COUNT(*) AS frequency
    ,to_char(RATIO_TO_REPORT(COUNT(*)) OVER (),'9.999')*100 AS PERCENT
    FROM dpv_status_codes d
    FULL OUTER JOIN name_address_stage n ON d.status_cd = n.dpv_status_cd
    INNER JOIN (SELECT partition_id
    FROM dw_prod.partition_map
    WHERE file_id = (SELECT file_id
    FROM member_files
    WHERE member_file_group_id =(276398) AND last_modified_by =('CleanAddress')
    )
    ) AS Subquery ON n.partition_id = Subquery.partition_id
    GROUP BY d.description
    Error at Command Line:12 Column:15
    Error report:
    SQL Error: ORA-00905: missing keyword
    00905. 00000 - "missing keyword"
    *Cause:
    *Action:
    DESCRIPTION FREQUENCY PERCENT
    ---------------------------------------------------------------- ---------- ----------
    Input Adress Matched to DPV (all components) 18997 91.9
    Suite/Apt Missing 1398 6.8
    167 0.8
    Missing PO, RR, or HC Box number 1 0
    Input Address Matched to CMRA but Suite/Apt Missing 5 0
    Input Address Matched to the ZIP + 4 file 8 0
    Postal or Rural Route invalid 2 0
    Street Number Invalid 90 0.4

    8 rows selected
  6. #4
  7. Lord of the Dance
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Oct 2003
    Posts
    4,204
    Rep Power
    2012
    Should have be more cleared that my example was just that - an example of who you could place the subquery into its own join statement.

    Only thing I could find is to try removing 'AS' on this line:
    ) AS Subquery ON n.partition_id = Subquery.partition_id

IMN logo majestic logo threadwatch logo seochat tools logo