Newbie question on identically named fields in a dataset

This is my first stab at this. I have a simple report that returns a few
columns from a database no sp no expressions just a select statement. There
are two tables that have the same field name and the dataset canâ't seem to
distinguish between them it returns the first field into both columns. When
I run it in Query Analyzer it looks fine. What am I missing here?Query Analyzer seems to be a bit more forgiving about identical names. In
your query, just use Aliases, and you should be ok.
A query like
"Select table1.col1 as Table1Col1, table2.Col1 as Table2Col1 from Table1,
Table2 (...)"
should give you fields named Table1Col1 and Table2Col1. A bit more trouble
to write, but you eliminate possible errors.
Kaisa M. Lindahl
"RYF" <RYF@.discussions.microsoft.com> wrote in message
news:BD3D2857-937A-4602-80AD-C472FB295245@.microsoft.com...
> This is my first stab at this. I have a simple report that returns a few
> columns from a database no sp no expressions just a select statement.
> There
> are two tables that have the same field name and the dataset can't seem to
> distinguish between them it returns the first field into both columns.
> When
> I run it in Query Analyzer it looks fine. What am I missing here?|||THX that did the trick!
"Kaisa M. Lindahl" wrote:
> Query Analyzer seems to be a bit more forgiving about identical names. In
> your query, just use Aliases, and you should be ok.
> A query like
> "Select table1.col1 as Table1Col1, table2.Col1 as Table2Col1 from Table1,
> Table2 (...)"
> should give you fields named Table1Col1 and Table2Col1. A bit more trouble
> to write, but you eliminate possible errors.
> Kaisa M. Lindahl
> "RYF" <RYF@.discussions.microsoft.com> wrote in message
> news:BD3D2857-937A-4602-80AD-C472FB295245@.microsoft.com...
> > This is my first stab at this. I have a simple report that returns a few
> > columns from a database no sp no expressions just a select statement.
> > There
> > are two tables that have the same field name and the dataset can't seem to
> > distinguish between them it returns the first field into both columns.
> > When
> > I run it in Query Analyzer it looks fine. What am I missing here?
>
>

Newbie Question

How do I get the unique fields from a select query without iterating. Is
this possible? My current statement is below. Thanks in advance.
"SELECT DealNumber, [User] FROM DealingFinal WHERE ([User] = 'dbo')"Hi
SELECT DISTINCT DealNumber, [User] FROM DealingFinal
"xfd" <xfd@.xfd.com> wrote in message
news:u9rL%23udkFHA.3756@.TK2MSFTNGP15.phx.gbl...
> How do I get the unique fields from a select query without iterating. Is
> this possible? My current statement is below. Thanks in advance.
> "SELECT DealNumber, [User] FROM DealingFinal WHERE ([User] = 'dbo')"
>

newbie query question

Hi,

I have a table [myRecipes] with 4 fields; Users can Modify existing recipes or add new recipes and my table stores the new values with a timestamp.

Question: Now that I have some sample data, I need a query or view to pull out each unique recipe in my table, but only return the latest and greatest recipe for each unique recipe.

[foodType] nvarchar

[recipeName] nvarchar

[lastSaved] datetime

[cupsOfSugar] float

Sample data:

foodType recipeName lastSaved cupsOfSugar

cookie, peanutButter, 3/1/2007, 1.0

cookie, peanutButter, 3/5/2007, 1.5

cookie, sugar, 2/28/2007, 5.0

How to:

What would be the query to return the latest and greatest recipes in my db? The resultset should return

cookie, sugar, 2/28/2007, 5

cookie, peanutButter, 3/5/2007, 1.5

...and not the original recipe for peanutButter Cookies with only 1.0 cups of sugar

thanks in advance,

bsierad

select TOP 1 foodType, recipeName, lastSaved, cupsOfSugar
FROM myRecipes Order by cupsOfSugar DESC --Returns greatest
UNION
select TOP 1 foodType, recipeName, lastSaved, cupsOfSugar

FROM myRecipes Order by lastSaved -- Returns latest

PS. Best forum for this question is Transact-SQL
|||

Thanks,

but, doesn't this only return one row?

I'm looking for:

For each unique foodType and recipeName, please return all the fields in my table, and, if there are any duplicate records with foodType and recipeName, please only return that record whose lastSaved field is the max for that particular set.

This table basically holds a history of all saved recipes created by the user, but he/she should only ever see the latest and greatest...

PS: The primary key on this table is foodType + recipeName + lastSaved

thanks again in advance,

ben

|||Check my response in TransactSQL

newbie query question

Hi,

I have a table [myRecipes] with 4 fields; Users can Modify existing recipes or add new recipes and my table stores the new values with a timestamp.

Question: Now that I have some sample data, I need a query or view to pull out each unique recipe in my table, but only return the latest and greatest recipe for each foodType / recipeName pair.

Primary Key = foodType + recipeName + lastSaved

[foodType] nvarchar

[recipeName] nvarchar

[lastSaved] datetime

[cupsOfSugar] float

Sample data:

foodType recipeName lastSaved cupsOfSugar

cookie, peanutButter, 3/1/2007, 1.0

cookie, peanutButter, 3/5/2007, 1.5

cookie, sugar, 2/28/2007, 5.0

How to:

What would be the query to return the latest and greatest recipes in my db? The resultset should return

cookie, sugar, 2/28/2007, 5

cookie, peanutButter, 3/5/2007, 1.5

...and not the original recipe for peanutButter Cookies with only 1.0 cups of sugar

thanks in advance,

bsierad

This should get you started. It will provide the lastest of each reciept variation.

SELECT
[FoodType],
[RecipeName],
max( [LastSaved] )
FROM [myRecipes]
GROUP BY
[FoodType],
[RecipeName]
ORDER BY
[FoodType],
[RecipeName]

This gets you the PK of each qualifying row, and then you could use it as a subquery or a JOIN derived table to get the remaining ingredients.

|||

Thanks, this really helps me out!

this is great...don't have to use a JOIN with this?

I'm under the impression subqueries as input to a parent query can only return one field?

I also did this:

SELECT FoodType,
recipeName,
LastSaved,
cupsOfSugar
FROM myRecipes q
WHERE cast(q.LastSaved as varchar(10)) in
(select MAX(cast(LastSaved as varchar(10)) ) from myRecipes
where FoodType= q.FoodType
and
recipeName= q.recipeName)

thanks again in advance,

bsierad

|||I was thinking as a sub-query in a WHERE clause to return the PK. Also, as a derived table for a JOIN.

Newbie needing help with joining 2 fields

I have a sql 2005 dev ed running an application developed in vs.net 2005 C# I have several gridviews which have a field call first name and a field called lastname I need to put both the firstname and lastname in the same cell or colum row. I do not now how to join these. Can some one help me with a SQL query string that will do this for me.You could do SELECT FirstName + ' ' + LastName FROM YourTable|||SELECT FirstName + ' ' + LastName AS FullName
FROM Employees

newbie join problem...

Hello,

I have 2 tables, software and waretype.

The waretype table is a simple table with 2 fields wareid and warename

So the table looks like this

wareid warename
1 shareware
2 trialware
3 donationware
etc etc

The wareid field in above is unique.

The other table software simply contains details about software shock horror
but most importantly in this table I have one field call wareid which
represents the waretype of the app.

I can list the software table to my hearts content and see all the 1 and 2
and 3's that I want but what I really want to see is the waretype name. So
if in the software table the wareid is 1 then going by above example I would
like to see the name shareware, at the minute all I see is 1.

I am new to this and know that it has something to do with 'joins' but I
find it all a tad confusing.

Thanks,

td.toedipper (send_rubbish_here734@.hotmail.com) writes:
> I can list the software table to my hearts content and see all the 1 and
> 2 and 3's that I want but what I really want to see is the waretype
> name. So if in the software table the wareid is 1 then going by above
> example I would like to see the name shareware, at the minute all I see
> is 1.

SELECT s.col1, s.col2, ..., w.warename
FROM softweare s
JOIN warename w ON s.wareid = w.wareid

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||toedipper wrote:
> Hello,
> I have 2 tables, software and waretype.
> The waretype table is a simple table with 2 fields wareid and warename
> So the table looks like this
> wareid warename
> 1 shareware
> 2 trialware
> 3 donationware
> etc etc
>
> The wareid field in above is unique.

You at least have a unique index on warename, right? Otherwise you're
begging for bad data.

Zach|||reterz675e7869780
"toedipper" <send_rubbish_here734@.hotmail.com> wrote in message
news:32p367F3p51ddU1@.individual.net...
> Hello,
> I have 2 tables, software and waretype.
> The waretype table is a simple table with 2 fields wareid and warename
> So the table looks like this
> wareid warename
> 1 shareware
> 2 trialware
> 3 donationware
> etc etc
>
> The wareid field in above is unique.
> The other table software simply contains details about software shock
> horror but most importantly in this table I have one field call wareid
> which represents the waretype of the app.
> I can list the software table to my hearts content and see all the 1 and 2
> and 3's that I want but what I really want to see is the waretype name.
> So if in the software table the wareid is 1 then going by above example I
> would like to see the name shareware, at the minute all I see is 1.
> I am new to this and know that it has something to do with 'joins' but I
> find it all a tad confusing.
> Thanks,
> td.
>
>|||ut
"toedipper" <send_rubbish_here734@.hotmail.com> wrote in message
news:32p367F3p51ddU1@.individual.net...
> Hello,
> I have 2 tables, software and waretype.
> The waretype table is a simple table with 2 fields wareid and warename
> So the table looks like this
> wareid warename
> 1 shareware
> 2 trialware
> 3 donationware
> etc etc
>
> The wareid field in above is unique.
> The other table software simply contains details about software shock
> horror but most importantly in this table I have one field call wareid
> which represents the waretype of the app.
> I can list the software table to my hearts content and see all the 1 and 2
> and 3's that I want but what I really want to see is the waretype name.
> So if in the software table the wareid is 1 then going by above example I
> would like to see the name shareware, at the minute all I see is 1.
> I am new to this and know that it has something to do with 'joins' but I
> find it all a tad confusing.
> Thanks,
> td.
>
>|||sorry... I am lll
"toedipper" <send_rubbish_here734@.hotmail.com> wrote in message
news:32p367F3p51ddU1@.individual.net...
> Hello,
> I have 2 tables, software and waretype.
> The waretype table is a simple table with 2 fields wareid and warename
> So the table looks like this
> wareid warename
> 1 shareware
> 2 trialware
> 3 donationware
> etc etc
>
> The wareid field in above is unique.
> The other table software simply contains details about software shock
> horror but most importantly in this table I have one field call wareid
> which represents the waretype of the app.
> I can list the software table to my hearts content and see all the 1 and 2
> and 3's that I want but what I really want to see is the waretype name.
> So if in the software table the wareid is 1 then going by above example I
> would like to see the name shareware, at the minute all I see is 1.
> I am new to this and know that it has something to do with 'joins' but I
> find it all a tad confusing.
> Thanks,
> td.
>
>|||Pero Peri wrote:
> ut
> "toedipper" <send_rubbish_here734@.hotmail.com> wrote in message
> news:32p367F3p51ddU1@.individual.net...
>>Hello,
>>
>>I have 2 tables, software and waretype.
>>
>>The waretype table is a simple table with 2 fields wareid and warename
>>
>>So the table looks like this
>>
>>wareid warename
>>1 shareware
>>2 trialware
>>3 donationware
>>etc etc
>>
>>
>>The wareid field in above is unique.
>>
>>The other table software simply contains details about software shock
>>horror but most importantly in this table I have one field call wareid
>>which represents the waretype of the app.
>>
>>I can list the software table to my hearts content and see all the 1 and 2
>>and 3's that I want but what I really want to see is the waretype name.
>>So if in the software table the wareid is 1 then going by above example I
>>would like to see the name shareware, at the minute all I see is 1.
>>
>>I am new to this and know that it has something to do with 'joins' but I
>>find it all a tad confusing.
>>
>>Thanks,
>>
>>td.
>>
>>
>>
>>
>>
>>
>
Humm I think you need to get a crash course in database structures and
data analysis. If you can get the concepts then you won't have a
problem, trying to explain the "correct" way to do things is way too
complicated for newsgroup posts.

newbie help

Hi

I have a database with three fields Fund, Date and Price and want to be able to send a query to it so I get the following

Fund Last_Date_Entered Price_at_last_date
Fund1 1/12/03 1.12
Fund2 31/10/03 6.65
etc...

I have tried the following simple query

SELECT Fund, Max(Date) AS Last_Date_Entered , Last(Price) AS Price_at_last_date
FROM Database
ORDER BY Date
GROUP BY Fund

with no success as the price it returns a price not necessarily at the last date even though I have sorted by Date?? I have tried using sub-queries with HAVING and WHERE clauses to no avail so any help would be most appreciated. I can easily get the maximum date (ie last entry date) but can't for the life of me get the price corresponding to this date is there a function I don't know about? CheersNot 100% sure, but I assume what you are looking for is the Last Price on the Last Date. If this is the case, will it not work to use the following:

SELECT fund, max(date) as last_date, price as price_at_last_date
FROM database
ORDER BY date, fund;

JoeB|||TRY THIS

SELECT
Fund, Date , Price
FROM Database
ORDER BY Date DESC <-- THIS WILL DISPLAY THE LATEST FIRST

REGARDS

EDWINJAMES|||What is Last()?|||apologies ignore the last() bit look at it again|||or if u just want the latest price then enter the following

SELECT
FUND, DATE AS LAST_DATE_ENTERED , PRICE AS PRICE_AT_LAST_DATE
FROM DATABASE
WHERE DATE SELECT MAX(DATE) FROM DATABASE|||Just to clarify there are a number of entries in the database under each fund at different dates... So firstly I want to group the Funds using a subquery perhaps

Fund1
.... Date, Price
.... 12/01/03, 1.15
.... 16/02/03, 1.21
Fund 2
.... Date, Price
.... 15/01/03, 3.23
.... 19/02/03, 4.01
etc.

Then select the last date entry and its corresponding price|||TRY THIS

SELECT FUND, DATE AS LAST_DATE_ENTERED , PRICE AS PRICE_AT_LAST_DATE
FROM DATABASE
WHERE (DATE SELECT MAX(DATE) FROM DATABASE
AND FUND = SELECT DISTINCT(FUND FROM DATEBASE))
ORDER BY 1,2|||Use GROUP BY with fund and max(date), date may need to be expanded, then apply an INNER JOIN to this result set with the original table to display the price aswell.|||Originally posted by r123456
Use GROUP BY with fund and max(date), date may need to be expanded, then apply an INNER JOIN to this result set with the original table to display the price aswell.

Thanks this worked a treat using two INNER JOINS one between Query1 to find the Last_Date_Entered AND Date of the Database then the other on the Fund. Here is the code

Query1
______
SELECT Fund, Max(Date) AS LAST_DATE_ENTERED
FROM DAtabase
GROUP BY Fund

Query2
______
SELECT Query1.Fund, AVG(Database.Price)
FROM Query1 INNER JOIN Database ON (Query1.Fund = Database.Fund) AND (Query1.LAST_DATE_ENTERED = Database.Date)
GROUP BY Query1.Fund

The average is necessary if there are two prices entered for any one date equally MAX could have been used. Thanks once again to all those who posted. Derek

newbie ? about stored procedures

I currently have several sprocs that require a variable which determines
which set of fields get returned. For ex: X returns fields 1, 2, 3 and Y
returns fields 4, 5, 6. When I run the sprocs the appropriate fields show up
in the results window but when I refresh the fields, reguardless of what
variable I used it displays X's fields in the fields window. Can I not create
two datasets running the same sproc using different variables. If not is this
so this something that I'll be able to do in the near future? Any help would
be most appreciated.Hey there,
As far as I know Reporting Services scans the Stored Procedure, caches the
field names and uses those.
Why don't you have your stored procedure return generic colum names such as
"Col1", "Col2", "Col3" and then apply some conditional formatting on those
column headers when they're placed on the report. Something like this:
txtColumn1.Value = IIF(Parameters!MyCondition.Value = "X", "Zip Code",
"Postal Code")
I'm pretty sure that this is by design since Reporting Services needs to
know the column names.
Hopefully I'm on the same page as you...
Ben
"Arly" <Arly@.discussions.microsoft.com> wrote in message
news:045E8DA3-0DA9-4EAE-9A7E-91343815D69D@.microsoft.com...
> I currently have several sprocs that require a variable which determines
> which set of fields get returned. For ex: X returns fields 1, 2, 3 and Y
> returns fields 4, 5, 6. When I run the sprocs the appropriate fields show
up
> in the results window but when I refresh the fields, reguardless of what
> variable I used it displays X's fields in the fields window. Can I not
create
> two datasets running the same sproc using different variables. If not is
this
> so this something that I'll be able to do in the near future? Any help
would
> be most appreciated.|||Thanks, Ben but that would mean changing hundreds of sprocs that are being
used by our VB programmers as well. Any other ideas?
"Benjamin Pierce" wrote:
> Hey there,
> As far as I know Reporting Services scans the Stored Procedure, caches the
> field names and uses those.
> Why don't you have your stored procedure return generic colum names such as
> "Col1", "Col2", "Col3" and then apply some conditional formatting on those
> column headers when they're placed on the report. Something like this:
> txtColumn1.Value = IIF(Parameters!MyCondition.Value = "X", "Zip Code",
> "Postal Code")
> I'm pretty sure that this is by design since Reporting Services needs to
> know the column names.
> Hopefully I'm on the same page as you...
>
> Ben
>
> "Arly" <Arly@.discussions.microsoft.com> wrote in message
> news:045E8DA3-0DA9-4EAE-9A7E-91343815D69D@.microsoft.com...
> > I currently have several sprocs that require a variable which determines
> > which set of fields get returned. For ex: X returns fields 1, 2, 3 and Y
> > returns fields 4, 5, 6. When I run the sprocs the appropriate fields show
> up
> > in the results window but when I refresh the fields, reguardless of what
> > variable I used it displays X's fields in the fields window. Can I not
> create
> > two datasets running the same sproc using different variables. If not is
> this
> > so this something that I'll be able to do in the near future? Any help
> would
> > be most appreciated.
>
>

Newbie : How to make Trigger for inserting/updating fields?

Hi All,
I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I update from
application, however since I new to SQLServer (used to use Access) I want to
insert/update these fields via trigger, the ModifyID should contain the user
name logged on to the SQLServer and the time he modify or insert the row.
Could this two insert/update containing in one trigger? Any help would be
highly appreciated. TIA.
HendrickCREATE TRIGGER YourTriggerName
ON YourTable
FOR INSERT, UPDATE
AS
BEGIN
UPDATE YourTable
SET
UpdateTime = GETDATE(),
UpdateID = USER_ID()
WHERE YourTable.PK IN
(
SELECT PK
FROM INSERTED
)
END
... Replace 'PK' with the primary key column(s) from your table.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Hendrick" <hbouty@.not.gmail.com> wrote in message
news:uZe0ITdzFHA.4032@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I update
> from
> application, however since I new to SQLServer (used to use Access) I want
> to
> insert/update these fields via trigger, the ModifyID should contain the
> user
> name logged on to the SQLServer and the time he modify or insert the row.
> Could this two insert/update containing in one trigger? Any help would be
> highly appreciated. TIA.
> Hendrick
>|||Great. Thanks for the prompt reply.
Hendrick
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#VvKdYdzFHA.3124@.TK2MSFTNGP12.phx.gbl...
> CREATE TRIGGER YourTriggerName
> ON YourTable
> FOR INSERT, UPDATE
> AS
> BEGIN
> UPDATE YourTable
> SET
> UpdateTime = GETDATE(),
> UpdateID = USER_ID()
> WHERE YourTable.PK IN
> (
> SELECT PK
> FROM INSERTED
> )
> END
>
> ... Replace 'PK' with the primary key column(s) from your table.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Hendrick" <hbouty@.not.gmail.com> wrote in message
> news:uZe0ITdzFHA.4032@.TK2MSFTNGP15.phx.gbl...
> > Hi All,
> >
> > I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I update
> > from
> > application, however since I new to SQLServer (used to use Access) I
want
> > to
> > insert/update these fields via trigger, the ModifyID should contain the
> > user
> > name logged on to the SQLServer and the time he modify or insert the
row.
> > Could this two insert/update containing in one trigger? Any help would
be
> > highly appreciated. TIA.
> >
> > Hendrick
> >
> >
>|||Hendrick,
Also see 'Multirow Considerations' in the SQL Server Books Online.
HTH
Jerry
"Hendrick" <hbouty@.not.gmail.com> wrote in message
news:O4b8RddzFHA.3408@.TK2MSFTNGP09.phx.gbl...
> Great. Thanks for the prompt reply.
> Hendrick
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:#VvKdYdzFHA.3124@.TK2MSFTNGP12.phx.gbl...
>> CREATE TRIGGER YourTriggerName
>> ON YourTable
>> FOR INSERT, UPDATE
>> AS
>> BEGIN
>> UPDATE YourTable
>> SET
>> UpdateTime = GETDATE(),
>> UpdateID = USER_ID()
>> WHERE YourTable.PK IN
>> (
>> SELECT PK
>> FROM INSERTED
>> )
>> END
>>
>> ... Replace 'PK' with the primary key column(s) from your table.
>>
>> --
>> Adam Machanic
>> SQL Server MVP
>> http://www.datamanipulation.net
>> --
>>
>> "Hendrick" <hbouty@.not.gmail.com> wrote in message
>> news:uZe0ITdzFHA.4032@.TK2MSFTNGP15.phx.gbl...
>> > Hi All,
>> >
>> > I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I update
>> > from
>> > application, however since I new to SQLServer (used to use Access) I
> want
>> > to
>> > insert/update these fields via trigger, the ModifyID should contain the
>> > user
>> > name logged on to the SQLServer and the time he modify or insert the
> row.
>> > Could this two insert/update containing in one trigger? Any help would
> be
>> > highly appreciated. TIA.
>> >
>> > Hendrick
>> >
>> >
>>
>|||Hey Jerry,
Did a search on Multirow Considerations in my book online, didn't get
anything back.
(SQL SERVER 2000 sp4). Do you have a more specific indication of what we
should be aware of here? Always wanna learn more ;-)
Thanks,
Bob
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:unJ3T5dzFHA.2884@.TK2MSFTNGP09.phx.gbl...
> Hendrick,
> Also see 'Multirow Considerations' in the SQL Server Books Online.
> HTH
> Jerry
> "Hendrick" <hbouty@.not.gmail.com> wrote in message
> news:O4b8RddzFHA.3408@.TK2MSFTNGP09.phx.gbl...
>> Great. Thanks for the prompt reply.
>> Hendrick
>> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
>> news:#VvKdYdzFHA.3124@.TK2MSFTNGP12.phx.gbl...
>> CREATE TRIGGER YourTriggerName
>> ON YourTable
>> FOR INSERT, UPDATE
>> AS
>> BEGIN
>> UPDATE YourTable
>> SET
>> UpdateTime = GETDATE(),
>> UpdateID = USER_ID()
>> WHERE YourTable.PK IN
>> (
>> SELECT PK
>> FROM INSERTED
>> )
>> END
>>
>> ... Replace 'PK' with the primary key column(s) from your table.
>>
>> --
>> Adam Machanic
>> SQL Server MVP
>> http://www.datamanipulation.net
>> --
>>
>> "Hendrick" <hbouty@.not.gmail.com> wrote in message
>> news:uZe0ITdzFHA.4032@.TK2MSFTNGP15.phx.gbl...
>> > Hi All,
>> >
>> > I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I
>> > update
>> > from
>> > application, however since I new to SQLServer (used to use Access) I
>> want
>> > to
>> > insert/update these fields via trigger, the ModifyID should contain
>> > the
>> > user
>> > name logged on to the SQLServer and the time he modify or insert the
>> row.
>> > Could this two insert/update containing in one trigger? Any help would
>> be
>> > highly appreciated. TIA.
>> >
>> > Hendrick
>> >
>> >
>>
>>
>|||Bob,
Here is the 2005 link http://msdn2.microsoft.com/en-us/library/ms250511.
Are you using the latest SQL Server Books Online? Here is the link to
upgrade if not:
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.mspx
HTH
Jerry
"Bob" <bdufournosp@.sgiims.com> wrote in message
news:eWEpoEezFHA.3588@.tk2msftngp13.phx.gbl...
> Hey Jerry,
> Did a search on Multirow Considerations in my book online, didn't get
> anything back.
> (SQL SERVER 2000 sp4). Do you have a more specific indication of what we
> should be aware of here? Always wanna learn more ;-)
> Thanks,
> Bob
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:unJ3T5dzFHA.2884@.TK2MSFTNGP09.phx.gbl...
>> Hendrick,
>> Also see 'Multirow Considerations' in the SQL Server Books Online.
>> HTH
>> Jerry
>> "Hendrick" <hbouty@.not.gmail.com> wrote in message
>> news:O4b8RddzFHA.3408@.TK2MSFTNGP09.phx.gbl...
>> Great. Thanks for the prompt reply.
>> Hendrick
>> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
>> news:#VvKdYdzFHA.3124@.TK2MSFTNGP12.phx.gbl...
>> CREATE TRIGGER YourTriggerName
>> ON YourTable
>> FOR INSERT, UPDATE
>> AS
>> BEGIN
>> UPDATE YourTable
>> SET
>> UpdateTime = GETDATE(),
>> UpdateID = USER_ID()
>> WHERE YourTable.PK IN
>> (
>> SELECT PK
>> FROM INSERTED
>> )
>> END
>>
>> ... Replace 'PK' with the primary key column(s) from your table.
>>
>> --
>> Adam Machanic
>> SQL Server MVP
>> http://www.datamanipulation.net
>> --
>>
>> "Hendrick" <hbouty@.not.gmail.com> wrote in message
>> news:uZe0ITdzFHA.4032@.TK2MSFTNGP15.phx.gbl...
>> > Hi All,
>> >
>> > I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I
>> > update
>> > from
>> > application, however since I new to SQLServer (used to use Access) I
>> want
>> > to
>> > insert/update these fields via trigger, the ModifyID should contain
>> > the
>> > user
>> > name logged on to the SQLServer and the time he modify or insert the
>> row.
>> > Could this two insert/update containing in one trigger? Any help
>> > would
>> be
>> > highly appreciated. TIA.
>> >
>> > Hendrick
>> >
>> >
>>
>>
>>
>

Newbie : How to make Trigger for inserting/updating fields?

Hi All,
I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I update from
application, however since I new to SQLServer (used to use Access) I want to
insert/update these fields via trigger, the ModifyID should contain the user
name logged on to the SQLServer and the time he modify or insert the row.
Could this two insert/update containing in one trigger? Any help would be
highly appreciated. TIA.
HendrickCREATE TRIGGER YourTriggerName
ON YourTable
FOR INSERT, UPDATE
AS
BEGIN
UPDATE YourTable
SET
UpdateTime = GETDATE(),
UpdateID = USER_ID()
WHERE YourTable.PK IN
(
SELECT PK
FROM INSERTED
)
END
... Replace 'PK' with the primary key column(s) from your table.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Hendrick" <hbouty@.not.gmail.com> wrote in message
news:uZe0ITdzFHA.4032@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I update
> from
> application, however since I new to SQLServer (used to use Access) I want
> to
> insert/update these fields via trigger, the ModifyID should contain the
> user
> name logged on to the SQLServer and the time he modify or insert the row.
> Could this two insert/update containing in one trigger? Any help would be
> highly appreciated. TIA.
> Hendrick
>|||Great. Thanks for the prompt reply.
Hendrick
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#VvKdYdzFHA.3124@.TK2MSFTNGP12.phx.gbl...
> CREATE TRIGGER YourTriggerName
> ON YourTable
> FOR INSERT, UPDATE
> AS
> BEGIN
> UPDATE YourTable
> SET
> UpdateTime = GETDATE(),
> UpdateID = USER_ID()
> WHERE YourTable.PK IN
> (
> SELECT PK
> FROM INSERTED
> )
> END
>
> ... Replace 'PK' with the primary key column(s) from your table.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Hendrick" <hbouty@.not.gmail.com> wrote in message
> news:uZe0ITdzFHA.4032@.TK2MSFTNGP15.phx.gbl...
want[vbcol=seagreen]
row.[vbcol=seagreen]
be[vbcol=seagreen]
>|||Hendrick,
Also see 'Multirow Considerations' in the SQL Server Books Online.
HTH
Jerry
"Hendrick" <hbouty@.not.gmail.com> wrote in message
news:O4b8RddzFHA.3408@.TK2MSFTNGP09.phx.gbl...
> Great. Thanks for the prompt reply.
> Hendrick
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:#VvKdYdzFHA.3124@.TK2MSFTNGP12.phx.gbl...
> want
> row.
> be
>|||Hey Jerry,
Did a search on Multirow Considerations in my book online, didn't get
anything back.
(SQL SERVER 2000 sp4). Do you have a more specific indication of what we
should be aware of here? Always wanna learn more ;-)
Thanks,
Bob
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:unJ3T5dzFHA.2884@.TK2MSFTNGP09.phx.gbl...
> Hendrick,
> Also see 'Multirow Considerations' in the SQL Server Books Online.
> HTH
> Jerry
> "Hendrick" <hbouty@.not.gmail.com> wrote in message
> news:O4b8RddzFHA.3408@.TK2MSFTNGP09.phx.gbl...
>|||Bob,
Here is the 2005 link http://msdn2.microsoft.com/en-us/library/ms250511.
Are you using the latest SQL Server Books Online? Here is the link to
upgrade if not:
http://www.microsoft.com/sql/techin...2000/books.mspx
HTH
Jerry
"Bob" <bdufournosp@.sgiims.com> wrote in message
news:eWEpoEezFHA.3588@.tk2msftngp13.phx.gbl...
> Hey Jerry,
> Did a search on Multirow Considerations in my book online, didn't get
> anything back.
> (SQL SERVER 2000 sp4). Do you have a more specific indication of what we
> should be aware of here? Always wanna learn more ;-)
> Thanks,
> Bob
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:unJ3T5dzFHA.2884@.TK2MSFTNGP09.phx.gbl...
>

Newbie : How to make Trigger for inserting/updating fields?

Hi All,
I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I update from
application, however since I new to SQLServer (used to use Access) I want to
insert/update these fields via trigger, the ModifyID should contain the user
name logged on to the SQLServer and the time he modify or insert the row.
Could this two insert/update containing in one trigger? Any help would be
highly appreciated. TIA.
Hendrick
CREATE TRIGGER YourTriggerName
ON YourTable
FOR INSERT, UPDATE
AS
BEGIN
UPDATE YourTable
SET
UpdateTime = GETDATE(),
UpdateID = USER_ID()
WHERE YourTable.PK IN
(
SELECT PK
FROM INSERTED
)
END
... Replace 'PK' with the primary key column(s) from your table.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Hendrick" <hbouty@.not.gmail.com> wrote in message
news:uZe0ITdzFHA.4032@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I update
> from
> application, however since I new to SQLServer (used to use Access) I want
> to
> insert/update these fields via trigger, the ModifyID should contain the
> user
> name logged on to the SQLServer and the time he modify or insert the row.
> Could this two insert/update containing in one trigger? Any help would be
> highly appreciated. TIA.
> Hendrick
>
|||Great. Thanks for the prompt reply.
Hendrick
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#VvKdYdzFHA.3124@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> CREATE TRIGGER YourTriggerName
> ON YourTable
> FOR INSERT, UPDATE
> AS
> BEGIN
> UPDATE YourTable
> SET
> UpdateTime = GETDATE(),
> UpdateID = USER_ID()
> WHERE YourTable.PK IN
> (
> SELECT PK
> FROM INSERTED
> )
> END
>
> ... Replace 'PK' with the primary key column(s) from your table.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Hendrick" <hbouty@.not.gmail.com> wrote in message
> news:uZe0ITdzFHA.4032@.TK2MSFTNGP15.phx.gbl...
want[vbcol=seagreen]
row.[vbcol=seagreen]
be
>
|||Hendrick,
Also see 'Multirow Considerations' in the SQL Server Books Online.
HTH
Jerry
"Hendrick" <hbouty@.not.gmail.com> wrote in message
news:O4b8RddzFHA.3408@.TK2MSFTNGP09.phx.gbl...
> Great. Thanks for the prompt reply.
> Hendrick
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:#VvKdYdzFHA.3124@.TK2MSFTNGP12.phx.gbl...
> want
> row.
> be
>
|||Hey Jerry,
Did a search on Multirow Considerations in my book online, didn't get
anything back.
(SQL SERVER 2000 sp4). Do you have a more specific indication of what we
should be aware of here? Always wanna learn more ;-)
Thanks,
Bob
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:unJ3T5dzFHA.2884@.TK2MSFTNGP09.phx.gbl...
> Hendrick,
> Also see 'Multirow Considerations' in the SQL Server Books Online.
> HTH
> Jerry
> "Hendrick" <hbouty@.not.gmail.com> wrote in message
> news:O4b8RddzFHA.3408@.TK2MSFTNGP09.phx.gbl...
>
|||Bob,
Here is the 2005 link http://msdn2.microsoft.com/en-us/library/ms250511.
Are you using the latest SQL Server Books Online? Here is the link to
upgrade if not:
http://www.microsoft.com/sql/techinf...000/books.mspx
HTH
Jerry
"Bob" <bdufournosp@.sgiims.com> wrote in message
news:eWEpoEezFHA.3588@.tk2msftngp13.phx.gbl...
> Hey Jerry,
> Did a search on Multirow Considerations in my book online, didn't get
> anything back.
> (SQL SERVER 2000 sp4). Do you have a more specific indication of what we
> should be aware of here? Always wanna learn more ;-)
> Thanks,
> Bob
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:unJ3T5dzFHA.2884@.TK2MSFTNGP09.phx.gbl...
>

newbie - Stored procedure

I have simple question - could be a bit stupid
I created a form in which several fields are obligatory, some are free to
fill in
now what is the best practice to follow
Should i create several SP's for every possible combination ?
Should i Create one SP where variables are possibly empty (if that is the
best thing to do, what's the right syntax ?)
Or should I create one SP with the obligatory values, and afterwards search
through the resultset?
thanx in advance...One proc should do it. You can have parameters with default values, if you
so choose:
create proc MyProc
(
@.id int
, @.x char (5) = 'ALFKI'
)
as
...
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Boonaap" <Boonaap@.discussions.microsoft.com> wrote in message
news:415B2831-6626-457A-98D4-42ED64F2C7F7@.microsoft.com...
I have simple question - could be a bit stupid
I created a form in which several fields are obligatory, some are free to
fill in
now what is the best practice to follow
Should i create several SP's for every possible combination ?
Should i Create one SP where variables are possibly empty (if that is the
best thing to do, what's the right syntax ?)
Or should I create one SP with the obligatory values, and afterwards search
through the resultset?
thanx in advance...

Newbee Question

I have a table with the following fields for example:
VerifyCode1
VerifyCode2
VerifyCode3
VerifyCode4
VerifyCode5
When I run a query which links the two tables together, and say for example
a row has entries in the VerifyCode1 and VerifyCode2 fields, this row is
dropped from the query dataset.
How can I get all the rows to show if they have one entry or all five.
Thanks for the helpPossibly you require an outer join, but that's just a guess. If you need
more help please read the following article which explains how best to
specify your problem for others to understand:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--

Newbe Questions

I am familier with crystal and just starting SSRS.

How do you set the report to print in Landscape?

How do you like combine two fields into a label with some additional text added it to build your custom label?

Salameh,

If you are developing in Visual Studio/Business Intelligence Dev. Studio, you can set the layout of the report by changing the Page width/height to be 11 by 8.5 instead of 8.5 by 11. You can do this in the properties window, or you can right-click below the report and select properties, and change the dimensions on the Layout tab.

To combine two fields with additional text, you could do something like the following...

="Field A is " & Fields!FieldA.Value & ", and Field B is " & Fields!FieldB.Value & "."

Hope that helps

NewB- Selected Data transfer from tables

New to the forums and SQL and need help to solve a tech support issue.
I need to export selected data from one table into a new table.

fields: NAME_LAST, NAME_FIRST, PURCHASE_ACCOUNT_BALANCE, EVENT_CREDIT_BALANCE
from table: C_CUSTOMER
where: EVENT_CREDIT_BALANCE, PURCHASE_ACCOUNT_BALANCE > 0

into new table: APPLE_CUSTOMERS

Any help would be great. My iterations haven't worked and I don't have my SQL book here and my searches aren't helping.
I am using interbase.
Thanksinsert into apple_customers(NAME_LAST, NAME_FIRST, PURCHASE_ACCOUNT_BALANCE, EVENT_CREDIT_BALANCE
) select NAME_LAST, NAME_FIRST, PURCHASE_ACCOUNT_BALANCE, EVENT_CREDIT_BALANCE
from C_CUstomers where
EVENT_CREDIT_BALANCE>0 AND PURCHASE_ACCOUNT_BALANCE > 0

or

insert into apple_customers(NAME_LAST, NAME_FIRST, PURCHASE_ACCOUNT_BALANCE, EVENT_CREDIT_BALANCE
) as select NAME_LAST, NAME_FIRST, PURCHASE_ACCOUNT_BALANCE, EVENT_CREDIT_BALANCE
from C_CUstomers where
EVENT_CREDIT_BALANCE>0 AND PURCHASE_ACCOUNT_BALANCE > 0|||Thanks, I was so close...|||Go to MS-Access.
Goto tables pane.
Right click->Import
Choose ms excel as Files of type. Specifiy the file and import the data
No rename the table name into all CAPITAL LETTERS

Right click on newly created table.
Export.
Choose ODBC Databases as Files of Type.
Choose the DSN . (If you havent created then goto control panel->administrative->ODBC. create as System DSN)

and then you are done
Cheers