Filtering subquery results

Question

I'm having a problems filtering the duplicate values from my query. This is my query

SELECT  cid.IDS_NO,cid.SUB_TITLE,cid.E_SUB_NAME, (cid.SUB_TITLE || ' '|| cid.E_SUB_NAME),
(SELECT(INST_BLK_HSE || ' ' || (SELECT E_STREET_NAME FROM street_table where street_code = d.INST_ST_CODE)) 
FROM del d where d.IDS_NO = cid.IDS_NO and ROWNUM = 1), 
(SELECT INST_ST_LEVEL || '-' || INST_STUNIT FROM detail d where d.IDS_NO = cid.IDS_NO and rownum = 1),
('COUNTRY' ||' ' || pl_post),pl_post 
FROM SUBSCRIBER cid where pl_postal_district = 15 and rownum < 3001 
and hi_property_type IN ('CONDO','COMMERCIAL BUILDING');

These are the parts I'm having problems with:

(SELECT(INST_BLK_HSE || ' ' || (SELECT E_STREET_NAME FROM street_table where street_code = d.INST_ST_CODE)) 
FROM del d where d.IDS_NO = cid.IDS_NO and ROWNUM = 1), 
(SELECT INST_ST_LEVEL || '-' || INST_STUNIT FROM detail d where d.IDS_NO = cid.IDS_NO and rownum = 1);

These parts always get multiple records, and I want to get only 1 record with the lowest sequence number, So here's what I did:

(SELECT(INST_BLK_HSE || ' ' || (SELECT E_STREET_NAME FROM street_table where street_code = d.INST_ST_CODE)) 
FROM del d where d.IDS_NO = cid.IDS_NO and ROWNUM = 1 ORDER BY d.SEQ_NO DESC), 
(SELECT INST_ST_LEVEL || '-' || INST_STUNIT FROM detail d where d.IDS_NO = cid.IDS_NO and rownum = 1 ORDER BY d.SEQ_NO DESC);

But I always get an error saying missing parenthesis, but when I remove the order by, it works fine. What should I do to get my query running correctly?

Maybe this post can help http://stackoverflow.com/questions/5119190/oracle-sql-order-by-in-subquery-problems — A.B.Cade, Jan 20 '13 at 09:47

score 3 · Accepted Answer · edited May 23 '17 at 12:33

Formatting your query to make it easier to read goes a long way to begin with.

But you also have a rather tricky problem, since you ..

want to get only 1 record with the lowest sequence number.

Should work like this:

SELECT s.ids_no
      ,s.sub_title
      ,s.e_sub_name
      ,(s.sub_title || ' '|| s.e_sub_name) AS title_sub_name
      ,d.some_col_name
      ,t.inst_name
      ,('COUNTRY' ||' ' || s.pl_post) AS country_post
      ,s.pl_post
FROM   subscriber s

LEFT   JOIN (
   SELECT d.ids_no
         ,(d.inst_blk_hse || ' ' || st.e_street_name) AS some_col_name
         ,ROW_NUMBER() OVER (PARTITION BY d.ids_no ORDER BY d.seq_no) AS rn
   FROM   del d
   LEFT   JOIN e_street_name st ON st.street_code = d.inst_st_code
   ) d ON d.ids_no = s.ids_no AND d.rn = 1

LEFT   JOIN (
   SELECT ids_no
         ,(inst_st_level || '-' || inst_stunit)  AS inst_name
         ,ROW_NUMBER() OVER (PARTITION BY d.ids_no ORDER BY d.seq_no) AS rn
   FROM   detail         
   ) t ON t.ids_no = s.ids_no AND t.rn = 1


WHERE  s.pl_postal_district = 15
AND    s.rownum < 3001 
AND    s.hi_property_type IN ('CONDO','COMMERCIAL BUILDING');

More details on how the subqueries work under this related question:
Select first row in each GROUP BY group?

The core features is ROW_NUMBER() to get the record with the lowest sequence number.
I rewrote your correlated subqueries, because they generally suck performance-wise. They are also hard to read.
I use LEFT JOIN, so you still get a row in case the subquery shouldn't find anything to match.

I tried the query, however it still had errors. But you did give me an idea on using over partition. Thanks, and by the way, sorry about my query if it was very messy because I'm still quite new in SQL. Thanks again. — marchemike, Jan 20 '13 at 12:23

score 1 · Answer 2 · answered Jan 20 '13 at 09:55

Subqueries must return only one row. So you have to add rownum = 1 to the subquery that is inside the subquery FROM del d. Something like:

SELECT  
  cid.IDS_NO,
  cid.SUB_TITLE,
  cid.E_SUB_NAME, 
  (cid.SUB_TITLE || ' '|| cid.E_SUB_NAME),
  (SELECT (INST_BLK_HSE || ' ' || (SELECT E_STREET_NAME 
                                   FROM street_table 
                                   where street_code = d.INST_ST_CODE
                                     and rownum = 1)) <<<<<<< here
   FROM del d 
   where d.IDS_NO = cid.IDS_NO 
     and ROWNUM = 1), 
  (SELECT INST_ST_LEVEL || '-' || INST_STUNIT 
   FROM detail d 
   where d.IDS_NO = cid.IDS_NO 
   and rownum = 1),
  ('COUNTRY' ||' ' || pl_post),
  pl_post 
FROM SUBSCRIBER cid 
where pl_postal_district = 15 
  and rownum < 3001 
  and hi_property_type IN ('CONDO','COMMERCIAL BUILDING');

Or, JOIN all the tables like this:

SELECT
  cid.IDS_NO,
  cid.SUB_TITLE,
  cid.E_SUB_NAME, 
  cid.SUB_TITLE || ' '|| cid.E_SUB_NAME,
  d.INST_BLK_HSE || ' ' || s.E_STREET_NAME, 
  d.INST_ST_LEVEL || '-' || d.INST_STUNIT 
  'COUNTRY' ||' ' || pl_post),
  pl_post 
FROM SUBSCRIBER cid 
INNER JOIN del          d ON d.IDS_NO      = cid.IDS_NO 
INNER JOIN detail      d2 ON d2.IDS_NO     = cid.IDS_NO 
INNER JOIN street_table s ON s.street_code = d.INST_ST_CODE
WHERE cid.pl_postal_district = 15 
  and cid.rownum < 3001 
  and cid.hi_property_type IN ('CONDO','COMMERCIAL BUILDING')
  and s.rownum = 1
  and d.rownum = 1
  and d2.rownum = 1;

Filtering subquery results

2 Answers2