Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2003
    Location
    Oxford, England
    Posts
    38

    Unanswered: A WHERE in a Union query

    Hi, i have a union query that lists all the years from a date field and add the currentyer if its not already listed:

    SELECT DISTINCT Cas Yearlist
    FROM dbo.ViewPressReleases UNION SELECT datepart(yyyy, getdate())
    ORDER BY DatePart(yyyy,[PressreleaseDate])

    what i need to do is filter it with something along the lines of:

    WHERE Yearlist LIKE myvariable

    although i know i cant simply use:

    WHERE Yearlist

    it would have to be something like:

    WHERE DatePart(yyyy,[PressreleaseDate]) UNION datepart(yyyy, getdate()) LIKE myvariable


    Does anyone know how to write this correctly?

  2. #2
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    Not tested it, but something like this:

    Code:
    select YearList from
    (select distinct Cas YearList
    from dbo.ViewPressReleases
    where YearList like myvariable) SomeNameYouLike
    union 
    select datepart(yyyy, getdate())
    order by YourOrderField
    Johan

  3. #3
    Join Date
    Dec 2003
    Location
    Oxford, England
    Posts
    38
    ok thanks,

    so now i have

    select YearList from (select distinct DatePart(yyyy,[PressreleaseDate]) as YearList
    from dbo.ViewPressReleases where Yearlist like stgetstyear) mixedyearlist
    union select datepart(yyyy, getdate()) order by Yearlist

    but i get an error that Yearlist in there WHERE statment is invalid.

    any ideas?

  4. #4
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    you can't use your alias in the where clause, so you have to reuse the DATEPART function again. Also, your DATEPART returns an integer value. If you want to perform a like with a wildcard character (%) on it, you can use the DATENAME function which returns a string.
    Johan

  5. #5
    Join Date
    Dec 2003
    Location
    Oxford, England
    Posts
    38
    i actually want the integer. This was my origianal probelm; that you cant use an alias in a where clause, but i cant simply use the "datepart..." as its part of a union query and i need to filter the resulting list of the union query, if i've explained myself clearly.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, it isn't clear, luke

    please explain again what the WHERE clause is supposed to find
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2003
    Location
    Oxford, England
    Posts
    38
    Originally posted by r937
    no, it isn't clear, luke

    please explain again what the WHERE clause is supposed to find
    Ok....

    i have

    SELECT DISTINCT DatePart(yyyy,[PressreleaseDate]) as Yearlist
    FROM dbo.ViewPressReleases UNION SELECT datepart(yyyy, getdate()) ORDER BY DatePart(yyyy,[PressreleaseDate])

    i want to then filter the resulting list of years by myvariable

    so i need to add

    WHERE ????????? LIKE myvariable

    if this was not a union query i would write something like

    WHERE DatePart(yyy,[PressreleaseDate]) LIKE my variable

    but as it is a union query i asume i have to write somethin like

    WHERE (SELECT DISTINCT DatePart(yyyy,[PressreleaseDate]) as Yearlist FROM dbo.ViewPressReleases UNION SELECT datepart(yyyy, getdate()) ORDER BY DatePart(yyyy,[PressreleaseDate])) LIKE my viable

    (which doesnt work) and i know i cant simply use the alias like:

    WHERE Yearlist Like myvaiable

    so i need to know how to phrase the SQL corrctly in order to filter the list of years.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, don't explain in terms of sql, you already tried that

    you cannot use LIKE on integers

    try explaining it in english

    "i want to select only years which ... ?"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Dec 2003
    Location
    Oxford, England
    Posts
    38
    I Think i have it.

    SELECT DISTINCT DatePart(yyyy,[PressreleaseDate]) as Yearlist
    FROM dbo.ViewPressReleases WHERE DatePart(yyyy,[PressreleaseDate]) LIKE stgetstyear UNION SELECT datepart(yyyy, getdate()) WHERE datepart(yyyy, getdate()) LIKE stgetstyear ORDER BY DatePart(yyyy,[PressreleaseDate])

    Thanks.

Posting Permissions

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