sub select

• newbieuser

  SSCrazy Eights

  Points: 9423

  More actions

  February 7, 2011 at 9:25 am

  #231614

  Hi friends,

  I get the below error for a sub-select..

  select count(*)

  from

  (select upper(s_name)

  from bmi_tr)

  dual;

  Msg 8155, Level 16, State 2, Line 5

  No column was specified for column 1 of 'dual'.

  The query works fine if I remove the upper() function. Will it not work if there is a string function in the Sub-select queries?

  Thanks

• Ken McKelvey

  SSCoach

  Points: 18843

  More actions

  February 7, 2011 at 9:35 am

  #1283238

  You need to give the columns in a derived table a name so do something like:

  SELECT COUNT(*)

  FROM

  (

  SELECT UPPER(s_name) AS s_name

  FROM bmi_tr

  ) dual;

  or:

  SELECT COUNT(*)

  FROM

  (

  SELECT UPPER(s_name)

  FROM bmi_tr

  ) dual (s_name)

  Of course, you will get the same result with:

  SELECT COUNT(s_name)

  FROM bmi_tr