Regarding Dynamic query...

Hi,

I have basic design question regarding dynamic query,

When we have to build a dynamic query (which has table name also as an input parameter),

->Is it better to write a stored procedure ..?

or

->directly specify the dynamic query and set the command type as text in .NET code...?

I think,since dynamic queries may not have the advantage of precompliation, it may not yield any performance in using SP's in such case..

Please through some light on this,

TIA

I don't think there's any right answer to this question. Personally I'd prefer to build a stored procedure for this because I like all of my database access code to be centrally located. Others will say it's a waste of time and effort to put this type of dynamic code into a stored procedure because it buys you nothing in terms of performance and it litters the database unnecessarily.

This is the type of topic that can generate a lot of debate. We'll see if anyone else bites. :-)

|||Why do you need to pass in a table name? Why does yourapplication have that much knowledge of your database? It soundsas though you have a serious architectural problem. You'vetightly coupled your application to your database. Figure out howto DECOUPLE the systems so that changing one won't break theother. One of the main benefits of stored procedures isencapsulation; passing in a table name defeats that goal.

sql

Regarding Creating Stored Procedures If One Does Not Exist

Hello,
Very quick question (I hope) regarding SQL Server:
I want to create a query that basically says that if a stored procedure (say
sproc_foo) does not exist, then create the stored procedure. I know that
there are easy ways to do this with creating tables/databases if they don't
exist, but how is this possible with stored procedures?
Regards,
James Simpson
Straightway Technologies Inc.Hi James,
You can try something like this
IF NOT EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
AND type in (N'P', N'PC'))
BEGIN
CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
...
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"James Simpson" wrote:
> Hello,
> Very quick question (I hope) regarding SQL Server:
> I want to create a query that basically says that if a stored procedure (say
> sproc_foo) does not exist, then create the stored procedure. I know that
> there are easy ways to do this with creating tables/databases if they don't
> exist, but how is this possible with stored procedures?
> Regards,
> James Simpson
> Straightway Technologies Inc.
>|||Ben,
Alas, that will not work as is, since CREATE PROCEDURE must be the first
statement in a batch. Scripting from SQL Server 2005 with the If Not Exists
check turned on produces:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @.statement = N'
CREATE PROC [dbo].[MyProc]
@.Parm1 char(6)
AS
-- Do Something'
So, as you can see it is a 'dynamic SQL' implementation. (I believe that
there is a CONNECT request for something better than this.)
RLF
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...
> Hi James,
> You can try something like this
> IF NOT EXISTS (SELECT * FROM sys.objects
> WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
> AND type in (N'P', N'PC'))
> BEGIN
> CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
> ...
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "James Simpson" wrote:
>> Hello,
>> Very quick question (I hope) regarding SQL Server:
>> I want to create a query that basically says that if a stored procedure
>> (say
>> sproc_foo) does not exist, then create the stored procedure. I know that
>> there are easy ways to do this with creating tables/databases if they
>> don't
>> exist, but how is this possible with stored procedures?
>> Regards,
>> James Simpson
>> Straightway Technologies Inc.|||Dear Ben/Russel,
Ben - I tried the solution you gave and for whatever reason it does not
appear to work. I have also seen this type of example online and this query
fails miserably on SQL Server 2005 Express. Russel, the solution you gave
works perfectly, and hopefully Microsoft resolves this issue.
Regards,
James Simpson
Straightway Technologies Inc.|||Hi Russell,
I agree with your note but that was not intended to be a final and complete
solution and my primary purpose was to show the IF NOT EXISTS part.
Perhaps it is like saying that your code does not work as is either, because
you have BEGIN but END is missing :-)
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Russell Fields" wrote:
> Ben,
> Alas, that will not work as is, since CREATE PROCEDURE must be the first
> statement in a batch. Scripting from SQL Server 2005 with the If Not Exists
> check turned on produces:
> IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id => OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
> BEGIN
> EXEC dbo.sp_executesql @.statement = N'
> CREATE PROC [dbo].[MyProc]
> @.Parm1 char(6)
> AS
> -- Do Something'
> So, as you can see it is a 'dynamic SQL' implementation. (I believe that
> there is a CONNECT request for something better than this.)
> RLF
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...
> >
> > Hi James,
> >
> > You can try something like this
> >
> > IF NOT EXISTS (SELECT * FROM sys.objects
> > WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
> > AND type in (N'P', N'PC'))
> > BEGIN
> > CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
> > ...
> >
> > Hope this helps,
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> >
> >
> > "James Simpson" wrote:
> >
> >> Hello,
> >> Very quick question (I hope) regarding SQL Server:
> >> I want to create a query that basically says that if a stored procedure
> >> (say
> >> sproc_foo) does not exist, then create the stored procedure. I know that
> >> there are easy ways to do this with creating tables/databases if they
> >> don't
> >> exist, but how is this possible with stored procedures?
> >>
> >> Regards,
> >>
> >> James Simpson
> >> Straightway Technologies Inc.
> >>
>
>|||Ben, OK. Sorry. I was just trying to emphasize that the 'natural' way will
not work as expected. - RLF
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:3C07E41D-E259-4887-ABCE-2C403FD6929A@.microsoft.com...
> Hi Russell,
> I agree with your note but that was not intended to be a final and
> complete
> solution and my primary purpose was to show the IF NOT EXISTS part.
> Perhaps it is like saying that your code does not work as is either,
> because
> you have BEGIN but END is missing :-)
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Russell Fields" wrote:
>> Ben,
>> Alas, that will not work as is, since CREATE PROCEDURE must be the first
>> statement in a batch. Scripting from SQL Server 2005 with the If Not
>> Exists
>> check turned on produces:
>> IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =>> OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
>> BEGIN
>> EXEC dbo.sp_executesql @.statement = N'
>> CREATE PROC [dbo].[MyProc]
>> @.Parm1 char(6)
>> AS
>> -- Do Something'
>> So, as you can see it is a 'dynamic SQL' implementation. (I believe that
>> there is a CONNECT request for something better than this.)
>> RLF
>>
>> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...
>> >
>> > Hi James,
>> >
>> > You can try something like this
>> >
>> > IF NOT EXISTS (SELECT * FROM sys.objects
>> > WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
>> > AND type in (N'P', N'PC'))
>> > BEGIN
>> > CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
>> > ...
>> >
>> > Hope this helps,
>> >
>> > Ben Nevarez
>> > Senior Database Administrator
>> > AIG SunAmerica
>> >
>> >
>> >
>> > "James Simpson" wrote:
>> >
>> >> Hello,
>> >> Very quick question (I hope) regarding SQL Server:
>> >> I want to create a query that basically says that if a stored
>> >> procedure
>> >> (say
>> >> sproc_foo) does not exist, then create the stored procedure. I know
>> >> that
>> >> there are easy ways to do this with creating tables/databases if they
>> >> don't
>> >> exist, but how is this possible with stored procedures?
>> >>
>> >> Regards,
>> >>
>> >> James Simpson
>> >> Straightway Technologies Inc.
>> >>
>>|||Russell, I think you were right. Thanks for the additional explanation.
I also got the code using the SQL Server Scripts Wizard and the 'Include if
NOT EXISTS' option turned on. The problem was that I replaced EXEC
dbo.sp_executesql with CREATE PROCEDURE to make the code easier to read and
understand.
Thanks,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Russell Fields" wrote:
> Ben, OK. Sorry. I was just trying to emphasize that the 'natural' way will
> not work as expected. - RLF
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:3C07E41D-E259-4887-ABCE-2C403FD6929A@.microsoft.com...
> >
> > Hi Russell,
> >
> > I agree with your note but that was not intended to be a final and
> > complete
> > solution and my primary purpose was to show the IF NOT EXISTS part.
> >
> > Perhaps it is like saying that your code does not work as is either,
> > because
> > you have BEGIN but END is missing :-)
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> >
> >
> > "Russell Fields" wrote:
> >
> >> Ben,
> >>
> >> Alas, that will not work as is, since CREATE PROCEDURE must be the first
> >> statement in a batch. Scripting from SQL Server 2005 with the If Not
> >> Exists
> >> check turned on produces:
> >>
> >> IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id => >> OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
> >> BEGIN
> >> EXEC dbo.sp_executesql @.statement = N'
> >> CREATE PROC [dbo].[MyProc]
> >> @.Parm1 char(6)
> >> AS
> >> -- Do Something'
> >>
> >> So, as you can see it is a 'dynamic SQL' implementation. (I believe that
> >> there is a CONNECT request for something better than this.)
> >>
> >> RLF
> >>
> >>
> >> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> >> news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...
> >> >
> >> > Hi James,
> >> >
> >> > You can try something like this
> >> >
> >> > IF NOT EXISTS (SELECT * FROM sys.objects
> >> > WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
> >> > AND type in (N'P', N'PC'))
> >> > BEGIN
> >> > CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
> >> > ...
> >> >
> >> > Hope this helps,
> >> >
> >> > Ben Nevarez
> >> > Senior Database Administrator
> >> > AIG SunAmerica
> >> >
> >> >
> >> >
> >> > "James Simpson" wrote:
> >> >
> >> >> Hello,
> >> >> Very quick question (I hope) regarding SQL Server:
> >> >> I want to create a query that basically says that if a stored
> >> >> procedure
> >> >> (say
> >> >> sproc_foo) does not exist, then create the stored procedure. I know
> >> >> that
> >> >> there are easy ways to do this with creating tables/databases if they
> >> >> don't
> >> >> exist, but how is this possible with stored procedures?
> >> >>
> >> >> Regards,
> >> >>
> >> >> James Simpson
> >> >> Straightway Technologies Inc.
> >> >>
> >>
> >>
> >>
>
>

Regarding Creating Stored Procedures If One Does Not Exist

Hello,
Very quick question (I hope) regarding SQL Server:
I want to create a query that basically says that if a stored procedure (say
sproc_foo) does not exist, then create the stored procedure. I know that
there are easy ways to do this with creating tables/databases if they don't
exist, but how is this possible with stored procedures?
Regards,
James Simpson
Straightway Technologies Inc.
Hi James,
You can try something like this
IF NOT EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
AND type in (N'P', N'PC'))
BEGIN
CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
...
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"James Simpson" wrote:

> Hello,
> Very quick question (I hope) regarding SQL Server:
> I want to create a query that basically says that if a stored procedure (say
> sproc_foo) does not exist, then create the stored procedure. I know that
> there are easy ways to do this with creating tables/databases if they don't
> exist, but how is this possible with stored procedures?
> Regards,
> James Simpson
> Straightway Technologies Inc.
>
|||Ben,
Alas, that will not work as is, since CREATE PROCEDURE must be the first
statement in a batch. Scripting from SQL Server 2005 with the If Not Exists
check turned on produces:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @.statement = N'
CREATE PROC [dbo].[MyProc]
@.Parm1 char(6)
AS
-- Do Something'
So, as you can see it is a 'dynamic SQL' implementation. (I believe that
there is a CONNECT request for something better than this.)
RLF
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...[vbcol=seagreen]
> Hi James,
> You can try something like this
> IF NOT EXISTS (SELECT * FROM sys.objects
> WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
> AND type in (N'P', N'PC'))
> BEGIN
> CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
> ...
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "James Simpson" wrote:
|||Dear Ben/Russel,
Ben - I tried the solution you gave and for whatever reason it does not
appear to work. I have also seen this type of example online and this query
fails miserably on SQL Server 2005 Express. Russel, the solution you gave
works perfectly, and hopefully Microsoft resolves this issue.
Regards,
James Simpson
Straightway Technologies Inc.
|||Hi Russell,
I agree with your note but that was not intended to be a final and complete
solution and my primary purpose was to show the IF NOT EXISTS part.
Perhaps it is like saying that your code does not work as is either, because
you have BEGIN but END is missing :-)
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Russell Fields" wrote:

> Ben,
> Alas, that will not work as is, since CREATE PROCEDURE must be the first
> statement in a batch. Scripting from SQL Server 2005 with the If Not Exists
> check turned on produces:
> IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
> OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
> BEGIN
> EXEC dbo.sp_executesql @.statement = N'
> CREATE PROC [dbo].[MyProc]
> @.Parm1 char(6)
> AS
> -- Do Something'
> So, as you can see it is a 'dynamic SQL' implementation. (I believe that
> there is a CONNECT request for something better than this.)
> RLF
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...
>
>
|||Ben, OK. Sorry. I was just trying to emphasize that the 'natural' way will
not work as expected. - RLF
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:3C07E41D-E259-4887-ABCE-2C403FD6929A@.microsoft.com...[vbcol=seagreen]
> Hi Russell,
> I agree with your note but that was not intended to be a final and
> complete
> solution and my primary purpose was to show the IF NOT EXISTS part.
> Perhaps it is like saying that your code does not work as is either,
> because
> you have BEGIN but END is missing :-)
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Russell Fields" wrote:
|||Russell, I think you were right. Thanks for the additional explanation.
I also got the code using the SQL Server Scripts Wizard and the 'Include if
NOT EXISTS' option turned on. The problem was that I replaced EXEC
dbo.sp_executesql with CREATE PROCEDURE to make the code easier to read and
understand.
Thanks,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Russell Fields" wrote:

> Ben, OK. Sorry. I was just trying to emphasize that the 'natural' way will
> not work as expected. - RLF
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:3C07E41D-E259-4887-ABCE-2C403FD6929A@.microsoft.com...
>
>

Regarding Creating Stored Procedures If One Does Not Exist

Hello,
Very quick question (I hope) regarding SQL Server:
I want to create a query that basically says that if a stored procedure (say
sproc_foo) does not exist, then create the stored procedure. I know that
there are easy ways to do this with creating tables/databases if they don't
exist, but how is this possible with stored procedures?
Regards,
James Simpson
Straightway Technologies Inc.Hi James,
You can try something like this
IF NOT EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
AND type in (N'P', N'PC'))
BEGIN
CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
...
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"James Simpson" wrote:

> Hello,
> Very quick question (I hope) regarding SQL Server:
> I want to create a query that basically says that if a stored procedure (s
ay
> sproc_foo) does not exist, then create the stored procedure. I know that
> there are easy ways to do this with creating tables/databases if they don'
t
> exist, but how is this possible with stored procedures?
> Regards,
> James Simpson
> Straightway Technologies Inc.
>|||Ben,
Alas, that will not work as is, since CREATE PROCEDURE must be the first
statement in a batch. Scripting from SQL Server 2005 with the If Not Exists
check turned on produces:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @.statement = N'
CREATE PROC [dbo].[MyProc]
@.Parm1 char(6)
AS
-- Do Something'
So, as you can see it is a 'dynamic SQL' implementation. (I believe that
there is a CONNECT request for something better than this.)
RLF
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...[vbcol=seagreen]
> Hi James,
> You can try something like this
> IF NOT EXISTS (SELECT * FROM sys.objects
> WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
> AND type in (N'P', N'PC'))
> BEGIN
> CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
> ...
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "James Simpson" wrote:
>|||Dear Ben/Russel,
Ben - I tried the solution you gave and for whatever reason it does not
appear to work. I have also seen this type of example online and this query
fails miserably on SQL Server 2005 Express. Russel, the solution you gave
works perfectly, and hopefully Microsoft resolves this issue.
Regards,
James Simpson
Straightway Technologies Inc.|||Hi Russell,
I agree with your note but that was not intended to be a final and complete
solution and my primary purpose was to show the IF NOT EXISTS part.
Perhaps it is like saying that your code does not work as is either, because
you have BEGIN but END is missing :-)
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Russell Fields" wrote:

> Ben,
> Alas, that will not work as is, since CREATE PROCEDURE must be the first
> statement in a batch. Scripting from SQL Server 2005 with the If Not Exis
ts
> check turned on produces:
> IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
> OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
> BEGIN
> EXEC dbo.sp_executesql @.statement = N'
> CREATE PROC [dbo].[MyProc]
> @.Parm1 char(6)
> AS
> -- Do Something'
> So, as you can see it is a 'dynamic SQL' implementation. (I believe that
> there is a CONNECT request for something better than this.)
> RLF
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...
>
>|||Ben, OK. Sorry. I was just trying to emphasize that the 'natural' way will
not work as expected. - RLF
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:3C07E41D-E259-4887-ABCE-2C403FD6929A@.microsoft.com...[vbcol=seagreen]
> Hi Russell,
> I agree with your note but that was not intended to be a final and
> complete
> solution and my primary purpose was to show the IF NOT EXISTS part.
> Perhaps it is like saying that your code does not work as is either,
> because
> you have BEGIN but END is missing :-)
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Russell Fields" wrote:
>|||Russell, I think you were right. Thanks for the additional explanation.
I also got the code using the SQL Server Scripts Wizard and the 'Include if
NOT EXISTS' option turned on. The problem was that I replaced EXEC
dbo.sp_executesql with CREATE PROCEDURE to make the code easier to read and
understand.
Thanks,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Russell Fields" wrote:

> Ben, OK. Sorry. I was just trying to emphasize that the 'natural' way wil
l
> not work as expected. - RLF
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:3C07E41D-E259-4887-ABCE-2C403FD6929A@.microsoft.com...
>
>sql

Regarding a sample query

Hello,
I am a very complex problem in front of me. Kindly help me out

in acheiving the same.

Say I have a table called InfoName with two columns Name and ID

InfoName

Name ID

OS 1
SP 2
Driver 3
fasdf **
** ***
** ****

(I AM INTERESTED IN ONLY FIRST THREE ROWS )

I have another table Infotxt which uses the ID of InfoName as

foreign key. It stores the value of this ID as shown

InFotxt

ID Value UnitNAME

1 Win 2000 raj
2 SP 4 raj
3 40 GB raj

1 Win xp jay
2 SP 2 jay
3 20 GB jay

NOw I need to present it with unitname's configuration of OS,

Sp and disk capacity like below.

name OS SP Drive
Raj win2000 sp4 40 GB
Jay winxp sp2 2o GB

That is, the rows of the InfoName table (first 3 rows) should

be the columns of my resultant query.

How can I achieve the same.
Please give me some ideas, and if the question is silly, I am

very sorry, because I am new to database queries...

Thanks,
cspek

cspek

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Try this:

SELECT unitname,
MIN(CASE WHEN id = 1 THEN value END) AS os,
MIN(CASE WHEN id = 2 THEN value END) AS sp,
MIN(CASE WHEN id = 3 THEN value END) AS drive
FROM InfoText
WHERE id BETWEEN 1 AND 3
GROUP BY unitname

You have to be more specific than "first three rows". Understand that
tables in SQL are not ordered. There is no fixed concept of a first,
second or Nth row.

This is called a cross-tab report. There are other solutions for
producing cross-tabs dynamically in SQL Server but many people would
say that you should do this instead in your client application or
reporting tool. See:

http://www.aspfaq.com/show.asp?id=2462

--
David Portas
SQL Server MVP
--|||Hello,
Thanks...Wil look into it...

cspek

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Regarding a query

Hello,
I am facing a very complex problem in front of me. I dont know if its complex...Kindly help me out in acheiving the same.

Say I have a table called InfoName with two columns Name and ID

InfoName

Name ID

OS 1
SP 2
Driver 3
fasdf **
** ***
** ****

(I AM INTERESTED IN ONLY FIRST THREE ROWS )

I have another table Infotxt which uses the ID of InfoName as foreign key. It stores the value of this ID as shown

InFotxt

ID Value UnitNAME

1 Win 2000 raj
2 SP 4 raj
3 40 GB raj

1 Win xp jay
2 SP 2 jay
3 20 GB jay

NOw I need to present it with unitname's configuration of OS, Sp and disk capacity like below.

name OS SP Drive
Raj win2000 sp4 40 GB
Jay winxp sp2 2o GB

That is, the rows of the InfoName table (first 3 rows) should be the columns of my resultant query.

How can I achieve the same.
Please give me some ideas, and if the question is silly, I am very sorry, because I am new to database queries...

Thanks,
cspekWhat you are trying to achieve is called a cross-tab query (or a pivot table). I think you should be able to do something like this:

SELECT
 I.UnitName,
 CASE WHEN I.ID = 1 THEN MAX(I.Value) ELSE NULL END AS OS,
 CASE WHEN I.ID = 2 THEN MAX(I.Value) ELSE NULL END AS SP,
 CASE WHEN I.ID = 3 THEN MAX(I.Value) ELSE NULL END AS Drive
FROM
 InFotxt I
GROUP BY
 I.UnitName
ORDER BY
 I.UnitName

Terri|||I got the following error with the above select query

Server: Msg 8120, Level 16, State 1, Line 1
Column 'I.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

You can try the below statement,

select
unitName,
[Value] = (SELECT [Value] from InFotxt Where ID = 1 and s.unitName = unitName),
[Value] = (SELECT [Value] from InFotxt Where ID = 2 and s.unitName = unitName),
[Value] = (SELECT [Value] from InFotxt Where ID = 3 and s.unitName = unitName)
from (
select
UnitName
from InFotxt
group by UnitName
) S|||You must agggregate any columns not contained in the group by, ie:

MAX(CASE WHEN I.ID = 1 THEN MAX(I.Value) ELSE NULL END) as col1

sql

Reg: Query Performence

Below query output results time taking very much.

SELECT NON EMPTY

{[Measures].[Score]} ON COLUMNS,

NON EMPTY {

(DESCENDANTS({[Organisation].[Organisation].&[36]},0),

[Question].[Type Id].[Category Id],

[Question].[Type Description].[Category Description],

[Question].[Short Description].[Short Description]

)}

HAVING [Measures].[Points]>0 ON ROWS

FROM (SELECT [Assignment].[Id].&[1] ON COLUMNS

FROM [Sample])

Total 7 dimensions 2 measure groups

Fact Table : 18,000,00 Records

Dimensions: 2000 Records

[Organisation].[Organisation] having 9 levels

like

Level1

Level2

LEVEl3

Is there any solution to improve the query performence.Any steps to inreasing performence of cube. Please help me. this is urgent for me

Could you please rewrite your query in terms of AdventureWorks?

Reg -MDX in Query issue

Hi,
I am using the following query in reporting services
="With Member [Measures].[Time] as '[TimeDim].CurrentMember.UniqueName'
MEMBER [Measures].[DisplayTime] AS '[TimeDim].CurrentMember.Name' SELECT
{[Measures].[Time],[Measures].[DisplayTime]} on columns, {Descendants({" &
Parameters!Year.value & "},[TimeDim].[Actual Date],BEFORE)} on rows from
ICMDestinationService"
I have given even spaces between the Query.
I am getting the following error
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
Cannot set the command text for data set 'Time_Dim'.
(rsErrorSettingCommandText) Get Online Help
Error during processing of the CommandText expression of dataset â'Time_Dimâ'.
(rsQueryCommandTextProcessingError)
Any ideas or solutionVerify the MDX runs in the MDX Sample Application first.
Also, make sure you are using Generic Query Designer - 3rd or 4th button to
the right of dataset dropdown in data window
Finally, be sure there are no carriage returns in the query.
If still having problems, see advice offered in
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp
--
-- "This posting is provided 'AS IS' with no warranties, and confers no
rights."
jhmiller@.online.microsoft.com
"Yalaman" <Yalaman@.discussions.microsoft.com> wrote in message
news:4BADB3C5-20E1-495C-B1A0-8914E30E34DE@.microsoft.com...
> Hi,
> I am using the following query in reporting services
> ="With Member [Measures].[Time] as '[TimeDim].CurrentMember.UniqueName'
> MEMBER [Measures].[DisplayTime] AS '[TimeDim].CurrentMember.Name' SELECT
> {[Measures].[Time],[Measures].[DisplayTime]} on columns, {Descendants({" &
> Parameters!Year.value & "},[TimeDim].[Actual Date],BEFORE)} on rows from
> ICMDestinationService"
> I have given even spaces between the Query.
>
> I am getting the following error
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> Cannot set the command text for data set 'Time_Dim'.
> (rsErrorSettingCommandText) Get Online Help
> Error during processing of the CommandText expression of dataset 'Time_Dim'.
> (rsQueryCommandTextProcessingError)
> Any ideas or solution
>

reg Bulk Insert

Hi,
I have a query reg the bulk insert. I have some data in text file with
tab delimetered and using BULK INSERT to get this data into a phycical table
.
i have more columns in the table than in the file. Table is having 10
columns and text file is having only 8 columns.
i am getting the below error ..
'Bulk insert data conversion error (type mismatch) for row 1, column 8 '
Is there any option for Bulk insert to specify about the number columns to
be inserted in the table?
Thanks in advance
BhaskarYou can do this by specifying a FORMATFILE on your BULK INSERT statement.
The format file describes the fields in the text file and which are to be
imported. See 'Using Format Files' the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Bhaskar" <Bhaskar@.discussions.microsoft.com> wrote in message
news:9AD6481B-C9E5-4676-ADB4-CF00234E944A@.microsoft.com...
> Hi,
> I have a query reg the bulk insert. I have some data in text file with
> tab delimetered and using BULK INSERT to get this data into a phycical
> table.
> i have more columns in the table than in the file. Table is having 10
> columns and text file is having only 8 columns.
> i am getting the below error ..
> 'Bulk insert data conversion error (type mismatch) for row 1, column 8 '
> Is there any option for Bulk insert to specify about the number columns to
> be inserted in the table?
> Thanks in advance
> Bhaskar|||Hi ,
Thanks for the reply. Is there any example with anybody about how to create
the Format file..
Thanks
Bhaskar
"Dan Guzman" wrote:

> You can do this by specifying a FORMATFILE on your BULK INSERT statement.
> The format file describes the fields in the text file and which are to be
> imported. See 'Using Format Files' the Books Online for more information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bhaskar" <Bhaskar@.discussions.microsoft.com> wrote in message
> news:9AD6481B-C9E5-4676-ADB4-CF00234E944A@.microsoft.com...
>
>|||The Books Online has examples of format files with fewer fields than the
destination table as well as with more fields.
Hope this helps.
Dan Guzman
SQL Server MVP
"Bhaskar" <Bhaskar@.discussions.microsoft.com> wrote in message
news:93DFC97E-B9D7-461E-BD17-702996CA0F96@.microsoft.com...
> Hi ,
> Thanks for the reply. Is there any example with anybody about how to
> create
> the Format file..
> Thanks
> Bhaskar
> "Dan Guzman" wrote:
>sql

Refresh selection in Query Analyzer?

I add a new table and I want to see it in my list, I can go to EM and refresh there but it appears I have to close QA in order to see the new table. Is there a refresh option in QA to see my new object?

thx,

Kat

Never Mind, I found it, use F5.

Kat

Refresh of Data in Crystal Report

Hello,

I'm developing Crystal Report and data is being fetched using Query. Now each time I "run" report, I get stale data. I tested query several times and that is working fine.

I also checked that,

1. File->Report Options-> Save Data with Report is UNChecked.
2. File->Options->Reporting->Save Data with Report is UNChecked.
3. File->Options->Reporting->Discard Saved Data while Loading Report is Checked.

Whenever I open report, I only see Design tab and no Preview tab. however, on running report, I get stale data. Please let me know if you know solution to this problem.

Thanks a lot in Advance,
Regards,
RupeshI am having a very similar problem. I just gave one of our users a new computer. She was running windows 2000 and she is now running windows xp. I installed crystal reports for her and imported all of her ODBC settings from the old computer to the new computer. There are two crystal reports that when she opens them, it only shows the "design tab", but no "preview tab". When I go back and open those two reports from her old computer, they open fine. I see that nobody responded to your post. Did you ever find a solution to the problem?

Thanks - Jeff|||Rupsan,
What do you mean by stale data?
Also what Ver are you using?

Texasman,
The reason your preview tab does not show up is because the savedata box is not checked in report options.|||ya exactly dear when you uncheck the save data you don't find any preview tab if you want preview click on print preview in report you will find it.

and rupsan i also don't understand what stale data means.

thanks|||I have the same issue. I have unchecked (and checked) the appropriate boxes as did the OP. On open, I do not get a preview tab (This is what I expected). However, on refresh the data on my report is last weeks data. I looked directly at the data table and there have been several additional entries that are not shown on the report. I have disconnected and reconnected to the databse, all to no avail. Can anyone figure this out?? Thanks.

Refresh default value

Hello,

I have report parameters B with default from query.

This query depend on other report parameters A that updates befor report parameter B.

While user open the report and update report parameter A, the report parameter B get default value with the right default value but when user change his selection in parameter A, the default value in parameter B doesn't changed

Any idea ?

Smashing values that the user selected (either explicitly or implicitly by not changing the default) is usually considered to be a bad thing.There exist uncommon cases where the author of the report can reasonably know the user would expect their previously selected value will be overridden. Unfortunately, RS currently doesn't have a feature which would allow the report author to distinguish the common case from the uncommon case. As a result, there is no choice but to go with a design that optimizes for the common case. (Note: RS 2000 defaulted to smashing the user’s selection with the default when an upstream parameter value changed. The resulted in a large volume of customer complaints).
One option you may want to explore which may help in this particular case: If the Valid Values list changes (as a result of the upstream parameter value changing) and it no longer contains the user’s selection, we are forced to revert to the default. Careful use of the Valid Values list can in many cases simulate the desired behavior of always overriding the user’s selection back to the default.|||

Sorry but I don't understand your answer.

I have parameter "Date Unit" (Shift, Day, Week, Month, Quarter , Year)

I have parameter "From Date"

I want while user select under "Date Unit" :

Shift or Day the from date will be Today - 7 days

Week the from date will be today - 12 weeks

Month, Quarter the from date will be today - 12 month

Year - the from date will ve today - 2 years.

Any option to do that ?

|||You can trying plugging in an expression for the default value of the From Date cascading parameter that checks Parameters!DateUnit.Value but I think this will only work the first time the report is rendered. Once the user changes the Date Unit parameter and the report refreshes, the default value of the From Date parameter won't be reset. I explained the reasons for this behaviour in my previous post.|||

This is my problem

"Once the user changes the Date Unit parameter and the report refreshes, the default value of the From Date parameter won't be reset"

|||

I know this is an older post but I found it while searching for something else. Would this work for your situation?

Write a stored procedure that takes a parameter where that parameter is the "Date Unit" you're selecting as a VARCHAR. In that sproc, you test that value and then return dynamically the correct date you want as the default for your From Date.

The sproc always returns a single row with a single column called DEFAULT_DATE or something, but what it returns is dynamic based on the parameter input. Then you create a dataset using this sproc and set it's input parameter to your Paarameters!DateUnit and set your default for your From Date to use this dataset's DEFAULT_DATE value.

I think I've done that in the past. Every time the user changes the date unit, the from date will change to the appropriate default.

...I think.

:)

sql

Refresh default value

Hello,

I have report parameters B with default from query.

This query depend on other report parameters A that updates befor report parameter B.

While user open the report and update report parameter A, the report parameter B get default value with the right default value but when user change his selection in parameter A, the default value in parameter B doesn't changed

Any idea ?

Smashing values that the user selected (either explicitly or implicitly by not changing the default) is usually considered to be a bad thing.There exist uncommon cases where the author of the report can reasonably know the user would expect their previously selected value will be overridden. Unfortunately, RS currently doesn't have a feature which would allow the report author to distinguish the common case from the uncommon case. As a result, there is no choice but to go with a design that optimizes for the common case. (Note: RS 2000 defaulted to smashing the user’s selection with the default when an upstream parameter value changed. The resulted in a large volume of customer complaints).
One option you may want to explore which may help in this particular case: If the Valid Values list changes (as a result of the upstream parameter value changing) and it no longer contains the user’s selection, we are forced to revert to the default. Careful use of the Valid Values list can in many cases simulate the desired behavior of always overriding the user’s selection back to the default.|||

Sorry but I don't understand your answer.

I have parameter "Date Unit" (Shift, Day, Week, Month, Quarter , Year)

I have parameter "From Date"

I want while user select under "Date Unit" :

Shift or Day the from date will be Today - 7 days

Week the from date will be today - 12 weeks

Month, Quarter the from date will be today - 12 month

Year - the from date will ve today - 2 years.

Any option to do that ?

|||You can trying plugging in an expression for the default value of the From Date cascading parameter that checks Parameters!DateUnit.Value but I think this will only work the first time the report is rendered. Once the user changes the Date Unit parameter and the report refreshes, the default value of the From Date parameter won't be reset. I explained the reasons for this behaviour in my previous post.|||

This is my problem

"Once the user changes the Date Unit parameter and the report refreshes, the default value of the From Date parameter won't be reset"

|||

I know this is an older post but I found it while searching for something else. Would this work for your situation?

Write a stored procedure that takes a parameter where that parameter is the "Date Unit" you're selecting as a VARCHAR. In that sproc, you test that value and then return dynamically the correct date you want as the default for your From Date.

The sproc always returns a single row with a single column called DEFAULT_DATE or something, but what it returns is dynamic based on the parameter input. Then you create a dataset using this sproc and set it's input parameter to your Paarameters!DateUnit and set your default for your From Date to use this dataset's DEFAULT_DATE value.

I think I've done that in the past. Every time the user changes the date unit, the from date will change to the appropriate default.

...I think.

:)

Refresh a table

Can i referesh a open table?

The scenario is that i have two windows open... one is the table and the other is the query that modifies the same table. I was wondering if there is a way to refresh the table so that after i modify the table i don't have to close it and reopen it to verify the changes.

cheers

In open table, you can refresh by clicking on the "Execute" button, the red exclamation point.

Peter

sql

Reformatting Parameters before using them in Sql query

Hello,
We use Reporting Services to do reports from a (rather old iSeries /
AS400) database. Date values are stored as numeric data in the format
YYYYMMDD, rather than with a proper "Date" type. This is quite common in
older host-based databases.
In one of our reports, the user should be able to select data by
entering date-from and date-to into parameters, before starting the report.
All that we are able to do right now is to let the user enter the data
in the original YYYYMMDD format, in order to be able to use it in a
parametrized query. What I want to do, is:
a) Let the user enter a date as a parameter in a proper Date format
b) Before executing the query, convert this parameter to YYYYMMDD number
format
I guess this conversion should be done in a custom assembly. However,
there doesn't seem to be a "plug" where I could attach this conversion
code. The only thing I found is to use Generic Query - but this would
really be last resort, because I think it is very ugly and not really
RAD, to lose all of the graphic helpers just because of the date
conversions.
Any help would be greatly appreciated.
Ursuse the following custom code:
Public Function ConvertToYYYYMMDD(pYourDateFormat as Date) as String
ConvertToYYYYMMDD = Format(pYourDateFormat, "yyyyMMdd")
End Function 'ConvertToYYYYMMDD
Then in your query you would use for your parameter portion:
code.ConvertToYYYYMMDD(Parameters!YourDateParameter.Value)
"Urs Eichmann" <xx@.yy.ch> wrote in message
news:eRjnk4%23nEHA.324@.TK2MSFTNGP11.phx.gbl...
> Hello,
> We use Reporting Services to do reports from a (rather old iSeries /
> AS400) database. Date values are stored as numeric data in the format
> YYYYMMDD, rather than with a proper "Date" type. This is quite common in
> older host-based databases.
> In one of our reports, the user should be able to select data by
> entering date-from and date-to into parameters, before starting the
report.
> All that we are able to do right now is to let the user enter the data
> in the original YYYYMMDD format, in order to be able to use it in a
> parametrized query. What I want to do, is:
> a) Let the user enter a date as a parameter in a proper Date format
> b) Before executing the query, convert this parameter to YYYYMMDD number
> format
> I guess this conversion should be done in a custom assembly. However,
> there doesn't seem to be a "plug" where I could attach this conversion
> code. The only thing I found is to use Generic Query - but this would
> really be last resort, because I think it is very ugly and not really
> RAD, to lose all of the graphic helpers just because of the date
> conversions.
> Any help would be greatly appreciated.
> Urs|||Thanks mike, but AFAIK I can only insert "code.convertto..." into my
query if I don't use the Graphic Query designer and instead use the
Generic Query Designer, which I don't want to (see my first message).
Urs
mike wrote:
> use the following custom code:
> Public Function ConvertToYYYYMMDD(pYourDateFormat as Date) as String
> ConvertToYYYYMMDD = Format(pYourDateFormat, "yyyyMMdd")
> End Function 'ConvertToYYYYMMDD
> Then in your query you would use for your parameter portion:
> code.ConvertToYYYYMMDD(Parameters!YourDateParameter.Value)
>
> "Urs Eichmann" <xx@.yy.ch> wrote in message
> news:eRjnk4%23nEHA.324@.TK2MSFTNGP11.phx.gbl...
>>Hello,
>>We use Reporting Services to do reports from a (rather old iSeries /
>>AS400) database. Date values are stored as numeric data in the format
>>YYYYMMDD, rather than with a proper "Date" type. This is quite common in
>>older host-based databases.
>>In one of our reports, the user should be able to select data by
>>entering date-from and date-to into parameters, before starting the
> report.
>>All that we are able to do right now is to let the user enter the data
>>in the original YYYYMMDD format, in order to be able to use it in a
>>parametrized query. What I want to do, is:
>>a) Let the user enter a date as a parameter in a proper Date format
>>b) Before executing the query, convert this parameter to YYYYMMDD number
>>format
>>I guess this conversion should be done in a custom assembly. However,
>>there doesn't seem to be a "plug" where I could attach this conversion
>>code. The only thing I found is to use Generic Query - but this would
>>really be last resort, because I think it is very ugly and not really
>>RAD, to lose all of the graphic helpers just because of the date
>>conversions.
>>Any help would be greatly appreciated.
>>Urs
>
>|||Urs,
Did you ever find a work around. I am in the same boat.
Thanks, Eric
"Urs Eichmann" wrote:
> Thanks mike, but AFAIK I can only insert "code.convertto..." into my
> query if I don't use the Graphic Query designer and instead use the
> Generic Query Designer, which I don't want to (see my first message).
> Urs
>
> mike wrote:
> > use the following custom code:
> >
> > Public Function ConvertToYYYYMMDD(pYourDateFormat as Date) as String
> > ConvertToYYYYMMDD = Format(pYourDateFormat, "yyyyMMdd")
> > End Function 'ConvertToYYYYMMDD
> >
> > Then in your query you would use for your parameter portion:
> > code.ConvertToYYYYMMDD(Parameters!YourDateParameter.Value)
> >
> >
> >
> > "Urs Eichmann" <xx@.yy.ch> wrote in message
> > news:eRjnk4%23nEHA.324@.TK2MSFTNGP11.phx.gbl...
> >
> >>Hello,
> >>We use Reporting Services to do reports from a (rather old iSeries /
> >>AS400) database. Date values are stored as numeric data in the format
> >>YYYYMMDD, rather than with a proper "Date" type. This is quite common in
> >>older host-based databases.
> >>
> >>In one of our reports, the user should be able to select data by
> >>entering date-from and date-to into parameters, before starting the
> >
> > report.
> >
> >>All that we are able to do right now is to let the user enter the data
> >>in the original YYYYMMDD format, in order to be able to use it in a
> >>parametrized query. What I want to do, is:
> >>
> >>a) Let the user enter a date as a parameter in a proper Date format
> >>b) Before executing the query, convert this parameter to YYYYMMDD number
> >>format
> >>
> >>I guess this conversion should be done in a custom assembly. However,
> >>there doesn't seem to be a "plug" where I could attach this conversion
> >>code. The only thing I found is to use Generic Query - but this would
> >>really be last resort, because I think it is very ugly and not really
> >>RAD, to lose all of the graphic helpers just because of the date
> >>conversions.
> >>
> >>Any help would be greatly appreciated.
> >>
> >>Urs
> >
> >
> >
>sql

Referencing textbox values in expressions

I have a table displaying output from a query. I have cells "a" and "b"
that contain expressions performing calculations on fields in the query. I
would like to create an expression in cell "c" that performs a calculation
on the values in cells "a" and "b".
I can't seem to figure out how to reference the values of the textboxes in
the table. Even putting something as simple as "=a.text" or "=a.value"
returns an error that "a" is not declared ("a" being the name of the
textbox control).
Considering you need "Fields!" for referencing fields and "Parameters!" for
referencing parameters, I suspect that there is something comparable for
referencing the report controls, but I can't find any references to it in
the Books Online.
Does anyone know if this is possible, and if so how?
Thanks.One thing you might want to consider is adding a field to your dataset. When
you have the list of fields that you would drag and drop from when in the
layout tab, do a right mouse click, add. Pick calculated field. Add the
expressions for cell a and cell b. Now these are considered a field like
anything else. You can now create an expression in c that refers to field a
and field b. I find things more understandable and cleaner. Plus, let's say
you want to do a sum or something else on a grouping. No problem, it is just
a field like any other.
This is not real discoverable but in some situations it really makes things
simplier.
HTH,
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Kursplat" <newsgroup-spam@.microsoft.com> wrote in message
news:%23$H72k1%23FHA.2676@.TK2MSFTNGP10.phx.gbl...
>I have a table displaying output from a query. I have cells "a" and "b"
> that contain expressions performing calculations on fields in the query.
> I
> would like to create an expression in cell "c" that performs a calculation
> on the values in cells "a" and "b".
> I can't seem to figure out how to reference the values of the textboxes in
> the table. Even putting something as simple as "=a.text" or "=a.value"
> returns an error that "a" is not declared ("a" being the name of the
> textbox control).
> Considering you need "Fields!" for referencing fields and "Parameters!"
> for
> referencing parameters, I suspect that there is something comparable for
> referencing the report controls, but I can't find any references to it in
> the Books Online.
> Does anyone know if this is possible, and if so how?
> Thanks.|||I would do that if I could (I've tried), but the expression in the
source textboxes are calling functions in an Assembly, which I've
learned the hard way can't be called from the query (it crashes Visual
Studio every time I try).
So, I'm still looking for a way to have the expression of one textbox in
a table reference the value of another textbox in that table.
Thanks for the suggestion, though.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in
news:e$iAA91#FHA.952@.TK2MSFTNGP10.phx.gbl:
> One thing you might want to consider is adding a field to your
> dataset. When you have the list of fields that you would drag and drop
> from when in the layout tab, do a right mouse click, add. Pick
> calculated field. Add the expressions for cell a and cell b. Now these
> are considered a field like anything else. You can now create an
> expression in c that refers to field a and field b. I find things more
> understandable and cleaner. Plus, let's say you want to do a sum or
> something else on a grouping. No problem, it is just a field like any
> other.
> This is not real discoverable but in some situations it really makes
> things simplier.
> HTH,
>|||I found it in another post. You use "ReportItems!" to reference the
controls on the report.
Kursplat <newsgroup-spam@.microsoft.com> wrote in news:eHD9dGB$FHA.1028
@.TK2MSFTNGP11.phx.gbl:
> So, I'm still looking for a way to have the expression of one textbox in
> a table reference the value of another textbox in that table.|||Hi Kursplat,
For a workaround, in your query, you can set up the calculations there,
then refer to them in cells a and b in your table. From there, you can add
another cell c, and use the Fields! method to call the values in cells a and
b and manipulate those values as you wish in an expression.
Does that make sense?
Cheers,
Daniel.
"Kursplat" wrote:
> I would do that if I could (I've tried), but the expression in the
> source textboxes are calling functions in an Assembly, which I've
> learned the hard way can't be called from the query (it crashes Visual
> Studio every time I try).
> So, I'm still looking for a way to have the expression of one textbox in
> a table reference the value of another textbox in that table.
> Thanks for the suggestion, though.
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in
> news:e$iAA91#FHA.952@.TK2MSFTNGP10.phx.gbl:
> > One thing you might want to consider is adding a field to your
> > dataset. When you have the list of fields that you would drag and drop
> > from when in the layout tab, do a right mouse click, add. Pick
> > calculated field. Add the expressions for cell a and cell b. Now these
> > are considered a field like anything else. You can now create an
> > expression in c that refers to field a and field b. I find things more
> > understandable and cleaner. Plus, let's say you want to do a sum or
> > something else on a grouping. No problem, it is just a field like any
> > other.
> >
> > This is not real discoverable but in some situations it really makes
> > things simplier.
> >
> > HTH,
> >
> >
>

Referencing query results by number

Is there a way to reference the results of a query by column number?
i.e. select Name, City from tblEmployee
so referencing field 1 from the query in a table will list all the employee
names
Thanks.select Name As Field1, City As Field2 from tblEmployee
OR
select au_id As [1], city As [2], state As [3] from authors
[In the latter case, the fields will be generated as ID1, ID2, and ID3
respectively.]
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ali Jaffer" <Ali Jaffer@.discussions.microsoft.com> wrote in message
news:0BB8CBE4-0FCD-4444-B00B-99EDE49CF5D0@.microsoft.com...
> Is there a way to reference the results of a query by column number?
> i.e. select Name, City from tblEmployee
> so referencing field 1 from the query in a table will list all the
employee
> names
> Thanks.

Referencing Multi-value parameters in SQL code.

Hi everyone,

I have a report with 2 parameters - Account Manager and Company.

They are both populated from SQL Queries.

The Company query uses the value of the Account Manager parameter in the Where clause so that if an Account Manager is slected only the companies they are in charge of are displayed in the Company parameter.

My Select statement for the Company parameter list is as follows:

SELECT C.Company_Name
FROM Company C INNER JOIN
Employee E ON C.Account_Manager_Id = E.Employee_Id
WHERE (C.Type LIKE ('Customer%') OR C.Type = ('Partner - Customer'))
AND (E._Full_Name = @.Account_Manager)

How can I get this to work if I change the Account Manager parameter to be a Multi-value parameter?

I have tried replacing the "E._Full_Name = @.Account_Manager" with "E._Full_Name IN @.Account_Manager" but this does not work - is there another way to get this working?

Thanks,

Paul.

The IN clause requires the use of brackets.

Try E._Full_Name IN (@.Account_Manager)

|||That did the trick, thanks!

Referencing another Database in a query

I'm in query analyzer and I have 2 databases on this same server that I want
to reference in a query. How do I go about doing that? In other words, I
want to create an insert query which takes records from one database and
inserts them into a table in another database.
Thanks,
BrianAssuming that you have appropriate rights in both databases, use three
part naming:
INSERT INTO db1.owner.tablename (Column)
SELECT COLUMN
FROM db2.owner.tablename
HTH,
Stu|||Thanks - I thought I tried that, but I didn't include the 'owner'
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1145403151.928575.228920@.i39g2000cwa.googlegroups.com...
> Assuming that you have appropriate rights in both databases, use three
> part naming:
> INSERT INTO db1.owner.tablename (Column)
> SELECT COLUMN
> FROM db2.owner.tablename
> HTH,
> Stu
>|||Do you have access to both databases?
In SQL each object has a four part identifier which enables cross-database
and cross-server access, provided that the user privileges also allow it.
More information under "Using Identifiers as Object Names" in Books Online.
ML
http://milambda.blogspot.com/|||Here:
http://msdn2.microsoft.com/en-us/library/ms187879.aspx
ML
http://milambda.blogspot.com/

Referencing a conditional column in the WHERE clause -- Possible?

I have two tables which I'm joining in a query. In the join, I'm creating a column whose value is conditional (see columns Actual_Latitude and Actual_Longitude below). Is it possible to reference the created column in the WHERE clause?

Consider the following query:

---

SELECT
S.StationID
, P.PoleID
, CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Latitude
WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Latitude
ELSE NULL
END AS Actual_Latitude
, CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Longitude
WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Longitude
ELSE NULL
END AS Actual_Longitude
FROM Stations S
LEFT JOIN Poles P on S.PoleID = P.PoleID

---

I'd like to be able to add the following:

---

WHERE Actual_Latitude > 50

---

...But, I'm getting an "Invalid column name" error. Is this possible in some way?

The benefit, of course, would be that I wouldn't have to repeat the conditions in the WHERE clause.

Unfortunately, you can't.

You are creating an ALIAS for an expression, and the expression is not 'known' by that ALIAS in the 'acquisition' part of the query. Once the data is acquired, you can refer to the ALIAS in the ORDER BY because a 'derived table' has been determined.

You could, however, wrap this query in another, and use the ALIAS in the outer query. That does't provide much help with filtering though...

The 'best' option is to repeat the CASE structure for Actual_Latitude in the WHERE clause.

|||

I agree with Arnie for the most part. This will work:

SELECT S.StationID , P.PoleID

--NOTE: change to this means a change to the where clause for Actual_latitude!!
, CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Latitude
WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Latitude
ELSE NULL
END AS Actual_Latitude
, CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Longitude
WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Longitude
ELSE NULL
END AS Actual_Longitude
FROM Stations S
LEFT JOIN Poles P on S.PoleID = P.PoleID
where CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Latitude
WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Latitude
ELSE NULL
END > 50 --Actual_Latitude > 50

In 2005, I would probably try this and see how it works out. It probably will have the same plan and is a bit clearer. If this is a highly used, performance intensive operation I would consider rewriting the query to eliminate the CASE in the where clause and express it as just expressions (it could be done, I think):

WITH stationQuery AS (

SELECT S.StationID , P.PoleID
, CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Latitude
WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Latitude
ELSE NULL
END AS Actual_Latitude
, CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Longitude
WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Longitude
ELSE NULL
END AS Actual_Longitude
FROM Stations S
LEFT JOIN Poles P on S.PoleID = P.PoleID
select *

from stationQuery

where actual_latitude > 50

The thing is, you are not going to get good performance no matter how you do it. Since the both of your tables in the join are tied up in the CASE expression, very unlikely to get any index utilization. You could also do it as a derived table in 2000.