Newbie question on SQL Query writing

Hi,

I am new to writing SQL queries in MS SQL & would like to do the
following: Write a query to retrieve all strings that start with a
particular value.

Basically, I am looking for the SQL equivalent of the regex "^".

Thanks,
AshokSELECT column1 FROM mytable WHERE charindex('Search Text', column1) = 1|||On 18 May 2005 08:01:46 -0700, ashok.anbalan@.gmail.com wrote:

>Hi,
>I am new to writing SQL queries in MS SQL & would like to do the
>following: Write a query to retrieve all strings that start with a
>particular value.
>Basically, I am looking for the SQL equivalent of the regex "^".
>Thanks,
>Ashok

Hi Ashok,

Assuming they need to start with 'a':

SELECT Column list
FROM MyTable
WHERE TheStringColumn LIKE 'a%'

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

newbie question on accessing count(*) results

hello,
this is my sql query with access:
Dim SQLstr2 As String = "Select count(*) as total, oDate from Order_Details where oNo = " + Request("oNo") + " group by oDate"

However, problem is, I am clueless about how to access the data that is derived from my count(*).

I tried this: Response.Write(reader2("total"))

All I get is that No data exists for the row/column.

I tried not to select oDate and even took away the WHERE, but the same problem persists. Any ideas please?here's a template code that might help you.

Dim ConnectionString As String = "Your_Connection_String"
Dim CommandText As String = "select Col1, Col2, Col3 from TableName"
Dim myConnection As New System.Data.SqlClient.SqlConnection(ConnectionString)
Dim myCommand As New System.Data.SqlClient.SqlCommand(CommandText, myConnection)
myConnection.Open()
Dim DataReader As System.Data.SqlClient.SqlDataReader = myCommand.ExecuteReader()
If DataReader.HasRows Then
 Do While DataReader.Read()
 Response.Write(DataReader.Item("Col1"))
 Response.Write(DataReader.Item("Col2"))
 Response.Write("<BR>")
 Loop
End If
DataReader.Close()
myconnection.close()

hth

Newbie question about sprocs

When I run my stored proceedure in query analyzer and copy the text to the pane in the report designer, all is well, but if I run the same stored proceedure as a stored procedure in the report designer it doesn't return all the fields that it does as text. What's the difference? What do I do to fix this?Two things come to mind. Ownership and location of the stored procedure.
OWNERSHIP:
Objects like tables, views, and stored procedures can be owned by different
logins.
Lets say you have two users in your database
user1
user2
They can both create a table called FOO
Each table can have different columns.
Each user can also create a stored procedure named procFOO
These stored procedures can return different columns.
LOCATION OF THE STORED PROCEDURE:
It is possible to create stored procedures in multiple databases. For
example, procFOO within northwind could select from the Products table and
procFOO within pubs could select from the authors table. Are you calling
the stored procedure from the database that you created it in (and are
running it from Query Analyzer)?
--
Keith
"Arly" <Arly@.discussions.microsoft.com> wrote in message
news:8F8426EB-7C6C-49C5-9C8F-825EB089A880@.microsoft.com...
> When I run my stored proceedure in query analyzer and copy the text to the
pane in the report designer, all is well, but if I run the same stored
proceedure as a stored procedure in the report designer it doesn't return
all the fields that it does as text. What's the difference? What do I do to
fix this?|||So I hit the nail on the head; was it an ownership or a location issue?
--
Keith
"Arly" <Arly@.discussions.microsoft.com> wrote in message
news:8EC3378E-037A-4C6A-93BE-7367603FDBE3@.microsoft.com...
> Thanks you were a great help. All is well!
> "Keith Kratochvil" wrote:
> > Two things come to mind. Ownership and location of the stored
procedure.
> >
> > OWNERSHIP:
> > Objects like tables, views, and stored procedures can be owned by
different
> > logins.
> > Lets say you have two users in your database
> > user1
> > user2
> > They can both create a table called FOO
> > Each table can have different columns.
> > Each user can also create a stored procedure named procFOO
> > These stored procedures can return different columns.
> >
> > LOCATION OF THE STORED PROCEDURE:
> > It is possible to create stored procedures in multiple databases. For
> > example, procFOO within northwind could select from the Products table
and
> > procFOO within pubs could select from the authors table. Are you
calling
> > the stored procedure from the database that you created it in (and are
> > running it from Query Analyzer)?
> >
> > --
> > Keith
> >
> >
> > "Arly" <Arly@.discussions.microsoft.com> wrote in message
> > news:8F8426EB-7C6C-49C5-9C8F-825EB089A880@.microsoft.com...
> > > When I run my stored proceedure in query analyzer and copy the text to
the
> > pane in the report designer, all is well, but if I run the same stored
> > proceedure as a stored procedure in the report designer it doesn't
return
> > all the fields that it does as text. What's the difference? What do I do
to
> > fix this?
> >
> >

Newbie Question about parameters

Hi there,

i'm using HTTP to get XML from SQLserver 2000. I need to query the database with parameters. most of them are arrays.

i am however unable to get any data from my database using

WHERE name IN (@.param )

i then tried

exec (' ...

WHERE name IN (' + @.param + ')
')

and still nothing.

Does anybody know what i'm doing wrong?

Thank alot

Wim Horemans :confused:
p.s. in @.param there should be something like "jef, jan, gert, dunno"Your local variable should look like "'jef', 'jan', 'gert', 'dunno'"

and your where caluse should evaluate to

exec (' ...

WHERE name IN ('jef', 'jan', 'gert', 'dunno')
')

Newbie question - Need count to return multiple values

Hi all,
This is probably a simple question but I still new enough that I can't
figure it out (this is only my second real query i'm so REALLY new).
I have a table that looks like this:
CustKey InvoiceDate
01 2006-05-19
02 2006-05-19
03 2006-05-19
04 2006-04-28
02 2006-05-19
03 2006-05-19
04 2006-05-19
04 2006-05-19
03 2006-05-19
I want my output to look like this:
CustKey Total for 2006-05-19
01 1
02 2
03 3
04 2
Basically I need a list that will tell me, by CustKey, how many Invoices
were done on a given day.
Now I can do a quick count that will tell me for a given customer and date
but I don't know how to have it check and return values for all 4 customers
based on date.
So far I have:
SELECT COUNT * FROM "Table1"
WHERE CustKey = '01'
AND "InvoiceDate" = ('2006-05-19')
Any help would be appreciated. I'm trying to save myself from having to do
a manual count of invoices on a wly basis.
Thanks in advance,
NancySELECT CustKey,COUNT( *) FROM Table1
WHERE InvoiceDate = ('2006-05-19')
GROUP BY CustKey
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks Denis, I figured it would be something simple.
This works perfectly!
"SQL" wrote:

> SELECT CustKey,COUNT( *) FROM Table1
> WHERE InvoiceDate = ('2006-05-19')
> GROUP BY CustKey
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>sql

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 Question

I have a table called cr. Within that table I have the colums c_id, c_type, first_name, last_name. I need help creating a query that will pull from an excel file that have the same colum names. This excel file is automaticaly generated from one of our sys
tems and the c_id will never change for the record.
So this is what im wanting to do. I need it to look at the xls file and if there are new records in that xls file it will add those to the table or if the records have changed it will update the records within the table based on the c_id. I think this is
feasable but I can't seem to get it to work. And example would be great.
Thanks
Eric
1) DTS package
2) SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\MyExcel.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:254E3DCB-06A0-4D59-9BAB-120B33A59F0B@.microsoft.com...
> I have a table called cr. Within that table I have the colums c_id,
c_type, first_name, last_name. I need help creating a query that will pull
from an excel file that have the same colum names. This excel file is
automaticaly generated from one of our systems and the c_id will never
change for the record.
> So this is what im wanting to do. I need it to look at the xls file and if
there are new records in that xls file it will add those to the table or if
the records have changed it will update the records within the table based
on the c_id. I think this is feasable but I can't seem to get it to work.
And example would be great.
> Thanks
|||and that will insert or update any records?
THanks
"Uri Dimant" wrote:

> Eric
> 1) DTS package
> 2) SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="c:\MyExcel.xls";
> User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
>
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:254E3DCB-06A0-4D59-9BAB-120B33A59F0B@.microsoft.com...
> c_type, first_name, last_name. I need help creating a query that will pull
> from an excel file that have the same colum names. This excel file is
> automaticaly generated from one of our systems and the c_id will never
> change for the record.
> there are new records in that xls file it will add those to the table or if
> the records have changed it will update the records within the table based
> on the c_id. I think this is feasable but I can't seem to get it to work.
> And example would be great.
>
>
sql

Newbie Question

I have a table called cr. Within that table I have the colums c_id, c_type,
first_name, last_name. I need help creating a query that will pull from an e
xcel file that have the same colum names. This excel file is automaticaly ge
nerated from one of our sys
tems and the c_id will never change for the record.
So this is what im wanting to do. I need it to look at the xls file and if t
here are new records in that xls file it will add those to the table or if t
he records have changed it will update the records within the table based on
the c_id. I think this is
feasable but I can't seem to get it to work. And example would be great.
ThanksEric
1) DTS package
2) SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\MyExcel.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:254E3DCB-06A0-4D59-9BAB-120B33A59F0B@.microsoft.com...
> I have a table called cr. Within that table I have the colums c_id,
c_type, first_name, last_name. I need help creating a query that will pull
from an excel file that have the same colum names. This excel file is
automaticaly generated from one of our systems and the c_id will never
change for the record.
> So this is what im wanting to do. I need it to look at the xls file and if
there are new records in that xls file it will add those to the table or if
the records have changed it will update the records within the table based
on the c_id. I think this is feasable but I can't seem to get it to work.
And example would be great.
> Thanks|||and that will insert or update any records?
THanks
"Uri Dimant" wrote:

> Eric
> 1) DTS package
> 2) SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="c:\MyExcel.xls";
> User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
>
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:254E3DCB-06A0-4D59-9BAB-120B33A59F0B@.microsoft.com...
> c_type, first_name, last_name. I need help creating a query that will pull
> from an excel file that have the same colum names. This excel file is
> automaticaly generated from one of our systems and the c_id will never
> change for the record.
> there are new records in that xls file it will add those to the table or i
f
> the records have changed it will update the records within the table based
> on the c_id. I think this is feasable but I can't seem to get it to work.
> And example would be great.
>
>

Newbie Question

I have a table called cr. Within that table I have the colums c_id, c_type, first_name, last_name. I need help creating a query that will pull from an excel file that have the same colum names. This excel file is automaticaly generated from one of our systems and the c_id will never change for the record.
So this is what im wanting to do. I need it to look at the xls file and if there are new records in that xls file it will add those to the table or if the records have changed it will update the records within the table based on the c_id. I think this is feasable but I can't seem to get it to work. And example would be great.
ThanksEric
1) DTS package
2) SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\MyExcel.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:254E3DCB-06A0-4D59-9BAB-120B33A59F0B@.microsoft.com...
> I have a table called cr. Within that table I have the colums c_id,
c_type, first_name, last_name. I need help creating a query that will pull
from an excel file that have the same colum names. This excel file is
automaticaly generated from one of our systems and the c_id will never
change for the record.
> So this is what im wanting to do. I need it to look at the xls file and if
there are new records in that xls file it will add those to the table or if
the records have changed it will update the records within the table based
on the c_id. I think this is feasable but I can't seem to get it to work.
And example would be great.
> Thanks

Newbie Query Syntax question

I just need to restore a deleted table from a local backup of the entire
database. For my example the database name is MYDB and the table MYTABL. I
THINK I understand the start to be:
USE MYDB
Go
Restore Database MYDB
From MYDB
Disk = 'C:\Location of folder\MYDB.bak
With NORECOVERY
MOVE
and from there I am stumped. I am not sure about the FILE clause and
"Logical file name" v. "Operating system file name"
Thanks in advance for any help.
I'm sorry, Monty, but there's no single table restore facility in SQL Server (think about data integrity and
relationships between tables).
You can restore the whole database into a new database (use MOVE option to specify new physical file names)
and then copy the desired table(s)/data into your production database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Monty" <montysl@.nospam.hotmail.com> wrote in message news:uKYJNBVgEHA.3932@.TK2MSFTNGP09.phx.gbl...
> I just need to restore a deleted table from a local backup of the entire
> database. For my example the database name is MYDB and the table MYTABL. I
> THINK I understand the start to be:
> USE MYDB
> Go
> Restore Database MYDB
> From MYDB
> Disk = 'C:\Location of folder\MYDB.bak
> With NORECOVERY
> MOVE
> and from there I am stumped. I am not sure about the FILE clause and
> "Logical file name" v. "Operating system file name"
> Thanks in advance for any help.
>
>
|||Thanks Tibor...I had been looking for the answer all morning...Thought I was
missing something.....feel slightly less ignorant of the matter now.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23tNy3CVgEHA.704@.TK2MSFTNGP09.phx.gbl...
> I'm sorry, Monty, but there's no single table restore facility in SQL
Server (think about data integrity and
> relationships between tables).
> You can restore the whole database into a new database (use MOVE option to
specify new physical file names)
> and then copy the desired table(s)/data into your production database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Monty" <montysl@.nospam.hotmail.com> wrote in message
news:uKYJNBVgEHA.3932@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
I
>
sql

Newbie Query Syntax question

I just need to restore a deleted table from a local backup of the entire
database. For my example the database name is MYDB and the table MYTABL. I
THINK I understand the start to be:
USE MYDB
Go
Restore Database MYDB
From MYDB
Disk = 'C:\Location of folder\MYDB.bak
With NORECOVERY
MOVE
and from there I am stumped. I am not sure about the FILE clause and
"Logical file name" v. "Operating system file name"
Thanks in advance for any help.I'm sorry, Monty, but there's no single table restore facility in SQL Server
(think about data integrity and
relationships between tables).
You can restore the whole database into a new database (use MOVE option to s
pecify new physical file names)
and then copy the desired table(s)/data into your production database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Monty" <montysl@.nospam.hotmail.com> wrote in message news:uKYJNBVgEHA.3932@.TK2MSFTNGP09.phx
.gbl...
> I just need to restore a deleted table from a local backup of the entire
> database. For my example the database name is MYDB and the table MYTABL. I
> THINK I understand the start to be:
> USE MYDB
> Go
> Restore Database MYDB
> From MYDB
> Disk = 'C:\Location of folder\MYDB.bak
> With NORECOVERY
> MOVE
> and from there I am stumped. I am not sure about the FILE clause and
> "Logical file name" v. "Operating system file name"
> Thanks in advance for any help.
>
>|||Thanks Tibor...I had been looking for the answer all morning...Thought I was
missing something.....feel slightly less ignorant of the matter now.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23tNy3CVgEHA.704@.TK2MSFTNGP09.phx.gbl...
> I'm sorry, Monty, but there's no single table restore facility in SQL
Server (think about data integrity and
> relationships between tables).
> You can restore the whole database into a new database (use MOVE option to
specify new physical file names)
> and then copy the desired table(s)/data into your production database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Monty" <montysl@.nospam.hotmail.com> wrote in message
news:uKYJNBVgEHA.3932@.TK2MSFTNGP09.phx.gbl...
I[vbcol=seagreen]
>

Newbie Query Syntax question

I just need to restore a deleted table from a local backup of the entire
database. For my example the database name is MYDB and the table MYTABL. I
THINK I understand the start to be:
USE MYDB
Go
Restore Database MYDB
From MYDB
Disk = 'C:\Location of folder\MYDB.bak
With NORECOVERY
MOVE
and from there I am stumped. I am not sure about the FILE clause and
"Logical file name" v. "Operating system file name"
Thanks in advance for any help.I'm sorry, Monty, but there's no single table restore facility in SQL Server (think about data integrity and
relationships between tables).
You can restore the whole database into a new database (use MOVE option to specify new physical file names)
and then copy the desired table(s)/data into your production database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Monty" <montysl@.nospam.hotmail.com> wrote in message news:uKYJNBVgEHA.3932@.TK2MSFTNGP09.phx.gbl...
> I just need to restore a deleted table from a local backup of the entire
> database. For my example the database name is MYDB and the table MYTABL. I
> THINK I understand the start to be:
> USE MYDB
> Go
> Restore Database MYDB
> From MYDB
> Disk = 'C:\Location of folder\MYDB.bak
> With NORECOVERY
> MOVE
> and from there I am stumped. I am not sure about the FILE clause and
> "Logical file name" v. "Operating system file name"
> Thanks in advance for any help.
>
>|||Thanks Tibor...I had been looking for the answer all morning...Thought I was
missing something.....feel slightly less ignorant of the matter now.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23tNy3CVgEHA.704@.TK2MSFTNGP09.phx.gbl...
> I'm sorry, Monty, but there's no single table restore facility in SQL
Server (think about data integrity and
> relationships between tables).
> You can restore the whole database into a new database (use MOVE option to
specify new physical file names)
> and then copy the desired table(s)/data into your production database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Monty" <montysl@.nospam.hotmail.com> wrote in message
news:uKYJNBVgEHA.3932@.TK2MSFTNGP09.phx.gbl...
> > I just need to restore a deleted table from a local backup of the entire
> > database. For my example the database name is MYDB and the table MYTABL.
I
> > THINK I understand the start to be:
> >
> > USE MYDB
> > Go
> > Restore Database MYDB
> > From MYDB
> > Disk = 'C:\Location of folder\MYDB.bak
> > With NORECOVERY
> > MOVE
> >
> > and from there I am stumped. I am not sure about the FILE clause and
> > "Logical file name" v. "Operating system file name"
> >
> > Thanks in advance for any help.
> >
> >
> >
>

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 query question

I need to include data from a table which is not directly related to
the "hub" table, and am kinda stuck.
I doubted it would work (tried anyhow), but this is the code from my
attempt:
SELECT
ConsignmentCodes.CodeDesc,
Stockline.ConsignmentCode,
Reserve.SalesOrderId,
Invoice.ShipDate,
Part.Number,
Reserve.Shipped as Qty,
SOItem.UnitAmount,
(Reserve.Shipped*SOItem.UnitAmount) as Total,
1-(ConsignmentCodes.CodeCommRate*.01) as ConsignorPercent
FROM Stockline JOIN Part ON (Stockline.PartId=Part.PartId)
LEFT JOIN Reserve ON (Stockline.StockId=Reserve.StockId)
LEFT JOIN ConsignmentCodes ON
(Stockline.ConsignmentCode=ConsignmentCodes.Consig nmentCode)
LEFT JOIN SOItem ON (Stockline.PartId=SOItem.PartId)
WHERE Reserve.SalesOrderId=Invoice.SalesOrderId
AND Reserve.InvoiceId=Invoice.InvoiceId
You can see why it did didn't work, and hopefully, what I am trying to
do.
I need "Invoice.ShipDate", but "Invoice" doesn't have any fields which
relate to "Stockline".
If I try to base all my relationships on "Resreve", then I end up with
ths same problem for "Part".
What's my next step?
-Mo
On Feb 22, 2:41Xpm, Lucas Kartawidjaja
<LucasKartawidj...@.discussions.microsoft.com> wrote:
> Without knowing the table structure and their relationship, I would suggest
> try the following query or something close:
> SELECT
> X X X X ConsignmentCodes.CodeDesc,
> X X X X Stockline.ConsignmentCode,
> X X X X Reserve.SalesOrderId,
> X X X X Invoice.ShipDate,
> X X X X Part.Number,
> X X X X Reserve.Shipped as Qty,
> X X X X SOItem.UnitAmount,
> X X X X (Reserve.Shipped*SOItem.UnitAmount) as Total,
> X X X X 1-(ConsignmentCodes.CodeCommRate*.01) as ConsignorPercent
> FROM X XStockline LEFT OUTER JOIN Part ON (Stockline.PartId=Part.PartId)
> X X X X LEFT OUTER JOIN Reserve ON (Stockline.StockId=Reserve.StockId)
> X X X X LEFT OUTER JOIN ConsignmentCodes ON
> X X X X X X X X (Stockline.ConsignmentCode=ConsignmentCodes.Consig nmentCode)
> X X X X LEFT OUTER JOIN SOItem ON (Stockline.PartId=SOItem.PartId)
> X X X X LEFT OUTER JOIN Invoice ON (Reserve.SalesOrderId=Invoice..SalesOrderId)
> X X X X AND (Reserve.InvoiceId=Invoice.InvoiceId)
> Lucas
>
> "Mehile.Orl...@.gmail.com" wrote:
>
>
>
> - Show quoted text -
Thanks, I think that gets me going again.
If I run into anything else, I'll repost.
Mo
sql

Newbie Query Question

I have three tables I'm trying to query.
Table 1 - Employees
Table 2 - Cell Phones
Table 3 - Pagers
What I'm trying to do is query ALL of the employees in Table 1 and show
either their pager number or cell phone number or still list them even if
both those fields are Null.
Can someone help point me in the right direction. Everytime I try to run my
own query I just get the employees that have both a cell and a pager.
Any information would be greatly appreciated.
Thanks, Correyselect * from Employees E
left outer join CellPhones C on
C.EmployeeID = E.EmployeeID
left outer join Pagers P on
E.EmployeeID = P.EmployeeID
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Correy" <cgrist@.comcast.net> wrote in message
news:euChehtqIHA.2256@.TK2MSFTNGP05.phx.gbl...
>I have three tables I'm trying to query.
> Table 1 - Employees
> Table 2 - Cell Phones
> Table 3 - Pagers
> What I'm trying to do is query ALL of the employees in Table 1 and show
> either their pager number or cell phone number or still list them even if
> both those fields are Null.
> Can someone help point me in the right direction. Everytime I try to run
> my own query I just get the employees that have both a cell and a pager.
> Any information would be greatly appreciated.
> Thanks, Correy
>
>

Newbie query question

I need to include data from a table which is not directly related to
the "hub" table, and am kinda stuck.
I doubted it would work (tried anyhow), but this is the code from my
attempt:
SELECT
ConsignmentCodes.CodeDesc,
Stockline.ConsignmentCode,
Reserve.SalesOrderId,
Invoice.ShipDate,
Part.Number,
Reserve.Shipped as Qty,
SOItem.UnitAmount,
(Reserve.Shipped*SOItem.UnitAmount) as Total,
1-(ConsignmentCodes.CodeCommRate*.01) as ConsignorPercent
FROM Stockline JOIN Part ON (Stockline.PartId=Part.PartId)
LEFT JOIN Reserve ON (Stockline.StockId=Reserve.StockId)
LEFT JOIN ConsignmentCodes ON
(Stockline.ConsignmentCode=ConsignmentCodes.ConsignmentCode)
LEFT JOIN SOItem ON (Stockline.PartId=SOItem.PartId)
WHERE Reserve.SalesOrderId=Invoice.SalesOrderId
AND Reserve.InvoiceId=Invoice.InvoiceId
You can see why it did didn't work, and hopefully, what I am trying to
do.
I need "Invoice.ShipDate", but "Invoice" doesn't have any fields which
relate to "Stockline".
If I try to base all my relationships on "Resreve", then I end up with
ths same problem for "Part".
What's my next step?
-MoWithout knowing the table structure and their relationship, I would suggest
try the following query or something close:
SELECT
ConsignmentCodes.CodeDesc,
Stockline.ConsignmentCode,
Reserve.SalesOrderId,
Invoice.ShipDate,
Part.Number,
Reserve.Shipped as Qty,
SOItem.UnitAmount,
(Reserve.Shipped*SOItem.UnitAmount) as Total,
1-(ConsignmentCodes.CodeCommRate*.01) as ConsignorPercent
FROM Stockline LEFT OUTER JOIN Part ON (Stockline.PartId=Part.PartId)
LEFT OUTER JOIN Reserve ON (Stockline.StockId=Reserve.StockId)
LEFT OUTER JOIN ConsignmentCodes ON
(Stockline.ConsignmentCode=ConsignmentCodes.ConsignmentCode)
LEFT OUTER JOIN SOItem ON (Stockline.PartId=SOItem.PartId)
LEFT OUTER JOIN Invoice ON (Reserve.SalesOrderId=Invoice.SalesOrderId)
AND (Reserve.InvoiceId=Invoice.InvoiceId)
Lucas
"Mehile.Orloff@.gmail.com" wrote:
> I need to include data from a table which is not directly related to
> the "hub" table, and am kinda stuck.
> I doubted it would work (tried anyhow), but this is the code from my
> attempt:
> SELECT
> ConsignmentCodes.CodeDesc,
> Stockline.ConsignmentCode,
> Reserve.SalesOrderId,
> Invoice.ShipDate,
> Part.Number,
> Reserve.Shipped as Qty,
> SOItem.UnitAmount,
> (Reserve.Shipped*SOItem.UnitAmount) as Total,
> 1-(ConsignmentCodes.CodeCommRate*.01) as ConsignorPercent
> FROM Stockline JOIN Part ON (Stockline.PartId=Part.PartId)
> LEFT JOIN Reserve ON (Stockline.StockId=Reserve.StockId)
> LEFT JOIN ConsignmentCodes ON
> (Stockline.ConsignmentCode=ConsignmentCodes.ConsignmentCode)
> LEFT JOIN SOItem ON (Stockline.PartId=SOItem.PartId)
> WHERE Reserve.SalesOrderId=Invoice.SalesOrderId
> AND Reserve.InvoiceId=Invoice.InvoiceId
> You can see why it did didn't work, and hopefully, what I am trying to
> do.
> I need "Invoice.ShipDate", but "Invoice" doesn't have any fields which
> relate to "Stockline".
> If I try to base all my relationships on "Resreve", then I end up with
> ths same problem for "Part".
>
> What's my next step?
> -Mo
>|||On Feb 22, 2:41=A0pm, Lucas Kartawidjaja
<LucasKartawidj...@.discussions.microsoft.com> wrote:
> Without knowing the table structure and their relationship, I would sugges=t
> try the following query or something close:
> SELECT
> =A0 =A0 =A0 =A0 ConsignmentCodes.CodeDesc,
> =A0 =A0 =A0 =A0 Stockline.ConsignmentCode,
> =A0 =A0 =A0 =A0 Reserve.SalesOrderId,
> =A0 =A0 =A0 =A0 Invoice.ShipDate,
> =A0 =A0 =A0 =A0 Part.Number,
> =A0 =A0 =A0 =A0 Reserve.Shipped as Qty,
> =A0 =A0 =A0 =A0 SOItem.UnitAmount,
> =A0 =A0 =A0 =A0 (Reserve.Shipped*SOItem.UnitAmount) as Total,
> =A0 =A0 =A0 =A0 1-(ConsignmentCodes.CodeCommRate*.01) as ConsignorPercent
> FROM =A0 =A0Stockline LEFT OUTER JOIN Part ON (Stockline.PartId=3DPart.Par=tId)
> =A0 =A0 =A0 =A0 LEFT OUTER JOIN Reserve ON (Stockline.StockId=3DReserve.St=ockId)
> =A0 =A0 =A0 =A0 LEFT OUTER JOIN ConsignmentCodes ON
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 (Stockline.ConsignmentCode=3DConsignmentCo=des.ConsignmentCode)
> =A0 =A0 =A0 =A0 LEFT OUTER JOIN SOItem ON (Stockline.PartId=3DSOItem.PartI=d)
> =A0 =A0 =A0 =A0 LEFT OUTER JOIN Invoice ON (Reserve.SalesOrderId=3DInvoice=.SalesOrderId)
> =A0 =A0 =A0 =A0 AND (Reserve.InvoiceId=3DInvoice.InvoiceId)
> Lucas
>
> "Mehile.Orl...@.gmail.com" wrote:
> > I need to include data from a table which is not directly related to
> > the "hub" table, and am kinda stuck.
> > I doubted it would work (tried anyhow), but this is the code from my
> > attempt:
> > SELECT
> > ConsignmentCodes.CodeDesc,
> > Stockline.ConsignmentCode,
> > Reserve.SalesOrderId,
> > Invoice.ShipDate,
> > Part.Number,
> > Reserve.Shipped as Qty,
> > SOItem.UnitAmount,
> > (Reserve.Shipped*SOItem.UnitAmount) as Total,
> > 1-(ConsignmentCodes.CodeCommRate*.01) as ConsignorPercent
> > FROM Stockline JOIN Part ON (Stockline.PartId=3DPart.PartId)
> > LEFT JOIN Reserve ON (Stockline.StockId=3DReserve.StockId)
> > LEFT JOIN ConsignmentCodes ON
> > (Stockline.ConsignmentCode=3DConsignmentCodes.ConsignmentCode)
> > LEFT JOIN SOItem ON (Stockline.PartId=3DSOItem.PartId)
> > WHERE Reserve.SalesOrderId=3DInvoice.SalesOrderId
> > AND Reserve.InvoiceId=3DInvoice.InvoiceId
> > You can see why it did didn't work, and hopefully, what I am trying to
> > do.
> > I need "Invoice.ShipDate", but "Invoice" doesn't have any fields which
> > relate to "Stockline".
> > If I try to base all my relationships on "Resreve", then I end up with
> > ths same problem for "Part".
> > What's my next step?
> > -Mo- Hide quoted text -
> - Show quoted text -
Thanks, I think that gets me going again.
If I run into anything else, I'll repost.
Mo

Newbie Query Problem

I want to use the same field in one table and return multiple columns for
different criteria. In other words...
First column
SUM(Sales.NetSales) as 'Total Sales').
Then I want a second column as
SUM(Sales.NetSales) as 'Category 02' where Sales.categoryid='02'.
Is this reasonable? I am sure that it is a simple thing that I am just
ignorant of.
Thanks.
ChuckChuck,
Try:
--rows
SELECT CategoryID, SUM(Sales) AS 'Total Sales'
FROM NetSales
GROUP BY CategoryID
--or
--columns
SELECT 'Category 1' = (SELECT SUM(Sales) AS 'Total Sales'FROM NetSales WHERE
CategoryID = 1),
'Category 2' = (SELECT SUM(Sales) AS 'Total Sales'FROM NetSales WHERE
CategoryID = 2)
HTH
Jerry
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:94FD5CDE-C7BE-43CF-857C-2A847675A1EF@.microsoft.com...
>I want to use the same field in one table and return multiple columns for
> different criteria. In other words...
> First column
> SUM(Sales.NetSales) as 'Total Sales').
> Then I want a second column as
> SUM(Sales.NetSales) as 'Category 02' where Sales.categoryid='02'.
> Is this reasonable? I am sure that it is a simple thing that I am just
> ignorant of.
> Thanks.
> Chuck|||SELECT
(SELECT SUM(NetSales) FROM Sales) as TotalSales,
(SELECT SUM(NetSales) FROM Sales WHERE categoryid = '02') as Category02
Chuck wrote:
> I want to use the same field in one table and return multiple columns for
> different criteria. In other words...
> First column
> SUM(Sales.NetSales) as 'Total Sales').
> Then I want a second column as
> SUM(Sales.NetSales) as 'Category 02' where Sales.categoryid='02'.
> Is this reasonable? I am sure that it is a simple thing that I am just
> ignorant of.
> Thanks.
> Chuck|||On Mon, 19 Sep 2005 11:58:06 -0700, Chuck wrote:

>I want to use the same field in one table and return multiple columns for
>different criteria. In other words...
>First column
>SUM(Sales.NetSales) as 'Total Sales').
>Then I want a second column as
>SUM(Sales.NetSales) as 'Category 02' where Sales.categoryid='02'.
>Is this reasonable? I am sure that it is a simple thing that I am just
>ignorant of.
>Thanks.
>Chuck
Hi Chuck,
Here's a way that requires only one pass over the table:
SELECT SUM(NetSales) AS 'Total Sales',
SUM(CASE WHEN categoryid = '02' THEN NetSales ELSE NULL END) AS
'Category 02'
FROM YourTable
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

newbie query help

hello all,i am developing an application which displays available pcs in our lab.all activites are stored in a sql server table.Sql server 2000

Table structure is field name datatype null values
logon varchar(6) canbe null (values = login or logoff)
username varchar(20) canbe null (eg user01,B023)
computername varchar(20) canbenull (if its in lab1 name will be lab1-01,lab1-02..,if its in lab2 name will be lab2-01,lab2-02.)
logontime datetime(8) canbenull (26/11/2002 11:17:52)

table sample is

Logon computername username logontime



Login lab1-01 x 2003-07-09 11:17:00.000

Login lab1-03 y 2003-07-09 11:19:00.000

Logoff lab1-01 x 2003-07-09 11:20:00.000

with these details is it possible to see currently how many machines r occupied in each lab.if so how?i am planning to write an asp page which will display currently available machines,so tat user can go to particular lab .i am a sql newbie .if any 1 can guide me tat wuld be helpful for me .thx in advance

-regards
aravindas far as i can see (im finding it hard to understand exactly what fields will be in your database) all you should need to extract the info is something along the lines of:

select from tableA computername
where (logon = ' ')

presuming the table is called 'tableA'

you might be better off having another field of type boolean which simply shows whether anyone is logged into the machine, maybe 0 for vacant or 1 for in-use.

hope this helps|||assume that a computer is "occupied" when the latest logon for it is "Login"

this query will list the individual machines:
select computername
, logontime as LastLoginTime
from yourtable as TT
where logontime
= ( select max(logontime)
from yourtable
where logontime = TT.logontime )
and logon = 'Login'
order
by computername
this query will count them by lab and list them in order by the fewest occupied to the most:
select left(computername,4) as Lab
, count(*) as OccupiedCount
from yourtable as TT
where logontime
= ( select max(logontime)
from yourtable
where logontime = TT.logontime )
and logon = 'Login'
group
by left(computername,4)
order
by 2
rudy|||How can you tell if they are occupied if all you have is the last logon time? You need a field that indicates their logoff time as well. With that, you can not only tell which machines are occupied (at least one associated logon has a null logoff time), but also other valuable information such as usage patterns.

blindman|||blindman, the logoffs are there, as separate rows

that's why my query checks for when the latest logon for the machine is "Login"

:)|||hello all thx for ur replies ,i didt read ur query.i created a new table with computername,status (which can be login or logoff) ,when old table gets inserted it triggers an event which sets the status of particular machine in new table .now i can query the new table to see the availability...is it a good practice? its my first sql prg .gimme some inputs

-regards
aravind|||Ahh...I see your logoff rows now. I've worked tables that way before, but I prefer storing logoffs as a separate field rather than a separate record. That way each row represents a single session and it makes calculations easier. By using a separate field you don't need the Max subquery, and finding the total amount of time for a particular user across sessions is as simple as sum(LogoffTime - LogonTime).

blindman

Newbie problem: Saving a view from a linked server won't work

Hi. I've created a linked server to Oracle 8i. I want to save a view as
follows:
SELECT *
FROM ORACLE8I..SCOTT.EMP EMP_1
From SQL Query Analyser, this returns a nice set or records. Running this
from the view designer also returns a nice result. However, if I try to to
save the view, I get the following error message:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
could not be performed because the OLE DB provider 'MSDAORA' was unable to
begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
Anyone know what I'm doing wrong?
Hi
Have you checked
http://support.microsoft.com/default...b;EN-US;280106
John
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributedtransaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?
>
|||Hi
Have you checked
http://support.microsoft.com/default...b;EN-US;280106
John
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributedtransaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?
>
|||None of the stuff in that article seems to help. Same error message occurs.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:417a46c7$0$12657$afc38c87@.news.easynet.co.uk. ..
> Hi
> Have you checked
> http://support.microsoft.com/default...b;EN-US;280106
> John
> <arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> to
>
|||Hi
This one seems to imply MSDTC is not running:
http://tinyurl.com/4wghd
John
<arch> wrote in message news:417a85ec@.funnel.arach.net.au...
> None of the stuff in that article seems to help. Same error message
occurs.[vbcol=seagreen]
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:417a46c7$0$12657$afc38c87@.news.easynet.co.uk. ..
as[vbcol=seagreen]
to[vbcol=seagreen]
operation[vbcol=seagreen]
[OLE/DB[vbcol=seagreen]
0x8004d01b].
>
|||(arch) writes:
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running
> this from the view designer also returns a nice result. However, if I
> try to to save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
I assume that with "view designer" you mean what's in Enterprise Manager.
I used the Profiler, to see what Enterprise Manager passes to SQL Server,
and I found that it starts a transaction before it creates a view, no matter
if the view refers to local tables only or remote tables as well.
Apparently you have not set things so you can run distributed transactions
against your Oracle box. I have no expierence with Oracle servers, so I
cannot help there. But checking that MSDTC is running on the local SQL
Server machine as John suggested is a simple thing.
But if you don't need distrubuted transactions against your Oracle server,
there is a very simple workaround: create the view from Query Analyzer
instead.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||Thanks John and Erland. That seems to have solved it. DTC is certainly
running. Simply avoiding the use of the View Designer in Enterprise Manager
seems to prevent the error from occurring. Damn, I wish I'd thought of
that!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns958C2C52D211Yazorman@.127.0.0.1...
> (arch) writes:
> I assume that with "view designer" you mean what's in Enterprise Manager.
> I used the Profiler, to see what Enterprise Manager passes to SQL Server,
> and I found that it starts a transaction before it creates a view, no
> matter
> if the view refers to local tables only or remote tables as well.
> Apparently you have not set things so you can run distributed transactions
> against your Oracle box. I have no expierence with Oracle servers, so I
> cannot help there. But checking that MSDTC is running on the local SQL
> Server machine as John suggested is a simple thing.
> But if you don't need distrubuted transactions against your Oracle server,
> there is a very simple workaround: create the view from Query Analyzer
> instead.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
|||Linked server connections only allow select insert update and delete... and
( unless you do tricks) you may not change the DDL on the Linked server...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?
>
sql