Aaron Bertrand PLEASE HELP

Is there a way to Reference an alias field name in an SQL Statement?
Example:
Select
1 + 1 AS F1,
F1 + 1 AS F2You can reuse the column/expression aliases only if they are in a derived
table construct, other wise you'll have to use the entire expression again:
SELECT 1 + 1 AS "f1",
1 + 1 + 1 AS "f2
FROM ... ;
-- or
SELECT f1,
f1 + 1 AS "f2"
FROM ( SELECT 1 + 1 AS "f1"
FROM ... ) D ;
Anith|||>> Is there a way to Reference an alias field [sic] name in an SQL Statement? <<
Here is how a SELECT works in SQL ... at least in theory. Real
products will optimize things, but the code has to produce the same
results.
a) Start in the FROM clause and build a working table from all of the
joins, unions, intersections, and whatever other table constructors are
there. The <table expression> AS <correlation name> option allows you
give a name to this working table which you then have to use for the
rest of the containing query.
b) Go to the WHERE clause and remove rows that do not pass criteria;
that is, that do not test to TRUE (i.e. reject UNKNOWN and FALSE). The
WHERE clause is applied to the working set in the FROM clause.
c) Go to the optional GROUP BY clause, make groups and reduce each
group to a single row, replacing the original working table with the
new grouped table. The rows of a grouped table must be group
characteristics: (1) a grouping column (2) a statistic about the group
(i.e. aggregate functions) (3) a function or (4) an expression made up
those three items. The original table no longer exists.
d) Go to the optional HAVING clause and apply it against the grouped
working table; if there was no GROUP BY clause, treat the entire table
as one group.
e) Go to the SELECT clause and construct the expressions in the list.
This means that the scalar subqueries, function calls and expressions
in the SELECT are done after all the other clauses are done. The AS
operator can also give names to expressions in the SELECT list. These
new names come into existence all at once, but after the WHERE clause,
GROUP BY clause and HAVING clause have been executed; you cannot use
them in the SELECT list or the WHERE clause for that reason.
If there is a SELECT DISTINCT, then redundant duplicate rows are
removed. For purposes of defining a duplicate row, NULLs are treated
as matching (just like in the GROUP BY).
f) Nested query expressions follow the usual scoping rules you would
expect from a block structured language like C, Pascal, Algol, etc.
Namely, the innermost queries can reference columns and tables in the
queries in which they are contained.
g) The ORDER BY clause is part of a cursor, not a query. The result
set is passed to the cursor, which can only see the names in the SELECT
clause list, and the sorting is done there. The ORDER BY clause cannot
have expression in it, or references to other columns because the
result set has been converted into a sequential file structure and that
is what is being sorted.
As you can see, things happen "all at once" in SQL, not "from left to
right" as they would in a sequential file/procedural language model. In
those languages, these two statements produce different results:
READ (a, b, c) FROM File_X;
READ (c, a, b) FROM File_X;
while these two statements return the same data:
SELECT a, b, c FROM Table_X;
SELECT c, a, b FROM Table_X;
Think about what a mess this statement is in the SQL model.
SELECT f(c2) AS c1, f(c1) AS c2 FROM Foobar;
That is why such nonsense is illegal syntax.|||Not sure if this can do what you wish, but create the subquery in your FROM
clause.
EXAMPLE:
Using the Northwind database
SELECT quant.Quantity
FROM (SELECT Quantity + 1 AS [Quantity]
FROM [Order Details]) AS quant
The subquery is aliased using quant and will return the quantity field +1.
This could also be written as
SELECT *
FROM (SELECT Quantity + 1 AS [Quantity]
FROM [Order Details]) AS quant
as the only column created in the sub query is the quantity +1, but just to
give you direction for your query.
Good Luck and hope this helped.
"Kent Prokopy" wrote:

> Is there a way to Reference an alias field name in an SQL Statement?
> Example:
> Select
> 1 + 1 AS F1,
> F1 + 1 AS F2
>
>|||Why are you only asking me?
"Kent Prokopy" <kent_prokopy@.hotmail.com> wrote in message
news:OiAzjPWkGHA.4660@.TK2MSFTNGP03.phx.gbl...
> Is there a way to Reference an alias field name in an SQL Statement?
> Example:
> Select
> 1 + 1 AS F1,
> F1 + 1 AS F2
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OhQIR5WkGHA.1320@.TK2MSFTNGP04.phx.gbl...
> Why are you only asking me?
Because some other gurus are mean... :)sql

A WHERE in a Union query

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

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

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

WHERE Yearlist LIKE myvariable

although i know i cant simply use:

WHERE Yearlist

it would have to be something like:

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

Does anyone know how to write this correctly?Not tested it, but something like this:

select YearList from
(select distinct Cas YearList
from dbo.ViewPressReleases
where YearList like myvariable) SomeNameYouLike
union
select datepart(yyyy, getdate())
order by YourOrderField|||ok thanks,

so now i have

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

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

any ideas?|||you can't use your alias in the where clause, so you have to reuse the DATEPART function again. Also, your DATEPART returns an integer value. If you want to perform a like with a wildcard character (%) on it, you can use the DATENAME function which returns a string.|||i actually want the integer. This was my origianal probelm; that you cant use an alias in a where clause, but i cant simply use the "datepart..." as its part of a union query and i need to filter the resulting list of the union query, if i've explained myself clearly.|||no, it isn't clear, luke

please explain again what the WHERE clause is supposed to find|||Originally posted by r937
no, it isn't clear, luke

please explain again what the WHERE clause is supposed to find

Ok...

i have

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

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

so i need to add

WHERE ??? LIKE myvariable

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

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

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

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

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

WHERE Yearlist Like myvaiable

so i need to know how to phrase the SQL corrctly in order to filter the list of years.|||no, don't explain in terms of sql, you already tried that ;)

you cannot use LIKE on integers

try explaining it in english

"i want to select only years which ... ?"|||I Think i have it.

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

Thanks.sql

A suggestion that can help SQL Server community

I have noticed that the area of writing stored procedures for muti-user databases is a very specialised field and requires knowledge that's much more than the locking topics covered in 'online books' . I am sure there are some standard tips and tricks that are used in mutil-user databases for writing to tables.Most books have a chapter or two on locking, but I think this topic should be dealt withseparately in a dedicated book to locking with extensive examples on locking. Does anyone know of such a dedicated book out there?

The person who I think goes under SQL Server transaction is Dusan Petkovic, his books are by no means Beginner's books but Osborne gave them that title but your understanding of SQL Server transaction will improve after you read his chapter and do the questions at the end of the chapter.

He also covered ANSI SQL transaction features SQL Server implements but is not documented. Try the link below for his books I have not read the SQL Server 2005 version.

http://www.amazon.com/gp/product/007212587X/102-0765109-8072934?v=glance&n=283155

http://books.mcgraw-hill.com/getbook.php?isbn=0072260939&template

A substr()-like functin for IMAGE data...

Friends, I'd like to examine bytes 40 through 47 of a very large IMAGE field
in records in one table. I'd like to handle the bytes as text if possible.
A simple litle utility is needed by our customer, so I had hoped to avoid
doing the utility in C++, and thought maybe perhaps I could hanlde this all
as a SQL script. (I looked around, and it doesn't appear SQL really lets
you work with binary.)
Any ideas will be greatly appreciated. (Otherwise, I'll just code up a
little C++/ODBC app for them.)
Thanks in advance,
JamesDid you try SUBSTRING?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:upYfBsLKFHA.1280@.TK2MSFTNGP09.phx.gbl...
> Friends, I'd like to examine bytes 40 through 47 of a very large IMAGE
field
> in records in one table. I'd like to handle the bytes as text if
possible.
> A simple litle utility is needed by our customer, so I had hoped to avoid
> doing the utility in C++, and thought maybe perhaps I could hanlde this
all
> as a SQL script. (I looked around, and it doesn't appear SQL really lets
> you work with binary.)
> Any ideas will be greatly appreciated. (Otherwise, I'll just code up a
> little C++/ODBC app for them.)
> Thanks in advance,
> James
>|||I "read", I did not "try", and it appeared that it would only work with
character fields. I feel stupid. Thanks, I tried it, that works.
My next task is to write the entire IMAGE field to a disk file so that it
may be opened and examined by an external program.
James|||See READTEXT and TEXTPTR commands in Books Online.
<bol>
READTEXT
Reads text, ntext, or image values from a text, ntext, or image column,
starting from a specified offset and reading the specified number of bytes.
Syntax
READTEXT { table.column text_ptr offset size } [ HOLDLOCK ]
...
Examples
This example reads the second through twenty-sixth characters of the pr_info
column in the pub_info table.
USE pubs
GO
DECLARE @.ptrval varbinary(16)
SELECT @.ptrval = TEXTPTR(pr_info)
FROM pub_info pr INNER JOIN publishers p
ON pr.pub_id = p.pub_id
AND p.pub_name = 'New Moon Books'
READTEXT pub_info.pr_info @.ptrval 1 25
GO
</bol>
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:upYfBsLKFHA.1280@.TK2MSFTNGP09.phx.gbl...
> Friends, I'd like to examine bytes 40 through 47 of a very large IMAGE
> field in records in one table. I'd like to handle the bytes as text if
> possible. A simple litle utility is needed by our customer, so I had hoped
> to avoid doing the utility in C++, and thought maybe perhaps I could
> hanlde this all as a SQL script. (I looked around, and it doesn't appear
> SQL really lets you work with binary.)
> Any ideas will be greatly appreciated. (Otherwise, I'll just code up a
> little C++/ODBC app for them.)
> Thanks in advance,
> James
>

A strange problem with SQL query fro getting field names

Hello All,

I have been trying to get this code work, but I could not. Every thing seems going well. However, The result of running the sql query is strange. It shows the field names twice.
Eg:) if you have a table called "newtable" that has two fields[Custnumber, Custname], you will get somthing like this [Custnumber, Custname Custnumber, Custname]. I have tried many times, but I couldn't fix it.

 Sub Page_Load(sender As Object, e As EventArgs) handles Mybase.Load
 if not page.Ispostback then
 try
 Sqlconnection = New Sqlconnection (connectionString)
 querystring = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNs
 WHERE TABLE_NAME = 'Newtable'"
 SqlCommand = New SqlCommand(queryString, Sqlconnection)
 SqlConnection.Open
 dataReader = SqlCommand.ExecuteReader(CommandBehavior.CloseConnection)
 while dataReader.Read()
 Tablefields_txt.text += dataReader.Getstring(0) & ", "
 End while
 catch ex as Exception
 msgbox("An error has occured: " + ex.Message,0, "Error Message")
 finally
 SqlConnection.Close()
 End try
End if

Any help , pleaseCheck this bit. I assume this might have an impact on your problem.

Tablefields_txt.text += dataReader.Getstring(0) & ", "|||I have tried this:
Dim temp as string
while dataReader.Read()

temp += dataReader.Getstring(0) & ", "

End while

Tablefields_txt.text = temp

I think the problem might be from the querystring "select ....." , but i do know how to deal with it . I need help|||As I posted in your other thread, the problem is due to the "handles Mybase.Load". Remove this and you should see the results you expect.

Terri

a small program for generating bulk data.

Plz help me write a small prog for generating random data

It should create a table,
field 1 : ID (should be filled with 5000records of random numbers)
field 2 : name (should be filled with 5000records of random character data, 15length)

Actually i'm confused on whether i should use cursors or what..

Thank you.

The following query may help you...But there is no garnetee about unique values..

Create table #Data

(

Id int,

Name varchar(20)

);

Set NOCOUNT ON;

Declare @.I as Int;

Set @.I = 0;

While @.I<5000

Begin

Insert Into #Data

Select

Cast(Rand() * 100000000as float)

, Char(65 + cast(rand() * 1000 as int) % 26)

+ Char(97 + cast(rand() * 1000 as int) % 26)

+ Char(97 + cast(rand() * 1000 as int) % 26)

+ Char(97 + cast(rand() * 1000 as int) % 26)

+ Char(97 + cast(rand() * 1000 as int) % 26)

+ Char(97 + cast(rand() * 1000 as int) % 26)

+ Char(97 + cast(rand() * 1000 as int) % 26)

+ Char(97 + cast(rand() * 1000 as int) % 26)

+ Char(97 + cast(rand() * 1000 as int) % 26)

+ Char(97 + cast(rand() * 1000 as int) % 26)

+ Char(97 + cast(rand() * 1000 as int) % 26)

+ Char(97 + cast(rand() * 1000 as int) % 26)

+ Char(97 + cast(rand() * 1000 as int) % 26)

+ Char(97 + cast(rand() * 1000 as int) % 26)

+ Char(97 + cast(rand() * 1000 as int) % 26)

Set @.I = @.I + 1;

End

Select * from #Data

Drop table #Data

|||Why is

Set NOCOUNT ON;

|||

To suppress the Row Inserted feedback from your server.. (1 row(s) affected)

It will consume unnecessary time.. It is one of the tuning tips. You can use this wherever required.

A question on Conversation timer persistence

I'd like to add code to a trigger to calculate the time to fire a message into a queue based on a field changing, and conversation timers seem like the way to go. My first question refers to this line from the BOL:

"Calling BEGIN CONVERSATION TIMER on a conversation before the timer has expired sets the timeout to the new value."

I think that in this trigger, I can simply begin a new conversation if the given field has changed to reset the timer. But intuition tells me that in order to change the timer to a new value, I need to retrieve the existing conversation, correct?

Also, I've read that conversation timers are persistent in that they survive database restarts and shutdowns. But I'm not sure to what extent. After a database restart/shutdown, does the conversation timer "reset" itself to the time interval specified when the conversation was begun or is it able to account for the time the database was down/offline?

Thanks,

Chris

I'm not sure I understand the requirements. Why is that you need to fire a timer as a result of a field change? The usual requirement is to fire a message so that some asynch processing happens later, but not based on a timer. Can you give some more details?

You can have only one timer per conversation. That what the BOL line refers to. You cannot have multiple timers, setting a new timer will erase the old one.

The timers are set as absolute time, not interval. After a database/server restart, if the time of the timer is in the past, then the timer will be fired.

HTH,
~ Remus

|||

Remus,

Thanks for the quick response.

Here's the workflow of the process: A user creates a work order for which they can assign a follow-up time. When this follow-up time arrives, I want to send a message to a Service Broker queue that I have already set up to process the message. If the follow-up time changes, the timer is adjusted to account for the change in time.

The only difference between what I need to do now and what I've already done is sending the message to the queue at a specific time. I imagined a trigger that would fire every time the follow-up time changed so that I could alter the conversation timer. This trigger would begin a new conversation, set a timer, and an activated stored procedure would look for the message type http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer and send a message to my original queue where it will be processed. I see 2 problems with my logic: I am looking for a DialogTimer message type, so the activated stored procedure only knows it is time to do something, but it has no message body that I can use to forward onto the final Service Broker queue. Also, I have no way of finding the conversation I started the last time the trigger fired.

I'm wondering if using a conversation timer is the wrong approach, and if so, what is?

Thanks,

Chris

|||

What you need is a table to associate the conversation which fired the timer with the original work order. When the work order is created, the trigger begin a dialog, sets the timer and then inserts into this table the newly created conversation handle and the work order id.

When the timer fires, the activated procedure receives the message, looks up the work order id in this table (based on the conversation handle the message was RECEIVE on) and does whatever work is required at that moment.

The same table can be also used when updates occur on the follow_up field. Instead of beginning a conversation, the trigger will look up this association table and find the existing conversation.

One thing to note is that timer messages are unlike any message in the sense that they are sent by one conversation endpoint to itself. So the conversation handle on which the timer was set is the same one as on which is going to be received.

I do believe that conversation timers are the right approach. No other approach I can think of is better. Conversation timers are very cheap from a resource point of view, completely contained within the database (this gives lots of advantages related to backup/restore, failover and availability), and offer the possibility to actually luch a procedure.

HTH,
~ Remus

|||

Thanks a lot Remus. A state table was what I came up with as well. I really appreciate being able to come here for valuable, practical advice on how to approach Service Broker issues. Thanks again,

Chris

A query to locate a specific column in various tables?

I have over 65 databases, all with various numbers of tables. Within these
tables I need to see which tables contain a specific field called vendor_id
Is there a query that can be ran for this?
Thanks
Jeff
Message posted via http://www.droptable.com
SELECT Table_name from INFORMATION_SCHEMA.COLUMNS Where Column_Name LIKE
'YADAYADA'
HTH, Jens Suessmeyer

A query to locate a specific column in various tables?

I have over 65 databases, all with various numbers of tables. Within these
tables I need to see which tables contain a specific field called vendor_id
Is there a query that can be ran for this?
Thanks
Jeff
--
Message posted via http://www.sqlmonster.comSELECT Table_name from INFORMATION_SCHEMA.COLUMNS Where Column_Name LIKE
'YADAYADA'
HTH, Jens Suessmeyer

A query to locate a specific column in various tables?

I have over 65 databases, all with various numbers of tables. Within these
tables I need to see which tables contain a specific field called vendor_id
Is there a query that can be ran for this?
Thanks
Jeff
Message posted via http://www.droptable.comSELECT Table_name from INFORMATION_SCHEMA.COLUMNS Where Column_Name LIKE
'YADAYADA'
HTH, Jens Suessmeyer

a query for a view

Hello,
I have a View called View1 with the field ID, F1, F2, F3. Now I need to
check if (Total F1 < Total F2 + Total F3) per ID, if yes fetch all records
(so if condition matches, I need to bring rows, not only totals) how can I
write my view query to handle this?
Thanks,Try,
select v1.[id], v1.f1, v1.f2, v1.f3
from
v1
inner join
(
select [id]
from v1
group by [id]
having sum(f1) < sum(f2 + f3)
) as v2
on v1.[id] = v2.[id]
AMB
"JIM.H." wrote:

> Hello,
> I have a View called View1 with the field ID, F1, F2, F3. Now I need to
> check if (Total F1 < Total F2 + Total F3) per ID, if yes fetch all records
> (so if condition matches, I need to bring rows, not only totals) how can I
> write my view query to handle this?
> Thanks,
>|||this should do:
select *
from view1
where id in(select id
from view1
group by id
having sum(f1)<(sum(f2)+sum(f3))
)
-oj
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:8A8C406B-8576-43F1-A679-F4A3C0EED5D4@.microsoft.com...
> Hello,
> I have a View called View1 with the field ID, F1, F2, F3. Now I need to
> check if (Total F1 < Total F2 + Total F3) per ID, if yes fetch all records
> (so if condition matches, I need to bring rows, not only totals) how can I
> write my view query to handle this?
> Thanks,
>|||It seems it is working, however in case of equality I still see the records,
I should not see anything for that ID if sum(f1) = sum(f2 + f3)
What is problem here?
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Try,
> select v1.[id], v1.f1, v1.f2, v1.f3
> from
> v1
> inner join
> (
> select [id]
> from v1
> group by [id]
> having sum(f1) < sum(f2 + f3)
> ) as v2
> on v1.[id] = v2.[id]
>
> AMB
> "JIM.H." wrote:
>

a query for a view

Hello,
I have a View called View1 with the field ID, F1, F2, F3. Now I need to
check if (Total F1 < Total F2 + Total F3) per ID, if yes fetch all records
(so if condition matches, I need to bring rows, not only totals) how can I
write my view query to handle this?
Thanks,
Try,
select v1.[id], v1.f1, v1.f2, v1.f3
from
v1
inner join
(
select [id]
from v1
group by [id]
having sum(f1) < sum(f2 + f3)
) as v2
on v1.[id] = v2.[id]
AMB
"JIM.H." wrote:

> Hello,
> I have a View called View1 with the field ID, F1, F2, F3. Now I need to
> check if (Total F1 < Total F2 + Total F3) per ID, if yes fetch all records
> (so if condition matches, I need to bring rows, not only totals) how can I
> write my view query to handle this?
> Thanks,
>
|||this should do:
select *
from view1
where id in(select id
from view1
group by id
having sum(f1)<(sum(f2)+sum(f3))
)
-oj
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:8A8C406B-8576-43F1-A679-F4A3C0EED5D4@.microsoft.com...
> Hello,
> I have a View called View1 with the field ID, F1, F2, F3. Now I need to
> check if (Total F1 < Total F2 + Total F3) per ID, if yes fetch all records
> (so if condition matches, I need to bring rows, not only totals) how can I
> write my view query to handle this?
> Thanks,
>
|||It seems it is working, however in case of equality I still see the records,
I should not see anything for that ID if sum(f1) = sum(f2 + f3)
What is problem here?
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Try,
> select v1.[id], v1.f1, v1.f2, v1.f3
> from
> v1
> inner join
> (
> select [id]
> from v1
> group by [id]
> having sum(f1) < sum(f2 + f3)
> ) as v2
> on v1.[id] = v2.[id]
>
> AMB
> "JIM.H." wrote:

a query for a view

Hello,
I have a View called View1 with the field ID, F1, F2, F3. Now I need to
check if (Total F1 < Total F2 + Total F3) per ID, if yes fetch all records
(so if condition matches, I need to bring rows, not only totals) how can I
write my view query to handle this?
Thanks,Try,
select v1.[id], v1.f1, v1.f2, v1.f3
from
v1
inner join
(
select [id]
from v1
group by [id]
having sum(f1) < sum(f2 + f3)
) as v2
on v1.[id] = v2.[id]
AMB
"JIM.H." wrote:
> Hello,
> I have a View called View1 with the field ID, F1, F2, F3. Now I need to
> check if (Total F1 < Total F2 + Total F3) per ID, if yes fetch all records
> (so if condition matches, I need to bring rows, not only totals) how can I
> write my view query to handle this?
> Thanks,
>|||this should do:
select *
from view1
where id in(select id
from view1
group by id
having sum(f1)<(sum(f2)+sum(f3))
)
--
-oj
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:8A8C406B-8576-43F1-A679-F4A3C0EED5D4@.microsoft.com...
> Hello,
> I have a View called View1 with the field ID, F1, F2, F3. Now I need to
> check if (Total F1 < Total F2 + Total F3) per ID, if yes fetch all records
> (so if condition matches, I need to bring rows, not only totals) how can I
> write my view query to handle this?
> Thanks,
>|||It seems it is working, however in case of equality I still see the records,
I should not see anything for that ID if sum(f1) = sum(f2 + f3)
What is problem here?
"Alejandro Mesa" wrote:
> Try,
> select v1.[id], v1.f1, v1.f2, v1.f3
> from
> v1
> inner join
> (
> select [id]
> from v1
> group by [id]
> having sum(f1) < sum(f2 + f3)
> ) as v2
> on v1.[id] = v2.[id]
>
> AMB
> "JIM.H." wrote:
> > Hello,
> > I have a View called View1 with the field ID, F1, F2, F3. Now I need to
> > check if (Total F1 < Total F2 + Total F3) per ID, if yes fetch all records
> > (so if condition matches, I need to bring rows, not only totals) how can I
> > write my view query to handle this?
> > Thanks,
> >

a problem with adding fields in ssems

hi,
i have a DB and it has some tables that the tables has related link (diagram).now when i wanna to change a table's field , the Sql Server errors that the table is not empty.when i try to delete the table's content , Sql server errors that the table is use a relation with another table.
so can i change a table's structure?
by the way before i forget , the Sql Server's error is below:

'UserManagement' table
- Unable to modify table.
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'isadmin' cannot be added to non-empty table 'UserManagement' because it does not satisfy these conditions.

thanks,
M.H.H

First, add the column to the table, allowing values to be NULL, or provide a 'starting out' (DEFAULT) value.

ALTER TABLE UserManagement
ADD IsAdmin int NULL
-->OR<--
ADD IsAdmin int DEFAULT 0

Then, update the table to provide the values you want the rows to have.

|||

hi,thanks for ur attention,

my problem is solved,

M.H.H

A problem on Group Sorting

is it true that the group sorting expert can only sort the groups according based on a summary?

I have inserted a formula field on the group header, and i want to sort the groups based on the value of this formula field.. Is it possible to do so?
I am using crystal report 9...

thanksIn the menu, goto report->Recordsort Export; Select the formula and add it to sortable list

A probably simple question

A third party vendor has a table with a field name of "desc" in it. Since "desc" is a reserved term in SQL Server 2005 how does one query Table.Desc ?

when you try it with table.desc it errors since it turned blue being a reserved word.

Jeff

try table.[desc]|||

I did this and it would not create the column. I have tried:

table.[desc]

[table].[desc]

Neither of which worked.

Jeff

|||

I just ran this code in sql 2005:

create table #tmp( [desc] varchar(10))

insert into #tmp values( 'one')

select #tmp.[desc] from #tmp

Is your table actually named "Table"?

|||

Even if your table is called “table” you should be able to query it.

For example,

create table [table]([desc] char(10))

go

insert into [table] values ('test')

go

select [desc] from [table]

go

When you said it did not work, what was the error message? What is the version of SQL Server are you using?

Consult this Books Online topic http://msdn2.microsoft.com/en-US/library/ms176027(SQL.90).aspx for more information.

HTH,

Boris.

|||

There wasn't an error, it just didn't show the desc column in the resultset grid.

ALTER PROCEDURE [dbo].[SUR_GiftsDetail]

@.StartDate DateTime,

@.EndDate DateTime

AS

BEGIN

SET NOCOUNT ON;

SELECT

ItemID,

Quantity,

TourID,

TourNumber,

MasterID,

Description

MasterCatID,

CategoryID,

CategoryDescription,

Arrival,

Depart,

BookingID,

it_arrival_date

FROM

(SELECT

Booking.bk_id AS BookingID,

i.it_arrival_date AS Arrival,

i.it_arrival_date,

i.it_id AS TourNumber,

i.it_arrival_date + i.it_nights AS Depart,

pt.fk_itemid AS ItemID,

pt.Qty AS Quantity,

pt.fk_tourid AS TourID,

pm.ItemID AS MasterID,

pm.[desc] AS Description,

pm.fk_categoryID AS MasterCatID,

pc.categoryID AS CategoryID,

pc.[Desc] AS CategoryDescription

FROM

Booking

LEFT JOIN ITINERARY i ON Booking.bk_id = i.fk_bk_id

LEFT JOIN pi_transactions pt ON pt.fk_tourid = Booking.bk_id

LEFT JOIN pi_master pm ON pm.itemid = pt.fk_itemid

LEFT JOIN pi_category pc ON pc.categoryid = pm.fk_categoryid

LEFT JOIN pi_transactions ptt ON ptt.fk_itemid = pm.itemid

WHERE

i.it_arrival_date BETWEEN @.StartDate AND @.EndDate AND i.fk_et_entity_type LIKE 'Hotel') AS derivedTour

WHERE

it_arrival_date BETWEEN @.StartDate AND @.EndDate

ORDER BY

TourID

END

All the columns show except for the two desc fields.

SQL Server 2005

Jeff

|||

Hi

You are missing a comma after Description in the outer SELECT list.

A performance question: "SELECT ID as subID FROM myTable AS myTable1"

For some reasons I need to access the same field of the same table twice in a query, and each give out a diferrent value
Like this:
"SELECT myTable.id, myTable1.id as subID FROM myTable INNER JOIN ... INNER JOIN myTable as myTable1 ..."
The question is, when I write it as myTable as myTable1 will it affect the query performance if myTable is a large table? will it create another so big copy of myTable? or I should create a view like "CREATE VIEW myTable1 AS SELECT id FROM myTable" to reduce the side of myTable1?
Thank you.

Views are actually slower than stored procedures...

A little script help

Using the data below as an example I am looking for help with script
that will return all rows of data where neither Field A or B are not 0
or Null

NameAB
John2
John1
John0
John
Ste1
Ste
Paul5
Paul
Paul0

Regards,
CiarnDo you really mean where EITHER A or B are not 0 or not NULL? Try:

WHERE A>0 OR B>0

conversely:

WHERE NULLIF(A,0) IS NULL AND NULLIF(A,0) IS NULL

--
David Portas
SQL Server MVP
--|||I tried your suggestions without success.
Using the data above, I want to return.

Name A B
John 2
John 1
Ste 1
Paul 5

Regards,
Ciarn|||The following should work:

WHERE ISNULL(A, 0) <> 0 OR ISNULL(B, 0) <> 0

-Tom.|||This is where it helps if you include CREATE TABLE and INSERT
statements with your question. The following works for me:

CREATE TABLE YourTable (name VARCHAR(10), a INTEGER NULL, b INTEGER
NULL /* PRIMARY KEY ? UNSPECIFIED */)

INSERT INTO YourTable (name,a,b)
SELECT 'John', 2 , NULL UNION ALL
SELECT 'John', NULL, 1 UNION ALL
SELECT 'John', 0 , NULL UNION ALL
SELECT 'John', NULL, NULL UNION ALL
SELECT 'Ste', NULL, 1 UNION ALL
SELECT 'Ste', NULL, NULL UNION ALL
SELECT 'Paul', 5 , NULL UNION ALL
SELECT 'Paul', NULL, NULL UNION ALL
SELECT 'Paul', NULL, 0

SELECT name, a, b
FROM YourTable
WHERE A>0 OR B>0

Result:

name a b
---- ---- ----
John 2 NULL
John NULL 1
Ste NULL 1
Paul 5 NULL

What did you do differently and what result did you get?

Does this table have a primary key? It should do, and it helps if you
specify the key when you post a question.

--
David Portas
SQL Server MVP
--|||Perfect.
Cheers|||For clarity sake try:

WHERE ISNULL(A,0) <> 0
AND ISNULL(B,0) <> 0

GeoSynch

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1113296365.931060.144050@.f14g2000cwb.googlegr oups.com...
> Do you really mean where EITHER A or B are not 0 or not NULL? Try:
> WHERE A>0 OR B>0
> conversely:
> WHERE NULLIF(A,0) IS NULL AND NULLIF(A,0) IS NULL
> --
> David Portas
> SQL Server MVP
> --

A good way to increment field value

Hi

I have a field containing numbers. I want to do some simple arithmetics with it, say value=value+1 or value=value-1 or or even value+2. What is to be done, is fixed at design time. I think this could be done by loading the row or record to my program and doing the calculations there. And then storing the record back. But this seems too complicated.

Is there a single query doing that in data table.

You can create all your calculation in an sql server user defined function.

And call this function while inserting the data into a table.

|||

Thanks. That seems interesting. And while searching for user defined functions I found help to other problem as well.

I found this:

create function getfulldate (@.date varchar(10))
returns datetime
as
begin
declare @.getfulldate datetime
set @.getfulldate = dateadd (mi,55,@.date)
return @.getfulldate
end

and normally we call this in the SQL statements as
select *, dbo.getfulldate('2006-05-03') from emp

If I undestand this right, this goes into code. Do you know how to put udf in sql server. Or rather, where to start learning it.

Regards

Leif

|||

I found a tutorial about T-sql.

A field or property with the name Jan was not found on the selected data source.

I have this Stored Procedure:

Create PROCEDURE ListEvent
as
If MONTH(GetDate()) <= 6
Begin
SELECT EventTitle, EventDuration,
(CASE WHEN MONTH(StartDate) = 1 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Jan',
(CASE WHEN MONTH(StartDate) = 2 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Feb',
(CASE WHEN MONTH(StartDate) = 3 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Mar',
(CASE WHEN MONTH(StartDate) = 4 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Apr',
(CASE WHEN MONTH(StartDate) = 5 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'May',
(CASE WHEN MONTH(StartDate) = 6 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Jun'
FROM dbo.tblEvent INNER JOIN dbo.tbl ON (tblEvent.EventID = tblEventdate.EventID)
WHERE YEAR(StartDate) = Year(GetDate())
group by EventTitle, EventDuration,StartDate,EndDate
End
Else
Begin
SELECT EventTitle, EventDuration,
(CASE WHEN MONTH(StartDate) = 7 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Jul',
(CASE WHEN MONTH(StartDate) = 8 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Aug',
(CASE WHEN MONTH(StartDate) = 9 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Sep',
(CASE WHEN MONTH(StartDate) = 10 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Oct',
(CASE WHEN MONTH(StartDate) = 11 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Nov',
(CASE WHEN MONTH(StartDate) = 12 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Dec'
FROM dbo.tblEvent INNER JOIN dbo.tbl ON (tblEvent.EventID = tblEventdate.EventID)
WHERE YEAR(StartDate) = Year(GetDate())
group by EventTitle, EventDuration,StartDate,EndDate
End

When I execute it in the SQLExpress, the result returned as expected. But when I bind to Gridview I got this error:

A field or property with the name 'Jan' was not found on the selected data source.

How do I solve this?

I am using drag and drop SQLDatasource to call the Stored Proc and VB.net is the language. Thanks

This is because when you set AS 'Jan', your table's coloumn name is JAN now. If your first Statement If MONTH(GetDate()) <= 6 is true, you have column JAN, it should work. But, if this is not true, you don't have column JAN, you get error.

|||

I tried and it does give me the problem. Is there a workaround for this? Thanks

|||

Can you try this?

1) Modify your SP, add '' for other month to make whole year like:

SELECT EventTitle, EventDuration,
(CASE WHEN MONTH(StartDate) = 1 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Jan',
(CASE WHEN MONTH(StartDate) = 2 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Feb',
(CASE WHEN MONTH(StartDate) = 3 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Mar',
(CASE WHEN MONTH(StartDate) = 4 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Apr',
(CASE WHEN MONTH(StartDate) = 5 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'May',
(CASE WHEN MONTH(StartDate) = 6 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Jun',

'' AS 'Jul',

'' AS 'Aug'

... for other month
FROM dbo.tblEvent INNER JOIN dbo.tbl ON (tblEvent.EventID = tblEventdate.EventID)
WHERE YEAR(StartDate) = Year(GetDate())
group by EventTitle, EventDuration,StartDate,EndDate

Do same thing for your second condition.

2). I am not sure which Data Control you used. Try to hide the column if the column value is ''.

(I am not sure the number 2 workable or not. Anyway, try to hide it).

|||

Ok, it is done. thanks