I would like to know that may I use case statement in where clause?
If not, are there any other solution to filter my records depends on my
field.
For example
MyType integer, MyDate datetime
MyType MyDate
1 03/24/2005
1 03/20/2005
2 03/24/2005
I want to show MyType one date before today like record 2,but I want to
everything for MyType 2
Are there any solution for this?
Any information is appreciated,
Souris,Try this
Select * from Tablename where id in (select max(id) from tablename
group by date)
Madhivanan|||You can use a case statement in a where clause, but you don't need to for
this example. If you just want to look for myDate on the previous day and
myType = 2 than you would do this:
select * from myTable
where MyDate = convert(varchar,dateadd(d,-1,getdate()),101)
and MyType = 2
If you are looking for ANY myDate prior to today and myType = 2 than:
select * from myTable
where MyDate < convert(varchar,getdate(),101)
and MyType = 2
"souris" wrote:
> I would like to know that may I use case statement in where clause?
> If not, are there any other solution to filter my records depends on my
> field.
> For example
> MyType integer, MyDate datetime
> MyType MyDate
> 1 03/24/2005
> 1 03/20/2005
> 2 03/24/2005
> I want to show MyType one date before today like record 2,but I want to
> everything for MyType 2
> Are there any solution for this?
> Any information is appreciated,
> Souris,
>
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
<<
No. This is because there is no such statement in SQL. There is a
CASE expression which returns a scalar value.
I want to
everything for MyType 2 <<
SELECT my_type, my_date
FROM Foobar
WHERE my_type = 2
OR my_date < CURRENT_TIMESTAMP;
Rows are not records, columns are not fields. You are still using
terminology from a procedural language with a file system and not
thinking in RDBMS yet.|||Thanks millions,
Souris,
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1111763824.117387.95340@.o13g2000cwo.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are. Sample data is also a good idea, along with clear
> specifications.
>
> <<
> No. This is because there is no such statement in SQL. There is a
> CASE expression which returns a scalar value.
>
> I want to
> everything for MyType 2 <<
> SELECT my_type, my_date
> FROM Foobar
> WHERE my_type = 2
> OR my_date < CURRENT_TIMESTAMP;
> Rows are not records, columns are not fields. You are still using
> terminology from a procedural language with a file system and not
> thinking in RDBMS yet.
>
Showing posts with label filter. Show all posts
Showing posts with label filter. Show all posts
Thursday, March 29, 2012
Tuesday, March 20, 2012
Case in inner join
Hi All,
Is it possible to use Case statement inside Joins?
Say,
Select ........ from TableA A
inner join TableB B on
Case
When @.filter <> ''
Then B.ID = A.ID
and B.ID in (Select id from temptable where Name = @.filter)
Else B.ID = A.ID
End
inner join TableC C on C.ID = A.ID
where....................................
Thanks in advance...
KuttyTry this
Run this on Pubs, change the 1 = 1 to false to test the fall through option
where you just join on A.id = b.id.
select * from titles a
inner join titleauthor b
on a.[title_id] = case when (1 = 1) then ('PS3333') else b.title_id end
and a.title_id = case when (1 = 1) then (b.title_id) end
This is how your code can be changed.
select * from TableA a
inner join TableB b
on B.ID = case when (@.filter <> '') then (@.filter) else (A.ID) end
and B.ID = case when (@.filter <> '') then (A.ID) end
Don't know if this will work.
Hope it's what you are looking for.
"Pradeep Kutty" wrote:
> Hi All,
> Is it possible to use Case statement inside Joins?
> Say,
> Select ........ from TableA A
> inner join TableB B on
> Case
> When @.filter <> ''
> Then B.ID = A.ID
> and B.ID in (Select id from temptable where Name = @.filter
)
> Else B.ID = A.ID
> End
> inner join TableC C on C.ID = A.ID
> where....................................
>
> Thanks in advance...
> Kutty
>
>|||Hi
Is it still open ? Did not you resolve the problem?
Use pubs
SELECT a.au_lname, a.au_fname, a.address,
t.title, t.type
FROM authors a INNER JOIN
titleauthor ta ON ta.au_id = a.au_id INNER JOIN
titles t ON t.title_id = ta.title_id
INNER JOIN publishers p on t.pub_id =
CASE WHEN t.type = 'Business' THEN p.pub_id ELSE null END
INNER JOIN stores s on s.stor_id =
CASE WHEN t.type = 'Popular_comp' THEN t.title_id ELSE null END
"Pradeep Kutty" <pradeepk@.healthasyst.com> wrote in message
news:%23Hq1W3XSFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> Is it possible to use Case statement inside Joins?
> Say,
> Select ........ from TableA A
> inner join TableB B on
> Case
> When @.filter <> ''
> Then B.ID = A.ID
> and B.ID in (Select id from temptable where Name =
@.filter)
> Else B.ID = A.ID
> End
> inner join TableC C on C.ID = A.ID
> where....................................
>
> Thanks in advance...
> Kutty
>|||There is no CASE statement in SQL. There is a CASE **expression**.
Expressions return values. Since SQL is a declarative language, your
guess at the syntax is fundamentally wrong; you still think you are
writing procedural code.|||--CELKO-- wrote:
> There is no CASE statement in SQL. There is a CASE **expression**.
> Expressions return values. Since SQL is a declarative language, your
> guess at the syntax is fundamentally wrong; you still think you are
> writing procedural code.
And, Joe, you still think yer Codd's gift to earth.
Lighten up, crotchety! Get laid, or somethin.
Is it possible to use Case statement inside Joins?
Say,
Select ........ from TableA A
inner join TableB B on
Case
When @.filter <> ''
Then B.ID = A.ID
and B.ID in (Select id from temptable where Name = @.filter)
Else B.ID = A.ID
End
inner join TableC C on C.ID = A.ID
where....................................
Thanks in advance...
KuttyTry this
Run this on Pubs, change the 1 = 1 to false to test the fall through option
where you just join on A.id = b.id.
select * from titles a
inner join titleauthor b
on a.[title_id] = case when (1 = 1) then ('PS3333') else b.title_id end
and a.title_id = case when (1 = 1) then (b.title_id) end
This is how your code can be changed.
select * from TableA a
inner join TableB b
on B.ID = case when (@.filter <> '') then (@.filter) else (A.ID) end
and B.ID = case when (@.filter <> '') then (A.ID) end
Don't know if this will work.
Hope it's what you are looking for.
"Pradeep Kutty" wrote:
> Hi All,
> Is it possible to use Case statement inside Joins?
> Say,
> Select ........ from TableA A
> inner join TableB B on
> Case
> When @.filter <> ''
> Then B.ID = A.ID
> and B.ID in (Select id from temptable where Name = @.filter
)
> Else B.ID = A.ID
> End
> inner join TableC C on C.ID = A.ID
> where....................................
>
> Thanks in advance...
> Kutty
>
>|||Hi
Is it still open ? Did not you resolve the problem?
Use pubs
SELECT a.au_lname, a.au_fname, a.address,
t.title, t.type
FROM authors a INNER JOIN
titleauthor ta ON ta.au_id = a.au_id INNER JOIN
titles t ON t.title_id = ta.title_id
INNER JOIN publishers p on t.pub_id =
CASE WHEN t.type = 'Business' THEN p.pub_id ELSE null END
INNER JOIN stores s on s.stor_id =
CASE WHEN t.type = 'Popular_comp' THEN t.title_id ELSE null END
"Pradeep Kutty" <pradeepk@.healthasyst.com> wrote in message
news:%23Hq1W3XSFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> Is it possible to use Case statement inside Joins?
> Say,
> Select ........ from TableA A
> inner join TableB B on
> Case
> When @.filter <> ''
> Then B.ID = A.ID
> and B.ID in (Select id from temptable where Name =
@.filter)
> Else B.ID = A.ID
> End
> inner join TableC C on C.ID = A.ID
> where....................................
>
> Thanks in advance...
> Kutty
>|||There is no CASE statement in SQL. There is a CASE **expression**.
Expressions return values. Since SQL is a declarative language, your
guess at the syntax is fundamentally wrong; you still think you are
writing procedural code.|||--CELKO-- wrote:
> There is no CASE statement in SQL. There is a CASE **expression**.
> Expressions return values. Since SQL is a declarative language, your
> guess at the syntax is fundamentally wrong; you still think you are
> writing procedural code.
And, Joe, you still think yer Codd's gift to earth.
Lighten up, crotchety! Get laid, or somethin.
Sunday, March 11, 2012
Cascading Date for Filter
Hi,
I have a Date Dimension with these attributes:
Datekey
FullDate (eg. 1/1/2007, 1/1/2008)
Year,
Month (eg. Feb 2006, March 2006, April 2006)
If i put the Year on the filter and select 2005, I want the Month filter to show only months with 2005 as the year.
Is it possible?
cherriesh
Hi,
It somewhat depends what tool you are using to query your cube, which tool/s are you using?
Matt
|||my cube is connected to ms excel 2007|||Hi,
I don't think it can do what you are asking, an alternative is to create another attribute which just has the month in it, so no matter what year it is the month can be shown.
I have seen this kind of thing implemented successfully, Panorama has been able to do this for years. ProClarity, I've seen it work, but when we got a later release it stop working. And a few custom made applicantions.
HTH
Matt
|||Hi,
I don't think it can do what you are asking, an alternative is to create another attribute which just has the month in it, so no matter what year it is the month can be shown.
So you mean in my Date Dimension, the month attribute there should only be like February, March etc instead of February 2007, March 2007 etc?
cherriesh
|||Hi,
Yeah. e.g.
Year= 2006
Measure
Month
Jan 20
Feb 30
March 40
april 50
May 60
..
Year= 2007
Measure
Month
Jan 90
Feb 80
March 75
april 60
May 40
..
This way the month is independent of the year, if you don't slice by year you will get ALL year values for that month. So Jan will be 2005+2006+2007... values.
Year= ALL
Measure
Month
Jan 110
Feb 110
March 115
april 110
May 100
If you have a copy of the adventure works database, the "month of year" attribute is the one you want to create. They only really use Month+Year in the hierarchies and have year, month, day .. as a seperate attributes.
HTH
Matt
Cascading Date for Filter
Hi,
I have a Date Dimension with these attributes:
Datekey
FullDate (eg. 1/1/2007, 1/1/2008)
Year,
Month (eg. Feb 2006, March 2006, April 2006)
If i put the Year on the filter and select 2005, I want the Month filter to show only months with 2005 as the year.
Is it possible?
cherriesh
Hi,
It somewhat depends what tool you are using to query your cube, which tool/s are you using?
Matt
|||my cube is connected to ms excel 2007|||Hi,
I don't think it can do what you are asking, an alternative is to create another attribute which just has the month in it, so no matter what year it is the month can be shown.
I have seen this kind of thing implemented successfully, Panorama has been able to do this for years. ProClarity, I've seen it work, but when we got a later release it stop working. And a few custom made applicantions.
HTH
Matt
|||Hi,
I don't think it can do what you are asking, an alternative is to create another attribute which just has the month in it, so no matter what year it is the month can be shown.
So you mean in my Date Dimension, the month attribute there should only be like February, March etc instead of February 2007, March 2007 etc?
cherriesh
|||Hi,
Yeah. e.g.
Year= 2006
Measure
Month
Jan 20
Feb 30
March 40
april 50
May 60
..
Year= 2007
Measure
Month
Jan 90
Feb 80
March 75
april 60
May 40
..
This way the month is independent of the year, if you don't slice by year you will get ALL year values for that month. So Jan will be 2005+2006+2007... values.
Year= ALL
Measure
Month
Jan 110
Feb 110
March 115
april 110
May 100
If you have a copy of the adventure works database, the "month of year" attribute is the one you want to create. They only really use Month+Year in the hierarchies and have year, month, day .. as a seperate attributes.
HTH
Matt
Cascading Date for Filter
Hi,
I have a Date Dimension with these attributes:
Datekey
FullDate (eg. 1/1/2007, 1/1/2008)
Year,
Month (eg. Feb 2006, March 2006, April 2006)
If i put the Year on the filter and select 2005, I want the Month filter to show only months with 2005 as the year.
Is it possible?
cherriesh
Hi,
It somewhat depends what tool you are using to query your cube, which tool/s are you using?
Matt
|||my cube is connected to ms excel 2007|||Hi,
I don't think it can do what you are asking, an alternative is to create another attribute which just has the month in it, so no matter what year it is the month can be shown.
I have seen this kind of thing implemented successfully, Panorama has been able to do this for years. ProClarity, I've seen it work, but when we got a later release it stop working. And a few custom made applicantions.
HTH
Matt
|||Hi,
I don't think it can do what you are asking, an alternative is to create another attribute which just has the month in it, so no matter what year it is the month can be shown.
So you mean in my Date Dimension, the month attribute there should only be like February, March etc instead of February 2007, March 2007 etc?
cherriesh
|||Hi,
Yeah. e.g.
Year= 2006
Measure
Month
Jan 20
Feb 30
March 40
april 50
May 60
..
Year= 2007
Measure
Month
Jan 90
Feb 80
March 75
april 60
May 40
..
This way the month is independent of the year, if you don't slice by year you will get ALL year values for that month. So Jan will be 2005+2006+2007... values.
Year= ALL
Measure
Month
Jan 110
Feb 110
March 115
april 110
May 100
If you have a copy of the adventure works database, the "month of year" attribute is the one you want to create. They only really use Month+Year in the hierarchies and have year, month, day .. as a seperate attributes.
HTH
Matt
Subscribe to:
Posts (Atom)