Query.....help

Cloumn Name, Data type, length, allow nulls:
ID int 4 0
NAAM nvarchar 254 1
DATUM smalldatetime 4 1
Dag int 4 1
Maand int 4 1
Jaar int 4 1
Reenval_Silo float 8 1
Using this table i need to get the average of "Reenval_Silo" per month
for "NAAM " for the years 2006, 2005 & 2004
Any help will be highly appreciated...thnx in advance
Hi
Since you have not posted DDL+ sample data + an expected result , I'll give
an idea about how you van do that
--Average of quantity per year group by stor_id
SELECT
stor_id,
AVG(CASE YEAR(ord_date)
WHEN 2004 THEN qty
ELSE 0
END) AS c2004 ,
AVG(CASE YEAR(ord_date)
WHEN 2005 THEN qty
ELSE 0
END) AS c2005 ,
AVG(CASE YEAR(ord_date)
WHEN 2006 THEN qty
ELSE 0
END) AS c2006
FROM Sales
GROUP BY stor_id
ORDER BY stor_id
"amatuer" <njoosub@.gmail.com> wrote in message
news:1160723854.407946.4020@.k70g2000cwa.googlegrou ps.com...
> Cloumn Name, Data type, length, allow nulls:
> ID int 4 0
> NAAM nvarchar 254 1
> DATUM smalldatetime 4 1
> Dag int 4 1
> Maand int 4 1
> Jaar int 4 1
> Reenval_Silo float 8 1
>
> Using this table i need to get the average of "Reenval_Silo" per month
> for "NAAM " for the years 2006, 2005 & 2004
> Any help will be highly appreciated...thnx in advance
>

Query.....help

Cloumn Name, Data type, length, allow nulls:
ID int 4 0
NAAM nvarchar 254 1
DATUM smalldatetime 4 1
Dag int 4 1
Maand int 4 1
Jaar int 4 1
Reenval_Silo float 8 1
Using this table i need to get the average of "Reenval_Silo" per month
for "NAAM " for the years 2006, 2005 & 2004
Any help will be highly appreciated...thnx in advanceHi
Since you have not posted DDL+ sample data + an expected result , I'll give
an idea about how you van do that
--Average of quantity per year group by stor_id
SELECT
stor_id,
AVG(CASE YEAR(ord_date)
WHEN 2004 THEN qty
ELSE 0
END) AS c2004 ,
AVG(CASE YEAR(ord_date)
WHEN 2005 THEN qty
ELSE 0
END) AS c2005 ,
AVG(CASE YEAR(ord_date)
WHEN 2006 THEN qty
ELSE 0
END) AS c2006
FROM Sales
GROUP BY stor_id
ORDER BY stor_id
"amatuer" <njoosub@.gmail.com> wrote in message
news:1160723854.407946.4020@.k70g2000cwa.googlegroups.com...
> Cloumn Name, Data type, length, allow nulls:
> ID int 4 0
> NAAM nvarchar 254 1
> DATUM smalldatetime 4 1
> Dag int 4 1
> Maand int 4 1
> Jaar int 4 1
> Reenval_Silo float 8 1
>
> Using this table i need to get the average of "Reenval_Silo" per month
> for "NAAM " for the years 2006, 2005 & 2004
> Any help will be highly appreciated...thnx in advance
>

Query.....help

Cloumn Name, Data type, length, allow nulls:
ID int 4 0
NAAM nvarchar 254 1
DATUM smalldatetime 4 1
Dag int 4 1
Maand int 4 1
Jaar int 4 1
Reenval_Silo float 8 1
Using this table i need to get the average of "Reenval_Silo" per month
for "NAAM " for the years 2006, 2005 & 2004
Any help will be highly appreciated...thnx in advanceHi
Since you have not posted DDL+ sample data + an expected result , I'll give
an idea about how you van do that
--Average of quantity per year group by stor_id
SELECT
stor_id,
AVG(CASE YEAR(ord_date)
WHEN 2004 THEN qty
ELSE 0
END) AS c2004 ,
AVG(CASE YEAR(ord_date)
WHEN 2005 THEN qty
ELSE 0
END) AS c2005 ,
AVG(CASE YEAR(ord_date)
WHEN 2006 THEN qty
ELSE 0
END) AS c2006
FROM Sales
GROUP BY stor_id
ORDER BY stor_id
"amatuer" <njoosub@.gmail.com> wrote in message
news:1160723854.407946.4020@.k70g2000cwa.googlegroups.com...
> Cloumn Name, Data type, length, allow nulls:
> ID int 4 0
> NAAM nvarchar 254 1
> DATUM smalldatetime 4 1
> Dag int 4 1
> Maand int 4 1
> Jaar int 4 1
> Reenval_Silo float 8 1
>
> Using this table i need to get the average of "Reenval_Silo" per month
> for "NAAM " for the years 2006, 2005 & 2004
> Any help will be highly appreciated...thnx in advance
>sql

Query XML type with values from another XML type

Hi - I'm using SQL Server 2005, and I have a view with an XML data type column. I would like to write a stored proc that takes an XML data type as a parameter and return the rows from the view that match the columns in the parameter.

At this point, I'm just trying to get the syntax correct, so I started with some simple queries. Below are the queries:

declare @.params xml

declare @.view xml

set @.params = '<HierarchyTypes>

<HierarchyType hierarchyTypeId="4" />

<HierarchyType hierarchyTypeId="5" />

</HierarchyTypes>'

set @.view = '<HierarchyTypes>

<HierarchyType hierarchyTypeId="4" otherProp = "1"/>

<HierarchyType hierarchyTypeId="5" otherProp = "2"/>

<HierarchyType hierarchyTypeId="6" otherProp = "3"/>

<HierarchyType hierarchyTypeId="7" otherProp = "4"/>

</HierarchyTypes>'

SELECT T.c.query('.') AS result

FROM @.view.nodes('/HierarchyTypes/HierarchyType') T(c)

I would like to get the nodes from @.view where @.view.hierarchyTypeId = @.params.hierarchyTypeId. This should be pretty simple, but I'm missing it...

Any thoughts are appreciated!

Thanks,

Phil

It sounds like you are trying to join view and params to get a results set which has one matching node per row. If that is the case, the below query should be doing what you are looking for. If you want to return a single xml fragment, a different technique would have to be used.

In the query below, we iterate over the HierarchyType nodes of both @.view and @.params, project the values of the hierarchyTypeId attributes as integers using the the value() function, verify that they match, and the return the @.param node that matches.

declare @.params xml
declare @.view xml

set @.params = '<HierarchyTypes>
<HierarchyType hierarchyTypeId="4" />
<HierarchyType hierarchyTypeId="5" />
</HierarchyTypes>'

set @.view = '<HierarchyTypes>
<HierarchyType hierarchyTypeId="4" otherProp = "1"/>
<HierarchyType hierarchyTypeId="5" otherProp = "2"/>
<HierarchyType hierarchyTypeId="6" otherProp = "3"/>
<HierarchyType hierarchyTypeId="7" otherProp = "4"/>
</HierarchyTypes>'

SELECT T.c.query('.') AS result
FROM @.view.nodes('/HierarchyTypes/HierarchyType') T(c),

@.params.nodes('/HierarchyTypes/HierarchyType') P(c)
WHERE T.c.value('@.hierarchyTypeId', 'int') = P.c.value('@.hierarchyTypeId', 'int')

|||

Hi Todd - this is very close to what I need. The only other wrinkle is that I'm trying to select all of the columns from the view (not just the hierarchyTypeId). The view has an XML column called HierarchyTypes, and I want to use that column in my 'join criteria'. And I'd like to do this in a stored proc.

CREATE PROCEDURE [dbo].[CodeHierarchy_SearchHierarchy]
@.codeTypes xml = NULL
AS
BEGIN
SET NOCOUNT ON;
SELECT v.nodeId,
v.parentNodeId,
v.HierarchyTypes
FROM VLinkedCodeHierarchies as v
WHERE /* hierarchyTypes in @.codeTypes are also in v.HierarchyTypes */

At this point, I also require that the return is NOT XML.

Thanks,

Phil

|||

So it sounds like you have a table or a view that has a few relational columns, and 1 xml column. And then you want to join it with an XML fragment.

Is that correct?

If so, then you can modify the query to use CROSS APPLY. In the example I create a test table which has an xml column, fill it with data and then join it with the fragment, projecting the relevant relational columns and xml data. You could then wrap it in a stored procedure or user defined function as needed.

(I CROSS APPLY the vTest table with nodes() function so that I get the nodes for the current row.)

declare @.params xml

drop table vTest
Create table vTest(
id int,
val xml
)

insert into vTest (id, val) values (1,'<HierarchyTypes>
<HierarchyType hierarchyTypeId="4" otherProp = "1"/>
<HierarchyType hierarchyTypeId="5" otherProp = "2"/>
<HierarchyType hierarchyTypeId="6" otherProp = "3"/>
<HierarchyType hierarchyTypeId="7" otherProp = "4"/>
</HierarchyTypes>')

insert into vTest (id, val) values (2,'<HierarchyTypes>
<HierarchyType hierarchyTypeId="8" otherProp = "1"/>
<HierarchyType hierarchyTypeId="9" otherProp = "2"/>
<HierarchyType hierarchyTypeId="10" otherProp = "3"/>
<HierarchyType hierarchyTypeId="11" otherProp = "4"/>
</HierarchyTypes>')

set @.params = '<HierarchyTypes>
<HierarchyType hierarchyTypeId="4" />
<HierarchyType hierarchyTypeId="5" />
<HierarchyType hierarchyTypeId="11" />
</HierarchyTypes>'

SELECT vTable.Id,
xVal.c.value('@.hierarchyTypeId', 'int') as hierarchyTypeId,
xVal.c.value('@.otherProp', 'int') as otherProp,
xVal.c.query('.') as MatchingFragment

FROM @.params.nodes('/HierarchyTypes/HierarchyType') P(c),
vTest as vTable CROSS APPLY vTable.Val.nodes('/HierarchyTypes/HierarchyType') xVal(c)
WHERE xVal.c.value('@.hierarchyTypeId', 'int') = P.c.value('@.hierarchyTypeId', 'int')

If you dont want to Join the parameter fragment and the table, but just want to check that for each row it's xml column has some data in common with the parameter fragment, then you could change the query to use an EXISTS.

|||

Hi Todd - thanks again for the excellent reply. I actually want to make sure that the rows returned have data in common with the XML fragment.

Ideally, I would like to have 3 options:

1) View column has some data from XML fragement

2) View column has all data from XML fragment, but could have more.

3) View column has ONLY data from XML fragement.

I can accomplish #1 above using a SELECT DISTINCT, but I don't think that's the most optimum.

Where are some good resources to educate myself on this?

Thanks!

Phil

|||

Here is where you can find the the basics of XQuery and the T-SQL functions that support it:
http://msdn2.microsoft.com/en-us/library/ms190262.aspx

The current w3c resources:
http://www.w3.org/TR/xquery/

T-SQL Reference:
http://msdn2.microsoft.com/en-us/library/ms189826.aspx

And there are probably a number of tutorials that would discuss the differences of when to use a DISTINCT vs EXISTS vs CROSS APPLY.

query xml datatype

In SQL 2005, we've defined a column as type "xml". We'd like to query
that column so that its nodes are returned in traditional columnar
format. For instance, to get the first & last name nodes, we have code
like...
Select cast(Info.query('
declare namespace m="http://tempuri.org/MyInfo";
data(/m:info/m:firstname)') as varchar(200)) as FirstName,
cast(Info.query('
declare namespace m="http://tempuri.org/MyInfo";
data(/m:info/m:lastname)') as varchar(200)) as LastName
from MyTest
That seems like a lot of work to get the first & last name in columnar
format. Is there a better way to do this?
You can't write it a whole lot simpler.
Here's what I would write if firstname and lastname elements have open
content:
WITH XMLNAMESPACES('http://tempuri.org/MyInfo' AS m)
SELECT
Info.value('(/m:info/m:firstname/text())[1]','varchar(200)') AS
FirstName,
Info.value('(/m:info/m:lastname/text())[1]','varchar(200)') AS LastName
FROM MyTest
Note that value() method does both atomization (data()) of the resulting
XQuery sequence element (must be singleton) and mapping it to a SQL type
provided as the 2nd parameter.
If your XML column is typed and firstname and lastname elements have simple
type/content than you'd need to remove "/text()" from the above XQuery
expressions.
Note that if there could be multiple firstname/lastname elements per XML
instance and you needed each of them on a separate row you'd use nodes()
method in FROM clause.
Regards,
Eugene
This posting is provided "AS IS" with no warranties, and confers no rights.
"--Marty" <Martin.McDonald@.us.logicalis.com> wrote in message
news:1126718758.082803.55550@.g47g2000cwa.googlegro ups.com...
> In SQL 2005, we've defined a column as type "xml". We'd like to query
> that column so that its nodes are returned in traditional columnar
> format. For instance, to get the first & last name nodes, we have code
> like...
> Select cast(Info.query('
> declare namespace m="http://tempuri.org/MyInfo";
> data(/m:info/m:firstname)') as varchar(200)) as FirstName,
> cast(Info.query('
> declare namespace m="http://tempuri.org/MyInfo";
> data(/m:info/m:lastname)') as varchar(200)) as LastName
> from MyTest
> That seems like a lot of work to get the first & last name in columnar
> format. Is there a better way to do this?
>

query xml datatype

In SQL 2005, we've defined a column as type "xml". We'd like to query
that column so that its nodes are returned in traditional columnar
format. For instance, to get the first & last name nodes, we have code
like...
Select cast(Info.query('
declare namespace m="http://tempuri.org/MyInfo";
data(/m:info/m:firstname)') as varchar(200)) as FirstName,
cast(Info.query('
declare namespace m="http://tempuri.org/MyInfo";
data(/m:info/m:lastname)') as varchar(200)) as LastName
from MyTest
That seems like a lot of work to get the first & last name in columnar
format. Is there a better way to do this?You can't write it a whole lot simpler.
Here's what I would write if firstname and lastname elements have open
content:
WITH XMLNAMESPACES('http://tempuri.org/MyInfo' AS m)
SELECT
Info.value('(/m:info/m:firstname/text())[1]','varchar(200)') AS
FirstName,
Info.value('(/m:info/m:lastname/text())[1]','varchar(200)') AS LastName
FROM MyTest
Note that value() method does both atomization (data()) of the resulting
XQuery sequence element (must be singleton) and mapping it to a SQL type
provided as the 2nd parameter.
If your XML column is typed and firstname and lastname elements have simple
type/content than you'd need to remove "/text()" from the above XQuery
expressions.
Note that if there could be multiple firstname/lastname elements per XML
instance and you needed each of them on a separate row you'd use nodes()
method in FROM clause.
Regards,
Eugene
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"--Marty" <Martin.McDonald@.us.logicalis.com> wrote in message
news:1126718758.082803.55550@.g47g2000cwa.googlegroups.com...
> In SQL 2005, we've defined a column as type "xml". We'd like to query
> that column so that its nodes are returned in traditional columnar
> format. For instance, to get the first & last name nodes, we have code
> like...
> Select cast(Info.query('
> declare namespace m="http://tempuri.org/MyInfo";
> data(/m:info/m:firstname)') as varchar(200)) as FirstName,
> cast(Info.query('
> declare namespace m="http://tempuri.org/MyInfo";
> data(/m:info/m:lastname)') as varchar(200)) as LastName
> from MyTest
> That seems like a lot of work to get the first & last name in columnar
> format. Is there a better way to do this?
>

Query value in between two columns

Hi All,

I am new to Sql and I want to know if a given value lets say its 225
is within ranges defined in the table.

I have a table TblControl. Data type of both field is int.

StartRange End Range
0 100
200 300
500 600
900 950

(Sql server 2000 on window 2000)

Thanks
-MaxOn 26 Aug 2004 12:13:48 -0700, Max wrote:

>Hi All,
>I am new to Sql and I want to know if a given value lets say its 225
>is within ranges defined in the table.
>I have a table TblControl. Data type of both field is int.
>StartRange End Range
>0 100
>200 300
>500 600
>900 950
>(Sql server 2000 on window 2000)
>Thanks
>-Max

Hi Max,

You can test this with BETWEEN. Example:

SELECT StartRange, EndRange
FROM Control
WHERE 225 BETWEEN StartRange AND EndRange

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo,

It solved my problem.

-Maxsql

Query Tuning

Hi,
I have a query that selects 4 fields. One is of type Varchar(500). When I
execute the query, the response is about 9 seconds (very slow). When I
comment out the varchar field, it returns in less than 1 second.
It took me a while to figure out that it's not a missing index, i can't
figure this one out.
please advise.
rafaelHow many records are being returned?
"Rafael Chemtob" wrote:

> Hi,
> I have a query that selects 4 fields. One is of type Varchar(500). When
I
> execute the query, the response is about 9 seconds (very slow). When I
> comment out the varchar field, it returns in less than 1 second.
> It took me a while to figure out that it's not a missing index, i can't
> figure this one out.
> please advise.
> rafael
>
>|||10
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:6F062F1B-91A3-45B6-AB3E-F08DF4A37831@.microsoft.com...
> How many records are being returned?
> "Rafael Chemtob" wrote:
>
When I|||> How many records are being returned?
..And what is the average length of the data in those rows?
Thomas
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:6F062F1B-91A3-45B6-AB3E-F08DF4A37831@.microsoft.com...
> How many records are being returned?
> "Rafael Chemtob" wrote:
>|||ok, sorry for not being very detailed.
4 fields
id_rating INT
summary VARCHAR(500)
dt_rating smalldatetime
id_user INT
these are the 4 fields. The record count that's returned is 11 rows.
Hope that helps
thanks
"Thomas" <replyingroup@.anywhere.com> wrote in message
news:#45wbrTRFHA.2384@.tk2msftngp13.phx.gbl...
> ..And what is the average length of the data in those rows?
>
> Thomas
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:6F062F1B-91A3-45B6-AB3E-F08DF4A37831@.microsoft.com...
When I
>|||Is the performance discrepancy consistent? i.e., have you tested this a
number of times? How many records are in the table?,
and finally, is there an index on the table that contains all the other
three columns from the table, but not the varchar(500) column?
Also, please post the DDL for the tables, and the actual Query.
Charly
"Rafael Chemtob" wrote:

> ok, sorry for not being very detailed.
> 4 fields
> id_rating INT
> summary VARCHAR(500)
> dt_rating smalldatetime
> id_user INT
> these are the 4 fields. The record count that's returned is 11 rows.
> Hope that helps
> thanks
>
> "Thomas" <replyingroup@.anywhere.com> wrote in message
> news:#45wbrTRFHA.2384@.tk2msftngp13.phx.gbl...
> When I
>
>|||I mean, is there an index on the table which includes columns
(id_rating, dt_rating, id_user), but not Column summary ?
"Rafael Chemtob" wrote:

> ok, sorry for not being very detailed.
> 4 fields
> id_rating INT
> summary VARCHAR(500)
> dt_rating smalldatetime
> id_user INT
> these are the 4 fields. The record count that's returned is 11 rows.
> Hope that helps
> thanks
>
> "Thomas" <replyingroup@.anywhere.com> wrote in message
> news:#45wbrTRFHA.2384@.tk2msftngp13.phx.gbl...
> When I
>
>|||I query the table using id_rating (which is the PK).
and this is consistent. I comment out the varchar field and i get the
results MUCH quicker.
rafael
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:456CEB9F-6A05-464C-9EE5-93F4FF0B7DB3@.microsoft.com...
> I mean, is there an index on the table which includes columns
> (id_rating, dt_rating, id_user), but not Column summary ?
>
> "Rafael Chemtob" wrote:
>
When I
can't|||As I asked above, one possible reason for this is if there's an index that
includes the columns (id_rating, dt_rating, id_user), but NOT the summary
column. If that were the case, the query processor could use the index alon
e
for the query without Summary, but would be forced to do a table scan when
you include summary.. Is there such an index?
"Rafael Chemtob" wrote:

> I query the table using id_rating (which is the PK).
> and this is consistent. I comment out the varchar field and i get the
> results MUCH quicker.
> rafael
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:456CEB9F-6A05-464C-9EE5-93F4FF0B7DB3@.microsoft.com...
> When I
> can't
>
>|||Hi Rafael,
For performance questions like these, it is very important to post all
relevant DDL (so including indexes, constraints, etc.) and the exact
query.
So just a wild guess for now: make sure you have a clustered index on
the table. If the table does not have a clustered index, and you delete
many rows, then querying the table can become very slow.
HTH,
Gert-Jan
Rafael Chemtob wrote:
> Hi,
> I have a query that selects 4 fields. One is of type Varchar(500). When
I
> execute the query, the response is about 9 seconds (very slow). When I
> comment out the varchar field, it returns in less than 1 second.
> It took me a while to figure out that it's not a missing index, i can't
> figure this one out.
> please advise.
> rafael

query to get sql server version information?

Hi!

I'm new to sql and would appreciate it if anyone could show me the query that i need to type to get the server version information - I do not have direct access to the sql database (doing some work on an old website that runs on windows box w/ coldfusion :p)

appreciate it!

execute this:

select @.@.Version

|||hey, thanks for the response!

Could you also please let me know what's the column name that's returned? The coldfusion markup that I need to use is something like this

<cfquery name="getDBVersion" username="*" password="*" datasource="*" dbtype="ODBC">
select @.@.Version
</cfquery>

<cfoutput query="getDBVersion">
#NameOfColumn#
</cfoutput>

I tried #Version# but it spit out HTTP/1.1 :)
|||

@.@.version doesn't have a column name by default. If you need to name it for your purposes, try something like this:

Select @.@.version as x

If you have access to SQL Server Management Studio (SQL Server 2005) or Query Analyzer (SQL Server 2000) you can try out your queries with them.

Paul

|||thanks worked well

this is what i'm dealing with:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
|||Hi you can use the statement to get version information:

SELECT @.@.VERSION

Query to Get and Set Image data type Value in SQL Query

Hi

I'm using Sharepoint Services in my application.My database is SQl server 2005 My Sharepoint site using one database there is one table called docs. In this table one column is called MetaInfo and its datatype is Image.
My Question is How to write a Query to Get and Set Image data type Value.I want to execute the Query in SQL Itself. If anyone knows the answer Please let me to know.
Thanks

Regards,
Vinoth

It is not clear what you want to do exactly. But you can just use regular insert/update statements to modify image data. You need to specify the image data as a binary string. Ex:

update t
set imagecol = 0xfff3030939393910

If you have the data in another table, then you can use UPDATE with the T-SQL syntax to copy value from one table to another. Ex:

update t
set imagecol = t1.imagecol
from t1
where t1.i = t.i

Also, you can have SPs with text/ntext/image data type as parameters so you can pass these values directly from the client side and use them in statements like above. Note that you cannot manipulate the variable directly in T-SQL.

For more control on the modifications, you can use UPDATETEXT. See also PATINDEX and SUBSTRING topics in Books Online.

query to find the top 3 in each type

Hi

I need help in finding the query which will provide the following resultset from the below table..

Table :
create table product_stocks(product_id int , product_type varchar(20) , no_of_units int)

Data:
insert into product_stocks values(1,'A',30)
insert into product_stocks values(2,'A',70)
insert into product_stocks values(3,'A',60)
insert into product_stocks values(4,'A',40)
insert into product_stocks values(1,'B',90)
insert into product_stocks values(2,'B',60)
insert into product_stocks values(3,'B',70)
insert into product_stocks values(4,'B',40)
insert into product_stocks values(1,'C',40)
insert into product_stocks values(2,'C',50)
insert into product_stocks values(3,'C',80)
insert into product_stocks values(4,'C',90)

Result Set:
product_type product_id no_of_units
----- ---- -----
A 2 70
A 3 60
A 4 40
B 1 90
B 3 70
B 2 60
C 4 90
C 3 80
C 2 50

i.e The result set gives the top 3 products in each product_type based on the no_of_units.

thanksselect * from product_stocks where product_id in (select top 3 product_id from product_stocks group by product_id )order by product_type,no_of_units desc|||harshal, fortunately for you, your solution has a wee flaw

by the way, did you not notice that this was another RFH post?

:)

RFH = request for homework|||Hi harshal,

Thanks for providing the query.
It was very helpful and met my requirement.

thanks|||harshal, fortunately for you, your solution has a wee flaw

by the way, did you not notice that this was another RFH post?

:)

RFH = request for homework

yeah I thought it would be a RFH..:mad: .

can u please enlighten me on the flaw part please...|||take a look at the subquery

you are grouping on product_id and then taking the top 3 of them

the top three based on what? there's no ORDER BY!!!!|||take a look at the subquery

you are grouping on product_id and then taking the top 3 of them

the top three based on what? there's no ORDER BY!!!!

OHH!! :confused:
I m getting lazy day by day.. need to spend more time on the forums i guess..;)

thanks for pointing out..

harshal|||Hi harshal

I tested the query , but the result is not correct .
It provides the result set for the product_id 1 , 2 , 3 in each product_type and not the
top 3 in each product_type based on no_of_units

thanks|||arjun, try this --select one.product_type
, one.product_id
, one.no_of_units
from daTable as one
inner
join daTable as two
on two.product_type = one.product_type
and two.no_of_units >= one.no_of_units
group
by one.product_type
, one.product_id
, one.no_of_units
having count(*) <= 3 and be sure you can explain it when your teacher asks you how you got it|||Hi

Even if order by is used in the sub query, it will give the top 3 product_id across all the product_type
But what i need is the top 3 from each of the product_type .

thanks|||Even if order by is used in the sub query, it will give the top 3 product_id across all the product_typeno, not if it's a correlated subquery

But what i need is the top 3 from each of the product_typedid you try my query?|||Hi r937 ,

I tried your query. It returns the top 3 product_id from all product_types.
I need another help.
In the result set , the order of the result set varies for each product_type.

product_type product_id no_of_units
----- ---- -----
A 2 70
A 3 60
A 4 40
B 1 90
B 2 60
B 3 70
C 2 50
C 3 80
C 4 90

How to modify this so that the no_of_units for each product_type is in the descending order.

thanks.|||I tried your query. It returns the top 3 product_id from all product_types.you could not possibly have tried it

here is what it produces:A 2 70
A 3 60
A 4 40
B 1 90
B 2 60
B 3 70
C 2 50
C 3 80
C 4 90this is exactly what you asked for|||Hi r937,

I am getting the same result as you have posted.

The Result set is here :

product_type product_id no_of_units
----- ---- -----
A 2 70
A 3 60
A 4 40
B 1 90
B 2 60
B 3 70
C 2 50
C 3 80
C 4 90

In this result set , the no_of_units for product_type 'A' is in descending order,
but the no_of_units for product_type 'B' and 'C' is not in descending order.

What i seek is to get the no_of_units in descending order for each of the product_types.

thanks|||look up ORDER BY in your manual

:)|||look up ORDER BY in your manual

:)
Firstly you must look up ORDER By in manual as r937 said ,thats for your knowledge..
well,this time just try this to get your results..

select one.product_type
, one.product_id
, one.no_of_units
from product_stocks as one
inner
join product_stocks as two
on two.product_type = one.product_type
and two.no_of_units >= one.no_of_units

group
by one.product_type
, one.product_id
, one.no_of_units

having count(*) <= 3 order by one.product_type,one.no_of_units desc

Joydeep|||This is coming pretty close to baby-sitting. arjun, you need to become familiar with books online. If you can't find your answer there, or you don't understand something, then post a question.

Query to display fiels names and types ?

Can anyone help me with a query which will display each filed name from a
table along with the type of field and length ? I have tried to google the
question with no luck
Thanks
John Jasper
John
select ordinal_position 'Seq',
cast(column_name as varchar(40)) 'Column',
isnull(character_maximum_length, numeric_precision) 'Size',
cast(data_type as varchar(12)) 'Type'
from information_schema.columns
where table_name = 'w_works'
"John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
> Can anyone help me with a query which will display each filed name from a
> table along with the type of field and length ? I have tried to google
> the
> question with no luck
> Thanks
> John Jasper
|||Try this
DECLARE @.ID INT
SELECT @.ID = [id]
FROM [sysobjects]
WHERE [name] = 'Test'
SELECT A.[name], B.[name], A.[Length]
FROM [syscolumns] A
JOIN [systypes] B
ON A.[xtype ] = B.[xusertype]
WHERE A.[id] = @.ID
ORDER BY [colid]
Nik Marshall-Blank MCSD/MCDBA
"John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
> Can anyone help me with a query which will display each filed name from a
> table along with the type of field and length ? I have tried to google
> the
> question with no luck
> Thanks
> John Jasper
|||That did not work - did not get anything ((0 row(s) affected)
"Nik Marshall-Blank (delete fcom for my e" wrote:

> Try this
> DECLARE @.ID INT
> SELECT @.ID = [id]
> FROM [sysobjects]
> WHERE [name] = 'Test'
> SELECT A.[name], B.[name], A.[Length]
> FROM [syscolumns] A
> JOIN [systypes] B
> ON A.[xtype ] = B.[xusertype]
> WHERE A.[id] = @.ID
> ORDER BY [colid]
> --
> Nik Marshall-Blank MCSD/MCDBA
> "John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
> news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
>
>
|||Actually - that did work - I had originally used the name of the database
instead of a table - Thank-You
"Nik Marshall-Blank (delete fcom for my e" wrote:

> Try this
> DECLARE @.ID INT
> SELECT @.ID = [id]
> FROM [sysobjects]
> WHERE [name] = 'Test'
> SELECT A.[name], B.[name], A.[Length]
> FROM [syscolumns] A
> JOIN [systypes] B
> ON A.[xtype ] = B.[xusertype]
> WHERE A.[id] = @.ID
> ORDER BY [colid]
> --
> Nik Marshall-Blank MCSD/MCDBA
> "John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
> news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
>
>

Query to display fiels names and types ?

Can anyone help me with a query which will display each filed name from a
table along with the type of field and length ? I have tried to google the
question with no luck
Thanks
John JasperJohn
select ordinal_position 'Seq',
cast(column_name as varchar(40)) 'Column',
isnull(character_maximum_length, numeric_precision) 'Size',
cast(data_type as varchar(12)) 'Type'
from information_schema.columns
where table_name = 'w_works'
"John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
> Can anyone help me with a query which will display each filed name from a
> table along with the type of field and length ? I have tried to google
> the
> question with no luck
> Thanks
> John Jasper|||Try this
DECLARE @.ID INT
SELECT @.ID = [id]
FROM [sysobjects]
WHERE [name] = 'Test'
SELECT A.[name], B.[name], A.[Length]
FROM [syscolumns] A
JOIN [systypes] B
ON A.[xtype ] = B.[xusertype]
WHERE A.[id] = @.ID
ORDER BY [colid]
Nik Marshall-Blank MCSD/MCDBA
"John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
> Can anyone help me with a query which will display each filed name from a
> table along with the type of field and length ? I have tried to google
> the
> question with no luck
> Thanks
> John Jasper|||That did not work - did not get anything ((0 row(s) affected)
"Nik Marshall-Blank (delete fcom for my e" wrote:

> Try this
> DECLARE @.ID INT
> SELECT @.ID = [id]
> FROM [sysobjects]
> WHERE [name] = 'Test'
> SELECT A.[name], B.[name], A.[Length]
> FROM [syscolumns] A
> JOIN [systypes] B
> ON A.[xtype ] = B.[xusertype]
> WHERE A.[id] = @.ID
> ORDER BY [colid]
> --
> Nik Marshall-Blank MCSD/MCDBA
> "John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
> news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
>
>|||Actually - that did work - I had originally used the name of the database
instead of a table - Thank-You
"Nik Marshall-Blank (delete fcom for my e" wrote:

> Try this
> DECLARE @.ID INT
> SELECT @.ID = [id]
> FROM [sysobjects]
> WHERE [name] = 'Test'
> SELECT A.[name], B.[name], A.[Length]
> FROM [syscolumns] A
> JOIN [systypes] B
> ON A.[xtype ] = B.[xusertype]
> WHERE A.[id] = @.ID
> ORDER BY [colid]
> --
> Nik Marshall-Blank MCSD/MCDBA
> "John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
> news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
>
>

Query to display fiels names and types ?

Can anyone help me with a query which will display each filed name from a
table along with the type of field and length ? I have tried to google the
question with no luck
Thanks
John JasperJohn
select ordinal_position 'Seq',
cast(column_name as varchar(40)) 'Column',
isnull(character_maximum_length, numeric_precision) 'Size',
cast(data_type as varchar(12)) 'Type'
from information_schema.columns
where table_name = 'w_works'
"John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
> Can anyone help me with a query which will display each filed name from a
> table along with the type of field and length ? I have tried to google
> the
> question with no luck
> Thanks
> John Jasper|||Try this
DECLARE @.ID INT
SELECT @.ID = [id]
FROM [sysobjects]
WHERE [name] = 'Test'
SELECT A.[name], B.[name], A.[Length]
FROM [syscolumns] A
JOIN [systypes] B
ON A.[xtype ] = B.[xusertype]
WHERE A.[id] = @.ID
ORDER BY [colid]
--
Nik Marshall-Blank MCSD/MCDBA
"John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
> Can anyone help me with a query which will display each filed name from a
> table along with the type of field and length ? I have tried to google
> the
> question with no luck
> Thanks
> John Jasper|||That did not work - did not get anything ((0 row(s) affected)
"Nik Marshall-Blank (delete fcom for my e" wrote:
> Try this
> DECLARE @.ID INT
> SELECT @.ID = [id]
> FROM [sysobjects]
> WHERE [name] = 'Test'
> SELECT A.[name], B.[name], A.[Length]
> FROM [syscolumns] A
> JOIN [systypes] B
> ON A.[xtype ] = B.[xusertype]
> WHERE A.[id] = @.ID
> ORDER BY [colid]
> --
> Nik Marshall-Blank MCSD/MCDBA
> "John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
> news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
> > Can anyone help me with a query which will display each filed name from a
> > table along with the type of field and length ? I have tried to google
> > the
> > question with no luck
> >
> > Thanks
> >
> > John Jasper
>
>|||Actually - that did work - I had originally used the name of the database
instead of a table - Thank-You
"Nik Marshall-Blank (delete fcom for my e" wrote:
> Try this
> DECLARE @.ID INT
> SELECT @.ID = [id]
> FROM [sysobjects]
> WHERE [name] = 'Test'
> SELECT A.[name], B.[name], A.[Length]
> FROM [syscolumns] A
> JOIN [systypes] B
> ON A.[xtype ] = B.[xusertype]
> WHERE A.[id] = @.ID
> ORDER BY [colid]
> --
> Nik Marshall-Blank MCSD/MCDBA
> "John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
> news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
> > Can anyone help me with a query which will display each filed name from a
> > table along with the type of field and length ? I have tried to google
> > the
> > question with no luck
> >
> > Thanks
> >
> > John Jasper
>
>

Query text column.

Hi,
When I using the Query Analyze do the select statement from table, the
column set up with data type text always truncate at right side. Any method
gets complete data from that text data type column?
Regards!
ChenIn SQL Query Analyzer, go to:
Tools/Options/Results Tab and set the Maximum characters per column to the
desired value E.g. 8000
Let me know if it works for you...
"Chen" wrote:
> Hi,
> When I using the Query Analyze do the select statement from table, the
> column set up with data type text always truncate at right side. Any method
> gets complete data from that text data type column?
> Regards!
> Chen
>|||Yes, it's working. Thanks a lot.
"Edgardo Valdez, MCSD, MCDBA" wrote:
> In SQL Query Analyzer, go to:
> Tools/Options/Results Tab and set the Maximum characters per column to the
> desired value E.g. 8000
> Let me know if it works for you...
> "Chen" wrote:
> > Hi,
> >
> > When I using the Query Analyze do the select statement from table, the
> > column set up with data type text always truncate at right side. Any method
> > gets complete data from that text data type column?
> >
> > Regards!
> > Chen
> >|||You are welcome!
Glad to be able to answer the question.
"Chen" wrote:
> Yes, it's working. Thanks a lot.
> "Edgardo Valdez, MCSD, MCDBA" wrote:
> > In SQL Query Analyzer, go to:
> >
> > Tools/Options/Results Tab and set the Maximum characters per column to the
> > desired value E.g. 8000
> >
> > Let me know if it works for you...
> >
> > "Chen" wrote:
> >
> > > Hi,
> > >
> > > When I using the Query Analyze do the select statement from table, the
> > > column set up with data type text always truncate at right side. Any method
> > > gets complete data from that text data type column?
> > >
> > > Regards!
> > > Chen
> > >

Query text column.

Hi,
When I using the Query Analyze do the select statement from table, the
column set up with data type text always truncate at right side. Any method
gets complete data from that text data type column?
Regards!
ChenIn SQL Query Analyzer, go to:
Tools/Options/Results Tab and set the Maximum characters per column to the
desired value E.g. 8000
Let me know if it works for you...
"Chen" wrote:

> Hi,
> When I using the Query Analyze do the select statement from table, the
> column set up with data type text always truncate at right side. Any metho
d
> gets complete data from that text data type column?
> Regards!
> Chen
>|||Yes, it's working. Thanks a lot.
"Edgardo Valdez, MCSD, MCDBA" wrote:
[vbcol=seagreen]
> In SQL Query Analyzer, go to:
> Tools/Options/Results Tab and set the Maximum characters per column to the
> desired value E.g. 8000
> Let me know if it works for you...
> "Chen" wrote:
>|||You are welcome!
Glad to be able to answer the question.
"Chen" wrote:
[vbcol=seagreen]
> Yes, it's working. Thanks a lot.
> "Edgardo Valdez, MCSD, MCDBA" wrote:
>

Query text column.

Hi,
When I using the Query Analyze do the select statement from table, the
column set up with data type text always truncate at right side. Any method
gets complete data from that text data type column?
Regards!
Chen
In SQL Query Analyzer, go to:
Tools/Options/Results Tab and set the Maximum characters per column to the
desired value E.g. 8000
Let me know if it works for you...
"Chen" wrote:

> Hi,
> When I using the Query Analyze do the select statement from table, the
> column set up with data type text always truncate at right side. Any method
> gets complete data from that text data type column?
> Regards!
> Chen
>
|||Yes, it's working. Thanks a lot.
"Edgardo Valdez, MCSD, MCDBA" wrote:
[vbcol=seagreen]
> In SQL Query Analyzer, go to:
> Tools/Options/Results Tab and set the Maximum characters per column to the
> desired value E.g. 8000
> Let me know if it works for you...
> "Chen" wrote:
|||You are welcome!
Glad to be able to answer the question.
"Chen" wrote:
[vbcol=seagreen]
> Yes, it's working. Thanks a lot.
> "Edgardo Valdez, MCSD, MCDBA" wrote:

Query tables in 2 databases using JDBC.

I need to Query tables in 2 databases after getting a connection. I cannot make this piece of code to work. In db2, oracle this type of code works.

Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
String sys = jdbc:microsoft:sqlserver://192.168.0.152";
conn = DriverManager.getConnection(sys, "name", "pswd");
Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery("SELECT * FROM pubs.jobs");

rs = stmt.executeQuery("SELECT * FROM mydb.myfile");

If I run this I get invalid object pubs.jobs error.

Any ideas?
Thanks.You are missing the dbo (object owner) in the table names. You want to use something more likeClass.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
String sys = jdbc:microsoft:sqlserver://192.168.0.152";
conn = DriverManager.getConnection(sys, "name", "pswd");
Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery("SELECT * FROM pubs.dbo.jobs");

rs = stmt.executeQuery("SELECT * FROM mydb.dbo.myfile"); I think that should get you rocking and rolling nicely.

-PatP|||Thanks Patp!
Now it works.
is dbo something that has to be included? I mean is it always the owner of tables?
Sorry I am a sql server newbie and know very little about it.|||dbo is "data base owner", and they normally "own" all of the objects in a production database. You can think of different owners in SQL Server much like different schemas in Oracle.

When using "one part names", such as a table name or a procedure name by itself, you don't need to use dbo. When using anything more than one part names (including the database), you should always use the owner name even though it can be allowed to default so that master.dbo.sysdatabases and master..sysdatabases are usually the same thing. Explicitly providing dbo allows object references to compile without the need for later lookup, so it also helps performance to always explicitly provide the dbo.

-PatP|||Thanks alot Pat!