SQL Server :: Select SubQuery / Bind Two Values Into One?
Feb 25, 2011
I have a SELECT query which grabs data from two different tables however when I run the query the CurrentCallOwner often pulls up the same name twice albeit with a different SLAFailed value. What I am trying to do is bind those two values into one. I thought that running a subquery and using a count would solve this however I all receive are a bunch ones. Do you know how I could bind those values into a single value (i.e. add them up?)
SELECT CurrentCallOwnerx, SLAFailed, COUNT(CurrentCallOwnerx) AS Expr1
FROM (SELECT BSFLBWF_1.CurrentCallOwnerx, COUNT(BSFLBWF_1.Incidentx) AS SLAFailed
FROM .SRFILE INNER JOIN
BSFLBWF AS BSFLBWF_1 ON BSFLBWF_1.Incidentx = SRFILE.SRONUMBER
WHERE (SRFILE.SR_GROUP = '38') AND (BSFLBWF_1.KPIFailx = '1') OR
(BSFLBWF_1.SchoolChargex > '0.00') OR
(BSFLBWF_1.LANMLEChargex > '0.00')
GROUP BY BSFLBWF_1.CurrentCallOwnerx
UNION
SELECT BSFWES_1.CurrentCallOwnerx, COUNT(BSFWES_1.Incidentx) AS SLAFailed
FROM SRFILE AS SRFILE_1 INNER JOIN
BSFWES AS BSFWES_1 ON BSFWES_1.Incidentx = SRFILE_1.SRONUMBER
WHERE (SRFILE_1.SR_GROUP = '38') AND (BSFWES_1.KPIFailx = '1') OR
(BSFWES_1.SchoolChargex > '0.00') OR
(BSFWES_1.LANMLEChargex > '0.00')
GROUP BY BSFWES_1.CurrentCallOwnerx) AS derivedtbl_1
GROUP BY CurrentCallOwnerx, SLAFailed
View 12 Replies
Similar Messages:
Jan 24, 2011
I have been utilising a SELECT query such as the following:
SELECT SRFILE.SR_GROUP, SRFILE.SROWNERSHIP, SRFILE.SRCONMAE, BSFLBWF_1.Incidentx,
BSFLBWF_1.Severityx, BSFLBWF_1.Openx, BSFLBWF_1.Closex, BSFLBWF_1.Closurex, BSFLBWF_1.Customerx, BSFLBWF_1.Availabilityx, BSFLBWF_1.KPIx,
CONVERT(Decimal, BSFLBWF_1.Minsx) AS Expr1, BSFLBWF_1.Unusedx, BSFLBWF_1.FirstIntx, BSFLBWF_1.RepeatIntx, BSFLBWF_1.Corex,
BSFLBWF_1.OffPeakx, BSFLBWF_1.NonCorex, BSFLBWF_1.DedTypex, CONVERT(money, BSFLBWF_1.SchoolChargex) AS Expr2, CONVERT(money,
BSFLBWF_1.LANMLEChargex) AS Expr3, BSFLBWF_1.KPIFailx, BSFLBWF_1.Overridex, BSFLBWF_1.Reasonx, BSFLBWF_1.Contactx,
SRFILE.SR_CLOCK, SRFILE.SR_BACK_REASON, CONVERT(int, BSFLBWF_1.Minsx) / CONVERT(decimal, BSFLBWF_1.FirstIntx)
* 100 AS PERCENTAGE
FROM support.SRFILE INNER JOIN
BSFLBWF AS BSFLBWF_1 ON BSFLBWF_1.Incidentx = SRFILE.SRONUMBER
WHERE (BSFLBWF_1.Closex IS NULL) AND (BSFLBWF_1.DedTypex = 'School' OR
BSFLBWF_1.DedTypex = 'LAN-MLE')
UNION ALL
The rest of the query continues as the above albeit selecting the same data from different tables and works fine.
The issue I have is that I want to Select some more data (another SELECT query) but with a different Where clause e.g. WHERE (BSFLBWF_1.KPIx NOT LIKE 'KPI%' OR
BSFLBWF_1.KPIx NOT LIKE 'Proactive%') AND (BSFLBWF_1.Availabilityx LIKE '')
The problem is that this WHERE clause returns data which the above in bold has null values for (and thus doesnt work). Im still selecting the same data its just the WHERE Clause has changed. Is it possible to run such a query? If so, can it be done via a sub query?
View 10 Replies
Feb 16, 2011
im doing an update in web page by pressing a button. At the start some of the update was from a trigger, but i decided to put the hole update statement in the web page as an updatecommand in VB. this is the error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.The statement has been terminated.and this is the code.
[Code]....
the error is in the updatecommand. And i dont know what is it.
View 1 Replies
Feb 2, 2010
I am wondering there is no one asking this question on the internet even I have used google. (May be I am using a strange way on this case)
Here is the insert statement with select statement
Insert into a_table (ref, user, pw) values ((Select max(ref)+1 from reference_table), txtUser.text, txtPw.text);
Ignore the sql injection first, this statement is incorrect. Any idea? thx.
View 6 Replies
Sep 13, 2010
I have a problem regarding this code:
select NotificationID, (select u.UserName from aspnet_Users u, notifications n where u.UserId = n.FromID) as 'UserName', [Message], DateCreated, TypeID from Notifications n, aspnet_Users u
View 2 Replies
Aug 29, 2010
[Code]....
I always received an error of: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
View 1 Replies
May 20, 2010
I have a tables with data like this
Id BookId TagId
34 113421 9
35 113421 10
36 113421 11
37 113421 1
38 113422 9
39 113422 1
40 113422 12
I need to write a query (SQL Server) which gives me data according to the tags say if I want bookIds where
tagid =9 it should return bookid 113421 and 113422 as it exists in both the books, but If I ask data for tags 9 and 10 it should return only book 113421 as that is the only book where both the tags are present.
View 3 Replies
Mar 24, 2011
SQL query:
I have a hierarchy structure like:
B reports to A
C reports to A
D reports to A
E reports to B
F reports to B
and so on......
So, when A logins he should be able to see B,C,D(who are reporting to A) and also E,F(who are actually reporting to B and not directly to A)Please help to write such sql query.
View 5 Replies
Jan 25, 2011
Using 2 tables
1) tblTitles(id, description)
2)tblGroupTitles(id, TitleID, GroupID)
How can i select all tblTitles NOT in GroupTitles for group X.
I am trying to do 2 lists
List 1
Available Titles (All from Titles NOT selected)
Selected Titles (All from Group Titles)
And allow for someone to move titles from one list to the next adding it to the Selected Titles
View 5 Replies
Feb 15, 2011
query in SQL Server Express, which worked fine, now I am trying to do it in WebMatrix using a Compact 4.0 db but it gives me an error.
SELECT *, (SELECT
count(1)
FROM Posts
WHERE CategoryI
[code]...
View 7 Replies
Apr 7, 2010
I'm working on an ASP.Net project to display information on a website from a database. I want to select the top 10 items from a news table but skip the first Item and I'm having some problem with it.
<asp:SqlDataSource ID="SqlDataSource1"
runat="server" ProviderName="System.Data.SqlClient"
ConnectionString="<%$ ConnectionStrings:ClubSiteDB %>"
[code]....
This is what I have so far but I can't find any info online about how to skip a record
View 3 Replies
Feb 3, 2011
I have stored procedure which selects records from an SQL table based on a bunch of user-input parameters.Just discovered that if a record in the table has null values in some of the columns (I haven't figured out which ones yet), then the SELECT is not returning the record (even if it satisfies all the parameters).The SELECT statement is supposed to allow for nulls, and I've been over it a bunch of times and am not sure what I'm doing wrong.Or do I need to get rid of all the null values in the SQL table, and prevent new ones from being introduced?
[Code]....
View 6 Replies
Jan 5, 2011
I have a table line (ID, LineNo, LineName) having values as :
1, 1, <blank>
2, 2, <blank>
3, 3, <blank>
ID is PK with Auto-Increment and LinNo(I am manually incrementing through ASP.Net Page while record insertion) Now i have to write a query such that it updates LineName column from line table which is blank(as shown above) with values from table linet(linenamet) having values as :
a
b
c
My trial query is :
update line set LineName = (select linenamet from linet);
It gives ERROR as : Subquery returns more than 1 row
Expected Result : After Updation it should show line table as :
1, 1, a
2, 2, b
3, 3, c
View 8 Replies
Oct 28, 2010
[Code]....
for some reason it doesnt return any values.
before i've added the inner join it worked perfectly.
i couldn't find my mistake, though i passed over it several times.
View 8 Replies
Nov 3, 2010
I'm working on an ecommerce project that is missing 5,000 product pictures.
I am trying to assign all products without pictures into a category located on a mapping table.
I know an insert can be accomplished from one table (or subquery) to another but I only need to find the product IDs and insert them with default values into the category mapping table that has all non-null columns.
The following code is the farthest I can get without help (I'm not strong in SQL), it won't work because my subquery returns many rows while the SQL statement is designed to insert one - I don't know how to handle this...
[Code]....
Any help would be appreciated from those more experienced than I. BTW... there are no known search results for this type of solution - if there is even a solution.
View 2 Replies
Oct 23, 2010
How would i make a select statement select the row with the nearest values? e.g.: I have 3 labels, labels1 2 and 3, with values of 1.2, 2 and 5.8 In my table i have 4 columns, first is the data im after, and the next 3 relate to the three labels. I want to select one row, where the values of labels match it the closest.......
View 8 Replies
Mar 7, 2011
Asp.net 3.5 linq2sql
I need to present gridView with the OrderDetails and having 1 column with product name.
How can I get select values from 2 types in 1 select?
Like from orderDetials.* and the product.name ?
[Code]....
I don't want to use a costume container like a class that contain all the above props,
Can I use the the datacontax or object[] or expression instead?
View 2 Replies
Jan 5, 2010
I am trying to select data with Linq and bind to a DDL. The custid field allows duplicates in this table, but I only want unique values in the ddl. I tried the following code:
[Code]....
The use of "Select Customer.CustID", with or without the Distinct, causes this runtime error. CustID is a valid field in the table, and it shows up in intellinsense and compiles. If I just Select Customer, the error goes away, but Distinct doesn't know to look for distinct custid's.
DataBinding: 'System.String' does not contain a property with the name 'CustID'.
View 2 Replies
Apr 8, 2010
After trying many solutions listed on the internet I am very confused now. I have a C#/SQL web application for which I am simply trying to bind an ExecuteReader command to a Dropdownlist so the user can select a value. This is a VS2008 project on an XP OS.
How it works is after the user selects a table, I use this selection as an input parameter to a method from my Datamatch.aspx.cs file. Then this Datamatch.aspx.cs file calls a method from my ADONET.cs class file. Finally this method executes a SQL procedure to return the list of columns from that table. (These are all tables in Adventureworks DB). I know that this method returns successfully the list of columns if I execute this SP in SSMS. However, I'm not sure how to tell if it works in VS or not.This should be simple. How can I do this? Here is some of my code. The T-sQL stored proc:
CREATE PROCEDURE [dbo].[getColumnNames]
@TableName VarChar(50) AS
BEGIN
SET NOCOUNT ON;
[code]...
The CreateLabels method above correctly shows me the labels. But the CreateDropDownLists method just shows me one dropdownlist with nothing in it. In other words, it is not selectable. So how can I verify that the datareader is returning all 4 columns and inspect their values? I was able to find from datareader "COLUMN_NAME" but I don't know what properties to search to verify the column names.
View 2 Replies
Jul 20, 2012
I have drop down list in my page that contain cities name i bind it from city table
city id
Paris 1
Germany 2
Italy 3
USA 4
View 1 Replies
Apr 28, 2010
binding values with dropdownlist.i have one dropdownlist name,dropdowndiagnosticcategory..i have made one function bind_category() for binding the category values.The code for binding the values with dropdownlist is as under,here
private void bind_category()
{
string s = "Data Source=LAB1-40;Initial Catalog=MNSDaignosticCenter;Integrated Security=True";
SqlConnection con = new SqlConnection(s);
con.Open();
SqlCommand cmd = new SqlCommand
[code]...
View 2 Replies
Aug 25, 2010
I'm using asp.net MVC 2.0, my controller coding is,
[code]...
i need to bind this value to the drpdown list in the aspx page.
[code]...
View 4 Replies
Jan 6, 2011
I am trying to write a stored procedure that takes out a list of blog entries from a table (tblFeedEntry) as well as the amount of comments that each blog entry has gotten (tblFeedComments) but all I can get is the total amount of comments of all blog entries together, displayed on all entries. This is how my code looks today:
[Code]....
I am guessing that the problem is that the declared variable is not picking up the id of the blog entries, instead it picks up everything. The original select and the select in the declared variable are not connected. How do I connect them?
View 3 Replies
Jun 10, 2010
i wants to display the related informations of each record of dataset. That mean, dataset having rows for A,B,C and etc..
Then i wants to display the information in same Gridview of each record(A,B,C). i wrote the below my coding, it dispaly info of the last row of dataset only. pls correct the coding.
[Code]....
View 3 Replies
Mar 31, 2014
There are multiple child nodes coming from Database in Treeview. Also, there is a DataGridview.
I want that when I select/click any of the child node from TreeView, it should show the related data(of selected child node) in DataGridview from Database on "Page Load".
My current code is below, which only binds all the data in DataGridview and Treeview from Database:
public Form1() {
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e) {
BindGrid();
FillchildNode();
[Code] .....
View 1 Replies