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 Complete Forum Thread with Replies
Related Forum Messages:
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 !
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 !
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 !
Differentiate Between Whether Stored Procedure A Is Executed Inside Query Analyzer Or Executed Inside System Application Itself.
Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it? What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results. Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application. However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password. Looking forward for replies from expert here. Thanks in advance. Note: Hope my explaination here clearly describe my current problems.
View Replies !
SELECT And UPDATE In The Same Query
I have a table with only one row to get unique ID-numbers. To get the nextID-number I have to increase the number in the row: first, get the numberfrom the database, and then do the update.Some ASP-code:Set rs = db.execute("SELECT id FROM tbl_id")id = rs("id")db.execute("UPDATE tbl_id SET id ="& id + 1 &"")This is not safe: two different sessions cat get the same ID.Is it possible to increase the value of id in tbl_id and get the value atthe same time? Or is there any other way?/matte
View Replies !
Update Query With Select
HI Guys, I have two table t1 and t2 t1 has a,b,c columns(no primary key defined) t2 has a,b columns (a is primary key) I want to update the table t2 with follwin query update t2 set b=(select avg(b) from t1 group by a) where a=(select a from t1 group by a) Is it possible to update table of result of other query? or give me other solution for that how can i do that thing...?
View Replies !
Select Query And Update
Hi Friends, I need a select query to identify how many records violate the basic dateformat, later on i update the junkdata with null. Initially I have a database, in which one field(for Date) is given as Varchar, now as we know varchar accepts all types of data, when migrating the same data to another server, i am using Date as datatype for the new field and want to remove all other format's of data (Junk data) entered to that field and want to ratain only the general format i.e, MM/DD/YYYY or MM/DD/YYYY, this query should also support MM<=12, DD<=31 Regards, Prasad K
View Replies !
Select And Update In Same Query
i am selecting the top 25 records from a table for processing. i would like to set the processstatus field to 3 as soon as i select the records. is there a way to do this in one query?
View Replies !
How Do I Use Select Statement In Update Query
hi myself avii am developing one appliacaion in which i am using vb 6 as front end,adodb as database library and sql sever 7 as backend.i want to update one table for which i required data from other table. andiretrive data from second table by giving some condition. when i get data,then to update first table i need to use do while loop. instead of that iwant to use select statement directly in update query.plz give me some help.following is the my queries and its out putStrSql = ""StrSql = "Select * From SalesVchMaterialDesc where TransactionID=" &txtTransactionID.text & ""rsMName.Open StrSql, Conn, adOpenKeysetDo While Not rsMName.EOFStrSql = ""StrSql = "Update StockTable Set Outward=Outward - " &rsMName("Netweight") & ",OutwardQty=OutwardQty - " & rsMName("Qty") & "Where MaterialId=" & rsMName("Material_Name") & " and VoucherDate='" &Format(rsMName("VoucherDate"), "mm/dd/yyyy") & "'RsAdd.Open StrSql, Conn, adOpenStaticrsMName.MoveNextLooprsMName.Closeout put***main querySelect * From SalesVchMaterialDesc where TransactionID=848do while not loopUpdate StockTable Set Outward=Outward - 8.06,OutwardQty=OutwardQty - 1Where MaterialId=221 and VoucherDate='04/01/2004' and SMID=0loop
View Replies !
Select And Update Query For Date
Hi Friends, I need a select query to identify how many records violate the basic dateformat, later on i update the junkdata with null. Initially I have a database, in which one field(for Date) is given as Varchar, now as we know varchar accepts all types of data, when migrating the same data to another server, i am using Date as datatype for the new field and want to remove all other format's of data (Junk data) entered to that field and want to ratain only the general format i.e, MM/DD/YYYY or MM/DD/YYYY, this query should also support MM<=12, DD<=31 Regards, Prasad K
View Replies !
Update Set Of Columns Using Select Query In Sqlserver 2000
hi !!!!!!!!!!!!!!!!!!!!!!! I have 3 tables let say Employee,Employee_temp,emp_approval i want to update employee table by selecting columns from employee_temp i do that using oracle but i want it using sql server 2000 Sample syntax Below: for oracle update employee e set (col1,col2,col3,col4)= (select t1.col1,t1.col2,t1.col3,t1.col4 from employee_temp t where t.col1=:new.col1) where e.col1=:new.col1 this syntax for oracle.... plsease provide sql 2000 syntax and sql 2005 syntax please......... thank you.
View Replies !
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 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 !
Update Variable Inside DataFlow
Dear friends, Someone told me that I couldnt update the global variavel inside a dataflow... but i need to have some solution to do that... :-( I crate my identity column manually based on a global variable... and it works perfect, but in one dataflow I have to insert 2 times in the some table... so... in the second time my identity will be GlobalVariavel + increment of the first time rows inserted... how can I save this increment in the dataflow? How can I count the rows inserted in the first time do use it in the second time i save Data to the some table?? Thanks
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 !
Update SSIS Variables Inside DTS Task ?
Hi all, I use a "Exec DTS 2000" task. I have a variable user::MYVAR in my SSIS package, wich value is "PARENT". I pass this variable as an outer variable to my dts 2000 package, wich receive it correctly, and then update it to "UPDATED". After this task finishes, when I come back to SSIS, MYVAR is still at "PARENT". Is it possible to see the update from the parent package ? Thanks
View Replies !
Is There A Way To Know The Condition In An Update/Delete Command Inside A SP?
Hi to all: I'm trying to make a sp who generates an export string for everey sql command, for example: Insert, update and delete. In the case of Insert I just have the sp, but in "update" and "delete" commands... how can I know the conditions?.. UDTATE DemoTable SET DemoField=77 WHERE (Condition1 AND Condition2) ....may I use a comparative with 'updated' table?, please help me with your ideas Thanks a lot.
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 !
Opening Up Odbc Data Source In The Query Query Inside Of The Server Manager
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 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 !
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 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 !
Can I Use IF Inside A Query?
Is it possible to use IF inside a query, in the WHERE statement? I started with the query right below, but I onlye got error. After testing and rewriting a lot I ended up with the last query. But there hast to be a better, smarter, more elegant way to write this query? Any hint? ALTER PROCEDURE [dbo].[LinksInCategory]-- =============================================-- Description: Return all links from the requested category.-- ============================================= (@CategoryId int, @AdminFilter bit)AS SELECT Link.Id, Link.Title, Link.Url, Link.ShortText, Link.Hidden FROM Link WHERE Link.Parent = @CategoryId IF (@AdminFilter = 1) print 'AND Link.Hidden = @AdminFilter' ORDER BY Link.Title ALTER PROCEDURE [dbo].[LinksInCategory]-- =============================================-- Description: Return all NOT hidden links from the requested category.-- If in Administrators role the return ALL links (the hidden ones also).-- ============================================= (@CategoryId int, @AdminFilter bit)AS IF (@AdminFilter = 1) BEGIN SELECT Link.Id, Link.Title, Link.Url, Link.ShortText, Link.Hidden FROM Link WHERE Link.Parent = @CategoryId ORDER BY Link.Title END ELSE BEGIN SELECT Link.Id, Link.Title, Link.Url, Link.ShortText, Link.Hidden FROM Link WHERE Link.Parent = @CategoryId AND Link.Hidden = @AdminFilter ORDER BY Link.Title END Regards, Sigurd
View Replies !
Stm Inside Sql Query
Hi all As following I show my sql server query.Please just look at the blue code.How can I add a statement to do not read the code if value received is null, i.e., do not add INNER JOIN stm.Thanks a lot string strCmd = "SELECT "; strCmd += " Codigo_cotacao as 'Cód. Proposta', "; strCmd += " Cod_empresa as 'Cód. Cliente', "; strCmd += " Nome_empresa as 'Nome Cliente', "; strCmd += " Negocios_atividades_propostas.Id_atividade_proposta as 'Cód. Atividade', "; strCmd += " Nome_atividade_proposta as 'Atividade', "; // add something here if Ramo_cotacao is null and not read the next line strCmd += " Negocios_ramos.Cod_ramo as 'Cód. Ramo', "; strCmd += " convert(varchar,Data_cotacao,103) as 'Data Proposta', "; strCmd += " convert(varchar,Vigencia_cotacao_inic,103) as 'InÃc. Vigência', "; strCmd += " convert(varchar,Vigencia_cotacao_fim,103) as 'Térm. Vigência', "; strCmd += " Nome_status as 'Status', "; strCmd += " NVIdas_cotacao as 'Núm. de Vidas', "; strCmd += " Premio_cotacao as 'Prêmio Estimado', "; strCmd += " Nome_canal as 'Canal', "; strCmd += " Nome_corretor as 'Corretor', "; strCmd += " Nome_pac as 'PAC', "; strCmd += " Negocios_gerentes_canais.Nome_gerente as 'Gerente Canal', "; strCmd += " Negocios_gerente_beneficios.Nome_gerente as 'Gerente BenefÃcios', "; strCmd += " Nome_filial as 'Filial', "; strCmd += " Nome_regiao as 'Região', "; strCmd += " Nome_consultor as 'Consultor' "; strCmd += " FROM Negocios_cotacoes "; strCmd += " INNER JOIN Negocios_empresas ON Cod_empresa = Empresa_cotacao "; strCmd += " INNER JOIN Negocios_atividades_propostas ON AtivProp_cotacao = Negocios_atividades_propostas.Id_atividade_proposta "; // add something here if Ramo_cotacao is null and not read the next line strCmd += " INNER JOIN Negocios_ramos ON Negocios_ramos.Cod_ramo = Ramo_cotacao "; strCmd += " INNER JOIN Negocios_status ON Id_status = Status_cotacao "; strCmd += " INNER JOIN Negocios_canais ON Cod_canal = Canal_cotacao "; strCmd += " INNER JOIN Negocios_corretores ON Cod_corretor = Corretor_cotacao "; strCmd += " INNER JOIN Negocios_pacs ON Cod_pac = Pac_cotacao "; strCmd += " INNER JOIN Negocios_gerentes_canais ON Negocios_gerentes_canais.Cod_gerente = GerenteCanal_cotacao "; strCmd += " INNER JOIN Negocios_gerente_beneficios ON Negocios_gerente_beneficios.Cod_gerente = GerenteBeneficios_cotacao "; strCmd += " INNER JOIN Negocios_filiais ON Negocios_filiais.Cod_filial = Filial_cotacao "; strCmd += " INNER JOIN Negocios_regioes ON Cod_regiao = Regiao_cotacao "; strCmd += " INNER JOIN Negocios_consultores ON Cod_consultor = Consultor_cotacao "; strCmd += " INNER JOIN Negocios_produtos ON Produto_cotacao = Id_produto "; strCmd += " WHERE Codigo_cotacao <> -1 "; if (hiddenddlEmpresa.Text != "Todas") strCmd += " AND Negocios_empresas.Cod_empresa = " + hiddenddlEmpresa.Text; if (hiddenddlCategoria.Text != "Todas") strCmd += " AND Negocios_categorias.Id_categoria = " + hiddenddlCategoria.Text; if (hiddenddlProduto.Text != "Todos") strCmd += " AND Negocios_produtos.Id_produto = " + hiddenddlProduto.Text; if (hiddenddlRamo.Text != "Todos") strCmd += " AND Negocios_ramos.Cod_ramo = " + hiddenddlRamo.Text; if (hiddenddlCorretor.Text != "Todos") strCmd += " AND Negocios_corretores.Cod_corretor = " + hiddenddlCorretor.Text; if (hiddenddlConsultor.Text != "Todos") strCmd += " AND Negocios_consultores.Cod_consultor = " + hiddenddlConsultor.Text; if (hiddenddlCanal.Text != "Todos") strCmd += " AND Negocios_canais.Cod_canal = " + hiddenddlCanal.Text; if (hiddenddlStatus.Text != "Todos") strCmd += " AND Negocios_status.Id_status = " + hiddenddlStatus.Text; if (hiddenddlRegiao.Text != "Todas") strCmd += " AND Negocios_regioes.Cod_regiao = " + hiddenddlRegiao.Text; if (hiddenddlGerenteCanal.Text != "Todos") strCmd += " AND Negocios_gerentes_canais.Cod_gerente = " + hiddenddlGerenteCanal.Text; if (hiddenddlFilial.Text != "Todas") strCmd += " AND Negocios_filiais.Nome_filial = '" + hiddenddlFilial.Text + "'"; if (hiddenddlAtividadeProposta.Text != "Todas") strCmd += " AND Negocios_atividades_propostas.Id_atividade_proposta = " + hiddenddlAtividadeProposta.Text; if (hiddenddlPAC.Text != "Todos") strCmd += " AND Negocios_pacs.Cod_pac = " + hiddenddlPAC.Text; if (hiddenddlGerenteBenef.Text != "Todos") strCmd += " AND Negocios_gerente_beneficios.Cod_gerente = " + hiddenddlGerenteBenef.Text; if (hiddentxtDataPropostaInic.Text != "" && hiddentxtDataPropostaFim.Text != "") strCmd += " AND Data_cotacao BETWEEN '" + hiddentxtDataPropostaInic.Text + "' AND '" + hiddentxtDataPropostaFim.Text + "'"; if (hiddentxtInicioVigenciaInic.Text != "") strCmd += " AND Vigencia_cotacao_inic BETWEEN '" + hiddentxtInicioVigenciaInic.Text + "' AND '" + hiddentxtInicioVigenciaFim.Text + "'"; if (hiddentxtDataPropostaFim.Text != "") strCmd += " AND Vigencia_cotacao_fim BETWEEN '" + hiddentxtFinalVigenciaInic.Text + "' AND '" + hiddentxtFinalVigenciaFim.Text + "'";
View Replies !
Using IIF Statements Inside A Query?
Hi, I'm wondering if it is possible to use IF statements in a query, for example if this was my query: SELECT Asset, Source, Val1, Val2, Val3 FROM tableA Say the sign of the Vals is always positive, but based on if the Source field is null i want to make the Vals negative. Could I do something like this: SELECT Asset, Source, (IIF Source = null, Val1*-1, Val1), (IIF Source = null, Val2*-1, Val2), (IIF Source = null, Val3*-1, Val3) FROM tableA When I try something like this it doesn't work, is there a way to do this in a query? Thanks.
View Replies !
If Statement Inside Sql Query??
Hello, I have an if statement for one of my columns in my query. I want to write the if statement as a part of my select statement. How would I do that. Do to a couple of rules I am not allowed to write a stored procedure for this. I could use the "decode function" but I have something like this: if column1 = '1' or column2 = '2' then select "Yes" etc.. I tried doing select decode (column1 or column2, , , ) but it doesn't work. Any ideas?
View Replies !
If Statement Inside Sql Query??
Hello, I have an if statement for one of my columns in my query. I want to write the if statement as a part of my select statement. How would I do that. Do to a couple of rules I am not allowed to write a stored procedure for this. I could use the "decode function" but I have something like this: if column1 = '1' or column2 = '2' then select "Yes" etc.. I tried doing select decode (column1 or column2, , , ) but it doesn't work. Any ideas?
View Replies !
Getting Result Of Query Inside Another Query
Hi to all,I just need to get two fields from a table and manipulate the resultsin next query of a procedure.I planned to code like what you seebelow,create procedure marks1as@ sql1 as varchar(50)@ sql1=select registerno ,subjectcode from mark;beginselect * from marksetting where registerno='@sql1.registerno' andsubjectcode='@sql1.subjectcode';endcan it be possible to get the results as shown in the code? elsepropose an alternative for this scenario.Thanks in Advance.
View Replies !
Query To Each Item Inside A Datalist
Hi! I'm creating a social network, and in one page I need to compare each result of the datalist to a value of a table in my database. For example, I have the datalist showing all the entries in the table users, and when I am showing this information, I want each dataitem to be compared to a select statement of the friends of the logged in user, so that if that datalistitem is present in the results of that other select, I will change the text of a field to say "already a friend". If the user is not present in that select, ie is not a friend of the user who is logged in, the text will say "add friend". I have this comparison working already for a specific name, but not for the database query. Can anyone please help me? The code is below... <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %><%@ Import Namespace="System.Data" %><%@ Import Namespace="System.Data.SqlClient" %><asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"><script language="VB" runat="server"> Sub Page_Load(Sender As Object, E As EventArgs) Dim DS As DataSet Dim MyConnection As SqlConnection Dim MyCommand As SqlDataAdapter MyConnection = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True") MyCommand = New SqlDataAdapter("select * from aspnet_Users where IsAnonymous='False'", MyConnection) 'Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|;Integrated Security=True;User Instance=True DS = New DataSet() MyCommand.Fill(DS, "aspnet_Users") MyDataList.DataSource = DS.Tables("aspnet_Users").DefaultView MyDataList.DataBind() End Sub</script><body> <br /> <ASP:DataList id="MyDataList" RepeatColumns="1" runat="server"> <ItemTemplate> <table cellpadding="10" style="font: 10pt verdana" cellspacing="0"> <tr> <td width="1" bgcolor="BD8672"/> <td valign="top"> </td> <td valign="top"> <div id="hey"><div id="dados"><b>Name: </b><%#DataBinder.Eval(Container.DataItem, "UserName")%><br><b>city: </b><%#DataBinder.Eval(Container.DataItem, "City")%><br></div> <div id="photo"><b>Photo: </b><%#DataBinder.Eval(Container.DataItem, "UserName")%><br>status: </div> <p></div> <a href='<%# DataBinder.Eval(Container.DataItem, "UserName", "purchase.aspx?titleid={0}") %>' > <%#IIf(Container.DataItem("UserName") = "marta", "<a href='mailto:" & Container.DataItem("UserName") & "'>Email</a>", "")%> <img border="0" src="/quickstart/aspplus/images/purchase_book.gif" > </a> </td> </tr> </table> </ItemTemplate> </ASP:DataList> </body></asp:Content> Thanks a lot.
View Replies !
Timeout For Query Inside BEGIN_INIT
Hi all, I have a website under development. when trying to load page for the first time, I am getting sometimes a timeout error messege. My guess is that one of the queries is problematic and takes too long. I need to track the problematic query. one way to do that is using the ASP trace option. So this is what I did and I got the below error. the error as I can see is inside a BEGIN_INIT function, but I dont have a fnction like this in my code. Do you have any suggestion how to track? here is the trace code: Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210 span.tracecontent b { color:white } span.tracecontent { background-color:white; color:black;font: 10pt verdana, arial; } span.tracecontent table { clear:left; font: 10pt verdana, arial; cellspacing:0; cellpadding:0; margin-bottom:25} span.tracecontent tr.subhead { background-color:#cccccc;} span.tracecontent th { padding:0,3,0,3 } span.tracecontent th.alt { background-color:black; color:white; padding:3,3,2,3; } span.tracecontent td { color: black; padding:0,3,0,3; text-align: left } span.tracecontent td.err { color: red; } span.tracecontent tr.alt { background-color:#eeeeee } span.tracecontent h1 { font: 24pt verdana, arial; margin:0,0,0,0} span.tracecontent h2 { font: 18pt verdana, arial; margin:0,0,0,0} span.tracecontent h3 { font: 12pt verdana, arial; margin:0,0,0,0} span.tracecontent th a { color:darkblue; font: 8pt verdana, arial; } span.tracecontent a { color:darkblue;text-decoration:none } span.tracecontent a:hover { color:darkblue;text-decoration:underline; } span.tracecontent div.outer { width:90%; margin:15,15,15,15} span.tracecontent table.viewmenu td { background-color:#006699; color:white; padding:0,5,0,5; } span.tracecontent table.viewmenu td.end { padding:0,0,0,0; } span.tracecontent table.viewmenu a {color:white; font: 8pt verdana, arial; } span.tracecontent table.viewmenu a:hover {color:white; font: 8pt verdana, arial; } span.tracecontent a.tinylink {color:darkblue; background-color:black; font: 8pt verdana, arial;text-decoration:underline;} span.tracecontent a.link {color:darkblue; text-decoration:underline;} span.tracecontent div.buffer {padding-top:7; padding-bottom:17;} span.tracecontent .small { font: 8pt verdana, arial } span.tracecontent table td { padding-right:20 } span.tracecontent table td.nopad { padding-right:5 } Request Details Session Id: sesqltrnnbq35g45fxkyqk45 Request Type: GET Time of Request: 6/9/2007 12:36:41 AM Status Code: 500 Request Encoding: Unicode (UTF-8) Response Encoding: Unicode (UTF-8) Trace Information Category Message From First(s) From Last(s) aspx.page Begin PreInit aspx.page End PreInit 4.97502699318581 4.975027 aspx.page Begin Init 5.03576665047642 0.060740 Unhandled Execution Error Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) at System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) at System.Web.UI.WebControls.WebParts.SqlPersonalizationProvider.GetConnectionHolder() at System.Web.UI.WebControls.WebParts.SqlPersonalizationProvider.LoadPersonalizationBlobs(WebPartManager webPartManager, String path, String userName, Byte[]& sharedDataBlob, Byte[]& userDataBlob) at System.Web.UI.WebControls.WebParts.PersonalizationProvider.LoadPersonalizationState(WebPartManager webPartManager, Boolean ignoreCurrentUser) at System.Web.UI.WebControls.WebParts.WebPartPersonalization.Load() at System.Web.UI.WebControls.WebParts.WebPartManager.OnInit(EventArgs e) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) 8.31843797517178 3.282671
View Replies !
Connecting To 2 Databases Inside One Query
hi there, I saw a similar thread before but i've tried it and no luck. I want run a query off a database (stawebdata.branchinfo) then a join into bluepages.phonelist i've tired JOIN bluepages.phonelist p Incorrect syntax near the keyword 'JOIN'. any ideas... is this possible? complete code below: select DISTINCT b.name, b.tel1, b.id, b.telprivate,b.sc, b.manager,b.branchEmail from branchInfo b inner JOIN bluepages.phonelist p on b.name = p.subdepartment where b.name like 'Sarah' or b.id LIKE '%@keyword%' OR b.county LIKE '%@keyword%' OR b.Area LIKE '%@keyword%' OR b.Manager LIKE '%@keyword%' OR b.AssistantManager LIKE '%@keyword%' OR b.Postcode LIKE '%@keyword%' OR b.PseudoCity LIKE '%@keyword%' OR b.TicketingTA LIKE '%@keyword%' OR b.SLC LIKE '%@keyword%' OR b.ItineraryTA LIKE '%@keyword%' OR b.TIDS LIKE '%@keyword%' OR b.ABTA LIKE '%@keyword%' and p.department = 'UK Branch' and b.isbranch <> 'False' or (p.lastName = '@keyword' or p.firstname = '@keyword') order by b.name asc GO
View Replies !
Executing A Query Inside A Procedure
Hello, I have written a procedure and I have a variable that has been declared as a varchar. For example: @test = 'select * from table_a' I want to able to execute this @test varible. Any help will be greatly appreciated. Thank you
View Replies !
Executing A Query Inside A Procedure
Hello, I have written a procedure and I have a variable that has been declared as a varchar. For example: @test = 'select * from table_a' I want to able to execute this @test varible. Any help will be greatly appreciated. Thank you
View Replies !
Hi, Pls See The Query Inside, Reg. Removing Duplicates..
-- declared variables declare @database_name varchar(100), @table_name varchar(100), @primary_key_field varchar(100) declare @list varchar(8000) -- set values to variables set @list = '' set @database_name = 'data200802_dan' set @table_name = 'other02' set @primary_key_field = 'callid' use database select @list = @list + column_name + ', ' from information_schema.columns where table_name = @table_name --table name and column_name != @primary_key_field --unique identifier select @list = substring(@list, 1, len(rtrim(@list)) - 1) --above 5 lines btw came from a helper in the msdn forum. thanks SELECT DISTINCT @list INTO '#' + @table_name FROM @table_name @table_name + ':' IF (SELECT COUNT(*) FROM @database_name + '.dbo.' + @table_name) = 0 BEGIN INSERT INTO @database_name + '.dbo.' + @table_name + '(' + @list + ')' SELECT @list FROM '#' + @table_name END ELSE BEGIN DELETE @database_name + '.dbo.' + @table_name +' ( ' + @list + ')' GOTO @table_name END DROP TABLE '#' + @table_name the query above is basically.. selecting all the fields from a table in database W/OUT their primary key. then putting them in a temp table.. delete all the records in the original table. then paste the records from the temp table into the original table. is there a way for this to work? i don't know how to use the variables w/ this script. please help me correcting this query.. this is for removing duplicates btw. thanks!
View Replies !
|
TRACKER
