|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
|
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. 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 I'm lost. Please show DDL, sample data, and desired results. > newest order. However when I pull the data I will get four records as I > need > to pull all the data for this order. 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 and --dtbl_orderstatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder Select dtbl_workOrder.corhOrderNumber, dtbl_attributeList.attvId, dtbl_1612BinData.Station1,dtbl_1612BinData.Station3,dtbl_1612BinData.Station5a,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, Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > >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 > > > 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. >> Here is my code: ..<< You have just been told by one of the people who wrote this languagethat 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!!
Show quote "--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. > > >> Here is my code: ..<< > > 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. > > >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 > You have just been told by one of the people who wrote this language Get a life, you no more wrote the language than I did, you only contributed > 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. on a committee of how many - 100 so, it's just the others don't see the need to boast because they are doing quite well within industry, unlike yourself judging by some of the poor posts you have made in the past years. Do us all a favour go and get a job as a junior programmer and come back when you have some real experience of using SQL and styles and methods around it. It takes more that just writing a book based around posts you've found on these forums to be an expert, it takes years of real industry expereince - day to day programming. And while your at it, get some lessons in manners. Show quote "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1137009597.469208.62470@g14g2000cwa.googlegroups.com... >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. > >>> Here is my code: ..<< > > 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; -- Show quoteWayne 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_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.Station5a,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 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 > > > > > > 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. Show quote "Wayne Snyder" <wayne.nospam.snyder@mariner-usa.com> wrote in message dtbl_1612BinData.Station1,dtbl_1612BinData.Station3,dtbl_1612BinData.Stationnews: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: > > > 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, > > 5a,dtbl_1612BinData.Station5b, Show quote > > 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 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 > > > > > > > > > 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. Show quote "meverts" <meve***@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.Station5a,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 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 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 Show quote "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" <meve***@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.Station5a,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 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 > >> > >> > >> > > > meverts (meve***@discussions.microsoft.com) writes:
> Here are the ddl's for the three table from which my query uses. I've been looking at your posts, and I feel that I am missing some> > 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. pieces of information. Do you know that there are always four rows? Or could there be fewer? And more importantly, could there be more than four? How do you know which attvId that goes into which column? The essence of my questions is that if you can more than four attributes, then you should not make them columns, but return them as rows. That's the normal thing to do from a relational database. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx I guess to make this a little more direct is it possible to loop through a
table based on a select statement, gather results from 5 fields everytime you loop through and then join them together as a string? Show quote "meverts" 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" <meve***@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.Station5a,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 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 > > >> > > >> > > >> > > > > > > meverts wrote:
> I guess to make this a little more direct is it possible to loop through a Yes, but what you are describing is a cursor and we usually try to> table based on a select statement, gather results from 5 fields everytime you > loop through and then join them together as a string? > avoid those in SQL. Usually it would be better to let SQL return all the rows as a result set. How you then choose to display those rows isn't something that SQL has control over or needs to be concerned with. It could be displayed as a grid, as a list or in red, white and blue script - as far as SQL is concerned it's just a result set. One reason this is an important distinction is that you may one day have to support many different clients with various different presentational needs. In fact ADO provides the GetString method, which does just what you've asked. -- David Portas SQL Server MVP -- It is possible to do this in a single SQL statement, but the only ways I
know of are rediculously messy to code. The way I have done it was like this... Assume that we will have exactly 4 entires for each unique ID Create table UglyTest ( IDValue varchar(10) DetailVal varchar(50) ) The SQL would look soemthign like this. Keep in mind as you add more fields to this the code gets uglier and unmanageable. The performance can also take a big hit when you do the final sort/group. select idvalue, max(detail1), max(detail2), max(detail3), max(detail4) from ( Select idvalue, detailval as detail1, '' as detail2,'' as detail 3,'' as detail 4 from table UglyTest where detailVal = 'Desired Value 1' union all Select idvalue, '' as detail1, detailval as detail2,'' as detail 3,'' as detail 4 from table UglyTest where detailVal = 'Desired Value 2' union all Select idvalue, '' as detail1, '' as detail2,detailval as detail 3,'' as detail 4 from table UglyTest where detailVal = 'Desired Value 3' union all Select idvalue, '' as detail1, '' as detail2,'' as detail 3,detailval as detail 4 from table UglyTest where detailVal = 'Desired Value 4' ) group by idvalue You could also do an outer join to the table 4 times with detailVal = 'Desired Value #' as part fo your join criteria and select distinct values. Show quote "meverts" <meve***@discussions.microsoft.com> wrote in message dtbl_1612BinData.Station1,dtbl_1612BinData.Station3,dtbl_1612BinData.Stationnews:74D76B6A-C603-43FC-8967-C49F28A8575D@microsoft.com... > I guess to make this a little more direct is it possible to loop through a > table based on a select statement, gather results from 5 fields everytime you > loop through and then join them together as a string? > > "meverts" 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 NO T > > 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" <meve***@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, > > > > 5a,dtbl_1612BinData.Station5b, Show quote > > > > 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 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 > > > >> > > > >> > > > >> > > > > > > > > >  |
|||||||||||||||||||||||
Other interesting topics