Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

SQL Query Help

Options
  • 25-04-2007 9:46am
    #1
    warrenaldo
    Registered Users Posts: 500 ✭✭✭


    making it as simple as possible - i have a table with 2 colums name and date.

    what i want my query to do is select all values from this and anywhere the date is = '' // ie: empty string then output "no". anywhere its not i output "yes"
    Name Date
    Mark 10-10-2006
    John
    Paul 10-10-2006
    Sean
    QUERY RETURNS
    Mark yes
    John no
    paul yes
    sean no

    heres what im trying but to no avial.

    declare @xxx varchar(20)
    SET @xxx = 'yes'
    SELECT Name, @xxx, Date, Date FROM TABLE
    WHERE Date = ''
    SET @xxx = 'no'

    Hope you can help?


Comments

  • Options
    #2
    Seth Brundle
    Moderators, Politics Moderators Posts: 38,863 Mod ✭✭✭✭Seth Brundle


    is this for MSSQL?
    is the date null?
    Why are you selecting the Date field twice?


  • Options
    #3
    warrenaldo
    Registered Users Posts: 500 ✭✭✭warrenaldo


    yes its for MSSQL - not selecting the date twice. just a typo in my sample code.
    i think i can do it via a union in some way.


  • Options
    #4
    Seth Brundle
    Moderators, Politics Moderators Posts: 38,863 Mod ✭✭✭✭Seth Brundle


    try 
    SELECT 
[Name], 
(CASE WHEN [Date] ISNULL THEN 'no' ELSE 'yes') as [Dates]
FROM TABLE
    or 
    SELECT 
[Name], 
(CASE WHEN [Date] ='' THEN 'no' ELSE 'yes') as [Dates]
FROM TABLE


  • Options
    #5
    seamus
    Registered Users Posts: 68,317 ✭✭✭✭seamus


    It may be worth building a simple function to do the processing for you, e.g.
    CREATE FUNCTION containsDate(@dateCol varchar(10))
RETURNS varchar(3)
AS
BEGIN
declare @Return varchar(3)
IF @dateCol = ''
    set @Return = 'no'
ELSE 
    set @Return = 'yes'

RETURN @Return
END

    Then you just call
    SELECT Name, containsDate(Date) FROM TABLE

    ...in theory.


  • Options
    #6
    warrenaldo
    Registered Users Posts: 500 ✭✭✭warrenaldo


    great stuff. 
    SELECT 
[Name], 
(CASE WHEN [Date] ISNULL THEN 'no' ELSE 'yes') end [Dates] 
FROM TABLE
    end instead of as.
    works like a dream - nice and fast too.
    cheers


  • Advertisement
  • Options
    #7
    Seth Brundle
    Moderators, Politics Moderators Posts: 38,863 Mod ✭✭✭✭Seth Brundle


    It should have been more like
    SELECT 
[Name], 
(CASE WHEN [Date] ISNULL THEN 'no' ELSE 'yes' END) as [Dates]
FROM TABLE


  • Options
    #8
    MOH
    Registered Users Posts: 6,464 ✭✭✭MOH


    warrenaldo wrote:
    yes its for MSSQL - not selecting the date twice. just a typo in my sample code.
    i think i can do it via a union in some way.
    You could have done it with a union with
    SELECT name, 'Yes', date
FROM table
WHERE date = ''
UNION
SELECT name, 'No', date
FROM table
WHERE date <> ''


Advertisement