Assistance building a query...

I am trying to generate some datasets with some queries...

With a given series information, it should return PART_NOs that has STD
= 1 and a unique price at that particular 'START', and keeping the
'TYPE' in consideration...

DB examples below:

Main DB

IDPART_NOSERIESSTD
1A-1A1
2A-2A1
3A-3A1
4D-1D1
5D-2D0

Price DB

IDPART_IDTYPESTARTPRICE
501X100050
511X1000040
521Y100060
531Y1000050
542X100050
552X1000040
562Y100060
572Y1000050
582X100090

etc.

main.ID and Price.PART_ID are paired together.

So in an example case, lets say I am querying for SERIES A, with TYPE
X. A table should be outputted something like

PART_NO
A-1100050
A-11000040
A-3100090

Note how it skipped printing A2 because the price is the same as A1.

I'm really looking for the SQL code here... I can't get it to filter on
distinct price.

SELECT MAIN.PART_NO, PRICING.START, PRICING.PRICE
FROM MAIN, PRICING
WHERE (MAIN.SERIES LIKE 'A')
AND (MAIN.STD = '1')
AND (PRICING.PRICE != '')
AND (PRICING.TYPE = 'X')
AND (MAIN.ID = PRICING.PART_ID)

I've been trying to use GROUP BY and HAVING to get what I need but it
doesn't seem to fit the bill. I guess I'm not terribly clear on how I
can use the SQL DISTINCT command...? If I try and use it in my WHERE
statement it gives me syntax errors, from what I understand you can
only have distinct in the select statement? I'm not sure how to
integrate that into the query to suit my needs.

Thanks for any help.A bit of clarification on my problem

If I just do a straight SQL distinct on my select statement, it does
what I want when you get down to it, but it completely destroys the
organization of the table. The part numbers were entered in a certain
manner and I do not believe they can be reorganized through any typical
sort. For example A-105A is higher then A-400B, but if you sorted in
Excel (for example) it would put 105 below 400

mazzarin@.gmail.com wrote:
> I am trying to generate some datasets with some queries...
> With a given series information, it should return PART_NOs that has STD
> = 1 and a unique price at that particular 'START', and keeping the
> 'TYPE' in consideration...
> DB examples below:
> Main DB
> IDPART_NOSERIESSTD
> 1A-1A1
> 2A-2A1
> 3A-3A1
> 4D-1D1
> 5D-2D0
> Price DB
> IDPART_IDTYPESTARTPRICE
> 501X100050
> 511X1000040
> 521Y100060
> 531Y1000050
> 542X100050
> 552X1000040
> 562Y100060
> 572Y1000050
> 582X100090
> etc.
> main.ID and Price.PART_ID are paired together.
>
> So in an example case, lets say I am querying for SERIES A, with TYPE
> X. A table should be outputted something like
> PART_NO
> A-1100050
> A-11000040
> A-3100090
> Note how it skipped printing A2 because the price is the same as A1.
>
> I'm really looking for the SQL code here... I can't get it to filter on
> distinct price.
> SELECT MAIN.PART_NO, PRICING.START, PRICING.PRICE
> FROM MAIN, PRICING
> WHERE (MAIN.SERIES LIKE 'A')
> AND (MAIN.STD = '1')
> AND (PRICING.PRICE != '')
> AND (PRICING.TYPE = 'X')
> AND (MAIN.ID = PRICING.PART_ID)
> I've been trying to use GROUP BY and HAVING to get what I need but it
> doesn't seem to fit the bill. I guess I'm not terribly clear on how I
> can use the SQL DISTINCT command...? If I try and use it in my WHERE
> statement it gives me syntax errors, from what I understand you can
> only have distinct in the select statement? I'm not sure how to
> integrate that into the query to suit my needs.
> Thanks for any help.|||Actually never mind, it doesn't do exactly what I want it to do... All
the prices are still duplicated, ideally I should only have at most 3
results being returned (according to the actual data being fed in)

I am beyond confused heh

I think I might have to do the filtering outside of SQL

mazzarin@.gmail.com wrote:
> A bit of clarification on my problem
> If I just do a straight SQL distinct on my select statement, it does
> what I want when you get down to it, but it completely destroys the
> organization of the table. The part numbers were entered in a certain
> manner and I do not believe they can be reorganized through any typical
> sort. For example A-105A is higher then A-400B, but if you sorted in
> Excel (for example) it would put 105 below 400
>
> mazzarin@.gmail.com wrote:
> > I am trying to generate some datasets with some queries...
> > With a given series information, it should return PART_NOs that has STD
> > = 1 and a unique price at that particular 'START', and keeping the
> > 'TYPE' in consideration...
> > DB examples below:
> > Main DB
> > IDPART_NOSERIESSTD
> > 1A-1A1
> > 2A-2A1
> > 3A-3A1
> > 4D-1D1
> > 5D-2D0
> > Price DB
> > IDPART_IDTYPESTARTPRICE
> > 501X100050
> > 511X1000040
> > 521Y100060
> > 531Y1000050
> > 542X100050
> > 552X1000040
> > 562Y100060
> > 572Y1000050
> > 582X100090
> > etc.
> > main.ID and Price.PART_ID are paired together.
> > So in an example case, lets say I am querying for SERIES A, with TYPE
> > X. A table should be outputted something like
> > PART_NO
> > A-1100050
> > A-11000040
> > A-3100090
> > Note how it skipped printing A2 because the price is the same as A1.
> > I'm really looking for the SQL code here... I can't get it to filter on
> > distinct price.
> > SELECT MAIN.PART_NO, PRICING.START, PRICING.PRICE
> > FROM MAIN, PRICING
> > WHERE (MAIN.SERIES LIKE 'A')
> > AND (MAIN.STD = '1')
> > AND (PRICING.PRICE != '')
> > AND (PRICING.TYPE = 'X')
> > AND (MAIN.ID = PRICING.PART_ID)
> > I've been trying to use GROUP BY and HAVING to get what I need but it
> > doesn't seem to fit the bill. I guess I'm not terribly clear on how I
> > can use the SQL DISTINCT command...? If I try and use it in my WHERE
> > statement it gives me syntax errors, from what I understand you can
> > only have distinct in the select statement? I'm not sure how to
> > integrate that into the query to suit my needs.
> > Thanks for any help.|||(mazzarin@.gmail.com) writes:
> I am trying to generate some datasets with some queries...
> With a given series information, it should return PART_NOs that has STD
>= 1 and a unique price at that particular 'START', and keeping the
> 'TYPE' in consideration...
> DB examples below:
> Main DB
> ID PART_NO SERIES STD
> 1 A-1 A 1
> 2 A-2 A 1
> 3 A-3 A 1
> 4 D-1 D 1
> 5 D-2 D 0
> Price DB
> ID PART_ID TYPE START PRICE
> 50 1 X 1000 50
> 51 1 X 10000 40
> 52 1 Y 1000 60
> 53 1 Y 10000 50
> 54 2 X 1000 50
> 55 2 X 10000 40
> 56 2 Y 1000 60
> 57 2 Y 10000 50
> 58 2 X 1000 90
> etc.
> main.ID and Price.PART_ID are paired together.
> So in an example case, lets say I am querying for SERIES A, with TYPE
> X. A table should be outputted something like
> PART_NO
> A-1 1000 50
> A-1 10000 40
> A-3 1000 90
> Note how it skipped printing A2 because the price is the same as A1.

But why does A-3 appear? Ir does not seem to appear in the Price DB
at all?

If there is an A-4 with the same values as A-1 would that be printed?

I'm sorry, but as you have presented the problem there are two many
unknowns. Furthermore, there is a standard recommendation that you include
in your post:

o CREATE TABLE statements for you table(s).
o INSERT statemetns with sample data.
o The desired result given the sample.

This makes it possible to easily copy and paste into Query Analyzer
to play around with the data and develop a tested solution.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I actually ended up doing multiple queries, passing info between VBA
and SQL. Its not as nice of a solution but it works. Thank you for your
advice though. I will keep it in mind next time!

Erland Sommarskog wrote:
> (mazzarin@.gmail.com) writes:
> > I am trying to generate some datasets with some queries...
> > With a given series information, it should return PART_NOs that has STD
> >= 1 and a unique price at that particular 'START', and keeping the
> > 'TYPE' in consideration...
> > DB examples below:
> > Main DB
> > ID PART_NO SERIES STD
> > 1 A-1 A 1
> > 2 A-2 A 1
> > 3 A-3 A 1
> > 4 D-1 D 1
> > 5 D-2 D 0
> > Price DB
> > ID PART_ID TYPE START PRICE
> > 50 1 X 1000 50
> > 51 1 X 10000 40
> > 52 1 Y 1000 60
> > 53 1 Y 10000 50
> > 54 2 X 1000 50
> > 55 2 X 10000 40
> > 56 2 Y 1000 60
> > 57 2 Y 10000 50
> > 58 2 X 1000 90
> > etc.
> > main.ID and Price.PART_ID are paired together.
> > So in an example case, lets say I am querying for SERIES A, with TYPE
> > X. A table should be outputted something like
> > PART_NO
> > A-1 1000 50
> > A-1 10000 40
> > A-3 1000 90
> > Note how it skipped printing A2 because the price is the same as A1.
> But why does A-3 appear? Ir does not seem to appear in the Price DB
> at all?
> If there is an A-4 with the same values as A-1 would that be printed?
> I'm sorry, but as you have presented the problem there are two many
> unknowns. Furthermore, there is a standard recommendation that you include
> in your post:
> o CREATE TABLE statements for you table(s).
> o INSERT statemetns with sample data.
> o The desired result given the sample.
> This makes it possible to easily copy and paste into Query Analyzer
> to play around with the data and develop a tested solution.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

Assigning query output to a variable

I am running a query that will return 1 field from 1 row. What is the
syntax to assign that to a variable.
Samplecode:
Declare @.DataFilePath varchar(200)
Declare @.DBName varchar(200)
Set @.DBName = 'TestDB'
@.DataFilePath = select FileName From master.dbo.sysaltfiles WHERE name =
@.DBName
Thanks!
Ron@.DataFilePath = select MAX(FileName) From master.dbo.sysaltfiles WHERE name
=
@.DBName
Or MIN. Since you are getting one row, it does not do much, but it does
tell SQL Server that only one value is coming out of the select.
RLF
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:Op0ca4p%23FHA.1032@.TK2MSFTNGP11.phx.gbl...
>I am running a query that will return 1 field from 1 row. What is the
>syntax to assign that to a variable.
> Samplecode:
> Declare @.DataFilePath varchar(200)
> Declare @.DBName varchar(200)
> Set @.DBName = 'TestDB'
> @.DataFilePath = select FileName From master.dbo.sysaltfiles WHERE name =
> @.DBName
>
>
> Thanks!
> Ron
>
>

assigning each record to one string

hello,

i would like to loop through a record set and assign each value to the
same string, (example i would like to return all of the first name in
the authors table = Authors_total.)

should i use a cursor or just a loop to do this? I have had some
trouble with the syntax in a cursor.

nicholas.gadaczDear Nicholas,

I hope following will be help full for you.
---------------
Declare @.varstr as varchar(4000) -- declreation of
set @.varstr = ''; --initializing you know the fact Null + somhting =
Null
select @.varstr = @.varstr+','+isnull(ProductName,',') from Product;
Select @.varstr;
---------------

Best of Luck :) :) :)

Saghir Taj
MCDBA
www.dbnest.com: Home of DB Professionals.

ngadacz@.ftresearch.com wrote:
> hello,
> i would like to loop through a record set and assign each value to
the
> same string, (example i would like to return all of the first name in
> the authors table = Authors_total.)
> should i use a cursor or just a loop to do this? I have had some
> trouble with the syntax in a cursor.
> nicholas.gadacz|||Why not do that client-side? SQL isn't the best place for this kind of
presentational functionality.

--
David Portas
SQL Server MVP
--|||I am still not sure how i would loop through all of the records. if a
use a cursor i get an error variable assignment is not allowed in a
cursor declaration.

The reason why I don't put this functionality is the client side is
that I have multiple client sides: asp php and soon .aspx (.net) with
changes I want to have the code centralized.

nicholas.gadacz|||(ngadacz@.ftresearch.com) writes:
> I am still not sure how i would loop through all of the records. if a
> use a cursor i get an error variable assignment is not allowed in a
> cursor declaration.

DELARE @.str varchar(8000), @.col varchar(30)

DECLARE cur INSENSTIVE CURSOR FOR
SELECT col FROM tbl ORDER BY col
OPEN cur
WHILE 1 = 1
BEGIN
FETCH cur INTO @.col
IF @.@.fetch_status <> 0
BREAK

SELECT @.str = CASE WHEN @.str IS NULL
THEN @.col
ELSE @.str + ',' + @.col
EMD
END
DEALLOCATE cur

This is one of the few things where you must use a cursor. Another poster
showed an example with a SELECT statement. However, that is not guaranteed
to work.

> The reason why I don't put this functionality is the client side is
> that I have multiple client sides: asp php and soon .aspx (.net) with
> changes I want to have the code centralized.

Beware that the above solution has a hard limit of the output string of
8000 characters.

In SQL2005 there will actually be a way to do this in a single statement,
by some fairly funny usage of the new XML stuff.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

ASP.Net with SQL Server

Hi All,

Could any one help me to know

How to return the Error Occured in Sql Server to ASP.Net Application with Customization based on the Error.

shiva kumar

If you are using an ObjectDataSource or SqlDataSource to connect to your database, then you could capture your error within the .Selected event of each control. You could also trap this error within a Try-Catch block. Either way, structure your error trapping from specific to general like so:

try{// Make my database call}catch (System.Data.SqlClient.SqlException ex){// Check the error number to be even more specificif (ex.Number == 2627){// We've just captured a primary key violation// Now do something here to customize your error display}}catch (Exception ex){// Some general error has occurred here}