This is probably quite simple, but it isn't sticking out at me.
In a simple table with two groups, I want to conditionally set the color of the first group to red or green based upon whether ANY value for this field equals a particular string.
I know how to conditionally set the color of the field. However, the below code appears to only compare the FIRST value in Fields!myField.Value to the string "bad". I want it to return "Red" if ANY of the values for Fields!myField.Value = "bad".
2) I am not able to get an If/Then statement to work. I am able to use the followingl; however, ideally I would want to have multiple if/else statements:
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
I have a stored procedure that performs a search function with params:@username nvarchar(50)@country nvarchar(50)and like 10 more.A user may provide values for these params optionally.So when the @username var is left blank, there should be no filtering on the username field (every field should be selected regardless of the username)Currently my statement is:select username,country from myUsers whereusername=@username and country=@countryWith this statement when a user provides no value for username the username field selects on ''m which returns ofcourse nothing...What can I do to solve this?Thanks!
Hi, is it possible to do a conditional WHERE in T-SQL? I have a table with a column that consists of a reference that starts with either a single alpha character or two alpha characters followed by four numeric digits (the numeric portion is always unique but the alpha isn’t). E.g. A1234, AB1235, AB1236, C1237, HT1238. What I want to do is select a range of rows based on the numeric portion of this reference column. In other words I want to select say 50 rows starting from row 1000 (rows 1000 to 1050) regardless of whether there is one or two alpha characters preceding the numerics.The Stored procedure I have so far works (using COUNT for testing) for selecting a range of rows that has two alpha's at the start. However, if I simply add an OR to the WHERE to select rows where there is a single alpha in the reference column, when a single alpha reference is found it will fail the first logical check for two alpha's giving an error condition. Therefore, how can I incorporate a conditional WHERE using IF or some alternative method, so that it will also give me all the rows in the number sequence that start with either single or double alpha's within the same SELECT / WHERE statement?
Thanks for any help.ALTER PROCEDURE [dbo].[sp_Test]
( @startRef int, @endRef int )
AS
BEGIN
SELECT Count(*) FROM myTable WHERE ((SUBSTRING(Ref,3,LEN(Ref)-2) BETWEEN @startRef AND (@startRef + @endRef)))
Hi all,I have one for all the blackbelters out there: is there a way i canmake a stored procedure where i can control the where statement withvariables? I have to do some complex transformations to get compose afact table for MSAS and there a a lot of similarities between thequeries and a few differences because of different account methodsetc. (booking in starting date, booking stuff on order entry datesetc) I want to put a combination of different rules in differentmembers of dimensions.An example of what i mean:CREATE STORED PROCEDURE dbo.FILLFACT (@PAR1, @PAR2)ASINSERT INTO FactTable (blah blah)SELECTIF @PAR1 = 'OrderDate'SourceView.OrderdateELSESourceView.StartDate,etc etc...FROMSourceViewWHEREIF @PAR2 = 'WholeTable'1=1IF @PAR2 = 'Incomplete'EndDate IS NULL OR EXIST (SELECT * FROM Exceptions WHERE...., etc)This way i could fill my fact table withEXEC dbo.FillFact 'beginDate','Wholetable'EXEC dbo.FillFact 'begindate', 'Rulebook1'EXEC dbo.FillFact 'BeginDate', 'Exceptions'etcetera.This is not an actual SQL script i use, just an example of what i'mtalking about. Or maybe i could pass the where statement entirley as avariable? But i can't use SET @PAR1 = 'EndDate IS NULL' and then useWHERE @PAR1 can I?I hope i'm making sense. Does anyone know if this is possible? Rightnow i have a procedure that is composed of a dozen of sql scripts thatare mostly the same, but i have to copy it for every combination ofsituations and then, of course, new stuff has to be added on 12different places. Again and again.Any thoughts?TIA,Gert-Jan van der Kamp
Hi, I've been looling around, but was unable to correctly use a conditional statement in a T-SQL Query. I have this SQL query and in it how I would do if it was C#. If someone could please help me get what I need I would appreciate it a lot. Here is the query:
Code Block
SELECT Ficha, Almoxarifado, [Código do Item], Descrição, Unidades.Unidade, [1ª Contagem], [2ª Contagem], Recontagem, Observações, Cancelar FROM Fichas INNER JOIN Itens ON [Código do Item] = Código INNER JOIN Unidades ON Itens.Unidade = Unidades.ID WHERE Ficha BETWEEN 01 AND 5000 AND Recontagem IS NULL AND
/* What I would do in C#, but need in T-SQL*/ if ([1ª Contagem] > [2ª Contagem]) {
if ([2ª Contagem] / [1ª Contagem] < 0.99)
return true; else {
if ([1ª Contagem] / [2ª Contagem] < 0.99)
return true; }
I really need a help in this. Does anyone know how to accomplish this? Thanks. Regars, Fábio
I'm trying to gather some user statistics based on 3 conditions. First I want to check if the referring querystring is already in the database. If not insert it into the db. Second, if the querystring is already in the database, then check if the ip-address of the user is already in the database. If it is, then check if the ip address was inserted today. If not, update the "refCountIn" field with +1. The problem lies in the third condition where we check if the ip-address was inserted today and if false, update the "refCountIn" field with +1 and if true, do nothing.Below is the code I have until now: 1 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load 2 3 ' *** Declare the variables 4 Dim getStatCmd As String 5 Dim updStatCmd As String 6 7 Dim myRef As String = Request.QueryString("ref") 8 Dim myQueryString As String = Request.ServerVariables("QUERY_STRING").Replace("ref=", "") 9 Dim myRemoteAddr As String = Request.ServerVariables("REMOTE_ADDR") 10 Dim myHttpReferer As String = Request.ServerVariables("HTTP_REFERER") 11 12 Dim dtNow As Date = DateTime.Now 13 Dim dtToday As Date = DateTime.Today 14 15 ' *** Conditional INSERT command 16 getStatCmd = _ 17 "IF EXISTS(SELECT 'True' FROM tblReferers WHERE robotName = '" & myQueryString & "' AND refIpAddress = '" & myRemoteAddr & "' AND refTime = '" & dtToday & "') " & _ 18 "BEGIN " & _ 19 "SELECT 'This clickin has already been recorded!'" & _ 20 "END ELSE BEGIN " & _ 21 "SELECT 'Clickin recorded' " & _ 22 "INSERT INTO tblReferers(robotName, refIpAddress, refReferer, refTime) " & _ 23 "VALUES(" & _ 24 "'" + myQueryString + "'," & _ 25 "'" + myRemoteAddr + "'," & _ 26 "'" + myHttpReferer + "'," & _ 27 "'" + dtToday + "')" & _ 28 "END " 29 30 31 ' *** Conditional UPDATE command 32 updStatCmd = _ 33 "IF EXISTS(SELECT 'True' FROM tblReferers WHERE robotName = '" & myQueryString & "' AND refIpAddress = '" & myRemoteAddr & "' AND refTime <> '" & dtToday & "') " & _ 34 "UPDATE tblReferers " & _ 35 "SET refCountIn = refCountIn + 1, refTime = '" & dtNow & "' " & _ 36 "WHERE refIpAddress = '" & myRemoteAddr & "' AND robotName = '" & myRef & "'" 37 38 Dim insConnCmd As New SqlCommand(getStatCmd, New SqlConnection(connSD)) 39 Dim updConnCmd As New SqlCommand(updStatCmd, New SqlConnection(connSD)) 40 41 insConnCmd.Connection.Open() 42 insConnCmd.ExecuteNonQuery() 43 insConnCmd.Connection.Close() 44 45 updConnCmd.Connection.Open() 46 updConnCmd.ExecuteNonQuery() 47 updConnCmd.Connection.Close() 48 49 End Sub Anyone with an idea on how to solve this one? I think I need to write a subquery for the third condition, but I don't have a clue on how to handle this.Thanks in advance for your help!
I'm learning SQL and here I'm trying to use two things that I'm not familiar with - IF statements and the Inserted temporary table. Here's the background - skip this paragraph if you like. I'm working on a tasking system for the Help Desk - they get requests from the web site for various items and I break up the request into Software, Hardware, Accounts, etc tables and list the status of each item as "Requested". I'm also keeping a Tasks table to make work orders for each item requested. I've got triggers on the Accounts and Hardware tables that automatically make a new task for those items but the Software is more tricky because all software for a given request should be just one task. Software installs are all done by one person at the same time. So I'm trying to make a trigger that creates a new Task when a new Software record is inserted. But if a task already exists with the same RequestID (meaning they requested two peices of software and this is the second one), then I just want to update the task already created. Here's what I got: 1 CREATE TRIGGER [NewSoftwareTask] 2 ON [dbo].[Software] 3 AFTER INSERT 4 AS 5 BEGIN 6 7 -- If a software task already exists for this request 8 -- then update it. Otherwise create a new task. 9 10 if exists(select TasksID 11 from Tasks 12 where Tasks.RequestsID = inserted.RequestsID and 13 TasksType = 'Software') 14 BEGIN 15 UPDATE [BGHelpdesk].[dbo].[Tasks] 16 SET [TasksDescription] = [TasksDescription] + vbcrlf + "Install " + inserted.SoftwareType + ". " + inserted.SoftwareComments 17 WHERE Tasks.RequestsID = inserted.RequestsID and 18 TasksType = 'Software' 19 END 20 21 else 22 23 BEGIN 24 INSERT INTO [BGHelpdesk].[dbo].[Tasks] 25 ([RequestsID] 26 ,[TasksType] 27 ,[TasksSubType] 28 ,[TasksTitle] 29 ,[TasksDescription]) 30 SELECT 31 s.RequestsID 32 ,'Software' 33 ,s.SoftwareType 34 ,'New ' + s.SoftwareType + ' Account for Request ' + cast(s.RequestsID AS varchar) 35 ,s.SoftwareComments 36 FROM Software s join 37 inserted ON s.SoftwareID = inserted.SoftwareID 38 END 39 END 40 GO
It keeps balking at lines 12 and 17 saying "The multi-part identifier "inserted.RequestsID" could not be bound." The ELSE statement is what I use on the other tables and it works fine so the inserted temp record seems pretty straightforward but I must be doing something wrong...
Yet another puzzling question. I remember I saw somewhere a particular syntax to select a column based on a conditional predicate w/o using a user defined function. What I want to accomplish is this : SELECT (if column colA is empty then colB else colA) as colC from SomeTable. Possible ? Not possible? Have I hallucinated ?
currently I am creating a total if the date is between two entered dates:
select sum(case when exigo_data_sync.orders.orderdate between @prevMonthStart and @prevMonthEnd then 1 else 0 end) as PrevMonthCount,
I need to check an additional column for ($1.00) or $1.00. If it contains $1.00 then proceed as normal and add to the total. If it contains ($1.00) then subtract one from the total. Any advice?
Have a View where I need to enter a conditional IF_THEN_ELSE statement in a new field (field has an alias). If this were an Access query I would do following:
IIf([dbo.AR1_CustomerMaster.EmailAddress] Is Null, "A", "B")
Hi,I'm trying to do calculations in a SQL statement, but depending on onevariable (a.type in example) I'll need to pull another variable fromseperate tables.Here is my code thus far:select a.DeptCode DeptCode,a.Type Type,(a.ExpenseUnit / (select volume from TargetData b where b.type =a.type)) ExpenseFromcalc1 aThe problem... a.Type can be FYTD, Budget, or Target... and dependingon which one it is, I need to make b either FYTDData, TargetData, orBudgetData. I'm thinking a case statement might do the trick, but Ican't find any syntax on how to use Case in an MS SQL statement. EvenIf statements will work (if that's possible), though case would beless messy.Any suggestions would be much appriciative. Thanks...Alex.
Is it possible to have a conditional union statement in a stored proc?Here's an example on the northwind database. If says there's a syntaxerror near the UNION statement. Looks like it doesn't like having theBEGIN directly in front of it.Is the only solution to create a dynamic sql string then call exec onit?Any help appreciated.Tom.CREATE PROCEDURE usp_test(@both int = 1)ASSET NOCOUNT ONSELECT * FROM territories WHERE regionid = 1IF @both = 1BEGINUNIONSELECT * FROM territories WHERE regionid = 2ENDGO
What I need to do in seperate a group of numbers into two different categories based on a phase code. I have acheived this through two conditional statements, but when I try to total the numbers that were returned for each group I receive an #error.
This is an example of the switch statement I used in order to return the correct values for the Implemenataion.
I've tried substituting a 0 in for the "" at the end of each statement. I've also tried to take the first statement and put it into its own table field named ImplementationLedger, and them summing it. ie. =SUM(Fields!ImplementationLedger.Value)
After many hours search many forums and many failed experiments, I figure it's time to turn to the experts.
I need to execute a query that changes the returned data based upon a parameter's value. In my example below, the lob field contains both text values and nulls.
SELECT uniqueID, lob, xdate FROM mytable WHERE
CASE WHEN @myparam = 'ALL'
THEN
xdate >= '2007-09-01' ELSE
xdate >= '2007-09-01' or lob = @myparm END
I've experimented with various forms of the LIKE function, checking for null/not null and keep coming up blank.
I thought about using an IF statement and creating different versions of the entire statement, however, in real-life I need to do this with four fields using four parameters (one for each field). The permutations are a little too much.
My source file is showing column 10 as string. My destination table is datetime. I am using the derived transformation with a conditional statement. How do I convert the value from string to date. Everywhere I try the (DT_DATE) I get an error.
I am trying to write a Transact-SQL statement and am having no success. I have a customers table that has some duplicate Customer Numbers in it, however the records do have a unique Reference Number. I am trying select all records that match a list of Customer Numbers but if there are more than 1 matching Customer Number I only want the one with the largest Reference Number.
My BASIC Select Statement is:
SELECT Cust_Ref_No, Customer_No, Customer_Name, Address1, Address2, Suburb, State, Postcode, Phone FROM Customers WHERE Customer_No IN (SELECT Customer_No FROM temp_Customers)
Much to my HORROR I found that what I am trying to do is simple in MS Access using the “First” Function – Such as below:
SELECT First(Cust_Ref_No) AS Cust_Ref_No, Customer_No FROM Customers GROUP BY Customer_No ORDER BY First(Cust_Ref_No), Customer_No;
There appears to be no “First” Function in Transact-SQL. Is someone able to show me how to achieve the same results in Transact-SQL as I can get with MS Access (I’d HATE to think that MS Access has more functionality in querying data than SQL Server in any way at all)?
P.S. I really need to run the select statement as one step rather than splitting it up into parts.
I have a data model with 7 tables and I'm trying to write a stored procedure for each table that allows four actions. Each stored procedure should have 4 parameters to allow a user to insert, select, update and delete a record from the table.
I want to have a stored procedure that can accept those 4 parameters so I only need to have one stored procedure per table instead of having 28 stored procedures for those 4 actions for 7 tables. I haven't found a good example online yet of conditional logic used in a stored procedure.
Is there a way to add a conditional logic IF statement to a stored procedure so if the parameter was INSERT, go run this statement, if it was UPDATE, go run this statement, etc?
I'm getting syntax errors that just aren't helping me at all, so I thought maybe what I'm trying to do can't be done. I'm creating a UDF with 4 parameters, and I want it to return a result set (i.e. a table). But I want a different result set depending upon the value of one of the parameters. This works totally fine as a SP, but I can't tell where to put the RETURN clause(s) on the UDF. I've got: CREATE FUNCTION myFunction(@param1,...,@param4) RETURNS TABLE AS BEGIN IF @param1='x' BEGIN RETURN(SELECT columns FROM TableX) END ELSE BEGIN RETURN(SELECT columns FROM TableY) END END I get an "Incorrect syntax near the keyword 'IF'" error. I also tried using just one RETURN() wrapped around the outside of the IF construction (right after the very first BEGIN and before the last END), but to no avail. I get no errors when I run this logic as an SP. Is this type of construct not allowed in a UDF? Is there an alternative? I can't just leave this as a proc because I'm going to have to call these results from several views. Help!
Is there a way in order to execute a subscribed report based on a certain criteria?
For example, let's say send a report to users when data exist on the report else if no data is returned by the query executed by the report then it will not send the report to users.
My current situation here is that users tend to say that this should not happen, since no pertinent information is contained in the report, why would they receive email with blank data in it.
I want to do conditional processing depending on values in the rows of a CTE. For example, is the following kind of thing possible with a CTE?:
WITH Orders_CTE (TerritoryId, ContactId) AS ( SELECT TerritoryId, ContactId FROM Sales.SalesOrderHeader WHERE (ContactId < 200) ) IF Orders_CTE.TerritoryId > 3 BEGIN /* Do some processing here */ END ELSE BEGIN /* Do something else here */ END
When I try this, I get a syntax error near the keyword 'IF'
Any ideas? I know this kind of thing can be done with a cursor but wanted to keep with the times and avoid using one!
ID (int, identity) Name (nvarchar(255)) Block (nvarchar(50)) Street (nvarchar(255)) Floor (nvarchar(50)) Unit (nvarchar(50)) Address1 (nvarchar(255)) Address2 (nvarchar(255))
I want to iterate through the table and populate Address1 as [Block] [Street] #[Floor]-[Unit].If the 'Floor' field contain a number < 10 (e.g., '8'), I want to add a '0' before it (e.g., '08'). Same for Unit.How would I do this using cursors (or other recommended method)?
HI, How to create package in SSIS by applying the business Logic like if the record already exist it should be and update else it should be an insert in the destination table. how to achive this funcality in SQL SERVER 2005 (Business Intelligence studion).
I have 3 tables:tblUsersuserID int PK(...)tblSportsSportID int PK(...)tblUsersAndSports (contains the link between users and sports..a single user may have multiple entries in this table)Usercode intSportID intNow I want a stored proc that enables visitors to search on all user that have a specific sportID.The SportIDs to search on are in the var @sports as a comma delimited string,like '3,6,7'@sports may also be null (or an empty string if that is more convenient for building the SQL) when a visitor does not want to search on any of the sports a user practices, in that case no selection based on the sport criteria should be done, so ONLY filter on sports when the value of @sports is not nullpseudo code:select * from tblUserswhere if @sports not null user.sports in @sportsand username=@usernameand age=@agehelp is greatly appreciated!
I have a source table which I'm splitting 3 ways based on a column value, but the target is the same OLE DB destination table. One conditional path is to a Multi-Cast two way split to same OLE DB gestination table. The default split is to a flat file for logging unknown record types. For a test I have data for only the 3 column values I want, but I'm having trouble with the process completing. If I pre-filter the data going into the source table by one or two values I can get the process to complete even if one split is to the multicast. If I include all three data types in the source table, I get different results depending on the order in which the conditions are specified - sometimes only two split paths are executed; other times all three are executed, but in some cases only one path of the multicast split is executed. In any case, when the three source data types are used in the test, the process never competes - the pathes are in a yellow condition and never complete.
Am I creating some kind of deadlock situation by having the source data directed to the same target table via 4 splits? Any help you can provide is appreciated. Thanks.
Hello, can anyone tell me if it is possible to conditionally join tables? If so, how could it be done?
What I would like to do is create a query similar to the one below but include the rows in the Asset table where the corresponding Alert field is NULL.
Assets AS A ON AL.IndustryId = A.IndustryId AND AL.RegionId = A.RegionId AND AL.RevenueId = A.RevenueId WHERE AlertId = 1
The output I am after would be the first row in the Assets table. But since the RevenueId column is NULL, I get nothing. The only time the above query will work is if all three Id columns are populated. That is not always going to be the case. Please don't suggest changing table structures or adding data. That is not an option.
I have the following code in the color property of a textbox. However, when I run my report all of the values in this column display in green regardless of their value.
