Hi,
Is there a work around for the following query on sql server compact edition
IF(Some Condition) --
BEGIN
INSERT INTO @TEMP
SELECT 1, XYZ_ID FROM My_Table
END
Thanks,
Dp
I'm looking to replace text in a given column given a set of conditions in the other columns. Please see below the M query in the advance editor and in particular the bold text. Here I've created a new entry that would appear in the query applied steps window in the power query editor that I have called "Replace Values". The logic is if Data.Column4 column equals "London" then replace null values in Data.Column5 with London. However when I save the query below I get the errorÂ
Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?
I plan to change the expression to test for multiple conditions however I need to get the basic expression working first. The other frustration i had with the "if" statement is it had to have an else even though I didn't require it, am i doing something wrong here?
let
FullFilePath = "C:PermanentDwellings.xlsx",
Source = Excel.Workbook(File.Contents(FullFilePath)),
  #"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Column1", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16",
[Code] ....
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
View 2 Replies View RelatedI 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!
View 6 Replies View RelatedHi, 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)))
END
RETURN
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
View 2 Replies View RelatedHi, 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...
I'm trying to use a conditional statement in the where clause.
Here is my table
UID Amount ID PID Amount2
1 30000 8064 NULL NULL
2 30000 8042 8064 30000
What I'm trying to achieve:
If Amount = Amount2 for UID 2 then show UID 1
Hello dbForumers,
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 ?
Thank You!
Table with water consumption per month and customer.
I want to sum up total consumption per customer with a select statement
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?
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".
Code Block=IIf(Fields!myField.Value = "bad", "Red", "Green")
myField is ouput in the detail scope if that makes any difference.
Is this possible?
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")
How can I accomplish same in View design??
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.
View 4 Replies View RelatedIs 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
View 5 Replies View RelatedWhat 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.
=Switch(Fields!Phase_Code.Value="PILOT", Fields!LedgerQuantity.Value, Fields!Phase_Code.Value="DATAMIGRAT", Fields!LedgerQuantity.Value/2, 1=1, "")
I've tried several different methods for aggregating the numbers that are returned.
=SUM(Switch(Fields!Phase_Code.Value="PILOT", Fields!LedgerQuantity.Value, Fields!Phase_Code.Value="DATAMIGRAT", Fields!LedgerQuantity.Value/2, 1=1, ""))
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)
Please Help!
Greetings,
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.
Any ideas?
Rob
Two questions:
1) I am unable to get a detail row of my table to suppress. I am using:
=Iif(IsNothing(Fields!Address2.Value), True, False)
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:
=Iif(Fields!Taxschid.Value = "CUST", 0, Fields!Selling_Price.Value*.08)
Tx
Les
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.
[Column 10] == "01/01/0001" ? " 01/01/1801" : [Column 10] <= "12/31/1801" ? "12/31/1801" : [Column 10]
Hi All,
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.
Regards, Trog28
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 have attached my data model for reference.
Hello everyone,
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.
Any help or suggestions will be much appreciated.
Thanks,
Larry
I've got a SQL Server hardware Replacement to do. This server has a merge replication setup on it which was setup by a contractor.
Do i have to do the configurations manually when moving the replication bits across two servers or is there a different process involved ? I am just not sure how to approach this.
Hello all,
I get some dates entered manually in french format in a varchar(255) field and want to convert that field in datetime. Since, Enterprise manager is warning me about a risk to loose information, I'm thinking about a way to update the french text in the corresponding english text before doing the conversion.
Example:
25-fév-08 (varchar(255)) has to become 25-feb-08 (datetime)
Could anybody help me, please?
Hello All!
I have a job that we wrote in SQL 2000 that loops through and finds all the orders pending verification and distributes them via emal to 100+ sales people every night. Each email is sent to the individual sales rep. I did this using sp_makewebtask and attaching the html file to an email and using a WHILE statement to loop thorugh all pending entries in sql.
I recently learned that sp_makewebtask is not going to be around for the next release and that I should use RSS for all future development.
Question: How can I achieve the same result outlined above using RS? How can I dynamically create and distribute HTML files?
I know that RS is xml based, but some pointer on how/where to start would be much appreciated.
Many Thanks....
I wish to replace column2 'ABC' value become blank.
EXAMPLE TABLE VIEW
column2
-------
ABC 123, Alt. ABC 456, Alt ABC 789
select replace(replace(B.[column 2],A.[column 1],''),'ABC','')
from TABLE A,TABLE B
where A.id = B.id
After executed
column2
-------
123, Alt. 456, Alt. 789
But not expected result, because i jz wan ABC become blank not Alt. ABC.
So what should i modify with my logic and script?
Dear all,I need some help from all my Transact SQL Guru friends out there..Here is the scenario in its most simplified form.. ..I have two tables.. A(Lookup table) and B(Transaction Table)TableA FieldsEmployeeLocationIDEmployeeLocation (This could have values say"B","BO","BOM","C","CA","CALC") etc...TableB FieldsEmployeeIDEmployeeName.......EmployeeLocationID (will have null initially when rows are populatedfirst time)EmployeeLocation (This could have values"BA123","BOMBAY","BOTS123","BRACK".... etc)I hope you get where I am leading this to, from my examples..Requirement is to populate the EmployeeLocationID in Table B withEmployeeLocationID from TableA by matching the field EmployeeLocationin both tables.Please note that table B's EmployeeLocation could be A'sEmployeeLocation + some additionalcodes like "123","RACK" etc in theabove example...Therefore, this is what I had wrote initially..update Bset B.EmployeeLocationID =A.EmployeeLocationID
Quote:
Help, I have a vendor file with the vendors name. I need to add afield that has alternate captionseg. :In my vendor file I have records for AT&T with three differentnames/spellings for AT&T.AT&TLucent TechnologiesA T & TI want to add a new field that would have AT&T for the above records.I know I could hardcode a queryUpdate F_VENDORSet VENDOR_CAPTION_ALT = 'AT&T'where VENDOR_CAPTION like '%Lucent%' orVENDOR_CAPTION like '%A T & T%'however, how can I use another table that has the my search keywordsfor the "like" and the replacement captions so I dont need literalqueries to update all the diffent vendors?I have identified 96 instances of where I want to have an alternativename that I can use for a type of grouping. This gets really wildwith the military such as Army and Navy.TIARob
View 4 Replies View Related
Hi,
Is there any replacement for BCP in Sql Server 2005 ?
Thanks
Altaf Hussain Nizamuddin
In the BOL, it states to replace DB_ID() with a valid database name when the compatibility level is 80 or below.
The original statement is:
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
I replaced DB_ID() as follows but none worked:
DB_ID(N'pubs')
pubs
What should the syntax look like?
In VS 2003 I used SQLDMO (Com Object) to list all available SQL Servers. Is in SQL Server 2005 a managed .net Component that can do that task?Thanks,Rainer.
View 2 Replies View Related