CODEWITHSUNDEEP
sqloracle-databasedate
great77
great77

Reputation: 143

single-row subquery returns more than one row on date columns

I have a code that is given this problem in Oracle : "single-row subquery returns more than one row" When I used only inline code code it was working

I have tried only the inline code below which is working fine 

select TO_CHAR(TO_DATE(theDATE,'DD.MM.YYYY HH24:MI', 'NLS_DATE_LANGUAGE = 
   NORWEGIAN'),
       'DD/MM/YYYY') 
       from mastertable

But the combination of code is not working by combining two select statement

   SELECT  aircraft
    , country
     ,   (select TO_CHAR(TO_DATE(theDATE,'DD.MM.YYYY HH24:MI', 'NLS_DATE_LANGUAGE 
  = NORWEGIAN'),
       'DD/MM/YYYY') 
       from mastertable )

        FROM mastertable

   FULL OUTER JOIN continent ON mastertable.id = continent.id

Upvotes: 0

Views: 95

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65363

You need to make your subquery restrict to only one ID as

SELECT aircraft,
       country,
       (SELECT TO_CHAR(TO_DATE(theDATE, 'DD.MM.YYYY HH24:MI',
                               'NLS_DATE_LANGUAGE = NORWEGIAN'),'DD/MM/YYYY')
          FROM mastertable
         WHERE id = m.id)
  FROM mastertable m
  FULL JOIN continent c
    ON m.id = c.id

presuming ID is a primary key column.

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142993

Why don't you simply

SELECT m.aircraft,
       c.country,
       to_char(to_date(m.thedate, 'dd.mm.yyyy hh24:mi', 'nls_date_language = norwegian'), 'dd/mm/yyyy') the_date
FROM mastertable m full outer join continent c on m.id = c.id;

I mean, what is the purpose of the query you're trying to (ab)use?

Also, what is THEDATE's datatype? Looks like a VARCHAR2; is it? What good does NLS_DATE_LANGUAGE do here? All you deal with are digits (there are no month or day names) so that could be simplified to

to_char(to_date(m.thedate, 'dd.mm.yyyy hh24:mi'), 'dd/mm/yyyy')

Upvotes: 1

Related Questions