Array?

I have a query where I pull data based on a min function the gives me the
newest order. However when I pull the data I will get four records as I nee
d
to pull all the data for this order. What I need to know is how do I take
the data that I need assign a variable to it and only return one record.
Do I need to write a stored procedure that writes the data fields to a
variable and then bring them into my query?>I have a query where I pull data based on a min function the gives me the
> newest order. However when I pull the data I will get four records as I
> need
> to pull all the data for this order.
I'm lost. Please show DDL, sample data, and desired results.
http://www.aspfaq.com/5006|||Here is my code:
declare @.con as varchar(30)
set @.con = (Select MIN(corhOrderNumber)from dtbl_workOrder, dtbl_OrderStatus
where dtbl_OrderStatus.orderstatus is null or dtbl_OrderStatus.orderstatus =
1 and
dtbl_orderstatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder)
--Select MIN(corhOrderNumber)from dtbl_workOrder, dtbl_OrderStatus where
dtbl_OrderStatus.orderstatus is null or dtbl_OrderStatus.orderstatus = 1 an
d
--dtbl_orderstatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder
Select dtbl_workOrder.corhOrderNumber, dtbl_attributeList.attvId,
dtbl_1612BinData.Station1,dtbl_1612BinData.Station3,dtbl_1612BinData.Station
5a,dtbl_1612BinData.Station5b,
dtbl_1612BinData.Station5c, dtbl_partList.partID,
dtbl_partList.CustomerPartId, dtbl_OrderStatus.OrderStatus,
pckgPackageNumber, pkctSlotNumber
From dtbl_workOrder, dtbl_attributeList, dtbl_1612BinData, dtbl_partList,
dtbl_OrderStatus, dtbl_packagingDetails
Where @.con=dtbl_workOrder.corhOrderNumber and
dtbl_workOrder.wordReleaseOrder=dtbl_attributeList.wordReleaseOrder
and dtbl_attributeList.attvId=dtbl_1612BinData.attri and
dtbl_partList.wordReleaseOrder=dtbl_workOrder.wordReleaseOrder and
dtbl_OrderStatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder and
dtbl_workOrder.wordReleaseOrder = dtbl_packagingDetails.wordReleaseOrder
Here is my response:
corhOrderNumber attvId
Station1 Station3 Station5a
Station5b Station5c partID
CustomerPartId OrderStatus
pckgPackageNumber pkctSlotNumber
----
---- --
-- -- -- --
----
---- --
---- --
2006-0100001 CRM
1,0 0,0 2,0
44040192,0 0,0 1655009B
7L1P 7J228 BDSMGJ 1
000000001 7
2006-0100001 CURLKOA
1,0 0,0 0,0
0,0 0,0 1655009B
7L1P 7J228 BDSMGJ 1
000000001 7
2006-0100001 222MID
115,0 47185920,0 0,0
0,0 0,0 1655009B
7L1P 7J228 BDSMGJ 1
000000001 7
2006-0100001 22xMOD
5,0 0,0 0,0
0,0 0,0 1655009B
7L1P 7J228 BDSMGJ 1
000000001 7
I need to take the attvID and station information and string it out like
this...
corhOrderNumber attvId
Station1 Station3 Station5a
Station5b Station5c attvId2 Station1.2 Station2.2
Station5a.2 Station5b.2 Station5c.2
Station1.3 Station2.3 Station5a.3 Station5b.3
Station5c.3
Station1.4 Station2.4 Station5a.4 Station5b.4
Station5c.4
partID CustomerPartId
OrderStatus pckgPackageNumber
pkctSlotNumber
Basically so it only returns one record. I will then need to pass this
information as a variable to another program.
Thanks,
"Aaron Bertrand [SQL Server MVP]" wrote:

> I'm lost. Please show DDL, sample data, and desired results.
> http://www.aspfaq.com/5006
>
>|||I believe we asked for DDL, not your code. What you posted looks like
"camelCase" names that violate ISO-11179; what the heck is a "dtbl_"
prefix?
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. Your entire view of RDBMS is
wrong. You do not know that rows are not ANYTHING like a record, etc.
You have just been told by one of the people who wrote this language
that you do not know what you are doing. Think about that. Then tell
your boss, or I will. You need more help than you can get in a
Newsgroup.|||Hey Celko I really appreciate your help with this. Your the MAN!!
"--CELKO--" wrote:

> I believe we asked for DDL, not your code. What you posted looks like
> "camelCase" names that violate ISO-11179; what the heck is a "dtbl_"
> prefix?
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files. Your entire view of RDBMS is
> wrong. You do not know that rows are not ANYTHING like a record, etc.
>
> You have just been told by one of the people who wrote this language
> that you do not know what you are doing. Think about that. Then tell
> your boss, or I will. You need more help than you can get in a
> Newsgroup.
>|||If I were in the position to need to do what you ask..
If there were always a max of 4 rows, and you can uniquely identify them,
I would create a user defined function which returns a table. THe table
definition would look like your code suggests.
You could insert the first row, then update it with the other rows of data -
appending to the trailing columns.
Then return the table with a single row...
Alternately you could create a stored procedure which has a local variable
for each value... populate the values with your 4 select statements, Then
SELECT the values out of the SP as a result set.
The first option is more flexible because it yeilds a table instead of a
result set...
Good luck, and hope this helps;
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"meverts" wrote:
> Here is my code:
> declare @.con as varchar(30)
> set @.con = (Select MIN(corhOrderNumber)from dtbl_workOrder, dtbl_OrderStat
us
> where dtbl_OrderStatus.orderstatus is null or dtbl_OrderStatus.orderstatus
=
> 1 and
> dtbl_orderstatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder)
> --Select MIN(corhOrderNumber)from dtbl_workOrder, dtbl_OrderStatus where
> dtbl_OrderStatus.orderstatus is null or dtbl_OrderStatus.orderstatus = 1
and
> --dtbl_orderstatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder
>
> Select dtbl_workOrder.corhOrderNumber, dtbl_attributeList.attvId,
> dtbl_1612BinData.Station1,dtbl_1612BinData.Station3,dtbl_1612BinData.Stati
on5a,dtbl_1612BinData.Station5b,
> dtbl_1612BinData.Station5c, dtbl_partList.partID,
> dtbl_partList.CustomerPartId, dtbl_OrderStatus.OrderStatus,
> pckgPackageNumber, pkctSlotNumber
> From dtbl_workOrder, dtbl_attributeList, dtbl_1612BinData, dtbl_partList,
> dtbl_OrderStatus, dtbl_packagingDetails
> Where @.con=dtbl_workOrder.corhOrderNumber and
> dtbl_workOrder.wordReleaseOrder=dtbl_attributeList.wordReleaseOrder
> and dtbl_attributeList.attvId=dtbl_1612BinData.attri and
> dtbl_partList.wordReleaseOrder=dtbl_workOrder.wordReleaseOrder and
> dtbl_OrderStatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder and
> dtbl_workOrder.wordReleaseOrder = dtbl_packagingDetails.wordReleaseOrder
> Here is my response:
> corhOrderNumber attvId
> Station1 Station3 Station5a
> Station5b Station5c partID
> CustomerPartId OrderSta
tus
> pckgPackageNumber pkctSlotNumber
> ----
> ---- --
> -- -- -- --
-
> ----
> ---- --
> ---- --
> 2006-0100001 CRM
> 1,0 0,0 2,0
> 44040192,0 0,0 1655009B
> 7L1P 7J228 BDSMGJ 1
> 000000001 7
> 2006-0100001 CURLKOA
> 1,0 0,0 0,0
> 0,0 0,0 1655009B
> 7L1P 7J228 BDSMGJ 1
> 000000001 7
> 2006-0100001 222MID
> 115,0 47185920,0 0,0
> 0,0 0,0 1655009B
> 7L1P 7J228 BDSMGJ 1
> 000000001 7
> 2006-0100001 22xMOD
> 5,0 0,0 0,0
> 0,0 0,0 1655009B
> 7L1P 7J228 BDSMGJ 1
> 000000001 7
>
> I need to take the attvID and station information and string it out like
> this...
> corhOrderNumber attvId
> Station1 Station3 Station5a
> Station5b Station5c attvId2 Station1.2 Station2.2
> Station5a.2 Station5b.2 Station5c.2
> Station1.3 Station2.3 Station5a.3 Station5b.3
> Station5c.3
> Station1.4 Station2.4 Station5a.4 Station5b.4
> Station5c.4
> partID CustomerPartId
> OrderStatus pckgPackageNumber
> pkctSlotNumber
> Basically so it only returns one record. I will then need to pass this
> information as a variable to another program.
> Thanks,
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||>I believe we asked for DDL, not your code.
Correct, we did not get any CREATE TABLE or INSERT statements, so we have to
guess about a lot of things (or keep asking, maybe if we put sugar on top?).
However, the rest of your post is a useless tangent. Who cares if he uses
camelCase names? He still has a database problem he needs help with. Just
because his metadata, element names and table structure aren't identical to
what *you* would have created with the same task in front of you, doesn't
mean he should be ignored or told to go take a class or told that he is not
worthy of the newsgroup because he is not as smart as you.
I have been in the situation where the structure is NOT mine and I just have
to deal with it. I have also been in the situation where I've been made
responsible for parts of the project that did not align exactly with my core
competencies. And I have been in the situation where the guy that knew all
about it has been fired or left for other reasons. You should consider
giving people the benefit of the doubt instead of sh*tting on their head.
I have met you in person multiple times, I have shared lunch with you, and I
know that you can be a polite and even humble person. Why you choose to be
such a hateful, miserable know-it-all here, I have yet to figure out.
A|||No offense, but I can't really read this (particularly the results, which
may have looked good while composing your message, but certainly don't now).
Could you provide DDL (CREATE TABLE ...), sample data (INSERT ...), and a
more concise version of your desired results, so we can understand exactly
what you need? You might need to see http://www.aspfaq.com/500 again.
"meverts" <meverts@.discussions.microsoft.com> wrote in message
news:549C0EB5-1406-4433-A165-CF06CE38ABD6@.microsoft.com...
> Here is my code:
> declare @.con as varchar(30)
> set @.con = (Select MIN(corhOrderNumber)from dtbl_workOrder,
> dtbl_OrderStatus
> where dtbl_OrderStatus.orderstatus is null or dtbl_OrderStatus.orderstatus
> =
> 1 and
> dtbl_orderstatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder)
> --Select MIN(corhOrderNumber)from dtbl_workOrder, dtbl_OrderStatus where
> dtbl_OrderStatus.orderstatus is null or dtbl_OrderStatus.orderstatus = 1
> and
> --dtbl_orderstatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder
>
> Select dtbl_workOrder.corhOrderNumber, dtbl_attributeList.attvId,
> dtbl_1612BinData.Station1,dtbl_1612BinData.Station3,dtbl_1612BinData.Stati
on5a,dtbl_1612BinData.Station5b,
> dtbl_1612BinData.Station5c, dtbl_partList.partID,
> dtbl_partList.CustomerPartId, dtbl_OrderStatus.OrderStatus,
> pckgPackageNumber, pkctSlotNumber
> From dtbl_workOrder, dtbl_attributeList, dtbl_1612BinData, dtbl_partList,
> dtbl_OrderStatus, dtbl_packagingDetails
> Where @.con=dtbl_workOrder.corhOrderNumber and
> dtbl_workOrder.wordReleaseOrder=dtbl_attributeList.wordReleaseOrder
> and dtbl_attributeList.attvId=dtbl_1612BinData.attri and
> dtbl_partList.wordReleaseOrder=dtbl_workOrder.wordReleaseOrder and
> dtbl_OrderStatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder and
> dtbl_workOrder.wordReleaseOrder = dtbl_packagingDetails.wordReleaseOrder
> Here is my response:
> corhOrderNumber attvId
> Station1 Station3 Station5a
> Station5b Station5c partID
> CustomerPartId
> OrderStatus
> pckgPackageNumber pkctSlotNumber
> ----
> ---- --
> -- -- -- --
-
> ----
> ---- --
> ---- --
> 2006-0100001 CRM
> 1,0 0,0 2,0
> 44040192,0 0,0 1655009B
> 7L1P 7J228 BDSMGJ 1
> 000000001 7
> 2006-0100001 CURLKOA
> 1,0 0,0 0,0
> 0,0 0,0 1655009B
> 7L1P 7J228 BDSMGJ 1
> 000000001 7
> 2006-0100001 222MID
> 115,0 47185920,0 0,0
> 0,0 0,0 1655009B
> 7L1P 7J228 BDSMGJ 1
> 000000001 7
> 2006-0100001 22xMOD
> 5,0 0,0 0,0
> 0,0 0,0 1655009B
> 7L1P 7J228 BDSMGJ 1
> 000000001 7
>
> I need to take the attvID and station information and string it out like
> this...
> corhOrderNumber attvId
> Station1 Station3 Station5a
> Station5b Station5c attvId2 Station1.2 Station2.2
> Station5a.2 Station5b.2 Station5c.2
> Station1.3 Station2.3 Station5a.3 Station5b.3
> Station5c.3
> Station1.4 Station2.4 Station5a.4 Station5b.4
> Station5c.4
> partID CustomerPartId
> OrderStatus pckgPackageNumber
> pkctSlotNumber
> Basically so it only returns one record. I will then need to pass this
> information as a variable to another program.
> Thanks,
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Here are the ddl's for the three table from which my query uses.
Basically what I want to do is take 4 sets of data back.
I would like to figure out how to make this into one set.
CREATE TABLE [dtbl_workOrder] (
[corhOrderNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[corhVinNumber] [varchar] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[corhSequenceNumber] [int] NOT NULL ,
[prdfId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wordId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[wordDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wordBatchQty] [int] NULL ,
[wordPreScheduleKey] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[wordBatchIdSched] [int] NOT NULL ,
[wordBatchSerialSched] [int] NOT NULL ,
[wordReleaseOrder] [int] NOT NULL ,
[wordPackOrder] [int] NOT NULL ,
[wordProcessStatus] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[wordProductionStatus] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[wordBackflushStatus] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[wordPackStatus] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wordHold] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wordCreationDateTime] [datetime] NULL ,
[wordUpdatedDateTime] [datetime] NULL ,
[wordReleaseDateTime] [datetime] NULL ,
[labelID] [numeric](18, 4) NULL ,
[wolvId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wolvVersion] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wolvDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[asshId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[assuId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wcelId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrderSequence] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [PK_dtbl_workOrder] PRIMARY KEY CLUSTERED
(
[wordReleaseOrder]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dtbl_OrderStatus] (
[wordReleaseOrder] [int] NOT NULL ,
[OrderStatus] [int] NULL ,
[OrderFillDate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrderSubmitDate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_dtbl_OrderStatus] PRIMARY KEY CLUSTERED
(
[wordReleaseOrder]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dtbl_attributeList] (
[wordReleaseOrder] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[partId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[attributeType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[attnId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[attvId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[attribkey] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [PK_dtbl_attributeList] PRIMARY KEY CLUSTERED
(
[attribkey]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Currently I return the following( I know this doesn't look very good)
2006-0100001 CRM 1,0 0,0 2,0 44040192,0 0,0
1655009B 7L1P 7J228 BDSMGJ 1 000000001 7
2006-0100001 CURLKOA 1,0 0,0 0,0 0,0 0,0
1655009B 7L1P 7J228 BDSMGJ 1 000000001 7
2006-0100001 222MID 115,0 47185920,0 0,0 0,0 0,0
1655009B 7L1P 7J228 BDSMGJ 1 000000001 7
2006-0100001 22xMOD 5,0 0,0 0,0 0,0 0,0
1655009B 7L1P 7J228 BDSMGJ 1 000000001 7
The desired results would be like this
2006-0100001 CRM 1,0 0,0 2,0 44040192,0 0,0
CURLKOA 1,0 0,0 0,0 0,0 0,0 222MID 115,0
47185920,0 0,0 0,0 0,0 22xMOD 5,0 0,0 0,0
0,0 0,0 1655009B1655009B 7L1P 7J228 BDSMGJ 1 000000001 7
I hope this helps. I appreciate you help. I obviously am very new at this,
and dont' know what I am doing, but sometimes in the real world you need to
learn on the job.
Thanks
"Aaron Bertrand [SQL Server MVP]" wrote:

> No offense, but I can't really read this (particularly the results, which
> may have looked good while composing your message, but certainly don't now
).
> Could you provide DDL (CREATE TABLE ...), sample data (INSERT ...), and a
> more concise version of your desired results, so we can understand exactly
> what you need? You might need to see http://www.aspfaq.com/500 again.
>
>
> "meverts" <meverts@.discussions.microsoft.com> wrote in message
> news:549C0EB5-1406-4433-A165-CF06CE38ABD6@.microsoft.com...
>
>|||In my experience, displaying multiple rows of data as one usually turns out
to be more trouble than it is worth. I reccomend looking for another way to
present the data, or possibly handling it in your app instead of in the
database. I've been forced to do what you are discussing in the past, and
have found ugly ways to do it, but I dont recomend it. The biggest problem
is that the number of rows which need to be consolidated has a tendency to
change over time, which means you are constantly updating your code to
match.
Revisit the reasons for this aproach and see if there is another way to
display this data that will fit your business need.
That said, if you absolutely have to do it this way, populating a temp table
in a stored procedure is better than any of the solutions I have used in the
past. It shouldnt be too much trouble to loop through the results and
insert/update the table as needed, then select from the table when you are
done.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:998EFAD9-7CCD-470D-9183-D89F8CB2FBFB@.microsoft.com...
> If I were in the position to need to do what you ask..
> If there were always a max of 4 rows, and you can uniquely identify them,
> I would create a user defined function which returns a table. THe table
> definition would look like your code suggests.
> You could insert the first row, then update it with the other rows of
data -
> appending to the trailing columns.
> Then return the table with a single row...
> Alternately you could create a stored procedure which has a local variable
> for each value... populate the values with your 4 select statements, Then
> SELECT the values out of the SP as a result set.
> The first option is more flexible because it yeilds a table instead of a
> result set...
> Good luck, and hope this helps;
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "meverts" wrote:
>
dtbl_OrderStatus
dtbl_OrderStatus.orderstatus =
and
dtbl_1612BinData.Station1,dtbl_1612BinData.Station3,dtbl_1612BinData.Station
5a,dtbl_1612BinData.Station5b,
dtbl_partList,
OrderStatus
> -- -- -- --
-
Station2.2
the
as I