SQL Query Question, Mind Puzzler?
Aug 8, 2001
Hi,
I have an interesting query problem. The result needs to be a single recordset as from a select statement, here's the prerequisites:
Tables:
tShip
tPerson
tAddress
tCertificate, has a field dtmExp (datetime)
where each Ship has one or more Persons which have one or more Addresses and each ship has one or more certificates.
Now, I need the query to return all ships, all persons and all addresses for those persons BUT ONLY the certificate with the latest expiration date (sort of like the result from a 'select top 1 from tCertificate order by dtmExp desc')
How can this be packed into one query? using inner joins on all tables will return multiple rows for each certficate for a ship, which it must not.
Any help greatly appreciated!
-. Balt
View 2 Replies
ADVERTISEMENT
Mar 23, 2000
Table Name= t1
uid subject marks
1 physics 68
1 chemistry 70
1 maths 80
1 english 75
2 physics 78
2 chemistry 56
2 maths 68
2 english 59
3 physics 54
3 chemistry 67
3 maths 77
3 english 59
query: i need sum of marks of physics,chemistry and maths
of each uid (english not included)
Thanks in Advance
Harry
View 1 Replies
View Related
Jun 15, 2006
For example, the table below, has a foreign key (ManagerId) that points to EmployeeId (primary key) of the same table.
-------Employees table--------
EmployeeID . . . . . . . . . . int
Name . . . . . . . . . . . nvarchar(50)
ManagerID . . . . . . . . . . . int
If someone gave you an ID of a manager, and asked you to get him all employee names who directly or indirectly report to this manager.
How can that be achieved?
View 6 Replies
View Related
Aug 7, 2001
Hey,
Here's a little puzzler for you all
I have a Windows NT4 server installed with MS SQL 7 and am working at the server itself. I log on and my regional settings are set to English (British).
I log off and a work mate of mine logs on and his regional settings are set to English (US).
Now, when we run a query that returns a date, the date format changes to the regional setting (as expected).
Here's the question: When no-one is logged on to the server and all the SQL Server services are running and I connect via a client machine, What regional settings does the server take when returning date formats? Also, If a stored procedure is called on the server and manipulates dates, what regional settings will it use?
Cheers
Jon
View 3 Replies
View Related
Nov 9, 2007
I need to write a query that finds a set of records from a table that meets certain conditions:
1) First, the table has to be sorted by a dollar amount column, lowest to highest
2) Then the table has a "rate" column that needs to add up to a certain value, say 1 (100%) (rate has numbers < 1 and >0, say .0024) starting with the bottom of the sorted table (highest dollar amt) working backwards until it reaches the target amount (1).
Example: After sorting the table by $, take the rate (e.g. .005) from the last record (highest $), add the rate to the second-to-last record, and keep doing a running total going backwards until the sum(rate) = 1. This then is the resultset that needs to be returned.
I know I can do this with temp tables and cartesian products, but would like to know if there is a more elegant way of doing this with the PARTITION, RANK and ORDER functions in SQL 2005, or possibly a CTE.
-Kory
View 10 Replies
View Related
Nov 8, 2007
Ok, here's a puzzler for you...
It's the classic story; I have inherited a system etc etc which stores timesheet information.
The design is, as you'd expect, "intriguing".
There are fields to store the number of hours worked as a decimal; Rate1 & Rate2
There are also fields used throughout the application which are the datetime equivalents (from the base date) of these times; Eng_Hours_Rate1 & Eng_Hours_Rate2.
The problem is, the Eng_Hours_Rate fields do not get updated when a user changes the Rate value(s).
Now because I don't know the language the FE is programmed in too well, I thought I'd stick to something I am a bit better in.
So I thought - UPDATE TRIGGER!
However, I can't get my head around the conversion calculation - below shows the closest I have got yet. When I set the Rate field to 2.5, I get the datetime value 1900-01-01 02:29:59.940 which is very close, but not quite!
Any ideas on what to do to get this working are greatly appreciated :D
Here's the ddl of the relevant fields
--Create mock table
CREATE TABLE timesheet_mod (
UniqueID int identity(1,1) primary key NOT NULL
, Eng_Hours_Rate1 datetime NULL
, Eng_Hours_Rate2 datetime NULL
, Rate1 decimal(5,2) NULL
, Rate2 decimal(5,2) NULL
)
Here's some test data
--Insert test data
SET NOCOUNT ON
INSERT INTO timesheet_mod (Eng_Hours_Rate1, Eng_Hours_Rate2, Rate1, Rate2)
SELECT '1900-01-01 01:30:00.000','1900-01-01 01:00:00.000',1.50 ,1.00 UNION ALL
SELECT '1900-01-01 03:00:00.000','1900-01-01 00:00:00.000',3.00 ,0.00 UNION ALL
SELECT '1900-01-01 04:30:00.000','1900-01-01 00:30:00.000',4.50 ,0.50
SET NOCOUNT OFF
GO
Here's my attempt at the trigger
CREATE TRIGGER rate_Update
ON timesheet_mod
FOR UPDATE
AS
UPDATE timesheet_mod
SET eng_hours_rate1 = (u.rate1 / 24)
, eng_hours_rate2 = (u.rate2 / 24)
FROM timesheet_mod t
INNER
JOIN inserted u
ON u.uniqueid = t.uniqueid
WHERE t.UniqueID IN (SELECT UniqueID FROM inserted)
GO
And here's the test bit followed by a cleanup
SELECT * FROM timesheet_mod WHERE uniqueid IN (1,2,3)
UPDATE timesheet_mod
SET rate1 = 2.5
WHERE uniqueid IN (1,2)
SELECT * FROM timesheet_mod WHERE uniqueid IN (1,2,3)
--Clean up
DROP TRIGGER rate1_Update
DROP TABLE timesheet_mod
If I've not made this clear, or you fancy insulting my methods vigorously - please feel free ;)
EDIT: In theory the users should only ever be entering timesheet entries accurate to the nearest 1/4 of an hour (or so I'm told, ha!)
View 6 Replies
View Related
Dec 4, 2007
Greetings,
I have a somewhat complex stored procedure running on 3 production servers. On 2 of the 3 servers the stored procedure executes in three seconds. The stored procedure runs to completion on the 3rd production server but requires 8 minutes to complete.
The sproc contains a single query that contains correlated subqueries that each call the same user function. If I copy the sproc's query and execute it directly from Management Studio, the query runs in 3 seconds, just like the stored procedure on the other two production servers. The sproc has one parameter.
I have updated statistics and recompiled the stored procedure and user function (on the 8 minute server). That doesn't help. I have used the WITH RECOMPILE option in the stored procedure. The query execution plan for the stored procedure is not the same execution plan prepared for the sproc's query when it is run on a standalone basis. I'm no expert with execution plan analysis but I can see significant differences between the sproc and the sproc's query.
The 3 servers are supposed to be identical in hardware and software configuration.
Can anyone provide any insight into what might be going wrong on the 8 minute server?
Thanks,
BCB
View 9 Replies
View Related
Nov 21, 2006
Hello everyone, this is my first time to these boards. I've been running all around for the last few days trying to solve a problem. So far, on 3 forums, we haven't been able to find a solution.
I am using MS SQL 2005 Workgroup. I have catalog of events on my site. Each event can have a little as 0 up to an unlimited amount of Photos attached to them(in a seperate table.) Here's the basic breakdown.
catalog
-----------
id - PK
act_name
(price and other such info)
Photos
-----------
id - PK
path_to_photo
event_id - FK(catalog.id)
What I'm trying to do is select all the events in the catalog, and order them by the number of photos they have in the photos table. The idea is to get the events that have photos at the top of the list.
here is the query that I'm using right now.
Code:
SELECTCOUNT(photos.id) AS PhotoCount, catalog.id AS item_id, catalog.company, catalog.act_name,
catalog.location, catalog.price_adult,
catalog.price_child, catalog.short_description, catalog.photo, catalog.children_allowed,
catalog.long_description, catalog.online,
catalog.act_type, event_types.act_type AS evt_type,
event_types.id AS event_id, catalog.rank, catalog.length, catalog.bullets,
photos.id
FROM catalog, event_types, photos
WHERE catalog.act_type = event_types.id
AND photos.event_id = catalog.id
GROUP BY COUNT(photos.id), catalog.id, catalog.company, catalog.act_name
ORDER BY photos.id, catalog.id, catalog.company, catalog.act_name
Which returns the following error.
Column 'catalog.location' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
No matter what I do, I always seem to get an error. I've removed the Count in the select list, and that tells me that I cannot call Aggregate functions in the Group By list.
Anyone got an idea what I'm doing wrong?
Thanks in advance,
Morgan
View 3 Replies
View Related
Aug 16, 2006
SELECT
[List1].[TITLE],
[List1].[FORENAME],
[List1].[SURNAME],
[List1].[JOB]
FROM List1
WHERE EXISTS(
SELECT [List2].[forename] FROM [List2]
WHERE [List2].[forename] = [List1].[FORENAME]
AND [List2].[surname] = [List1].[SURNAME]
AND [List2].[email] = [List1].[EMAIL]
)
I am on the right track here, aren't I?
That query will.. sort of... loop through List1, get the "current" records firstname, surname and email, and then go and see if there's any single record in List2 that contains exactly that combination of values in those fields. It's a stupid question but I'm not the brightest of sparks and BOL leaves me even more confused. I'm just worried in case it's going "well, record 34 matches the current firstname and record 96 matches the current email, so EXISTS returns true".
Assuming I've got it right, though - many of the records that I know to be duplicates have a NULL email address. And the query above seems to be automatically removing them. If there's a record in both List1 and List2 with identical firstname and surname, and both have NULL email addresses - should the query above pick it up? If not, how can I include those records?
View 9 Replies
View Related
Feb 21, 2008
I have the following bit of code that is an onclick event to save information in text boxes and list boxes. I cut out a bunch that was irrelevant to this because ALL items in the text boxes save fine. My biggest question that should lead to the answers for the rest of the code, is for lines 53-57... That stored proceedure does not appear to run. All it is set to do is and the code is DEFINITELY getting passed into it but it almost seems like it isnt and therefoe isnt doing anything. I get no errors, just nothing happens. I would of thought at the very least, it should run this proceedure and delete the information from the table even if it wouldnt save the new information based on the listboxes.
I am not sure if I am explaining this correctly, but if anyone has any thoughts, I would greatly appreciate it.
The delete proceedure that isnt actually making a changeALTER PROCEDURE [dbo].[Delete_Team_Data]
(
@Code as int
) AS
DELETE from tblSectyData2 where Code = @Code
DELETE from tblSectyData2 where SectyCode = @Code
The actual code
1
2
3 protected void SaveChanges(object sender, EventArgs e)
4 {
5
6 string selectedEmployee = "";
7 selectedEmployee = EmployeeList.SelectedValue;
8
9
10
11 string fName = "";
12 //Snipped out a bunch of code relating to the text boxes
13 string secretaryCode = "";
14
15 fName = txtFName.Text;
16 //Snipped out a bunch of code relating to the text boxes
17 secretaryCode = TeamList2.SelectedValue.ToString();
18 int selemp = Convert.ToInt32(selectedEmployee);
19
20
21
22
23 String Conn = (string)Application["Facebook"];
24 SqlConnection IntranetConnection;
25 SqlDataReader IntranetReader;
26 IntranetConnection = new SqlConnection(Conn);
27 //SaveEmpChanges works properly.
28 SqlCommand SaveEmpChanges = new SqlCommand("Exec dbo.Edit_Employee_Data '" + prefix + "','" + lName + "','" + fName + "','" + mName + "','" + pos + "','" + dept + "','" + directdial + "','" + ext + "','" + fax + "','" + hphone + "','" + cphone + "','" + partner + "','" + timekeeper + "','" + notary + "','" + practice + "','" + saddress + "','" + sphone + "','" + lnl + "','" + bar + "','" + oemail + "','" + haddresscom + "','" + haddress + "','" + hcity + "','" + hstate + "','" + hzip + "','" + school + "','" + degree + "','" + status + "','" + floor + "','" + code + "','" + email + "','" + language + "'", IntranetConnection);
29 //Delete Team does NOT work
30 SqlCommand DeleteTeam = new SqlCommand("Exec dbo.Delete_Team_Data '" + selemp + "'", IntranetConnection);
31 //GetEmployeeType does work
32 SqlCommand GetEmployeeType= new SqlCommand("Select EmpType from tblMain2 where Code = '" + selectedEmployee + "'", IntranetConnection);
33
34 IntranetConnection.Open();
35 IntranetReader = SaveEmpChanges.ExecuteReader();
36 IntranetReader.Close();
37 IntranetConnection.Close();
38
39
40
41
42 int count = 0;
43 string LinkT = "";
44 string LinkT2 = "";
45
46 string etype = "";
47
48 lselemp.Text = selectedEmployee;
49 //Basically, while the page loads fine, it does NOTHING below this line... Ive comments out sections, I have also put in a bunch of labels in to show the variables being passed, it all seems fine.
50
51
52
53 IntranetConnection.Open();
54 IntranetReader = DeleteTeam.ExecuteReader();
55 IntranetReader.Close();
56 IntranetConnection.Close();
57
58
59 IntranetConnection.Open();
60 IntranetReader = GetEmployeeType.ExecuteReader();
61 while (IntranetReader.Read())
62 {
63 etype = IntranetReader["EmpType"].ToString();
64 }
65 IntranetReader.Close();
66 IntranetConnection.Close();
67
68 int end = Convert.ToInt32(TeamList2.Items.Count);
69 string teamcode = "";
70
71 while (count < end)
72 {
73
74 teamcode = TeamList2.Items[count].Value.ToString();
75
76 if (etype == "S")
77 {
78 LinkT = "Secretary";
79 LinkT2 = "Works with";
80 }
81
82 else if (etype == "O")
83 {
84 LinkT = "Works with";
85 LinkT2 = "Secretary";
86 }
87
88
89
90 //This command does not work
91 SqlCommand saveTeam = new SqlCommand("Exec dbo.Add_Team_Data '" + selemp + "','" + teamcode + "','" + LinkT + "','" + LinkT2 + "'", IntranetConnection);
92
93
94 IntranetConnection.Open();
95 IntranetReader = saveTeam.ExecuteReader();
96 IntranetReader.Close();
97 IntranetConnection.Close();
98
99
100 count++;
101 }
102
103
104
105
106 Response.Redirect("ManageEmployeeDirectory.aspx");
107 }
108
View 6 Replies
View Related
Dec 10, 2007
I'm trying to create a "Self-Referential, Many-to-Many" relationship under the Database Diagrams section in Sql Server 2005 express and I'm having a heck of a time figuring out where to click and edit to create what I want.
The url below links to the pictorial represenation of what i'm trying to create. Any help is greatly appreciated. Thanks!
http://www.communitymx.com/content/source/A1A63/diag.gif
View 7 Replies
View Related
Oct 5, 2006
It's a dream of human beings to build machines that can think andbehave like human beings. The most important part of of such a machineis an artificial mind that can emulate the cognitive processing ofhuman mind.This book, "Next Generation Artificial Intelligence, Artificial Mind -Part One - Basic Architecture and Cognitive Structure" introduces abasic artificial mind architecture and computational model forcognitive processing. Inside the book, three important cognitiveprocess modeling components, mental objects network (MON),associative-learning mechanisms and a concept formation principle areintroduced. Based on the architecture and the computational model, onecan develop his own model of artificial mind according to his ownspecific requirements.The first edition of Artificial Mind - Part One is now available forpurchase from the author's personal web site. The price of the e-bookis USD7.00 (seven US dollars). An evaluation edition of this e-book isalso available for download from the web site.The author's personal web site:http://www.geocities.com/tomwingmak/
View 1 Replies
View Related
Jun 18, 2008
Hi all, I have the following SQLDataSource statement which connects to my Gridview:<asp:SqlDataSource ID="SqlDataSourceStandings" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT P.firstName, P.lastName, T.teamName, IsNull(P.gamesPlayed, 0) as gamesPlayed, IsNull(P.plateAppearances,0) as plateAppearances, IsNull( (P.plateAppearances - (P.sacrifices + P.walks)) ,0) as atbats, IsNull(P.hits,0) as hits, P.hits/(CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [average], (P.hits + P.walks)/(CONVERT(Decimal(5,2), IsNull(NullIF( (P.atbats + P.sacrifices + P.walks) , 0), 1))) AS [OBP], (P.hits - (P.doubles + P.triples + P.homeRuns) + (2 * P.doubles) + (3 * P.triples) + (4 * P.homeRuns)) / (CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [SLG], P.singles, P.doubles, P.triples, P.homeRuns, P.walks, P.sacrifices, P.runs, P.rbis FROM Players P INNER JOIN Teams T ON P.team = T.teamID ORDER BY P.firstName, P.lastName"></asp:SqlDataSource>There are 8 teams in the database, and somehow the average and obp results are as expected for all teams except where T.teamID = 1. This doesn't make sense to me at all! For example, I get the following results with this same query: First NameLast NameTeamGPPAABHAVGOBPSLG1B2B3BHRBBSACRRBI
BrianAustinHope83432230.7187500.7352941.15625014612201221
GabrielHelbigSafe Haven62119141.0000000.9375002.1428576404111519
MarkusJavorSafe Haven82927200.8695650.8000001.21739114501021218
RobBennettMelville83029240.8275860.8333331.55172411904102117
AdamBiesenthalSafe Haven82929210.9130430.9130431.56521712631001015
ErikGalvezMelville82625180.7200000.7307691.24000011322101015 As you can see, all teams except for Safe Haven's have the correct AVG and OBP. Since AVG is simply H/AB, it doesn't make sense for Gabriel Helbig's results to be 1.00000. Can anyone shed ANY light on this please?Thank you in advance,Markuu ***As a side note, could anyone also let me know how I could format the output so that AVG and OBP are only 3 decimal places? (ex: 0.719 for the 1st result)***
View 2 Replies
View Related
Dec 19, 2003
I'm running a query, actually its an insert that works when using the TSQL below.
However when I try to use the debugger to step through and using the exact same values as those below I get the following error:
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
Its Killing me because everything else works, but this. Can somebody help.
DECLARE @NoteID INT,-- NULL OUTPUT,
@Note_Description NVARCHAR(3000),-- = NULL,
@Date DateTime,-- = NULL OUTPUT,
@ByWho NVARCHAR(30),-- = NULL,
@FK_Action_Performed NVARCHAR(40),-- = NULL,
@FK_UserID INT,-- = NULL,
@FK_JobID INT,-- = NULL,
@Job_Date DateTime,-- = NULL,
@Start DateTime,-- = NULL,
@Finish DateTime,-- = NULL,
@BeenRead NVARCHAR(10),-- = NULL
@FK_UserIDList NVARCHAR(4000)-- = NULL
--SET @NoteID = 409 --NULL OUTPUT,
SET @Note_Description = 'Tetsing'
--SET @Date DateTime = NULL OUTPUT,
SET @ByWho = 'GeorgeAgaian'
SET @FK_Action_Performed = 'Worked hard'
SET @FK_UserID = 5
SET @FK_JobID = 29
SET @Job_Date = 28/01/03
SET @Start = '1:00:20 PM'
SET @Finish = '1:00:20 PM'
SET @BeenRead = 'UnRead'
SET @FK_UserIDList = '1,2,3'
--AS
--SET NOCOUNT ON
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRANSACTION
SET @Date = GETDATE()
-- Insert Values into the customer table
INSERT Note (Note_Description,
Date,
ByWho,
FK_Action_Performed,
FK_UserID,
FK_JobID,
Job_Date,
Start,
Finish)
SELECT --@NoteID,
@Note_Description,
@Date,
@ByWho,
@FK_Action_Performed,
@FK_UserID,
@FK_JobID,
@Job_Date,
@Start,
@Finish
-- Get the new Customer Identifier, return as OUTPUT param
SELECT @NoteID = @@IDENTITY
-- Insert new notes for all the users that the note pertains to, in this case this will be by the assigned
-- users.
IF @FK_UserIDList IS NOT NULL
EXECUTE spInsertNotesByAssignedUsers @NoteID, @FK_UserIDList
-- Insert New Address record
-- Retrieve Address reference into @AddressId
-- EXEC spInsertForUserNote
-- @FK_UserID,
--@NoteID,
-- @BeenRead
-- @Fax,
-- @PKId,
-- @AddressId OUTPUT
COMMIT TRANSACTION
--------------------------------------------------
GO
View 1 Replies
View Related
May 28, 2008
ok can someone tell me why i get two different answers for the same query. (looking for last day of month for a given date)
SELECT DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, CAST('12/20/2006' AS datetime)) + 1, 0)) AS Expr1
FROM testsupplierSCNCR
I am getting the result of 01/01/2007
but in query analizer I get the result of
12/31/2006
Why the different dates
View 4 Replies
View Related
Jan 22, 2001
Hi,
I get this error dialog when I try to open all the rows of any table from Enterprise manager..
Any help would be really appreciated..
Thanks,
-Srini.
View 1 Replies
View Related
May 24, 2007
SQL Server 2005 9.0.3161 on Win 2k3 R2
I receive the following error:
"Error: 8624, Severity: 16, State: 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."
I have traced this to an insert statement that executes as part of a stored procedure.
INSERT INTO ledger (journal__id, account__id,account_recv_info__id,amount)
VALUES (@journal_id, @acct_id, @acct_recv_id, @amount)
There is also an auto-increment column called id. There are FK contraints on all of the columns ending in "__id". I have found that if I remove the contraint on account__id the procedure will execute without error. None of the other constraints seem to make a difference. Of course I don't want to remove this key because it is important to the database integrity and should not be causing problems, but apparently it confuses the optimizer.
Also, the strange thing is that I can get the procedure to execute without error when I run it directly through management studio, but I receive the error when executing from .NET code or anything using ODBC (Access).
View 5 Replies
View Related
Mar 28, 2007
Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows.
SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches
FROM dbo.MAKES INNER JOIN
dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN
dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN
dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN
dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID
WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )
GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID
HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2
ORDER BY count(*) DESC
Here is the page source
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="	SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches 	FROM dbo.MAKES INNER JOIN 				 dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN 				 dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN 				 dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN 				 dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID 	WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) ) 	GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID 	HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2 	ORDER BY count(*) DESC ">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" />
<asp:Parameter Name="ATT_ID2" />
<asp:Parameter Name="VAL2" />
<asp:Parameter Name="ATT_ID3" />
<asp:Parameter Name="VAL3" />
<asp:Parameter Name="ATT_ID4" />
<asp:Parameter Name="VAL4" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource>
<br />
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2"
DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID">
</asp:DropDownList>
<asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br />
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False"
ReadOnly="True" SortExpression="DERIVATIVE_ID" />
<asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" />
</Columns>
</asp:GridView>
</asp:Content>
AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback).
Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage.
Please help!
(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)
View 4 Replies
View Related
Aug 5, 2014
I have the following code.
SELECT _bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc,
_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,
_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,
_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,
[code]...
However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.
View 2 Replies
View Related
Jun 15, 2007
I'm trying to find the command to open up an odbc conection inside sql2005 express. I only have ues of an odbc connector, we're conection to remedy. We will eventually be using stored procedures to extract the data we need from remedy and doing additional data crunching. I'm a foxpro programmer so once I get the correct syntax for making the odbc connector I shold be ok. Also I need a really good advanced book on sql2005. The type of book that would have my odbc answer. I've spent all morning trying to find this information and was unable to.
Thanks in advance
Daniel Buchanan.
If this was the wrong forum to post this on, please move this question to the correct one. I need this answer soon.
View 1 Replies
View Related
Jul 19, 2015
We have a issue with a MDS server that have been over us for a couple of days, the original error msg from SQL Server Engine is the one "The query processor could not produce a query plan" but the ones we get on the Excel-Addin are "Sequece contains no elements" or "The value cannot be null" T
• Using Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64) for 6months on this server without issues
• Two weeks ago we started to have 2 errors: "Sequence Contains No Elements" | "The Value Cannot Be Null"
• We are using the last version of Excel Add-in
• We try to reinstall the MDS feature
• If I backup/restore MDS database to other server it works
• We updated to SQL 2012 SP2 + CU4 but the error persisted ...
Looking at the MDSTraceLog we are routed to the this msg
SQL Error Debug Info: Number: 8624, Message: Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services., Server: bbdvsql03inst01, Proc: udpMetadataEntityGetDetailsXML, Line: 28
At line 28 udpMetadataEntityGetDetailsXML is calling udfMetadataEntityGetDetailsXML … and here is where we stopped
** Error found when try to get data from a entity using Excel add-in **
===================================
Sequence contains no elements
------------------------------
Program Location:
at Microsoft.MasterDataServices.AsyncEssentials.AsyncResultBase.EndInvoke()
at Microsoft.MasterDataServices.ExcelAddInCore.AsyncProviderBase`1.EndOperation(IAsyncResult ar)
[code]....
View 3 Replies
View Related
Jun 26, 2015
how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...
create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
insert into #temptable
SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)
[code]...
View 4 Replies
View Related
Sep 22, 2015
-- The 3rd query uses an incorrect column name in a sub-query and succeeds but rows are incorrectly qualified. This is very DANGEROUS!!!
-- The issue exists is in 2008 R2, 2012 and 2014 and is "By Design"
set nocount on
go
if object_id('tempdb.dbo.#t1') IS NOT NULL drop table #t1
if object_id('tempdb.dbo
[code]....
This succeeds when the invalid column name is a valid column name in the outer query. So in this situation the sub-query would fail when run by itself but succeed with an incorrectly applied filter when run as a sub-query. The danger here is that if a SQL Server user runs DML in a production database with such a sub-query which then the results are likely not the expected results with potentially unintended actions applied against the data. how many SQL Server users have had incorrectly applied DML or incorrect query results and don't even know it....?
View 2 Replies
View Related
Apr 30, 2007
Hello everybody,
I'm developing a report using the following structure :
declare @sql as nvarchar(4000)
declare @where as nvarchar(2000)
set @sql = 'select ....'
If <conditional1>
begin
set @where = 'some where'
end
If <conditional2>
begin
set @where = 'some where'
end
set @sql = @sql + @where
exec(@sql)
I run it in query analyser and works fine, but when I try to run in Reporting Services, Visual studio stops responding and the cpu reaches 100 %.
I realize that when I cut off the if clauses, then it works at Reporting services.
Does anybody know what is happening?
Why the query works in query analyser and doesn't work in Reporting Service ?
Thanks,
Maurício
View 2 Replies
View Related
Oct 6, 2015
SQL Server 2012 Performance Dashboard Main advices me this:
Since the application is from a vendor and I have no control over its code, how can improve this sitation?
View 3 Replies
View Related
Jul 30, 2015
For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?
I want my output to be
CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc
Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.
I want to do it using SELECT. Is it possible?
View 13 Replies
View Related
Nov 15, 2007
do i need to nest a query in RS if i want a calculated column to be compared against a multi value variable? It looks like coding WHERE calcd name in (@variable) violates SQL syntax. My select looked like
SELECT ... ,CASE enddate WHEN null then 1 else 0 END calcd name
FROM...
WHERE ... and calcd name in (@variable)
View 1 Replies
View Related
Oct 30, 2015
When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.
1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?
<ParameterList>
<ColumnReference Column="@Measure" ParameterCompiledValue="'all'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?
View 0 Replies
View Related
Mar 25, 2008
Hi all,
In the Programmability/Stored Procedure of Northwind Database in my SQL Server Management Studio Express (SSMSE), I have the following sql:
USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[SalesByCategory] Script Date: 03/25/2008 08:31:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SalesByCategory]
@CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998'
AS
IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998'
BEGIN
SELECT @OrdYear = '1998'
END
SELECT ProductName,
TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0)
FROM [Order Details] OD, Orders O, Products P, Categories C
WHERE OD.OrderID = O.OrderID
AND OD.ProductID = P.ProductID
AND P.CategoryID = C.CategoryID
AND C.CategoryName = @CategoryName
AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear
GROUP BY ProductName
ORDER BY ProductName
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
From an ADO.NET 2.0 book, I copied the code of ConnectionPoolingForm to my VB 2005 Express. The following is part of the code:
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Drawing
Imports System.Text
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Common
Imports System.Diagnostics
Public Class ConnectionPoolingForm
Dim _ProviderFactory As DbProviderFactory = SqlClientFactory.Instance
Public Sub New()
' This call is required by the Windows Form Designer.
InitializeComponent()
' Add any initialization after the InitializeComponent() call.
'Force app to be available for SqlClient perf counting
Using cn As New SqlConnection()
End Using
InitializeMinSize()
InitializePerfCounters()
End Sub
Sub InitializeMinSize()
Me.MinimumSize = Me.Size
End Sub
Dim _SelectedConnection As DbConnection = Nothing
Sub lstConnections_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles lstConnections.SelectedIndexChanged
_SelectedConnection = DirectCast(lstConnections.SelectedItem, DbConnection)
EnableOrDisableButtons(_SelectedConnection)
End Sub
Sub DisableAllButtons()
btnAdd.Enabled = False
btnOpen.Enabled = False
btnQuery.Enabled = False
btnClose.Enabled = False
btnRemove.Enabled = False
btnClearPool.Enabled = False
btnClearAllPools.Enabled = False
End Sub
Sub EnableOrDisableButtons(ByVal cn As DbConnection)
btnAdd.Enabled = True
If cn Is Nothing Then
btnOpen.Enabled = False
btnQuery.Enabled = False
btnClose.Enabled = False
btnRemove.Enabled = False
btnClearPool.Enabled = False
Else
Dim connectionState As ConnectionState = cn.State
btnOpen.Enabled = (connectionState = connectionState.Closed)
btnQuery.Enabled = (connectionState = connectionState.Open)
btnClose.Enabled = btnQuery.Enabled
btnRemove.Enabled = True
If Not (TryCast(cn, SqlConnection) Is Nothing) Then
btnClearPool.Enabled = True
End If
End If
btnClearAllPools.Enabled = True
End Sub
Sub StartWaitUI()
Me.Cursor = Cursors.WaitCursor
DisableAllButtons()
End Sub
Sub EndWaitUI()
Me.Cursor = Cursors.Default
EnableOrDisableButtons(_SelectedConnection)
End Sub
Sub SetStatus(ByVal NewStatus As String)
RefreshPerfCounters()
Me.statusStrip.Items(0).Text = NewStatus
End Sub
Sub btnConnectionString_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnConnectionString.Click
Dim strConn As String = txtConnectionString.Text
Dim bldr As DbConnectionStringBuilder = _ProviderFactory.CreateConnectionStringBuilder()
Try
bldr.ConnectionString = strConn
Catch ex As Exception
MessageBox.Show(ex.Message, "Invalid connection string for " + bldr.GetType().Name, MessageBoxButtons.OK, MessageBoxIcon.Error)
Return
End Try
Dim dlg As New ConnectionStringBuilderDialog()
If dlg.EditConnectionString(_ProviderFactory, bldr) = System.Windows.Forms.DialogResult.OK Then
txtConnectionString.Text = dlg.ConnectionString
SetStatus("Ready")
Else
SetStatus("Operation cancelled")
End If
End Sub
Sub btnAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAdd.Click
Dim blnError As Boolean = False
Dim strErrorMessage As String = ""
Dim strErrorCaption As String = "Connection attempt failed"
StartWaitUI()
Try
Dim cn As DbConnection = _ProviderFactory.CreateConnection()
cn.ConnectionString = txtConnectionString.Text
cn.Open()
lstConnections.SelectedIndex = lstConnections.Items.Add(cn)
Catch ex As Exception
blnError = True
strErrorMessage = ex.Message
End Try
EndWaitUI()
If blnError Then
SetStatus(strErrorCaption)
MessageBox.Show(strErrorMessage, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
SetStatus("Connection opened succesfully")
End If
End Sub
Sub btnOpen_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnOpen.Click
StartWaitUI()
Try
_SelectedConnection.Open()
EnableOrDisableButtons(_SelectedConnection)
SetStatus("Connection opened succesfully")
EndWaitUI()
Catch ex As Exception
EndWaitUI()
Dim strErrorCaption As String = "Connection attempt failed"
SetStatus(strErrorCaption)
MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
Sub btnQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click
Dim queryDialog As New QueryDialog()
If queryDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
Me.Cursor = Cursors.WaitCursor
DisableAllButtons()
Try
Dim cmd As DbCommand = _SelectedConnection.CreateCommand()
cmd.CommandText = queryDialog.txtQuery.Text
Using rdr As DbDataReader = cmd.ExecuteReader()
If rdr.HasRows Then
Dim resultsForm As New QueryResultsForm()
resultsForm.ShowResults(cmd.CommandText, rdr)
SetStatus(String.Format("Query returned {0} row(s)", resultsForm.RowsReturned))
Else
SetStatus(String.Format("Query affected {0} row(s)", rdr.RecordsAffected))
End If
Me.Cursor = Cursors.Default
EnableOrDisableButtons(_SelectedConnection)
End Using
Catch ex As Exception
Me.Cursor = Cursors.Default
EnableOrDisableButtons(_SelectedConnection)
Dim strErrorCaption As String = "Query attempt failed"
SetStatus(strErrorCaption)
MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
Else
SetStatus("Operation cancelled")
End If
End Sub
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
I executed the code successfully and I got a box which asked for "Enter the query string".
I typed in the following: EXEC dbo.SalesByCategory @Seafood. I got the following box: Query attempt failed. Must declare the scalar variable "@Seafood". I am learning how to enter the string for the "SQL query programed in the subQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click" (see the code statements listed above). Please help and tell me what I missed and what I should put into the query string to get the information of the "Seafood" category out.
Thanks in advance,
Scott Chang
View 4 Replies
View Related
Nov 7, 2007
I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w. one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included) INSERT INTO @users SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is: SELECT u.userName,u.userID
FROM tableU u
LEFT JOIN tableW w ON w.workgroupID = u.workgroupID
WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!
View 1 Replies
View Related
Feb 12, 2008
When I run the following query from Query Analyzer in SQL Serer 2005, I get a message back that says.
Command(s) completed successfully.
What I really need it to do is to display the results of the query. Does anyone know how to do this?
declare @SniierId as uniqueidentifierset @SniierId = '85555560-AD5D-430C-9B97-FB0AC3C7DA1F'declare @SniierAlias as nvarchar(50)declare @AlwaysShowEditButton as bitdeclare @SniierName as nvarchar (128)/* Check access for Sniier */SELECT TOP 1 @SniierName = Sniiers.SniierName, @SniierAlias = Sniiers.SniierAlias, @AlwaysShowEditButton = Sniiers.AlwaysShowEditButtonFROM SniiersWHERE Sniiers.SniierId=@SniierId
View 3 Replies
View Related
Jan 7, 2002
I am trying to run queries against any of the user tables in my MS SQL 7.0 database. I get a message the Query Designer encountered a query error.
We have tried rebooting the SQL Server and I am still getting these messages. Also, the SQL error logs look fine - all database
maintenance are running successfully including the DBCCs which show no errors. Any help would be greatly appreciated as we are to go
into production in a few days.
View 2 Replies
View Related
Nov 23, 2004
How to optimize the following Stored procedure running on MSSQL server 2000 sp4 :
CREATE PROCEDURE proc1
@Franchise ObjectId
, @dtmStart DATETIME
, @dtmEnd DATETIME
AS
BEGIN
SET NOCOUNT ON
SELECT p.Product
, c.Currency
, c.Minor
, a.ACDef
, e.Event
, t.Dec
, count(1) "Count"
, sum(Amount) "Total"
FROM tb_Event t
JOIN tb_Prod p
ON ( t.ProdId = p.ProdId )
JOIN tb_ACDef a
ON ( t.ACDefId = a.ACDefId )
JOIN tb_Curr c
ON ( t.CurrId = c.CurrId )
JOIN tb_Event e
ON ( t.EventId = e.EventId )
JOIN tb_Setl s
ON ( s.BUId = t.BUId
and s.SetlD = t.SetlD )
WHERE Fran = @Franchise
AND t.CDate >= @dtmStart
AND t.CDate <= @dtmEnd
AND s.Status = 1
GROUP BY p.Product
, c.Currency
, c.Minor
, a.ACDef
, e.Event
, t.Dec
RETURN 1
END
GO
View 8 Replies
View Related
