Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2008
    Posts
    5

    Unanswered: Case when in where clause

    Hi, I'm making some improvements in a database that is already implemented, in this database I need to make a query on a table that has a column for each day of the week, this can't be altered, otherwise I would found workaround easily, what I need to do is according to a day of the, see which day it is and the use just the column associated with this day to return just the rows that have values for that specific day. I'm trying with a case when clause but I'm find a problem in running the query. Does anybody have an idea on how to solve this in a different way? Sorry for the code but the database is in portuguese. Thanks for the help.
    By the way I'm not a dba expert.

    This is code that I'm trying to implement
    declare @ano int,@mes int,@dia int
    set @ano = 2008
    set @mes = 11
    set @dia = 26

    SELECT Componentes.Id_componente, Formularios_Dieteticos.Domingo, Formularios_Dieteticos.Segunda, Formularios_Dieteticos.Terca,
    Formularios_Dieteticos.Quinta, Formularios_Dieteticos.Quarta, Formularios_Dieteticos.Sexta, Formularios_Dieteticos.Sabado
    FROM Componentes INNER JOIN
    Formularios_Dieteticos ON Componentes.Id_componente = Formularios_Dieteticos.Id_Componente INNER JOIN
    Dietas ON Formularios_Dieteticos.Id_Dieta = Dietas.Id_Dieta
    WHERE (Dietas.Id_Dieta = 1) and Formularios_Dieteticos.id_momento=2 and
    (
    CASE
    WHEN (DATEPART(weekday, dbo.ymd2date(@ano, @mes, @dia)) = 1 THEN dbo.Formularios_Dieteticos.Domingo = 1
    WHEN (DATEPART(weekday, dbo.ymd2date(@ano, @mes, @dia)) = 2 THEN dbo.Formularios_Dieteticos.Segunda = 1
    WHEN (DATEPART(weekday, dbo.ymd2date(@ano, @mes, @dia)) = 3 THEN dbo.Formularios_Dieteticos.Terca = 1
    WHEN (DATEPART(weekday, dbo.ymd2date(@ano, @mes, @dia)) = 4 THEN dbo.Formularios_Dieteticos.Quarta = 1
    WHEN (DATEPART(weekday, dbo.ymd2date(@ano, @mes, @dia)) = 5 THEN dbo.Formularios_Dieteticos.Quinta = 1
    WHEN (DATEPART(weekday, dbo.ymd2date(@ano, @mes, @dia)) = 6 THEN dbo.Formularios_Dieteticos.Sexta = 1
    WHEN (DATEPART(weekday, dbo.ymd2date(@ano, @mes, @dia)) = 7 THEN dbo.Formularios_Dieteticos.Sabado = 1
    END
    )

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What is "ymd2date" for?
    I can guess, but it seems superfluous to me since DATEPART can handle mixed date/time values without any need for conversion.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2008
    Posts
    5
    Sure that is a function that they have that converts the separates fields year, month and day into to date format, bu the problem is the other part, because with the datepart I get for a specific date which day of the week it is, and then I need to get the rows that have that value a true.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    how about using OR instead of CASE.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Nov 2008
    Posts
    5
    if I use the or is solves the problem for that... one I need to put this in another query that also is no working and that one is more complicated to solve

  6. #6
    Join Date
    Nov 2008
    Posts
    5
    Problem solved

  7. #7
    Join Date
    Nov 2008
    Posts
    5
    Thanks for the help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •