How Do I Imbed A Select Inside A Select
I need a select that gets a value and than appends another value if the criteria is met otherwise nothing is appended.
The statement has a select with an imbedded select and when I execute it I get the error:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Thia is a crude sample of the statement
SELECT ID + ( select * from tableB where TableB = 0 ) as result1
FROM TableB
Why am I getting this error and how do I fix the statement?
thanks
View Complete Forum Thread with Replies
Related Forum Messages:
EXEC Inside CASE Inside SELECT
I'm trying to execute a stored procedure within the case clause of select statement. The stored procedure returns a table, and is pretty big and complex, and I don't particularly want to copy the whole thing over to work here. I'm looking for something more elegant. @val1 and @val2 are passed in CREATE TABLE #TEMP( tempid INT IDENTITY (1,1) NOT NULL, myint INT NOT NULL, mybool BIT NOT NULL ) INSERT INTO #TEMP (myint, mybool) SELECT my_int_from_tbl, CASE WHEN @val1 IN (SELECT val1 FROM (EXEC dbo.my_stored_procedure my_int_from_tbl, my_param)) THEN 1 ELSE 0 FROM dbo.tbl WHERE tbl.val2 = @val2 SELECT COUNT(*) FROM #TEMP WHERE mybool = 1 If I have to, I can do a while loop and populate another temp table for every "my_int_from_tbl," but I don't really know the syntax for that. Any suggestions?
View Replies !
Using IF Inside SELECT ?
Is there possibility to use IF conditions inside SELECT statements?For example, can i write something like this:CREATE PROCEDURE [search](@OPTION int,@KEYWORD nvarchar(40))ASBEGINSELECT id FROM projects WHERE title LIKE @KEYWORD IF (@OPTION = 1)THEN (OR description LIKE @KEYWORD)ENDor am i limited to this:....BEGINIF @OPTION = 1SELECT id FROM projects WHERE title LIKE @KEYWORD OR description LIKE@KEYWORDELSESELECT id FROM projects WHERE title LIKE @KEYWORDEND
View Replies !
Using A While Inside A Select Statement
Hi All, Can we use the while loop inside a select statement? Meaning, something like this: Code Block SELECT DATE, WHILE (SELECT TOP 1 DATEPART(HH,DATE) FROM SC_DATEDIMENSION_TABLE) <= 23 (SELECT DATEADD(HH,6,SC_DATEDIMENSION_TABLE.DATE) ) FROM SC_DATEDIMENSION_TABLE What I want to do here is I have a table which has all the dates but with time only representing 00 hrs. I want to display this column and along side, I want to have another column, which displays the date split at 6 hours. So, one left column, there will 4 columns on the right. Hope the question is clear. Thanks a lot. Mannu.
View Replies !
Select Statement Inside UDf
iam trying to rerieve a certain value from one table and i want to use that vaue inside a UDF iam usinf a table valued function as i have to retireve no of values Can i do something like this to retrieve the value SET @Value=Select Value from Table WHERE xyz='some no.' as this value is being calculated by some other fucntion and now this funcation has to use this at runtime.
View Replies !
Counter Inside Select Statement?
Hi, can you add a counter inside a select statement to get a unique id line of the rows? In my forum i have a page that displays a users past posts in the forum, these are first sorted according to their topicID and then they are sorted after creation date. What i want is in the select statement is to create a counter to get a new numeric order. This is the normal way: SELECT id, post, comment, created FROM forum_posts WHERE (topicID = @topicID) ... some more where/order by statements This is what i want: DECLARE @tempCounter bigintSET @tempCounter = 0SELECT @tempCounter, id, post, comment, created FROM forum_posts WHERE (topicID = @topicID)... some more where/order by statements and at the end.. (SELECT @tempCounter = @tempCounter + 1) Anyone know if this can be done?
View Replies !
Declare Inside Select Statement?
I have a need to execute a cursor inside a select statment, but I'm having problems figuring this out. The reason this need to be inside a select statement is that I am inserting the cursor logic into a query expression in PeopleSoft Query. So! Here's the statement that works: ====================== DECLARE @fixeddate datetime DECLARE @CVG_ELECT char(1) DECLARE @Effdt datetime DECLARE EFFDTS CURSOR FOR SELECT Z.EFFDT, COVERAGE_ELECT FROM PS_LIFE_ADD_BEN Z WHERE Z.EMPLID = '1000' AND Z.EFFDT <= GETDATE() AND Z.PLAN_TYPE = '20' ORDER BY Z.EFFDT DESC OPEN EFFDTS FETCH NEXT FROM EFFDTS INTO @Effdt, @CVG_ELECT WHILE @@FETCH_STATUS = 0 BEGIN if @CVG_ELECT <> 'E' break ELSE SET @fixeddate = @Effdt FETCH NEXT FROM EFFDTS INTO @Effdt, @CVG_ELECT END CLOSE EFFDTS DEALLOCATE EFFDTS PRINT @fixeddate ====================== If I execute this in SQL Query Analyzer it gives me the data I am looking for. However, if I try to paste this into a select statement, it goes boom (actually, it says "Incorrect syntax near the keyword 'DECLARE'.", but you get the idea). Is it possible to encapsulate this inside a select statement?
View Replies !
Select Case Inside Sql Statement ?
Code: function findingcinemaid(nameofthecinema) findcinemaid = "select cinemasid from cinemas" &_ " where brand = 'tgv' and cinemaplace2 like '"&nameofthecinema&"'" set cinemaidfound = objconndb.execute (findcinemaid) end function select case foreachcinema case 0 cinemaname = "ONE UTAMA" findingcinemaid(cinemaname) case 1 cinemaname = "MINES" findingcinemaid(cinemaname) case 2 cinemaname = "SEREMBAN 2" findingcinemaid(cinemaname) case 3 cinemaname = "KINTA CITY" findingcinemaid(cinemaname) case 4 cinemaname = "BUKIT RAJA" findingcinemaid(cinemaname) case 5 cinemaname = "TEBRAU CITY" findingcinemaid(cinemaname) case 6 cinemaname = "SUNWAY PYRAMID" findingcinemaid(cinemaname) case 7 cinemaname = "SURIA KLCC" findingcinemaid(cinemaname) end select any possible way I can merge this select case statement with the sql statement ? I try if else but too many code , defeating the original purpose of simplfying it
View Replies !
Select The CheckBox Inside The DataGridView
Hi, Can anybody help me fetch "True" & "False" value from a CheckBox inside a DataGridView row. I tried the following code:- But the cell.Selected doesnot take the correct value. objGrid = (System.Windows.Forms.DataGridView)controlObj[0]; foreach (DataGridViewRow row in objGrid.Rows) { DataGridViewCheckBoxCell cell = row.Cells[0] as DataGridViewCheckBoxCell; if (cell.Value != cell.FalseValue) { if (cell.Selected == true) { ///Some Code; } } } Kindly Help ASAP Thanks
View Replies !
Select Inside Update Query?
Is it possible for me to do something like update table1 SET var1=something,var2=something2 from table1 (SELECT * from table2) as newtable where newtable.field1=acondition
View Replies !
Subquery With Multiple Rows Inside SELECT
Hi there, I need to select rows from a table, but include the top 3 rows of another linked table as a single field in the results. Here is my basic structure: Table: Profiles Fields: Id, ProfileName Table: Groups Fields: Id, GroupName, ProfileId I then need to return something like this: ProfileName,Groups "Joe Soap","Group1, Group2, Group3" Does anyone know how this can be done? Thanks!
View Replies !
Newbe Question: Calling Function Inside Select
Hi!I have a scalar function that returns integer:xview (int)Now, I'm trying to build a procedure that has the following selectinside:select atr1, xview(atr2)from tablenameBut, I get the 'Invalid name' error when I try to execute thatprocedure.If I got it right, I must use user.fn_name() syntax, but I cannot usedbo.xview() inside my procedure since it means xview will always beexecuted as dbo, which is unaccaptable.I'm a bit confused, so any hint is very welcomed.Thanks!Mario.
View Replies !
SELECT Query Stmt Inside Stored Procedure
Friends, What are the possible usuages of a SELECT query stmt inside a stored procedure ?? How can we process the results of the SELECT query other than for documentation/Reporting purposes(Correct me if i'm wrong in this) ?? can any one throw some lite on this .. Thanks, SqlPgmr
View Replies !
Stored Procedure Output Parameter Inside Select...
Does anyone know how can I (or can I) use a stored procedure output parameter(s) inside Select statement. For example Select abc, cde, 'xyz' = Case When 'aaa' then {output parameter of my stored procedure with 'aaa' as input parameter} When 'bbb' then {output parameter of my stored procedure with 'bbb' as input parameter} end from MyTable Thanks Arcady
View Replies !
Problems Executing A SELECT Inside A TRAN Against Other Computer
Hi I have a problem executing a SELECT inside a TRAN against other computer For example: IN THE SQL Query Analizer of the COMPUTER2 1) this runs OK BEGIN TRAN SELECT * FROM COMPUTER2.DATABASE.DBO.TABLE COMMIT TRAN 2) this runs OK SELECT * FROM COMPUTER2.DATABASE.DBO.TABLE 3) this runs OK SELECT * FROM COMPUTER1.DATABASE.DBO.TABLE 4) this runs bad BEGIN TRAN SELECT * FROM COMPUTER1.DATABASE.DBO.TABLE COMMIT TRAN The problem is that TABLE locks and it does not finish. I've been looking for similar ERRORS in Microsoft Support but I found nothing I've uninstall and install de SQL server 2000 SP4 and the problems continues the same Please, someone could help me, thanks
View Replies !
How To Use A Stored Procedure Inside Select Query (sql Server Version 8)
Hi, Please help me in this problem... i am new to sql server.. i am using sql server version 8...(doesnot support function with retun values..) so i have created a procedure... -----------procedure------------------(to find next monday after 6 months)------------------- [code] create proc next_Monday ( @myDate DATETIME ) as BEGIN set @myDate = dateadd(mm, 6, @myDate) while datepart(dw,@myDate) <> 2 begin set @myDate = dateadd(dd, 1, @myDate) end select @myDate end go [/code] -------------------------------------------------------- i can able to execute this procedure separately.... working well... but don't know how to call it inside another query.... the following throws error.... select smaster.sname, smaster.Datex, 'xxx'=(execute next_monday smaster.Datex) from smaster please help me... how to fix this problem...
View Replies !
How Can I Fill Data In Textboxes From Sql Databases But Two Different Tables When I Select A Name That Is Inside A Dropdownlist
HI I need help how can i fill data in textboxes from sql databases but two different tables when i select a name that is inside a dropdownlist my controls are as follows <asp:DropDownList ID="ddl" runat="server" DataSourceID="SqlDataSource13" DataTextField="fullname" DataValueField="fullname"> </asp:DropDownList> <asp:SqlDataSource ID="SqlDataSource13" runat="server" ConnectionString="<%$ ConnectionStrings:NPI Employee MasterConnectionString2 %>" SelectCommand="SELECT [FirstName]+' '+ [Surname] as fullname FROM [Employee] where CurrentEmployee_YN=1 order by FirstName "></asp:SqlDataSource><table bordercolor="#111111" cellpadding="0" cellspacing="0" style="width: 100%; border-collapse: collapse; height: 32px; visibility: hidden;" id="table0"> <tr> <td style="width: 159px; visibility: hidden;"> </td> <td style="width: 170px"> </td> <td bgcolor="#eeeddb" style="width: 20%; height: 25px"> <strong> Order No：</strong></td> <td bgcolor="#eeeddb" style="width: 26%; height: 25px"> <asp:Label ID="OrderNo" runat="server" Width="104px"></asp:Label></td> </tr> <tr> <td bgcolor="#eeeddb" style="width: 159px; height: 25px"> <strong> Account No：</strong></td> <td bgcolor="#eeeddb" style="width: 170px"> <asp:TextBox ID="AccountNo" runat="Server" MaxLength="10" Width="130px"></asp:TextBox> <asp:RequiredFieldValidator ID="RequiredFieldValidator6" runat="server" ControlToValidate="AccountNo" Display="Static" ErrorMessage="Enter Acc No." Text="*"></asp:RequiredFieldValidator></td> <td bgcolor="#eeeddb" style="width: 20%; height: 25px"> <strong> Today's Date:</strong></td> <td> <asp:Label ID="Label1" runat="server" Text="Label" Width="200px"></asp:Label></td> </tr> <tr> <td bgcolor="#eeeddb" style="width: 159px; height: 25px"> <strong> Travel Consultant：</strong></td> <td bgcolor="#eeeddb" style="width: 170px"> <asp:TextBox ID="Consultant" runat="Server" MaxLength="30" Width="128px"></asp:TextBox> <asp:RequiredFieldValidator ID="RequiredFieldValidator7" runat="server" ControlToValidate="Consultant" Display="Static" ErrorMessage="Enter Travel Consultant." Text="*"></asp:RequiredFieldValidator></td> </tr> </table> <center> </center> <center> </center><table bordercolor="#111111" cellpadding="0" cellspacing="0" style="width: 80%; border-collapse: collapse; height: 32px; display: block; visibility: hidden;" id="table2"> <tr> <td align="center" bgcolor="#ffcc33" colspan="3" style="width: 90%; height: 29px"> <font color="#000000" size="5">Enter Passenger(s) Details</font></td> </tr> <tr> <td bgcolor="#eeeddb" style="width: 31%; height: 25px"> <strong> Surname：</strong></td> <td bgcolor="#eeeddb" style="width: 57%; height: 25px"> <asp:TextBox ID="Surname" runat="Server" MaxLength="30" Width="148px"></asp:TextBox> <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="Surname" Display="Static" ErrorMessage="Enter Surname." Text="*"></asp:RequiredFieldValidator></td> </tr> <tr> <td bgcolor="#eeeddb" style="width: 31%; height: 20px"> <strong> Name：</strong></td> <td bgcolor="#eeeddb" style="width: 57%; height: 20px"> <asp:TextBox ID="Name" runat="Server" MaxLength="30" Width="148px"></asp:TextBox> <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="Name" Display="Static" ErrorMessage="Enter Name." Text="*"></asp:RequiredFieldValidator></td> </tr> <tr> <td bgcolor="#eeeddb" style="width: 31%; height: 25px"> <strong> Initials：</strong></td> <td bgcolor="#eeeddb" style="width: 57%; height: 25px"> <asp:TextBox ID="Initials" runat="Server" MaxLength="5" Width="148px"></asp:TextBox> </td> </tr> <tr> <td bgcolor="#eeeddb" style="width: 31%; height: 25px"> <strong> Title：</strong></td> <td bgcolor="#eeeddb" style="width: 57%; height: 25px"> <asp:DropDownList ID="DropDownList1" runat="server" Width="156px"> <asp:ListItem></asp:ListItem> <asp:ListItem Value="Mr"></asp:ListItem> <asp:ListItem Value="Mrs"></asp:ListItem> <asp:ListItem Value="Ms"></asp:ListItem> <asp:ListItem Value="Dr"></asp:ListItem> <asp:ListItem Value="Prof"></asp:ListItem> <asp:ListItem Value="Min"></asp:ListItem> <asp:ListItem Value="Other"></asp:ListItem> </asp:DropDownList> <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="Dropdownlist1" Display="Static" ErrorMessage="Select Title." Text="*" Width="20px"></asp:RequiredFieldValidator> </td> </tr> <tr><td bgcolor="#eeeddb"> <strong> Department</strong> </td> <td bgcolor="#eeeddb" style="width: 57%; height: 25px"> <asp:TextBox ID="Department" runat="server"></asp:TextBox></td> </tr> <tr><td bgcolor="#eeeddb"> <strong> Cost Centre</strong> </td> <td bgcolor="#eeeddb" style="width: 57%; height: 25px"> <asp:TextBox ID="CostCentre" runat="server"></asp:TextBox></td> </tr> <tr> <td bgcolor="#eeeddb" style="width: 31%; height: 25px"> <strong> Tel：</strong></td> <td bgcolor="#eeeddb" style="width: 57%; height: 25px"> <input id="Tel" runat="SERVER" maxlength="15" name="Tel" onkeypress="if((event.keyCode<48)|| (event.keyCode>57))event.returnValue=false" style="width: 143px" type="text" /> </td> </tr> <tr> <td bgcolor="#eeeddb" style="width: 31%; height: 25px"> <strong> Fax：</strong></td> <td bgcolor="#eeeddb" style="width: 57%; height: 25px"> <input id="Fax" runat="SERVER" maxlength="15" name="Fax" onkeypress="if((event.keyCode<48)|| (event.keyCode>57))event.returnValue=false" style="width: 143px" type="text" /> </td> </tr> </table> cost centre and department are from cost table and the rest are from employee table
View Replies !
How To Write Select Statement Inside CASE Statement ?
Hello friends, I want to use select statement in a CASE inside procedure. can I do it? of yes then how can i do it ? following part of the procedure clears my requirement. SELECT E.EmployeeID, CASE E.EmployeeType WHEN 1 THEN select * from Tbl1 WHEN 2 THEN select * from Tbl2 WHEN 3 THEN select * from Tbl3 END FROM EMPLOYEE E can any one help me in this? please give me a sample query. Thanks and Regards, Kiran Suthar
View Replies !
Multiple Tables Select Performance - SQL 2005 - Should It Take 90 Seconds For A Select?
I have a problem where my users complain that a select statement takes too long, at 90 seconds, to read 120 records out of a database. The select statement reads from 9 tables three of which contain 1000000 records, the others contain between 100 and 250000 records. I have checked that each column in the joins are indexed - they are (but some of them are clustered indexes, not unclustered). I have run the SQL Profiler trace from the run of the query through the "Database Engine Tuning Advisor". That just suggested two statistics items which I added (no benefit) and two indexes for tables that are not involved at all in the query (I didn't add these). I also ran the query through the Query window in SSMS with "Include Actual Execution Plan" enabled. This showed that all the execution time was being taken up by searches of the clustered indexes. I have tried running the select with just three tables involved, and it completes fast. I added a fourth and it took 7 seconds. However there was no WHERE clause for the fourth table, so I got a cartesian product which might have explained the problem. So my question is: Is it normal for such a type of read query to take 90 seconds to complete? Is there anything I could do to speed it up. Any other thoughts? Thanks
View Replies !
Select Statement Problem - Group By Maybe Nested Select?
Hey guys i have a stock table and a stock type table and what i would like to do is say for every different piece of stock find out how many are available The two tables are like thisstockIDconsumableIDstockAvailableconsumableIDconsumableName So i want to,Select every consumableName in my table and then group all the stock by the consumable ID with some form of total where stockavailable = 1I should then end up with a table like thisEpson T001 - Available 6Epson T002 - Available 0Epson T003 - Available 4If anyone can help me i would be very appreciative. If you want excact table names etc then i can put that here but for now i thought i would ask how you would do it and then give it a go myself.ThanksMatt
View Replies !
SELECT Then DELETE Versus Extra Clause In SELECT
Far below (in section "original 3 steps"), you see the following:1. a temp table is created2. some data is inserted into this table3. some of the inserted data is removed based on a join with the sametable that the original select was made fromIn my opinion, there is no way that the join could produce more rowsthan were originally retrieved from viewD. Hence, we could get rid ofthe DELETE step by simply changing the query to be:INSERT INTO #details ( rec_id, orig_corr, bene_corr )SELECT rec_id, 0, 0FROM viewDWHERE SOURCE_SYS NOT IN ( 'G', 'K' )AND MONTH( VALUE_DATE_A8 ) = MONTH( @date )AND YEAR( VALUE_DATE_A8 ) = YEAR( @date )AND INMESS NOT LIKE '2__' ---- the added line===== original 3 steps (mentioned above) =====CREATE TABLE #details (rec_id UNIQUEIDENTIFIER PRIMARY KEY NOT NULL,orig VARCHAR(35) NULL,bene VARCHAR(35) NULL,orig_corr TINYINT NULL,bene_corr TINYINT NULL)INSERT INTO #details ( rec_id, orig_corr, bene_corr )SELECT rec_id, 0, 0FROM viewDWHERE SOURCE_SYS NOT IN ( 'G', 'K' )AND MONTH( VALUE_DATE_A8 ) = MONTH( @date )AND YEAR( VALUE_DATE_A8 ) = YEAR( @date )DELETE dFROM #details dJOIN viewD v ON ( d.rec_id = v.rec_id )WHERE INMESS LIKE '2__'
View Replies !
Implement SELECT Starement On A Results Of Prev SELECT
Hi, im getting from my first select a list of pairs of codes (let say the codes r of products.) so i have something like: FirstCode SecondCode 1 1 2 5 4 2 ... ... now i want to get the name of each product so it whould be like: FirstCode,FirstName,SecondCode,SeconeNam the names stored in other table. how can i do it? thanks Dovalle
View Replies !
Result Sets Using Select In Query Anlyzer Vs BCP Vs Select Into
When I run simple select against my view in Query Analyzer, I get result set in one sort order. The sort order differs, when I BCP the same view. Using third technique i.e. Select Into, I have observed the sort order is again different in the resulting table. My question is what is the difference in mechanisim of query analyzer, bcp, and select into. Thanks
View Replies !
Imbed &&<CR&&> In Parameter String?
Greetings - Am currently developing a set of form letters which will use parameters to complete 'boiler-plate' paragraphs and other information on the letter. One parameter is used to complete the 'Carbon Copy' portion of the letter. This could involve a singular name, or multiples. Normal business letter writing rules state that each name should appear on it's own line. Due to the number of parameters already in use, I would like to devote only one parameter to completion of the 'cc' info. I'm wondering if it's possible for the end-user to imbed a <CR> command of some sort in the string to force multiple names onto seperate lines? If this is possible, can you advise the character string they should use? I've played with ASCII characters, but am not having any luck. Tks & B/R
View Replies !
SQL Select Statement To Select The Last Ten Records Posted
SELECT Top 10 Name, Contact AS DCC, DateAdded AS DateTimeFROM NameTaORDER BY DateAdded DESC I'm trying to right a sql statement for a gridview, I want to see the last ten records added to the to the database. As you know each day someone could add one or two records, how can I write it show the last 10 records entered.
View Replies !
SELECT * Not Returning Any Rows, But SELECT COL_NAME Does!
I have a table which is returning inconsistent results when I queryit!In query analyzer:If I do "SELECT * FROM TABLE_NAME" I get no rows returned.If I do "SELECT COL1, COL2 FROM TABLE_NAME" I get 4 rows returned.In Enterprise manager:If I do "return all rows" I get 4 rows returned, and the SQL is listedas being "SELECT * FROM dbo.TABLE_NAME".I've tried adding the "dbo." before my table name in QA, but it seemsto make no difference.I'm using SQL Server 2000, which is apparently 8.00534.Can anyone help me, or give me ideas about what to check?Thanks,Rowland.
View Replies !
INSERT-SELECT Depending On The Select:ed Order
I'm doing a INSERT...SELECT where I'm dependent on the records SELECT:ed to be in a certain order. This order is enforced through a clustered index on that table - I can see that they are in the proper order by doing just the SELECT part. However, when I do the INSERT, it doesn't work (nothing is inserted) - can the order of the records from the SELECT part be changed internally on their way to the INSERT part, so to speak? Actually - it is a view that I'm inserting into, and there's an instead-of-insert trigger on it that does the actual insertions into the base table. I've added a "PRINT" statement to the trigger code and there's just ONE record printed (there should be millions).
View Replies !
Multi Select Parameter From Function - Select All?
I am using RS 2000. I have a multi select parameter where I can select multiple states by separating with a comma. I am trying to figure out how to incorporate an "All" parameter. Query: Select [name], city, state, zipcode From Golf inner join charlist_to_table(@State,Default)f on State = f.str Function: CREATE FUNCTION charlist_to_table (@list ntext, @delimiter nchar(1) = N',') RETURNS @tbl Table (listpos int IDENTITY(1, 1) NOT NULL, str varchar(4000), nstr nvarchar(2000)) AS BEGIN DECLARE @pos int, @textpos int, @chunklen smallint, @tmpstr nvarchar(4000), @leftover nvarchar(4000), @tmpval nvarchar(4000) SET @textpos = 1 SET @leftover = '' WHILE @textpos <= datalength(@list) / 2 BEGIN SET @chunklen = 4000 - datalength(@leftover) / 2 SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen) SET @textpos = @textpos + @chunklen SET @pos = charindex(@delimiter, @tmpstr) WHILE @pos > 0 BEGIN SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1))) INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval) SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr)) SET @pos = charindex(@delimiter, @tmpstr) END SET @leftover = @tmpstr END INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)), ltrim(rtrim(@leftover))) RETURN END GO Anyone have any ideas? Thanks, Deb
View Replies !
Combining 2 Select With Count And Datediff Into 1 Select. Need Help.
I have created two select clauses for counting weekdays. Is there a way to combine the two select together? I would like 1 table with two columns: Jobs Complete Jobs completed within 5 days 10 5 ------------------------------------------------------------------------------------------------- SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Jobs Completed within 5 days' FROM dbo.Project WHERE (SDCompleted > @SDCompleted) AND (SDCompleted < @SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND (DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5) --------------------------------------------------------------------------------------- Select COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Total Jobs Completed' From Project WHERE (SDCompleted > @SDCompleted) AND (SDCompleted < @SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ')
View Replies !
Date Select Query - Select Between Two Dates
have a table with students details in it, i want to select all the students who joined a class on a particular day and then i need another query to select all students who joined classes over the course of date range eg 03/12/2003 to 12/12/2003. i have tried with the following query, i need help putting my queries together select * from tblstudents where classID='1' and studentstartdate between ('03/12/2004') and ('03/12/2004') when i run this query i get this message Server: Msg 242, Level 16, State 3, Line 1 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. the studentstartdate field is set as datetime 8 and the date looks like this in the table 03/12/2004 03:12:15 please help mustfa
View Replies !
Question:select Where Another Select Statment
Hello all I create sp --------------------code---------------------- ALTER procedure [dbo].[uspInviteGroup] --uspInviteGroup 'fdi' @strUserId nvarchar(50)=null as select GroupName as 'strGroupName',GroupFounder as 'strGroupFounder' from SITE_MemberGroupswhere GroupId= (select GroupId from SITE_GroupMember where userId=@strUserId) --------------------code---------------------- but when I tested the above sp --uspInviteGroup 'fdi'return this error ------------------error--------------------- Msg 512, Level 16, State 1, Procedure uspInviteGroup, Line 6 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. ------------------error--------------------- in my case the second select statment return 2 value,I need the first select statment return two row how can I do that? thank you
View Replies !
SELECT WHERE RowID Is Not From Results Of Another SELECT
I have one query which uses a join query to gather all the projects that should show up in someone's list over a period of time (returns and id (int) and name (varchar) paired dataset). I want to do a separate query that takes that list and selects all projects (same paired set ... id and name) EXCEPT where it matches an id on a row of the given result set. The one query looks like this ..DECLARE @startDate datetimeDECLARE @endDate datetimeDECLARE @userId UNIQUEIDENTIFIERSELECT @startDate = ppStartDate FROM ppTablewhere payPeriodID = @payPeriodIDSELECT @endDate = ppEndDate FROM ppTable WHERE payPeriodID = @payPeriodIDSELECT @userId = userID FROM usersTable WHERE userName = @userNameSELECT DISTINCT p.projectID, p.projectNameFROM projectsTable pLEFT JOIN projectMemberhsip m ON m.ProjectId = p.ProjectIdLEFT JOIN timeEntryTable t ON t.ProjectID = p.ProjectIdWHERE t.TimeEntryUserId = @userID AND t.TimeEntryDate >= @startDate AND t.TimeEntryDate <= @endDateORm.UserId = @userID I want to get the same selection from projectsTable WHERE it's not anything from this result set.Haven't been able to get it by modifying the WHERE logic. Is there a way to select all WHERE id != (resultSet from this SELECT)? TIA!
View Replies !
NULL Values In A SELECT In Another SELECT
Hi,I have a query like this :SELECTx1,x2,( SELECT ... FROM ... WHERE ...UNIONSELECT ... FROM ... WHERE ...) as x3FROM ...WHERE ...The problem is that I don't want to return the results where x3 isNULL.Writing :SELECTx1,x2,( SELECT ... FROM ... WHERE ...UNIONSELECT ... FROM ... WHERE ...) as x3FROM ...WHERE ... AND x3 IS NOT NULLdoesn't work.The only solution I found is to write :SELECT * FROM((SELECTx1,x2,( SELECT ... FROM ... WHERE ...UNIONSELECT ... FROM ... WHERE ...) as x3FROM ...WHERE ...) AS R1)WHERE R1.x3 IS NOT NULLIs there a better solution? Can I use an EXISTS clause somewhere totest if x3 is null without having to have a 3rd SELECT statement?There's probably a very simple solution to do this, but I didn't findit.Thanks
View Replies !
SELECT * Vs. SELECT Field1, Field2, Etc.
This may be a more general question but I love SQL Server so Ill pose it here. I couldnt find this answer on google so that why Im asking. I was never taught (or I was too high to retain the info) form college in my one DB class. What are the performace impacts, if any, on doing a "SELECT * FROM table" vs. "SELECT each, column, called, out, but, not, all, of, them FROM table" ?
View Replies !
SELECT UNION SELECT Condition
let say i got such condition INSERT INTO TABLE SELECT WHERE XX NOT EXISTS (SELECT 1 FROM TABLE) UNION SELECT WHERE XX NOT EXISTS (SELECT 1 FROM TABLE) do you think that mssql will produce error or problem? from what i heard it will.
View Replies !
Select * From Table Does Not Select All Records
I have an unusual problem. I am using VB.Net 2003 and sqlexpress using .NET dataset to insert records into an timecards table. After inserting several records I tried a 'Select * from timecards' and the inserted records where not selected. if I 'select * from timecards order by employee' ( or any other field) the inserted records are selected! The table was created by an Access Upsize command. Any suggestions? Thanks! GordonG
View Replies !
&&"(Select All)&&" In Multi-select Enabled Drop Down Parameters Doesn't Work
Hello all, I have two mult-value parameters in my report. Both of them working with selecting one or more values. But, when I test using "(Select All)" values for both parameters , only one parameter works. The "available values" for these two parameters are both from the data set. select distinct ProductType from Product order by ProductType Any suggestion? thx
View Replies !
SELECT-Using Correlated Subqueries: Just &&"Name&&" In Results && 0 Row Affected In One Of MSDN2 SELECT Examples
Hi all, I copied and executed the following sql code in my SQL Server Management Studio Express (SSMSE): --SELECTeg8.sql from SELECT-Using correlated subqueries of MSDN2 SELECT Examples-- USE AdventureWorks ; GO SELECT DISTINCT Name FROM Production.Product p WHERE EXISTS (SELECT * FROM Production.ProductModel pm WHERE p.ProductModelID = pm.ProductModelID AND pm.Name = 'Long-sleeve logo jersey') ; GO -- OR USE AdventureWorks ; GO SELECT DISTINCT Name FROM Production.Product WHERE ProductModelID IN (SELECT ProductModelID FROM Production.ProductModel WHERE Name = 'Long-sleeve logo jersey') ; GO ========================================= I got: Results Messages Name o row affected ======================================== I think I did not get a complete output from this job. Please help and advise whether I should search somewhere in the SSMSE for the complete results or I should correct some code statements in my SELECTeg8.sql for obtaining the complete results. Thanks in advance, Scott Chang
View Replies !
Declaring A Table Variable Within A Select Table Joined To Other Select Tables In Query
Hello, I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post. I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table. This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off. So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining? Thanks in advance, Andy Select * from ( declare @years table (years int); insert into @years select CASE WHEN month(getdate()) in (1) THEN year(getdate())-1 WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate()) END select u.fullname , sum(tx.Dm_Time) LastMonthBillhours , sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage from Dm_TimeEntry tx join systemuserbase u on (tx.owninguser = u.systemuserid) where Month(tx.Dm_Date) = Month(getdate())-1 and year(dm_date) = (select years from @years) and tx.dm_billable = 1 group by u.fullname ) as A left outer join (select u.FullName , sum(tx.Dm_Time) Billhours , ((sum(tx.Dm_Time)) / ((day(getdate()) * ((5.0)/(7.0))) * 8)) perc from Dm_TimeEntry tx join systemuserbase u on (tx.owninguser = u.systemuserid) where tx.Dm_Billable = '1' and month(tx.Dm_Date) = month(GetDate()) and year(tx.Dm_Date) = year(GetDate()) group by u.fullname) as B on A.Fullname = B.Fullname Left Outer Join ( select u.fullname , sum(tx.Dm_Time) TwomosagoBillhours , sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage from Dm_TimeEntry tx join systemuserbase u on (tx.owninguser = u.systemuserid) where Month(tx.Dm_Date) = Month(getdate())-2 group by u.fullname ) as C on A.Fullname = C.Fullname
View Replies !
&&"(Select All)&&" In Multi-select Enabled Drop Down Parameters
There are several parameters on a report. One of the parameter is a multi-select enabled parameter and I suppressed the value "All" showing as one of the item in the drop down list, simply by filter out the [bha].[bha].CURRENTMEMBER.LEVEL.ORDINAL to 1, as "(Select All)" is pre-assigned to the drop list when multi-select is enabled and it is confusing to show "(Select All)" and "All" in the drop list. However I have another report which is linked to this report and the value which is required to pass to this report for this parameter is "All". Can I pass the "Select All" as a parameter from the other report? If so, how? Thanks.
View Replies !
|
TRACKER
