Show Multiple Order Record In Single Row

Show Multiple Order Records In Single Row
I have two tables CompanyTab and OrderTab .CompanyTab table contain one record for each client while OrderTab table contain multiple orders for clients.

I have data in both table like

CompanyTable
ID Name
1 name1
2 name2

OrderTable

OrderId CompanyTabID 
1  1
2  1
3  1
4  1

In my query I  want to show all orders in single row.

ID Name Orders
1 name1 1,2,3,4
2 name2 null

Is anybody can help on it.

Thanks
Arvind

View Replies !   View Related

---

Show Multiple Values In Single Textbox Comma Separated
 

I have a field called "Owners", and it's a child to an "Activities" table.

An Activity can have on or more owners, and what I'd like to do is some how comma separate the values that come back if there are more than one owners.

I've tried a subreport, but because the row is colored and if another field, title, expands to a second row (b/c of the length) and the subreport has just one name, then the sub-report has some different color underneath due to it being smaller in height.

I'm kinda stuck on how to do this.

Thanks!

View Replies !   View Related

---

Combine Multiple Rows Into Single SQL Record
Hello:

I have the following table. There are eight section IDs in all. I want to return a single row for each product with the various section results that I have information on.

productID SectionID statusID
10 1 0
10 2 1
10 3 2
10 4 1
10 5 3
10 6 1
11 1 0
11 2 1
11 3 2
11 7 3
11 8 3

Need to return two rows with the respective values for each section.

productID section1 section2 section3 section4 section5 section6 section7 section8
10 0 1 2 1 3 1
11 0 1 2 3 3

Any information or if you can point me in the right direction would be appreciated.

Thanks

View Replies !   View Related

---

SP Return Multiple Records For A Single Record
I have two tables
TermID, Term
1--- Abc
2--- Test
4--- Tunic

and
TermID, RelatedTermID
1 --- 2
1--- 4
2--- 4

I need to get back something like this

TermID, Term, RelatedTermsInformation
1--- test--- test,tunic#1,4


that above was my solution, get the relatedterms information and comma separate, and then put a # and get all the ids comma separate them and then put the in one field. then I can later parse it in the client

this does not seem like a very good solution ( or is it?)
If posible it would be nice to get something like this

TermID, Term, RelatedTermsInformation
1 test RelatedTermsTwoDimentionalArray

but I am not sure how this idea could be implemented using the capabilities of SQL.

my other option is have the client make one call to the database to get the terms and then lots of another calls to get the relatedTerms, but that will mean one trip to the DB for the list term, and one call for every single term found.

any ideas in how to make this better ?

View Replies !   View Related

---

How We Can Insert Multiple Query With Transaction Roll Bck For A Single Record
Hello,
I have problem for insert multiple query for insert in differenr tabels for a single record.
I have mail record for candidate and now i wants to insert candiate labour info, candidate passport detail in diff tabel like candidatLabour and candidatePassport,
i used two store procedure for it and i write code for it.and it works fine,but i think that if one SP executed and one record inserted but then some problem occure and 2nd SP not executed then...........
so plz help me
Thanks

View Replies !   View Related

---

DTS - Split Single Source Record (text) To Multiple Target (sql)
I am using DTS and VBScript in DataPump tasks in order to transfer large amounts of data from text files to an SQL database.

As the database uses a normalized schema, there is often the case of inserting multiple records in a destination table from various fields of the same record of the source text file.

For example, if the source record contains information about goods sold like date, customer, item code, item name and total amount, and does so for a maximum of 3 goods per sale (row), therefore has the structure:

[date], [custid], [code1], [name1], [amount1], [code2], [name2], [amount2], [code3], [name3], [amount3]

trying to transfer that record to a [SALES] target table (in a normalized database), we would have to split each source record as follows:

[date], [custid], [code1], [name1], [amount1]
[date], [custid], [code2], [name2], [amount2]
[date], [custid], [code3], [name3], [amount3]

What is the best way to do this using DTS?

I have tried using a datapump task and VBScript, and I guess it has to do with the DTSTransformStat_**** constants, but none of those I used seems to work

Vasilis Siatravanis,
siatravanisv@interamerican.gr , vasilliss@hotmail.com

View Replies !   View Related

---

How To Combine Multiple Rows Data Into Single Record Or String Based On A Common Field.
Hellow Folks.
Here is the Original Data in my single SQL 2005 Table:
Department:                                            Sells:
1                                                              Meat
1                                                              Rice
1                                                              Orange
2                                                              Orange
2                                                              Apple
3                                                             Pears
The Data I would like read separated by Semi-colon:
Department:                                            Sells:
1                                                             Meat;Rice;Orange
2                                                             Orange;Apple
3                                                             Pears
I would like to read my data via SP or VStudio 2005 Page . Any help will be appreciated. Thanks..
 
 

View Replies !   View Related

---

Results Produce A Single Record Based Off Of Parameters. Want To Change It So It Returns Multiple Records.
I have a query that will return one record as its results if you provide two variables: @login and @record_date.  This works great if you only want one result.  However, now what I want to do is not provide those variables and get the result set back for each login and record_date combination.  The hitch is that there are several other variables that are built off of the two that are supplied.  Here is the query:
 
DECLARE @login char(20),             /*This sets the rep for the query.*/
            @record_date datetime,            /*This is the date that we want to run this for.*/
            @RWPY decimal(18,2),            /*This is the required wins per year.*/
            @OCPW decimal(18,2),            /*This is the opportunities closed per week.*/
            @OACW decimal(18,2),            /*This is opportunities advanced to close per week.*/
            @TOC decimal(18,2),            /*This is the total number of opportunities in close.*/
            @OANW decimal(18,2),            /*This is opportunities advanced to negotiate per week.*/
            @TON decimal(18,2),            /*This is the total number of opportunities in negotiate.*/
            @OADW decimal(18,2),            /*This is the opportunities advanced to demonstrate per week*/
            @TOD decimal(18,2),            /*This is the total number of opportunities in demonstrate.*/
            @OAIW decimal(18,2),            /*This is the opportunities advanced to interview per week.*/
            @TOI decimal(18,2),            /*This is the total number of opportunities in interview.*/
            @OCW decimal(18,2),            /*This is the opportunities created per week.*/
            @TOA decimal(18,2)            /*This is the total number of opportunities in approach.*/
                                   
SET @login = 'GREP'
SET @record_date = '12/18/2007'
SET @RWPY = (SELECT ((SELECT annual_quota FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)/(SELECT target_deal FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)))
SET @OCPW = (SELECT @RWPY/weeks FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OACW = (SELECT @OCPW/cls_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOC = (SELECT @OACW*(cls_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OANW = (SELECT @OACW/neg_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TON = (SELECT @OANW*(neg_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OADW = (SELECT @OANW/dem_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOD = (SELECT @OADW*(dem_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OAIW = (SELECT @OADW/int_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOI = (SELECT @OAIW*(int_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OCW = (SELECT @OAIW/app_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOA = (SELECT @OCW*(app_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
 
SELECT loginname,
            CAST(@TOA AS decimal(18,1)) AS [Opps in Approach],
            app_time AS [Approach Average Time],
            app_perc_adv AS [Approach Perc Adv],
            CAST(@TOI AS decimal(18,1)) AS [Opps in Interview],
            int_time AS [Interview Average Time],
            int_perc_adv AS [Interview Perc Adv],
            CAST(@TOD  AS decimal(18,1)) AS [Opps in Demonstrate],
            dem_time AS [Demonstrate Average Time],
            dem_perc_adv AS [Demonstrate Perc Adv],
            CAST(@TON  AS decimal(18,1)) AS [Opps in Negotiate],
            neg_time AS [Negotiate Average Time],
            neg_perc_adv AS [Negotiate Perc Adv],
            CAST(@TOC  AS decimal(18,1)) AS [Opps In Close],
            cls_time AS [Close Average Time],
            cls_perc_adv AS [Close Perc Adv]
FROM #pipelinehist
WHERE loginname = @login AND record_date = @record_date
 
Here is some sample data to use with this.  With this sample data what I want to get back is a total of 30 records in the result set each with its data specific to the login and record_date of that returned record.
 
CREATE TABLE #pipelinehist (
            glusftboid int IDENTITY(1,1) NOT NULL,
            record_date datetime NOT NULL,
            loginname char(20) NOT NULL,
            app_new float NOT NULL,
            app_time float NOT NULL,
            app_perc_adv float NOT NULL,
            int_time float NOT NULL,
            int_perc_adv float NOT NULL,
            dem_time float NOT NULL,
            dem_perc_adv float NOT NULL,
            neg_time float NOT NULL,
            neg_perc_adv float NOT NULL,
            cls_time float NOT NULL,
            cls_perc_adv float NOT NULL,
            target_deal money NOT NULL,
            annual_quota money NOT NULL,
            weeks int NOT NULL
) ON [PRIMARY]
 
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'AREP', 56.8, 26.9, 0.57, 29.5, 0.47, 20, 0.67, 80.7, 0.53, 2.1, 0.97, 2194.93, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'BREP', 33.2, 0.5, 0.9, 7.7, 0.77, 8, 0.77, 9.2, 0.6, 7.7, 0.64, 971.1, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'CREP', 210.2, 0.3, 0.87, 6.6, 0.5, 13.7, 0.4, 16.3, 0.43, 1.5, 0.91, 461.25, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'DREP', 47.6, 5, 0.53, 33.3, 0.6, 57.5, 0.53, 50, 0.7, 1.5, 1, 2045.7, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'EREP', 75.3, 110.9, 0.47, 36, 0.5, 17.4, 0.87, 20.3, 0.6, 7.2, 0.83, 2021.74, 775000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'FREP', 17.2, 23.3, 0.73, 6.8, 0.8, 6.3, 0.93, 29.7, 0.67, 15.5, 0.83, 2218.95, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'GREP', 105.4, 67, 0.2, 32.9, 0.43, 18.5, 0.67, 8.9, 0.77, 3.5, 0.93, 1838.91, 400000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'HREP', 116.4, 118.5, 0.33, 30.9, 0.77, 46.3, 0.77, 46.3, 0.6, 0.9, 0.97, 1735.13, 1150000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'IREP', 143.3, 9, 0.77, 96, 0.17, 21.6, 0.77, 39.9, 0.43, 0.9, 0.93, 1385.43, 400000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'JREP', 179.4, 66.7, 0.7, 67.6, 0.1, 41.4, 0.6, 20.2, 0.8, 14, 0.7, 1563.76, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'KREP', 107.6, 38.2, 0.23, 47.5, 0.47, 21.3, 0.77, 9.6, 0.73, 2.1, 0.83, 2120, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'LREP', 18.6, 8.3, 0.87, 23.2, 0.57, 2.6, 0.87, 12.2, 0.67, 1, 1, 1229.02, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'MREP', 4, 46.2, 0.6, 26.7, 0.57, 8.1, 0.87, 1.7, 0.9, 1.4, 1, 1091.22, 350000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'NREP', 54, 21.6, 0.57, 1.7, 0.77, 11, 0.8, 7.4, 0.9, 49, 0.47, 3240.68, 1300000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'OREP', 37.6, 24.4, 0.57, 50.1, 0.43, 6.7, 0.87, 15.6, 0.73, 0.9, 0.97, 1163.48, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'AREP', 57.2, 32.5, 0.6, 29.5, 0.47, 20, 0.67, 85.6, 0.5, 2.1, 0.97, 2194.93, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'BREP', 33.9, 0.5, 0.93, 7.8, 0.73, 8.3, 0.77, 9.2, 0.6, 7.7, 0.64, 971.1, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'CREP', 152.1, 0, 0.87, 4.3, 0.67, 9.7, 0.47, 15.7, 0.47, 1.8, 0.85, 396.43, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'DREP', 80.5, 9.8, 0.5, 40.7, 0.57, 68.3, 0.43, 64.2, 0.57, 1.5, 1, 2045.7, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'EREP', 61, 92.1, 0.5, 31, 0.53, 16.9, 0.83, 17.7, 0.6, 7.3, 0.83, 2318.04, 775000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'FREP', 19.4, 21.1, 0.7, 5.3, 0.77, 2.2, 0.93, 33.3, 0.7, 9.7, 0.87, 1937.17, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'GREP', 81.7, 40.5, 0.3, 33, 0.37, 18.5, 0.67, 8.9, 0.77, 3.5, 0.93, 1838.91, 400000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'HREP', 128.6, 115.7, 0.3, 30.9, 0.77, 46.3, 0.77, 48.8, 0.6, 0.9, 0.97, 1728.29, 1150000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'IREP', 100.9, 3.4, 0.77, 86.2, 0.27, 18, 0.8, 54.7, 0.37, 0.9, 0.93, 1385.43, 400000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'JREP', 179.4, 66.7, 0.7, 63.5, 0.1, 41.4, 0.6, 20.2, 0.8, 14, 0.7, 1563.76, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'KREP', 285.2, 36.5, 0.1, 46, 0.43, 24.2, 0.73, 9.6, 0.73, 2.1, 0.83, 2120, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'LREP', 17.6, 7.3, 0.9, 21.5, 0.57, 1.7, 0.87, 12.2, 0.67, 1, 1, 1250.54, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'MREP', 26.7, 46.2, 0.6, 26.7, 0.57, 8.1, 0.87, 1.7, 0.9, 1.3, 1, 979.7, 350000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'NREP', 61.6, 20.8, 0.5, 1.7, 0.77, 11, 0.8, 7.4, 0.9, 49, 0.47, 3240.68, 1300000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'OREP', 31.6, 16.9, 0.63, 50.1, 0.43, 7.2, 0.87, 19.5, 0.7, 0.9, 0.97, 1303.48, 330000, 50)

View Replies !   View Related

---

How To Get Just A Single Value From An Sql Database And Show It In A Table.
I would like to get single values from a huge sql server and put it into a table. let's say 4 by 4. How do I do that?
I have a connection string with a select statement that will only return a value. But, I do not know how to put that value into a table.
Thank you.

View Replies !   View Related

---

How To Show Single Row Wise Percentage In Matrix
 

Hi,
 
I'm using Reporting Services 2005. In my report I'm binding data in a matrix by grouping 3 row fields (nested within each other) and  1 column field. Now I get the subtotals alright but I also want to get percentage at row1 only rather than grouping it with either in row or in column. I've tried so many ways but am not able to do it. Can anybody help me on this  or is there a way in SSRS 2005 to get over this.

 
Also please suggest something instantly, as I'm terribly in the need
Thanks

View Replies !   View Related

---

How Do Order The List By Date To Show For New Dates.
  <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NWHCConnectionString %>"
SelectCommand="SELECT [Title], [URL], [Date] FROM [Article] ORDER BY [Date] DESC"></asp:SqlDataSource>


<asp:Repeater id="myRepeaterUL" runat="server" DataSourceID="SqlDataSource1">
<HeaderTemplate>
<ul>
</HeaderTemplate>
<ItemTemplate>
<li><a href="<%# DataBinder.Eval(Container.DataItem, "URL") %>"><%#DataBinder.Eval(Container.DataItem, "Title")%></a><br /><%# Eval("Date") %></li>
</ItemTemplate>
<FooterTemplate>
</ul>
</FooterTemplate>
</asp:Repeater>
 This is my code above, I am trying to order them to show the four new list of news. Here is a picture, yeah its old and everybody loves pictures. see the box on the right, i want to show only four, not all of it.  

View Replies !   View Related

---

3 Results From One Field - Show Levels In Right Order
 
Hi there
 
We have a web application (database) that uses one field called Application and another called TicketType.
 
When a user fills out a ticket they can choose up to 3 levels of this field.
Eg Application, Application2, Application3
 
Eg TicketType, TicketType2, TicketType3
 
The extra two levels not being compulsory.
 
I am using sql server 2005 // Reporting Services
 
My query is as below:
SELECT     Ticket.TicketNumber, Ticket.CreatedDate, Application_2.ApplicationName AS Application, Application_1.ApplicationName AS [App 2],
                      Application.ApplicationName AS [App 3], TicketType_2.TicketTypeName AS Tickettype, TicketType_1.TicketTypeName AS [Type 2],
                      TicketType.TicketTypeName AS [Type 3], Ticket.Description, Company.CompanyName
FROM         Ticket INNER JOIN
                      TicketType AS TicketType ON Ticket.TicketTypeID = TicketType.TicketTypeID LEFT OUTER JOIN
                      TicketType AS TicketType_1 ON TicketType.ParentTicketTypeID = TicketType_1.TicketTypeID LEFT OUTER JOIN
                      TicketType AS TicketType_2 ON TicketType_1.ParentTicketTypeID = TicketType_2.TicketTypeID INNER JOIN
                      Application AS Application ON Ticket.ApplicationID = Application.ApplicationID INNER JOIN
                      Company ON Application.CompanyID = Company.CompanyID FULL OUTER JOIN
                      Application AS Application_1 ON Application.ParentApplicationID = Application_1.ApplicationID FULL OUTER JOIN
                      Application AS Application_2 ON Application_1.ParentApplicationID = Application_2.ApplicationID
WHERE     (Ticket.CreatedDate >= @StartDate)
ORDER BY Ticket.TicketNumber
 
 
 
End result looks like this:
 




Application

App 2

App 3

TicketType

Type 2

Type 3


Software

Internal Apps

proACT

 

 

SW Other


 

 

Office Issues

 

 

General


 

Application

Click Track server

 

Alert (App)

Service


 

Network

Other

 

 

Network Fault


Software

Internal Apps

Other

 

User Account

New


 

Hardware

Network

 

 

HW Fault


 

Application

Click Track server

 

Alert (App)

Disk space


 

 

Office Issues

 

 

General


 

 

proACT

 

Configuration

Deployment


Software

Server Software

SharePoint

 

SW Fault

App Failure (Function)


Software

Server Software

SharePoint

 

SW Fault

App Failure (Function)
 
 
Ultimately I would like the Application (TicketType) fields to have the Master Information in it and the other two fields populated in order as well.
 
Can someone help please.

 
Please ask if I haven't explained myself.
 
thanks
Dianne

View Replies !   View Related

---

Filter One Record, Show The Others.
Hi,

I've got a question...

sample record x
id field1 field2
1 a q
2 b x
3 a y
4 b z

I want to set a "y" filter on field2 but want to have all records with the same field1 value as result.

so, in the field2 = "y" filter, I want record 1 and 3 as result because field1 is in both records "a"

(Show me all records with the same Field1 value when field2 is y)

Is this possible and if so, how?

Kind regards,

Emiel Romein
Netherlands, the

View Replies !   View Related

---

How To Show One Record Per One Page
 

I am using reporting services to build a report to show customer's balance statements. I need each page only show one customer and related transactions and balance etc.
 
Now it shows several customers' balance in one page, how can i change it to show only one record per one page, and also one customer per one page when print it out.
 
how can i achieve that?
 
cheers

View Replies !   View Related

---

How To Merge Multiple Rows One Column Data Into A Single Row With Multiple Columns
 

Please can anyone help me for the following?
 
I want to merge multiple rows (eg. 3rows) into a single row with multip columns.
 
for eg:
data

Date           Shift  Reading
01-MAR-08     1     879.880
01-MAR-08     2     854.858
01-MAR-08     3     833.836
02-MAR-08     1     809.810
02-MAR-08     2     785.784
02-MAR-08     3     761.760
 
i want output for the above as:

Date              Shift1         Shift2         Shift3
01-MAR-08     879.880       854.858       833.836
02-MAR-08     809.810       785.784       761.760
 Please help me.

View Replies !   View Related

---

Multiple Columns With Different Values OR Single Column With Multiple Criteria?
Hi,

I have multiple columns in a Single Table and i want to search values in different columns. My table structure is

col1 (identity PK)
col2 (varchar(max))
col3 (varchar(max))

I have created a single FULLTEXT on col2 & col3.
suppose i want to search col2='engine' and col3='toyota' i write query as

SELECT

TBL.col2,TBL.col3
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col2,'engine') TBL1
ON

TBL.col1=TBL1.[key]
INNER JOIN

CONTAINSTABLE(TBL,col3,'toyota') TBL2
ON

TBL.col1=TBL2.[key]

Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.

I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information.
for e.g.;
i was thinking to concatinate both fields like
col4= ABengineBA + ABBToyotaBBA
and in search i use
SELECT

TBL.col4
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1
ON

TBL.col1=TBL1.[key]
Result = 1 row

But it don't work in following scenario
col4= ABengineBA + ABBCorola ToyotaBBA

SELECT

TBL.col4
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1
ON

TBL.col1=TBL1.[key]

Result=0 Row
Any idea how i can write second query to get result?

View Replies !   View Related

---

Record Locking: Multiple Users Accessing The Same Record
I have read several discussions about SQL 7 having built-in record locking. I am assuming that this is only during the transaction process.

I have a problem with multiple users access the same record on a SQL table. We have these users accessing the SQL data with an Access 2000 DB Project form. When one person accesses the form to pull up a record, someone doing this at the same time will get an error window that asks the user to Save/Drop changes. Is there any way to LOCK DOWN a record until a user has finished making changes to it?

View Replies !   View Related

---

XML Data To SQL Server With Multiple Orders An Multiple Order Details??
Hi all!

I'm trying to get some XML data into SQL Server but i ran into problem when inserting the data (multiple orders with multiple order details) using a single sproc. Is it possible, or do I have to do in some other way? :confused:

I simplified my example to this:
-----------------------------
--CREATE PROCEDURE sp_InsertOrders AS

DECLARE @docHandle INT, @xmlDoc VARCHAR(4000), @orderID INT

--DROP TABLE #Orders
CREATE TABLE #Orders
(
OrderId SMALLINT IDENTITY(1,1),
FkCustomerID SMALLINT NOT NULL,
OrderDate DATETIME NOT NULL
)

--DROP TABLE #OrderDetails
CREATE TABLE #OrderDetails
(
OrderDetailsId SMALLINT IDENTITY(1,1),
FkOrderID SMALLINT NOT NULL,
ProductID SMALLINT NOT NULL,
UnitPrice SMALLINT NOT NULL
)

Set @xmlDoc = '
<Orders>
<Order CustomerID="1" OrderDate="2004-04-01">
<OrderDetails ProductID="6" UnitPrice="19"/>
<OrderDetails ProductID="3" UnitPrice="11"/>
<OrderDetails ProductID="9" UnitPrice="7"/>
</Order>
<Order CustomerID="2" OrderDate="2004-04-12">
<OrderDetails ProductID="2" UnitPrice="24"/>
<OrderDetails ProductID="4" UnitPrice="13"/>
</Order>
</Orders>'

EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDoc

INSERT INTO #Orders (FkCustomerID, OrderDate)
SELECT CustomerID, OrderDate
FROM OpenXML(@docHandle, 'Orders/Order', 3)
WITH (
CustomerID INTEGER,
OrderDate DATETIME
)

SET @OrderID = @@IDENTITY;

--INSERT INTO #OrderDetails (@OrderID, ProductID, UnitPrice)
SELECT @OrderID AS OrderID, ProductID, UnitPrice
FROM OpenXML(@docHandle, 'Orders/Order/OrderDetails', 3)
WITH (
ProductID INTEGER,
UnitPrice INTEGER
)
-----------------------------

All orders are inserted first which makes the use of @@IDENTITY incorrect (it works fine if you insert a single order with multiple order details). Since it was quite some time since I last worked with SQL I am not sure if am doing it the right way... :confused: :confused: Anybody out there who knows how to solve the problem?

Cheers,
Christian

View Replies !   View Related

---

Multiple Record Insertion For Each Record From Source
 

Hi,
 
How can we insert multiple records in a OLEDB destination table for each entry from the source table.
To be more clear, for every record from source we need to insert some 'n' number of records into the destination table. this 'n' changes depending on the record.
how is this achieved.
 
thanks.
 

View Replies !   View Related

---

Multiple Databases And Multiple Exe For A Single Solution
Dear Reader,Currently Am working on a Management Information System.Need to develop some part of the solution as almost Hard Coded Details: Both Front end and Database carry default valuse...which will never change in the near future. And some parts are depending on Changing rules ....so to be developed as separate exes...and separate databse are requires so ...Changes if needed can be adopted easily...Please guide ...How to manage abobe requirement?Please feel free to write for further clarifications.SuryaPrakash Paaatel--Message posted via http://www.sqlmonster.com

View Replies !   View Related

---

A Recordset Obtained Via ADO Doesn&#39;t Show The First Record Using Data Report
Te first record of a Recordest obtained from a Command Object executing a Stored Procedure, doesn't show the first record when I asociate this to a data report.(VB6 - SQL7) (ADO 2.1)

If I execute the stored procedure directly from query analizer, I have obtained the right resultset.

Does anyone Knows what could be happening?

Thank You ...

View Replies !   View Related

---

How To Get The 2nd The 2nd Record AND DISPLAY IN SINGLE ROW ?
Can you please assist me on how to get the 2nd record in case there are3 or more records of an employee, the query below gets the MAX and MINBasicSalary. However, my MIN Basic Salary is wrong because I should getthe Basic Salary Prior to the 1st Record (DESC)in case there are 3 ormore records and not the last Basic Salary of the Last Record.How to GET the 2nd Row of Record in Case that There are 3 or morerecords IN A SINGLE ROW ???---------------------------------------------------------------------------*-----This query gets the Max and Min Basic Salary on a certain Date Range.In case there are 5 records of an employee on certain date range howcan I get the record before the Max and would reflect as my OLDBASIC,if I use TOP2 DESC it will display 2 records. I only need one recordwhich should be the Basic Salary before the 1st record on a DESC order.Please add the solution to my 2nd Select Statement which get theOLDBASIC salary Thanks ...SELECT TOP 100 PERCENT E.EmployeeNo, E.LastName, E.FirstName,E.SectionCode, E.Department, E.DateHired, E.Remarks,(SELECT TOP 1 ([BasicSalary])FROM empsalaries AS T14WHERE T14.employeeno = E.employeeno AND startdate BETWEEN @FromDate AND@ToDateORDER BY startdate DESC) AS NEWBASIC,******************************* BELOW I SHOULD ALWAYS GET THE BASICSALARY PRIOR TO THE 1ST RECORD AND IN A SINGLE ROW ???(SELECT TOP 1 ([BasicSalary]) (FROM empsalaries AS T14WHERE T14.employeeno = E.employeeno AND startdate BETWEEN @FromDate AND@ToDateORDER BY startdate ASC) AS OLDBASICFROM dbo.Employees EWHERE CONVERT(VARCHAR(10),E.DateHired, 101) BETWEEN @FromDate AND@ToDate ORDER BY E.LastName

View Replies !   View Related

---

Can't Delete Single Record? HELP!
OK,

This one is driving me nuts. I've issued a very simple statement to delete a single row from a table. It appears that when I execute it in SQL Query Analyzer the CPUTime spikes and holds one of the CPUs on the box pegged at 100%. I've let this thing run for over a day, and it's not deleting the one damn record. Any thoughts? :confused: :confused: Here's the command I'm executing:

DELETE FROM Invoices WHERE InvoiceID = 153345

Running SELECT * FROM Invoices WHERE InvoiceID = 153345 returns only a single record as it should. InvoiceID is the PK in this table. Any and all help is greatly appreciated. I've rebooted the server, but to no avail. Same thing happens after a reboot.

TIA

View Replies !   View Related

---

Single Record On Each Page
Hi All,

i have made a simple report that shows all our customer addresses

What i am trying to figure out is how do i get 1 record on 1 page
for all my records.

The page size should be 254mm x 178mm

Any ideas or clues how to achieve this ????

Kind Regards

Dave C

View Replies !   View Related

---

Single Record / Row Locking
From SQL Server Books Online, there is a topic: Concurrency Effects, and did mention the following side effects:

- Lost updates.
- Uncommitted dependency (dirty read).
- Inconsistent analysis (nonrepeatable read).
- Phantom reads.
 
Can someone please tell me which type of Isolation Levels in the Database Engine to avoid the above side effects respectively.
 
For Record Locking issue:
Example, two editors make an electronic copy of the same document. Each editor changes the copy independently and then saves the changed copy thereby overwriting the original document. The editor who saves the changed copy last overwrites the changes made by the other editor.
This problem could be avoided if one editor could not access the file until the other editor had finished and committed the transaction.
 
For the above example, is it possible to do like this way:
Editor 1: SELECT and Lock a record
Editor 2: Before SELECT the record, check for the record whether it is locked or not. If it is not lock, then the record can be selected
 
Please advise.
 
Thanks.

 
 

View Replies !   View Related

---

Stopping A Single Record From Being Deleted
 I am using a drop down list box to select values from an SQL 2000 DB.  I have put a record into the table which says"...Select Item"I used the dots so that It will apear at the top of the list and therefore display at startup.  I also have a gridview to allow users to edit and delete from the same table.I am worried that a user may accidently delete the "...Select Item" row from the table.Can anyone think of a way round this?  Is it possible with SQL Server 2000 to stop a single record from being deleted?Any help would be most appreciated.ThanksPaul     

View Replies !   View Related

---

Returning One Single Record Rather Than Multiples
HiIs it possible to return the results of a query so that instead ofhaving say 10 rows its concatenated, egMy query returns 'M' 10 times, can this be returned as 'M M M M M M MM M M'?ThanksLee

View Replies !   View Related

---

Get Value Of A Single Record Instead Of Aggregated Value With GROUP BY
How to get


Code Block

a record value instead of aggregated value with GROUP BY?

Assume that I have a PRODUCT_COMMENT table defined as below.  It logs
the multiple comments for products.  A product may have multiple
comments logged at different time.





Code Block

CREATE TABLE [dbo].[PRODUCT_COMMENT](
        [COMMENT_ID] [int] IDENTITY(1,1) NOT NULL,
        [PRODUCT_ID] [int] NOT NULL,
        [COMMENT] [nvarchar](50) NULL,
        [UPDATED_ON] [datetime] NOT NULL,
 CONSTRAINT [PK_PRODUCT_COMMENT] PRIMARY KEY CLUSTERED
(
        [COMMENT_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[PRODUCT_COMMENT]  WITH CHECK ADD  CONSTRAINT
[FK_PRODUCT_COMMENT_PRODUCT] FOREIGN KEY([PRODUCT_ID])
REFERENCES [dbo].[PRODUCT] ([PRODUCT_ID])
GO
ALTER TABLE [dbo].[PRODUCT_COMMENT] CHECK CONSTRAINT
[FK_PRODUCT_COMMENT_PRODUCT]

I would like to use the following SQL statement to get the latest
comments for all products.







Code Block

SELECT  PRODUCT_ID, COMMENT, UPDATED_ON
FROM    PRODUCT_COMMENT
GROUP BY PRODUCT_ID
HAVING  UPDATED_ON = MAX(UPDATED_ON)



But this leads to the following error:




Code Block

Column 'PRODUCT_COMMENT.UPDATED_ON' is invalid in the HAVING clause
because it is not contained in either an aggregate function or the
GROUP BY clause.



Is there a way to do that?

Thanks!

View Replies !   View Related

---

Convert Single Record To Table
Hi all!
 
I have imported a table into SQL Server from a legacy program.  Each record has a repeating sequence of similar fields. (Ex. Accnt1, Assesed1, Paid1, Accnt2, Assesed2, Paid2, etc.)  I would like to take a single record and put data from these fields into a table that has the columns Accnt, Assesed, and Paid.  I am doing this for easier use in a program I am developing in VB 2005.  Can this be done in SQL or do I need to have help from some VB code?  If it's possible, what might the SQL look like?
 
Thanks.

View Replies !   View Related

---

Want To Get The Order Number Of Each Record
I want to retrieve data from SQL server database table I also want to get the order number of each record also For example consider the following table I want to get one (1) associated with A, Two (2) associated with B Three (3) associated with E Name Mark A 10 B 9 E 10 How can I do this?
I want to retrieve data from SQL server database table
 
I  want to get the order number of each record also
 
For example   consider the following table 
 
I want to get one
 
Name          Mark
A                  10
B                   9
E                 10
 
(1) associated with A,
 
  Two (2) associated with B
Three (3) associated with E
 
 
 
 
 
How can I do this?
Please help me
 
 
 
hopefully
Sujithukvl

View Replies !   View Related

---

DTS - Destination Record Set Is Out Of Order...
Hi,

I am using DTS to transfer data from one DB to another on a separate server using the Data Pump Task. My query to return the source recordset is ordered correctly by a datetime field, however the records are inserted into the destination table incorrectly. The first record in my source recordset becomes the last in my destination table.

ie. source dataset = row1,row2,row3 but the destination table = row2,row3,row1

Does anyone know why this would be? I'd appeciate reading any ideas anyone has on this.

Thanks,

M :)

View Replies !   View Related

---

How To Retriece Single Record In Database By Using SqlDataSource???
Is me again,and now i facing problem to retrieve a single record from the database.
here is my code:
 
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click        Dim user As String        user = TextBox1.Text        Dim varpassword        Dim mydata As SqlDataSource
        mydata.SelectCommand = "Select * from tbluser where uLogin = '" & user & "'"        varpassword = mydata.SelectCommand.uPassword
    End SubEnd Class
 
but i get the error : 'uPassword' is not a member of 'String'
i wan to retrieve the password of that user,can anyone help me?
thanks

View Replies !   View Related

---

How To Read Single Record From MSSQL In VS2005?
Hi all,I've just received my VS2005 and got all excited about it! However, many concept had changed. Please help me out if you can! Any input will be good!I'm using MS-SQL Server 2000, i have a table in the database called "firsttest" and inside have two fields. First one called "ID" second one called "Message" (LOOK BELOW FOR DIAGRAM). I can connect and display query from Server Explorer without any problem. I can use the new "GridView" control without problem. However, i'd like to Response.write() and display the first record of the field "Message."How can I do this with dataset?How can I do this with sqlDataSource?Thank you all in advanced!DATABASE DIAGRAM+------------------------------------------------+|   ID   |                     Message                         |+------------------------------------------------+|    1   | "Hello world"                                         |+------------------------------------------------+|    2   | "Hi, this is 2nd message"                         |+------------------------------------------------+|    3   | "Hi, this is my 3rd message!"                    |+------------------------------------------------+

View Replies !   View Related

---

Single Statement To Delete Record Into More Tables
Hi ,

I little question for you ... is it possibile to write a SQL statement to delete records in several tables at the same time?

For example if I've two tables involved by join

DELETE <...> from Customers A
INNER JOIN CustomerProperties B ON A.CustomerID=B.CustomerID

I Must use two statement to remove records from both the tables?

Thx

View Replies !   View Related

---

Config Table - Single Record For All Packages
Is there any way to use a single record (Which had Connection String) for all my packages in the project ?
 
I don't want to have multiple connection informations for each of the packages (Based on Configuration Filter)

View Replies !   View Related

---

Concatenate Values From Same Column But Different Record In Single Row
Hi, I have a difficult case that I need to solve. I will try to be the very clear explaining my problem:


I have a sql query which brings me many records.
This records have a column in common which have the same value (COL1)

There is a second column (COL2) which has different values bewteen these records.
I need to concatenate values from the second column in records with same value in COL1. And I need only one record of the ones that have the same values. If two records have the same COL1 value, only one row should be in my result.

Let me give you an example:
COL1        COL2       
RECORD1        1-A            HHH
RECORD2        1-A            GGG
RECORD3        1-B            LLL
RECORD4        1-B            MMM
RECORD4        1-B            OOO
RECORD5        1-C            NNN

Me result should be:


COL1            COL2       
RECORD          1-A            HHHGGG
RECORD          1-B            LLLMMMOOO
RECORD          1-C            NNN

It is clear what I need? I dont know if I can solve it through sql or any function inside SSIS.
Thanks for any help you can give me.

View Replies !   View Related

---

Merged Record Order Wierdness
I'm using the Merge transformation to merge two sorted tables on four sort fields.
 
It all looks OK until several records share some of the same sort fields.
 
For example if table 1  contains   

 
Zip Code(sort field 1)   Cust Name(sf2)   Cust Number(sf3)     Record type(sf4)
1234                               Bob                     444                     A  
1234                               Bob                     555                     A
 
and table 2 contains 
 
Zip Code   Cust Name   Cust Number     Record type
1234         Bob             444                   B  
1234         Bob             555                   B
 
then the output order is
 
Zip Code   Cust Name   Cust Number     Record type
1234         Bob             444                   A  
1234         Bob             555                   A
1234         Bob             444                   B  
1234         Bob             555                   B
 
and I would have expected
 
Zip Code   Cust Name   Cust Number     Record type
1234         Bob             444                   A  
1234         Bob             444                   B

1234         Bob             555                   A
1234         Bob             555                   B
 
Both input table are explicitly sorted just prior to the merge.
 
Any ideas?
 

View Replies !   View Related

---

How To Show Multiple Items In A Textbox?
In a multi-parameters report I want to show the parameter(s) user selected in a text box. How to set this up. For example user selected A, B, C in the parameter dropdown etc in the text box I want to show A, B, C.
Thanks 

View Replies !   View Related

---

Using An Open SQLTransaction In Order To Lock A Record...
Hello,I am trying to begin a transaction is ASP.NET that acts as a temporary lock on a record so that another web user can not gain access to that row of information.  My problem is that when I begin the transaction, if the user then closes out of the browser completely, the temporary lock that is put on my table from the transaction does not go away.  Further, I have no idea if it will or if there is a time range you can specifiy to do so.What I am wondering is if anyone has ever tried this before, or does anyone have a definite answer to the following question, "If I begin a transaction, and then I do NOT rollback or committ that transaction, will it ever rollback on its own and if so where can I set this time frame?"Thanks in advance for any help.

View Replies !   View Related

---

Single Record View In SQL Server Management Studio (2005)
Hello,
In SQL Server Management Studio (2005), 'Open table' command or a SELECT query displays table rows in a grid (or text).Please tell how to view a single row at a time i.e. all only ONE row is displayed at a time (evenly arranged to cover the screen).
This feature (Single record view) is available in other database client like TOAD.
Thank You

View Replies !   View Related

---

.NET Class To Hold Single Disconnected Record? Nothing Smaller Than DataSet?
What is the most efficient standalone .NET class that can hold a single disconnected record?  The class must also retain column names, but other schema is not relevant (.NET data type is sufficient).If I understand System.Data.Common.DbDataRecord, it provides an interface on a DbDataReader, and has no storage of its own.I'm familiar with DataSet, is that the only .NET-standard class to do this? Thank you,Shannon 

View Replies !   View Related

---

How To Make The Report Show Fields In Multiple Columns?
I am writing a report in SQL server 2005 Reporting service. The report has two parts: first part shows basic information about the client; the second part lists all the softwares the client has. My question is how to make the softwares listed in two columns as shown below?

 

John Smith

Title: MSTP            Location: Main Campus            IP:127.0.0.1

Softwares:

Adobe Standard 7.0                               Access 5.0

Internet Explore 6.0                               Office XP

 

Any suggestion is appreciated.

View Replies !   View Related

---

SQLCE V3.5: Single SDF With Multiple Tables Or Multiple SDFs With Fewer Tables
Hi!  I have a general SQL CE v3.5 design question related to table/file layout.  I have an system that has multiple tables that fall into categories of data access.  The 3 categories of data access are:


1 is for configuration-related data.  There is one application that will read/write to the data, and a second application that will read the data on startup.

1 is for high-performance temporal storage of data.  The data objects are all the same type, but they are our own custom object and not just simple types.

1 is for logging where the data will be permanent - unless the configured size/recycling settings cause a resize or cleanup.  There will be one application writing alot [potentially] of data depending on log settings, and another application searching/reading sections of data.
When working with data and designing the layout, I like to approach things from a data-centric mindset, because this seems to result in a better performing system.  That said, I am thinking about using 3 individual SDF files for the above data access scenarios - as opposed to a single SDF with multiple tables.  I'm thinking this would provide better performance in SQL CE because the query engine will not have alot of different types of queries going against the same database file.  For instance, the temporal storage is basically reading/writing/deleting various amounts of data.  And, this is different from the logging, where the log can grow pretty large - definitely bigger than the default 128 MB.  So, it seems logical to manage them separately.
 
I would greatly appreciate any suggestions from the SQL CE experts with regard to my approach.  If there are any tips/tricks with respect to different data access scenarios - taking into account performance, type of data access, etc. - I would love to take a look at that.
 
Thanks in advance for any help/suggestions,
Bob

View Replies !   View Related

---

How To Show Multiple Images And Links Using Report Viewer Control ?
Hello Everyone,

 

I have a report which runs in local mode and while designing the report I have placed a single image control inside the list box as I don't know before hand how many images are there :

 

What is the way to show all the images using the one image control inside the report viewer control. ? Can be 2 or 3 or 4.....

 

Thanks for all the help.

 

Harsimrat

View Replies !   View Related

---