difficult SQL select statement

Hi all,

i have a table containing 24 columns, i would like to generate a new table by input the program gets from the user concurning of the columns he would like to see.

sound like an easy "select ? from table", but the thing is that how can the function know how to get a different number of variables, i mean, one time the user will want to see one column, and afterwards he will want to see 10 columns, is there a solution except generating 24 functions?.

i looked in all kinds of SQL tutorials and nothing came up so i came here, tnx for your help!.

Alon.

public string GenerateSqlSelect(string[] columnNames){

return "SELECT "+String.Join(", ",columnNames)+" FROM MyTable";

}

|||You could use params keyword to specify that your function has a variable number of arguments.
|||How user will know what are the columns from the table. Does he will write them, or select them using check boxes or some other method. I realy don't understand what are you for. Can you describe how user interface will look like, or some scenario of application work.
It is common practice that you always get all columns from table, but user can control which column want to see, for example in a grid or listview. So he will at first see all columns but it will be able to remove some of them. That configuration can be saved and used for next application start.

difficult SQL select statement

Hi all,

i have a table containing 24 columns, i would like to generate a new table by input the program gets from the user concurning of the columns he would like to see.

sound like an easy "select ? from table", but the thing is that how can the function know how to get a different number of variables, i mean, one time the user will want to see one column, and afterwards he will want to see 10 columns, is there a solution except generating 24 functions?.

i looked in all kinds of SQL tutorials and nothing came up so i came here, tnx for your help!.

Alon.

public string GenerateSqlSelect(string[] columnNames){

return "SELECT "+String.Join(", ",columnNames)+" FROM MyTable";

}

|||You could use params keyword to specify that your function has a variable number of arguments.
|||How user will know what are the columns from the table. Does he will write them, or select them using check boxes or some other method. I realy don't understand what are you for. Can you describe how user interface will look like, or some scenario of application work.
It is common practice that you always get all columns from table, but user can control which column want to see, for example in a grid or listview. So he will at first see all columns but it will be able to remove some of them. That configuration can be saved and used for next application start.sql

difficult SQL select statement

Hi all,

i have a table containing 24 columns, i would like to generate a new table by input the program gets from the user concurning of the columns he would like to see.

sound like an easy "select ? from table", but the thing is that how can the function know how to get a different number of variables, i mean, one time the user will want to see one column, and afterwards he will want to see 10 columns, is there a solution except generating 24 functions?.

i looked in all kinds of SQL tutorials and nothing came up so i came here, tnx for your help!.

Alon.

public string GenerateSqlSelect(string[] columnNames){

return "SELECT "+String.Join(", ",columnNames)+" FROM MyTable";

}

|||You could use params keyword to specify that your function has a variable number of arguments.
|||How user will know what are the columns from the table. Does he will write them, or select them using check boxes or some other method. I realy don't understand what are you for. Can you describe how user interface will look like, or some scenario of application work.
It is common practice that you always get all columns from table, but user can control which column want to see, for example in a grid or listview. So he will at first see all columns but it will be able to remove some of them. That configuration can be saved and used for next application start.

different results in query analyzer and .net

ok the following statement returns the correct results in sql query analyzer but in the .net environment with c# it returns back less 3 records

SELECT SUM(ao.amount),bl.dpc,bl.city from billinglocation bl
inner join shippinglocation sl on bl.billinglocationid = sl.billinglocationid
inner join absorbentorder ao on sl.shippinglocationid = ao.shippinglocationid
group by bl.dpc, bl.city

billinglocation 1 - 1 shippinglocation 1 - many absorbentorder

anybody have any ideas why this might be occuring
thanks!

are you connecting to the right database?|||If you are receiving different results then you are not running the exact same thing. Are there any parameters involved? As Dinakar asked, are you sure you are running the query against the same database? We are probably going to need to see your C# code in order to be of any real help.

Different results

I am trying to return a two character result for the day number and was
wondering why I get a one character day returned in Statement A, and a two
character result in Statement B?
declare @.day varchar(2)
--Statement A:
set @.day = case len(day(getdate()))
when 1 then cast('0' + cast (day(getdate()) as varchar(1)) as
varchar(2))
else
day(getdate())
end
print @.day
--the result is a one character day, if the date was February 7, 2005, the
result = 7
--Statement B:
if len(day(getdate())) = 1
set @.day = '0' + cast (day(getdate()) as varchar(1))
else
set @.day = day(getdate())
print @.day
--the result is a two character day, if the date was February 7, 2005, the
result = 07
Message posted via http://www.webservertalk.comThis is because of implicit conversion and datatype precedence for case/when
statement. Your 'else' clause has higher precedence (INT). Thus, your TRUE
(varchar(2)) clause has to be implicitly converted to INT (i.e. '07' -> 7).
This is the fix.
declare @.day varchar(2)
--Statement A:
set @.day = case len(day(getdate()))
when 1 then cast('0' + cast (day(getdate()) as varchar(1)) as
varchar(2))
else
cast(day(getdate()) as varchar(2)) --explicit conversion to
varchar
end
print @.day
And here is a trick without case/when or if/else:
e.g.
set @.day = right(day(getdate())+100,2)
print @.day
-oj
"Robert Richards via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:79f6ea5cca2a4a679dea642da149fc43@.SQ
webservertalk.com...
>I am trying to return a two character result for the day number and was
> wondering why I get a one character day returned in Statement A, and a two
> character result in Statement B?
> declare @.day varchar(2)
> --Statement A:
> set @.day = case len(day(getdate()))
> when 1 then cast('0' + cast (day(getdate()) as varchar(1)) as
> varchar(2))
> else
> day(getdate())
> end
> print @.day
> --the result is a one character day, if the date was February 7, 2005, the
> result = 7
> --Statement B:
> if len(day(getdate())) = 1
> set @.day = '0' + cast (day(getdate()) as varchar(1))
> else
> set @.day = day(getdate())
> print @.day
> --the result is a two character day, if the date was February 7, 2005, the
> result = 07
> --
> Message posted via http://www.webservertalk.com|||The answer - don't use implicit conversions. And read BOL about the CASE
expression and how it determines the datatype of the returned value. Below
is a quick script that demonstrates two much easier ways to accomplish the
task.
declare @.test datetime
set @.test = '20050115'
select '0' + cast(datepart(day, @.test) as varchar(2))
,RIGHT('0' + cast(datepart(day, @.test) as varchar(2)), 2)
,convert(char(2), @.test, 4)
set @.test = '20050102'
select '0' + cast(datepart(day, @.test) as varchar(2))
,RIGHT('0' + cast(datepart(day, @.test) as varchar(2)), 2)
,convert(char(2), @.test, 4)
"Robert Richards via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:79f6ea5cca2a4a679dea642da149fc43@.SQ
webservertalk.com...
> I am trying to return a two character result for the day number and was
> wondering why I get a one character day returned in Statement A, and a two
> character result in Statement B?
> declare @.day varchar(2)
> --Statement A:
> set @.day = case len(day(getdate()))
> when 1 then cast('0' + cast (day(getdate()) as varchar(1)) as
> varchar(2))
> else
> day(getdate())
> end
> print @.day
> --the result is a one character day, if the date was February 7, 2005, the
> result = 7
> --Statement B:
> if len(day(getdate())) = 1
> set @.day = '0' + cast (day(getdate()) as varchar(1))
> else
> set @.day = day(getdate())
> print @.day
> --the result is a two character day, if the date was February 7, 2005, the
> result = 07
> --
> Message posted via http://www.webservertalk.com|||Robert,
The diff are here:
(A)
> when 1 then cast('0' + cast (day(getdate()) as varchar(1)) as
(B)
> set @.day = '0' + cast (day(getdate()) as varchar(1))
In 'A' you are casting the whole expression to varchar(1), that is why you
get 1 character.
Another way of doing this is:
set @.day = right('0' + ltrim(day(getdate())), 2)
AMB
"Robert Richards via webservertalk.com" wrote:

> I am trying to return a two character result for the day number and was
> wondering why I get a one character day returned in Statement A, and a two
> character result in Statement B?
> declare @.day varchar(2)
> --Statement A:
> set @.day = case len(day(getdate()))
> when 1 then cast('0' + cast (day(getdate()) as varchar(1)) as
> varchar(2))
> else
> day(getdate())
> end
> print @.day
> --the result is a one character day, if the date was February 7, 2005, the
> result = 7
> --Statement B:
> if len(day(getdate())) = 1
> set @.day = '0' + cast (day(getdate()) as varchar(1))
> else
> set @.day = day(getdate())
> print @.day
> --the result is a two character day, if the date was February 7, 2005, the
> result = 07
> --
> Message posted via http://www.webservertalk.com
>|||I am completely wrong. I missed the "as varchar(2)" part. OJ and Scott post
s
explain the problem correctly.
AMB
"Alejandro Mesa" wrote:
> Robert,
> The diff are here:
> (A)
> (B)
> In 'A' you are casting the whole expression to varchar(1), that is why you
> get 1 character.
> Another way of doing this is:
> set @.day = right('0' + ltrim(day(getdate())), 2)
>
> AMB
>
> "Robert Richards via webservertalk.com" wrote:
>

different records result if a use N (unicode data)

A same query with a NOT LIKE statement and a wild character % returns
different records result if a use N (that means that the string follow is
unicode data) or not.
Par example:
select * from company
where company_name not like N'%'
select * from company
where company_name not like '%'
the result of the two queries is different.
How is it possible?
Company_Name is a varchar field (not a nvarchar)
RegardsI forget this happen if there are at least one record company with
company_name NULL
Thanks

Different query plans for view and view definition statement

I compared view query plan with query plan if I run the same statement
from view definition and get different results. View plan is more
expensive and runs longer. View contains 4 inner joins, statistics
updated for all tables. Any ideas?which version?|||SQL Server 2000, Enterprise Edition with SP4|||ysfinks (ysfinks@.gmail.com) writes:
> I compared view query plan with query plan if I run the same statement
> from view definition and get different results. View plan is more
> expensive and runs longer. View contains 4 inner joins, statistics
> updated for all tables. Any ideas?

Since you didn't share anything close to a repro, I have little idea
of you what you are doing. Since a view essential is a macro, it should
not matter that much. Then again, I've been wrong before. Anyway, it
would help if you posted the view, and the two SELECT you run.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I narrowed down to one join. Same difference in query plans. Query cost
for 1 is 5.48%, for 2 is 94.52%
My view is:
create view dbo.sf_test as
SELECT
dbo.ManagedNodes.NodeID, dbo.ManagedNodes.SubscriptionID,
CompanyAccounts.root_account_id AS ECCRootID
FROM dbo.ManagedNodes WITH (NOLOCK)
INNER JOIN dbo.accounts CompanyAccounts WITH (NOLOCK)
ON dbo.ManagedNodes.ECCRootID = CompanyAccounts.account_id

My queries are:
1.
SELECT dbo.ManagedNodes.NodeID, dbo.ManagedNodes.SubscriptionID,
CompanyAccounts.root_account_id AS ECCRootID
FROM dbo.ManagedNodes WITH (NOLOCK)
INNER JOIN dbo.accounts CompanyAccounts WITH (NOLOCK)
ON dbo.ManagedNodes.ECCRootID = CompanyAccounts.account_id
where ECCRootID=15427
2.
select NodeID, SubscriptionID, ECCRootID
from dbo.sf_test where eccrootid=15427|||ysfinks (ysfinks@.gmail.com) writes:
> I narrowed down to one join. Same difference in query plans. Query cost
> for 1 is 5.48%, for 2 is 94.52%
> My view is:
> create view dbo.sf_test as
> SELECT
> dbo.ManagedNodes.NodeID, dbo.ManagedNodes.SubscriptionID,
> CompanyAccounts.root_account_id AS ECCRootID
> FROM dbo.ManagedNodes WITH (NOLOCK)
> INNER JOIN dbo.accounts CompanyAccounts WITH (NOLOCK)
> ON dbo.ManagedNodes.ECCRootID = CompanyAccounts.account_id
> My queries are:
> 1.
> SELECT dbo.ManagedNodes.NodeID, dbo.ManagedNodes.SubscriptionID,
> CompanyAccounts.root_account_id AS ECCRootID
> FROM dbo.ManagedNodes WITH (NOLOCK)
> INNER JOIN dbo.accounts CompanyAccounts WITH (NOLOCK)
> ON dbo.ManagedNodes.ECCRootID = CompanyAccounts.account_id
> where ECCRootID=15427
> 2.
> select NodeID, SubscriptionID, ECCRootID
> from dbo.sf_test where eccrootid=15427

I will have to admit that I don't have any good answers at this
point. But I still like to ask some questions, just to check:

Exactly how do you create the view? From Query Analyzer or Enterprise
Manager? If the latter, what happens, if you run a script in QA
where you first create the view, and then run the queries?

What happens if you take out the NOLOCK hints?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||so if i am understanding, if you do a select against a view, it takes a
very long time.

but if copy that exact same code into query analyzer or a stored
procedure, it goes MUCH faster.

and If I am understanding correctly the issue, there will be an index
on eccrootid.

And, if I am understanding, the view won't use the index on ECCrootid,
but everything else will.
Do I have the issue correctly? If so, yup, it does that in SS2000. You
can try compiler hints in the view to FORCE it to sue the index, but
that only works sometimes.

Best workaround is to move all your views to stored procedures, and
pass the eccrootid parameter to the sproc.

I reported this 5 years ago, adn even discussed it with Erland at that
time.

Views suck.

Regards,
Doug|||A VIEW is handled two ways in SQL. The text of the VIEW is "pasted"
into the query that uses it and then the parser and optimizer handle it
as if the query had been written with a derived table. The parser can
do a lot stuff at this point, so the original view text is "spread out
all over the place".

The second way is materialize the VIEW as a temporary table. The good
news is that this materialized table can be shared by multiple users,
so the overall processing time goes down, even if each user's plan is
not optimal for their query. This is a feature of larger SQL products
like Ingres, DB2 or Oracle.

Trust in the optimizer, Luke.|||if you are going ot have a materialized view, why not just bite the
bullet and have a denormalized table hanging around that gets updated
all the time.

the optimizer is fine for 90 percent of the time.|||View was created from Query Analyzer. If I remove nolock - same result.
Another fact - if I change condition value in where clause, for some
values it gives for the view the good query plan using index for
eccrootid.
For the query simulating the view - always good plan.|||ysfinks (ysfinks@.gmail.com) writes:
> View was created from Query Analyzer. If I remove nolock - same result.
> Another fact - if I change condition value in where clause, for some
> values it gives for the view the good query plan using index for
> eccrootid.
> For the query simulating the view - always good plan.

I will have admit that I am fairly stumped at this point. For this reason
I have consulted some other people offline. No promises, but keep watching
this space.

Nevertheless, you run the two queries bracketed by

set statistics profile on
set statistics profile off

If you can put that in a file as a attachment ot on web site, to avoid
that the output is mashsed in news transport, that would be great, but
anything goes.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Differences betwee 2000 and 2005 SQL

Hi,

I have a simple sql statement that used to work in SQL 2000 that isn't working in SQL 2005. The order by clause doesn't seem to have any effect on the result set. The sql statement is:

ALTER VIEW dbo.SELECT_PP_END
AS
SELECT TOP 100 PERCENT

PP_PERIOD_ID,

CONVERT(VARCHAR, PP_END_DATE, 101) AS PP
FROM dbo.PP_PERIODS
ORDER BY PP_END_DATE DESC

The period end date is appearing in ascinding order on sql server 2005 and in the correct order in sql 2000. Any idea? Thank you for your help

- T.A.

How about this..

Code Snippet

ALTER VIEW dbo.SELECT_PP_END
AS
SELECT TOP 100 PERCENT
PP_PERIOD_ID,
CONVERT(VARCHAR, PP_END_DATE, 101) AS PP,
rank() over (order by PP_END_DATE desc) as Rank
FROM dbo.PP_PERIODS
ORDER BY PP_END_DATE DESC

|||

This has been answered to your almost identical post in the 'Setup and Upgrade' forum, here.

It is best to not 'multi-post' like this. Post in one forum and wait for a response. That shows respect for the folks here since they don't have to spend time answering a question that was answered in another forum.

|||

Sorry about the multi-post Sankar Reddy answer is what i was looking for.

Thanks

T.A.

|||

You're selecting another work-around that you 'may' have to fix in the future.

It is 'smarter' to use 'best practices' from the beginning, or as soon as you become aware of the need to follow better and more robust methods.

|||Use this forum to learn things to do the right way instead of getting quick answers. Listen to the masters when they give advice to you. I showed you how to get that result and it may not be the right way of doing things when you consider bigger picture. It shouldn't be a big deal to add order by clause to views and I am not sure why it adds more work to you.
|||

Thank you guys,

I had to do a massive change on my application using "select" statements rather than using the view name.

I assume views do not output the order by due to execution cost.

that is the only reasonable conclusion i could think of.

Thanks Again

T.A.

difference??

Hi,
why the first statement returns nothing, but the second one returns the
result I want?
select soldto.soldtonumber from soldto where soldto.soldtonumber not in
(Select soldto from fsosoldto)
SELECT dbo.SoldTo.SoldToNumber, dbo.FSOsoldto.Soldto
FROM dbo.SoldTo Left JOIN
dbo.FSOsoldto ON dbo.SoldTo.SoldToNumber =
dbo.FSOsoldto.Soldto where FSOsoldto.Soldto is null
Tks
Edforget about this question since soldto contains null value that's why the
first statement always returns null
"Ed" wrote:

> Hi,
> why the first statement returns nothing, but the second one returns the
> result I want?
> select soldto.soldtonumber from soldto where soldto.soldtonumber not in
> (Select soldto from fsosoldto)
>
> SELECT dbo.SoldTo.SoldToNumber, dbo.FSOsoldto.Soldto
> FROM dbo.SoldTo Left JOIN
> dbo.FSOsoldto ON dbo.SoldTo.SoldToNumber =
> dbo.FSOsoldto.Soldto where FSOsoldto.Soldto is null
> Tks
> Ed