0

I am using mysql toad, i have a table which is empty and i am trying to return a string value if no records are found i have tried the under mention queries however none works, only an empty row is returned.

Query

select coalesce(stDate,'0')as StDate from tblStudents where studentNumber = 12213123;

select IFNULL(stDate,'0')as StDate from tblStudents where studentNumber = 12213123;

The results are only empty strings the column stDate is of type varchar.

devdar
  • 5,564
  • 28
  • 100
  • 153

1 Answers1

1

Try this

 select Cast(count(*) as char(10)) as StDate
 from tblStudents where studentNumber = 12213123;

You can also use the CASE statement, and if the Student Number is unique, the max() or min() functions to get the stDate from the table.

 select Cast(count(*) as char(10)) as NumRows,max(stDate) as stDate
 from tblStudents where studentNumber = 12213123;

If you only want a single field, try something like this

select 
CASE count(*)
    WHEN 0 THEN ''
    ELSE CAST(max(stDate) as char(12))
END CASE as StartDate
     from tblStudents where studentNumber = 12213123;
Sparky
  • 14,967
  • 2
  • 31
  • 45
  • what if there are records and i want to return the stDate – devdar May 04 '13 at 02:14
  • i just want a select that returns one value not two your select has a count and a date if there is no date i will get a count of zero and if there is data i will get a count of 1 and a date not what i am looking for – devdar May 04 '13 at 02:18
  • think i will try the case statement if the count is > 0 then select a date – devdar May 04 '13 at 02:19