conditional IF

Hello. I have a query whice look like this:
select a,b,c,d from table1;
Now - when pressing a cell in the first column we are jumping to
another report with the value as parameter (called- p_param);.
In the other report the query is:
select * from table1 where a=::p_param.
What I want to do is taht in the first report I'll will check if the
second query return any result and if so to leave it as a link. If the
second query does't return any result (zero rows) to remove the link so
the user won't go to an empty report.
So first i need to know how to check in the first report what will be
the result of the second query.
Can u help me?
Thanks in advance,
Roy.What if you use some code that calls a stored procedure and it will return a
0 or a 1. Then you can return a link or a javascript alert indicating there
was no data to return so the report would be empty.
--
"Everyone knows something you don't know"
"nicknack" wrote:
> Hello. I have a query whice look like this:
> select a,b,c,d from table1;
> Now - when pressing a cell in the first column we are jumping to
> another report with the value as parameter (called- p_param);.
> In the other report the query is:
> select * from table1 where a=::p_param.
> What I want to do is taht in the first report I'll will check if the
> second query return any result and if so to leave it as a link. If the
> second query does't return any result (zero rows) to remove the link so
> the user won't go to an empty report.
> So first i need to know how to check in the first report what will be
> the result of the second query.
> Can u help me?
> Thanks in advance,
> Roy.
>|||What if you use some code that calls a stored procedure and it will return a
0 or a 1. Then you can return a link or a javascript alert indicating there
was no data to return so the report would be empty.
--
"Everyone knows something you don't know"
"nicknack" wrote:
> Hello. I have a query whice look like this:
> select a,b,c,d from table1;
> Now - when pressing a cell in the first column we are jumping to
> another report with the value as parameter (called- p_param);.
> In the other report the query is:
> select * from table1 where a=::p_param.
> What I want to do is taht in the first report I'll will check if the
> second query return any result and if so to leave it as a link. If the
> second query does't return any result (zero rows) to remove the link so
> the user won't go to an empty report.
> So first i need to know how to check in the first report what will be
> the result of the second query.
> Can u help me?
> Thanks in advance,
> Roy.
>

conditional IF

Hello. I have a query whice look like this:
select a,b,c,d from table1;
Now - when pressing a cell in the first column we are jumping to another report with the value as parameter (called- p_param);.
In the other report the query is:
select * from table1 where a=::p_param.
What I want to do is taht in the first report I'll will check if the second query return any result and if so to leave it as a link. If the second query does't return any result (zero rows) to remove the link so the user won't go to an empty report.
So first i need to know how to check in the first report what will be the result of the second query.
Can u help me?
Thanks in advance,
Roy.
From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comWhat if you use some code that calls a stored procedure and it will return a
0 or a 1. Then you can return a link or a javascript alert indicating there
was no data to return so the report would be empty.
--
"Everyone knows something you don't know"
"roy mm" wrote:
> Hello. I have a query whice look like this:
> select a,b,c,d from table1;
> Now - when pressing a cell in the first column we are jumping to another report with the value as parameter (called- p_param);.
> In the other report the query is:
> select * from table1 where a=::p_param.
> What I want to do is taht in the first report I'll will check if the second query return any result and if so to leave it as a link. If the second query does't return any result (zero rows) to remove the link so the user won't go to an empty report.
> So first i need to know how to check in the first report what will be the result of the second query.
> Can u help me?
> Thanks in advance,
> Roy.
>
> From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
>|||What if you use some code that calls a stored procedure and it will return a
0 or a 1. Then you can return a link or a javascript alert indicating there
was no data to return so the report would be empty.
--
"Everyone knows something you don't know"
"roy mm" wrote:
> Hello. I have a query whice look like this:
> select a,b,c,d from table1;
> Now - when pressing a cell in the first column we are jumping to another report with the value as parameter (called- p_param);.
> In the other report the query is:
> select * from table1 where a=::p_param.
> What I want to do is taht in the first report I'll will check if the second query return any result and if so to leave it as a link. If the second query does't return any result (zero rows) to remove the link so the user won't go to an empty report.
> So first i need to know how to check in the first report what will be the result of the second query.
> Can u help me?
> Thanks in advance,
> Roy.
>
> From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
>

Condition in FROM vs WHERE

Hi
What's the difference between having the condition in FROM vs WHERE.
Alt 1:
SELECT t1.Title
FROM Table1 t1 INNER JOIN
Table2 t2 ON t1.Id = t2.Table1Id
WHERE t1.Active = 1
Alt 2:
SELECT t1.Title
FROM Table1 t1 INNER JOIN
Table2 t2 ON t1.Id = t2.Table1Id AND t1.Active = 1
Thanks.Senna wrote:
> Hi
> What's the difference between having the condition in FROM vs WHERE.
> Alt 1:
> SELECT t1.Title
> FROM Table1 t1 INNER JOIN
> Table2 t2 ON t1.Id = t2.Table1Id
> WHERE t1.Active = 1
>
> Alt 2:
> SELECT t1.Title
> FROM Table1 t1 INNER JOIN
> Table2 t2 ON t1.Id = t2.Table1Id AND t1.Active = 1
> Thanks.
In your examples there is no difference.
In the case of an OUTER join it does make a difference because the join
condition is evaluated first (logically speaking) and then the WHERE
clause is applied to the result.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks David, thats a good thing to know.
"David Portas" wrote:

> Senna wrote:
> In your examples there is no difference.
> In the case of an OUTER join it does make a difference because the join
> condition is evaluated first (logically speaking) and then the WHERE
> clause is applied to the result.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

concating columns

this is my DDL:
CREATE TABLE [dbo].[Table1] (
[Code] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ParentCode] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Code]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
(
[ParentCode]
) REFERENCES [dbo].[Table1] (
[Code]
)
I want to concat Column of Name:
Code Name ParentCode
1 test NULL
2 book NULL
3 Cake 1
4 Mouse 3
I want to concat column of Name for Code=4 and output will be: testCake
thanks in advance
perspolis wrote:
> this is my DDL:
> CREATE TABLE [dbo].[Table1] (
> [Code] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ParentCode] [int] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
> (
> [Code]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
> (
> [ParentCode]
> ) REFERENCES [dbo].[Table1] (
> [Code]
> )
> I want to concat Column of Name:
> Code Name ParentCode
> 1 test NULL
> 2 book NULL
> 3 Cake 1
> 4 Mouse 3
> I want to concat column of Name for Code=4 and output will be: testCake
> thanks in advance
SELECT c.[Name] + b.[Name] AS ConcatName
FROM Table1 AS a
JOIN Table1 AS b ON a.ParentCode = b.Code
JOIN Table1 AS c ON b.ParentCode = c.Code
WHERE a.Code = 4
|||I want to do that for many levels as is not for 2 rows.
"Ed Enstrom" <nospam@.invalid.net> wrote in message
news:np8Zh.98$eH4.18@.newsfe12.lga...
> perspolis wrote:
> SELECT c.[Name] + b.[Name] AS ConcatName
> FROM Table1 AS a
> JOIN Table1 AS b ON a.ParentCode = b.Code
> JOIN Table1 AS c ON b.ParentCode = c.Code
> WHERE a.Code = 4
>
>

concating columns

this is my DDL:
CREATE TABLE [dbo].[Table1] (
[Code] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ParentCode] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Code]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
(
[ParentCode]
) REFERENCES [dbo].[Table1] (
[Code]
)
I want to concat Column of Name:
Code Name ParentCode
1 test NULL
2 book NULL
3 Cake 1
4 Mouse 3
I want to concat column of Name for Code=4 and output will be: testCake
thanks in advanceperspolis wrote:
> this is my DDL:
> CREATE TABLE [dbo].[Table1] (
> [Code] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ParentCode] [int] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
> (
> [Code]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
> (
> [ParentCode]
> ) REFERENCES [dbo].[Table1] (
> [Code]
> )
> I want to concat Column of Name:
> Code Name ParentCode
> 1 test NULL
> 2 book NULL
> 3 Cake 1
> 4 Mouse 3
> I want to concat column of Name for Code=4 and output will be: testCake
> thanks in advance
SELECT c.[Name] + b.[Name] AS ConcatName
FROM Table1 AS a
JOIN Table1 AS b ON a.ParentCode = b.Code
JOIN Table1 AS c ON b.ParentCode = c.Code
WHERE a.Code = 4|||I want to do that for many levels as is not for 2 rows.
"Ed Enstrom" <nospam@.invalid.net> wrote in message
news:np8Zh.98$eH4.18@.newsfe12.lga...
> perspolis wrote:
>> this is my DDL:
>> CREATE TABLE [dbo].[Table1] (
>> [Code] [int] IDENTITY (1, 1) NOT NULL ,
>> [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [ParentCode] [int] NULL
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[Table1] ADD
>> CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
>> (
>> [Code]
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[Table1] ADD
>> CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
>> (
>> [ParentCode]
>> ) REFERENCES [dbo].[Table1] (
>> [Code]
>> )
>> I want to concat Column of Name:
>> Code Name ParentCode
>> 1 test NULL
>> 2 book NULL
>> 3 Cake 1
>> 4 Mouse 3
>> I want to concat column of Name for Code=4 and output will be: testCake
>> thanks in advance
> SELECT c.[Name] + b.[Name] AS ConcatName
> FROM Table1 AS a
> JOIN Table1 AS b ON a.ParentCode = b.Code
> JOIN Table1 AS c ON b.ParentCode = c.Code
> WHERE a.Code = 4
>
>|||On Apr 30, 9:13 am, "perspolis" <reza...@.hotmail.com> wrote:
> I want to do that for many levels as is not for 2 rows.
> "Ed Enstrom" <nos...@.invalid.net> wrote in message
> news:np8Zh.98$eH4.18@.newsfe12.lga...
>
> > perspolis wrote:
> >> this is my DDL:
> >> CREATE TABLE [dbo].[Table1] (
> >> [Code] [int] IDENTITY (1, 1) NOT NULL ,
> >> [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> >> [ParentCode] [int] NULL
> >> ) ON [PRIMARY]
> >> GO
> >> ALTER TABLE [dbo].[Table1] ADD
> >> CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
> >> (
> >> [Code]
> >> ) ON [PRIMARY]
> >> GO
> >> ALTER TABLE [dbo].[Table1] ADD
> >> CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
> >> (
> >> [ParentCode]
> >> ) REFERENCES [dbo].[Table1] (
> >> [Code]
> >> )
> >> I want to concat Column of Name:
> >> Code Name ParentCode
> >> 1 test NULL
> >> 2 book NULL
> >> 3 Cake 1
> >> 4 Mouse 3
> >> I want to concat column of Name for Code=4 and output will be: testCake
> >> thanks in advance
> > SELECT c.[Name] + b.[Name] AS ConcatName
> > FROM Table1 AS a
> > JOIN Table1 AS b ON a.ParentCode = b.Code
> > JOIN Table1 AS c ON b.ParentCode = c.Code
> > WHERE a.Code = 4- Hide quoted text -
> - Show quoted text -
If you are using SQL Server 2005 CTE with recursive query.
with temp as
(select convert(varchar(50),'') + convert(varchar(50),'')
name ,parentcode,code from table1 where code =4
union all
select convert(varchar(50),t1.name)+convert(varchar(50),t.name) as
name ,t1.parentcode,t1.code
from table1 t1 inner join temp t on t.parentcode = t1.code)
select name from temp where parentcode is null
Regards
Amish shah
http://shahamishm.tripod.com