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]
I created an SSIS package that pulls in legacy data from a DB2 AS400. There is an In Date field that is stored on the AS400 as OdbcType.Date or DATE. However, when I use SSIS to pull in the data, SQL wants it to be a string so I gave up and let SQL have it's way.
Now in my SELECT statement, I have this:
SELECT TLMST.TLNUMBER, TLMST.DOGNAM, BRDMST.BRDesc, TLMST.INDT
FROM TLMST INNER JOIN
BRDMST ON TLMST.BRDCOD = BRDMST.BreedNumber
WHERE (TLMST.INDT >= @startdate) AND (TLMST.INDT <= @enddate)
ORDER BY TLMST.INDT, TLMST.TLNUMBER
Because I see the dates stored in TLMST are yyyy-mm-dd format, this is not working. It is not pulling any records. I tried to use the CAST statement but I keep getting errors about casting from a string to a date and data overflow errors.
Should I go back and re-do the SSIS or is there a way to pull the records for this? Thanks for the information
Hello, I am using an SQLDataReader called DrCoInfo to check if any matching records exist in a table.
My code is as such:
Line 119:DrCoInfo = comm1.ExecuteReader()
Line 120:While DrCoInfo.Read()
Line 121: If DrCoInfo("DateOfMan") <> "" Then
Line 122: BValid = True
Line 123: End If
However, it fails to work with the message :System.InvalidCastException: Cast from string "" to type 'Date' is not valid.
Any suggestions? Thanks.
HiI have a problem to search between two dates, if I use Convert(varchar(10),ContactCreateDate,105) in a single textbow and for a single search date it is okay, but when I use the Convert(varchar(10),ContactCreateDate,105) against two textboxes it did't function.Can anybody help me ?MY codeif (TBContactSearchDateStart.Text.Length > 0 && TBContactSearchDateEnd.Text.Length > 0)
{
strWhere += " AND Convert(varchar(10),ContactCreateDate,105) BETWEEN '%" + TBContactSearchDateStart.Text + "%' AND '%" + TBContactSearchDateEnd.Text + "%'";
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 RelatedHi ,
I have a problem with casting a string number to int.
for exmaple i have the number '0110' (string) and i would like it to be 110. or '0001' to be 1.
i tried to do:
cast(MyNUM as int) .
but i get "Syntax error converting the nvarchar value 'O322' to a column of data type int."
is anyone have any idea how to solve it ?
thanks
I want to replace a column value with a null if the string is empty. I would have thought this simple expression would do it:
RTRIM([FromContractSymbol]) == "" ? NULL(DT_STR, 0, 1252) : (DT_STR, 6, 1252)FromContractSymbol
Yet, I get the following error:
For operands of the conditional operator, the data type DT_STR is supported only for input columns and cast operators. The expression "...see above..." has a DT_STR operand that is not an input column or the result of a cast, and cannot be used with the conditional operation.
The expression works if I replace the NULL(DT_STR, 0, 1252) with say "A" and the expression works on other non-string columns. (As in "NULL(DT_I1) : (DT_I1)100")
It must be something I'm overlooking but I keep getting an error message that this statement can't parse.
UPDATE product SET supplier = LEFT(supplier,LEN(supplier-4)) + CAST( '2100' AS varchar(4)) WHERE actualid = 'IS2100-CO2-CO2-0-4-I'
Any help would be greatly appreciated.
Hi guys,
The value in the field ACCOUNTS.ACCOUNTKEY is something like: '3130005'
I need to read the characters from position 4 to 6. If the value of that Substring is equal to the VALUE Zero then write "Zero".
In the code below, the first "case" is working nice, but the second (red one) is getting ERROR.
Please Help.
"SELECT ACCOUNTS.ACCOUNTKEY," _
& " Case When SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3)= '000' then 'Zero'" _
& " Case When CAST(SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3) as int) =0 then 'Zero'" _
& " Else 'Unknown'" _
& " End " _
& "AS 'Finding Zero' "
Thanks in advance,
Aldo.
Hello Everyone,
How do I change the format of the Date:
2006-06-01 00:00:00.000
to this:
06-01-2006.
I believe this can be done with a Cast.
Am I correct?
TIA
Kurt
Hi.. Please help me resolve this error "Cast from string 'OPEN' to type 'Double' is not valid.". Error here If CallStatus = 10 Then ....Code:Public Sub UpdateCallStatus() Dim CALLID, RequestorID, CommentsFromITD, MessageFromITD, MessageToITD, CallStatus, strSQL As String CALLID = Request.QueryString("CallID") RequestorID = Session("USER_ID") CommentsFromITD = lblcomments.Text MessageFromITD = lblmessage.Text MessageToITD = txt_desc.Text CallStatus = Trim(Request.Form(ddl_callstatus.UniqueID))
Dim ObjCmd As SqlCommand Dim ObjDR As SqlDataReader
Try If CallStatus = 10 Then strSQL = "UPDATE CALLS SET STATUS_ID=" & CallStatus & " WHERE CALL_ID= " & CALLID & "" ObjCmd = New SqlCommand(strSQL, ObjConn) ObjConn.Open() ObjDR = ObjCmd.ExecuteScalar() gbVariables.insertuserevents(CALLID, RequestorID, "Call Closed") Response.Redirect("UserCallClosed.aspx") ObjConn.Close() Else strSQL = "UPDATE CALLS SET STATUS_ID=" & CallStatus & " WHERE CALL_ID= " & CALLID & "" ObjCmd = New SqlCommand(strSQL, ObjConn) ObjConn.Open() ObjDR = ObjCmd.ExecuteScalar() ObjConn.Close()
strSQL = "SELECT STATUS_LABEL FROM STATUS WHERE STATUS_ID = " & CallStatus & "" ObjCmd = New SqlCommand(strSQL, ObjConn) ObjConn.Open() ObjDR = ObjCmd.ExecuteScalar() ObjConn.Close()
gbVariables.insertuserevents(CALLID, RequestorID, CallStatus) CallStatus = "" End If Catch ex As Exception lblmsg.Text = ex.Message.ToString End Try End SubThanks...
I am attempting to convert an integer value to a string using the Derived Column transformation with the following expression on the field:
(DT_STR,10,1252)prod_id
prod_id is an integer. I was able to do this before however, in the past couple of days, this has failed with the following error:
"An error occurred while attempting to perform a type cast."
To my knowledge, I have not changed anything about this particular data flow within the past couple of days. I have verified that the value is coming in as an integer.
Any help would be greatly appreciated.
I have developed a CF application and now need to add some charts to it for admin purposes. I have a form page that uses a calendar widget to let a manager select a date range and submits to display data for that timeframe. I need to convert the formfields to a date for my WHERE clause in my query. My experience to this point has just been simple queries of one table. below is a sample I found and modified numerous time to try and make it work with no success.
Can anyone give me a hand with this?
SELECT COUNT ((CHG_TYP_CD)) AS CHG_TYP,
CHG_TYP_CD
FROM WRK_RQS
WHERE(CAST('form.startdate' AS datetime) >= RQS_SBM_TS)
AND
(CAST('form.startdate' AS datetime) <= RQS_SBM_TS)
GROUP BYCHG_TYP_CD
Unable to cast object of type 'System.String' to type 'System.Web.UI.DataSourceSelectArguments'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Web.UI.DataSourceSelectArguments'.
Hi, In my pageload i have data retriving from a page and i want it to load it in the GridView. i have GridView and SqlDataSource but when the page loads i am getting the above error. i am not sure what im doing wrong here. i tried different ways. but no luck Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not IsPostBack Thenfp = CType(Context.Handler, Member)
'lblEmail.Text = fp.EMM.ToString
'this labelbox for Email address is under the Name'lblEmail.Text = fp.EmailAddress.ToString
Dim conn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("imacstestConnectionString").ConnectionString)
Dim cmd As SqlCommand = New SqlCommand("SELECT , [fname], [mname], [lname], [address], [city], [state], [zip], [phone] FROM [t_CustomerAcct] WHERE = @email", conn)cmd.Parameters.AddWithValue("@email", fp.EMM.ToString)
conn.Open()
SqlDataSource1.Select(cmd.ExecuteScalar())
'.ExecuteScalar()
conn.Close()
End If
End Sub
i am trying to take a field that has part of a date in it, so I have to parse it out as follows:
SUBSTRING(a1.Field1, 3, 2) + SUBSTRING(a1.Field1,5,2) + '20' + LEFT(a1.Field1,2)
This is because a date of 04/16/2014 will show as 160416 in the first part of the field I need to parse it out of, thus becoming 04162014.
From there I then need to convert this "date" into a legitimate SQL datetime type, so that I can then run a DATEDIFF to compare it to when the record was actually entered, which is a separate field in the table, and already in datetime format.
When I use the below statement, I am getting the message that, "Conversion failed when converting date and/or time from character string."
CAST((SUBSTRING(a1.Field1, 3, 2) + SUBSTRING(a1.Field1,5,2) + '20' + LEFT(a1.Field1,2)) as datetime)
I also tried CONVERT(datetime, (SUBSTRING(a1.Field1, 3, 2) + SUBSTRING(a1.Field1,5,2) + '20' + LEFT(a1.Field1,2)), and got the same message.
how I can parse that field, then convert it to a datetime format for running a DATEDIFF statement?
Hello Everyone,
I'm new to SQL within the past year and am having quite a bit of difficulty trying to replace NULL in a column cast as smalldatetime. What i'd like to do is return the value "EMPLOYEED" within any record containing a null value, obviously I need to CAST the column to a varchar but when I try and do this I keep getting errors. I'm trying hard to find the answer online but the only examples I get are is for money and i'm not sure i'm completely understanding how this is supposed to work. I've also tried using the ISNULL function as well - getting the same errors in this case (though i'm using ISNULL successfully in another column).
It seems to me that I should be able to write the syntax as the following:
CAST(ISNULL(TerminationDate, 'EMPLOYEED') AS Varchar(20)) AS TerminationDate
----OR---
CAST(COALESCE(TerminationDate, 'EMPLOYEED') AS Varchar(20)) AS TerminationDate
I'm new enough that I could just be confusing myself, but I really seriously cannot find any good explanations or examples online for what I am trying to do.
Can some nice SQL expert provide assistance?
Thanks!!!!!
Ave
Casting 0 to SMALLDATETIME or DATETIME is allowed in T-SQL which produces to 1900-01-01. However, you can't cast 0 to DATE or DATETIME2. Why is that?
View 15 Replies View RelatedI am getting an invalid cast specification when I call dtexec and try to /SET a user variable within the package that is defined as a string data type. Is there any solution to this? I have tried passing the GUID with {} w/o {} w/ '' w/ "" etc.. many variations... and still get an invalid cast specification. Is there a data type that I need to set the User variable to besides String? The User Variable is used to Select records from a SQL data source where the GUID is stored. I do not have an option of GUID data type for a User Variable.
Thanks for any help! Aaron B.
Hi guys,
I knew that the Function Cast can do this but I tried a lot and I dont want to use the MONTH, YEAR, DAY function.
I have a smalldatetime field with a value of this 12/18/2004 4:02:00 PM
I just like to see it like this 12/18/2004
Hope you can help me up. Thanks.
-vince
I have a column (varchar (50)), It has values from 1-12.
I want to cast them to their appropriate months.
Example: 1 = January
2 = Febuary
3 = March
etc.
how do you get it to sort correctly for the date? at the minute its sorting on the characters rather than actual date value
Code Snippet
INSERT INTO @TempItems (OrderID)
SELECT OrderID
FROM Orders o
INNER JOIN Customers c ON c.CustomerID = o.CustomerID
INNER JOIN Employees e ON e.EmployeeID = o.EmployeeID
ORDER BY
CASE @SortOrder --Order ASC
WHEN 0 THEN cast(OrderID as varchar(100))
WHEN 1 THEN cast(c.CompanyName as varchar(100))
WHEN 2 THEN cast(e.FirstName as varchar(100))
WHEN 3 THEN cast(o.OrderDate as varchar(100))
WHEN 4 THEN cast(o.RequiredDate as varchar(100))
WHEN 5 THEN cast(o.ShippedDate as varchar(100))
ELSE '1'
END,
I have a server on SQL Server 2008 R2.
I have the following code:
SELECT '1' AS Join_Field, T1.ID AS T1_ID, T2.ID AS T2_ID, T1.TimeStamp AS T1_Timestamp, T2.TimeStamp AS T2_Timestamp, DATEDIFF(Minute, T1.TimeStamp, T2.TimeStamp) AS Difference_Mins, T1.eventid, T1.shiftID,
T1.Value, SD.Shift_Start_Date, T1.StopCode, T1.Status, T1.JobID, T1.StatusDesc, T1.StopDesc, T1.MachineID,
CAST(CASE WHEN CONVERT(date, T1.TimeStamp, 103) < CONVERT(date, SD.Shift_Start_Date, 103)
THEN 1 ELSE 0 END AS int) AS flag, CAST(T1.TimeStamp as DATE) AS TS_Date
FROMÂ PolReporting.Event_M2_T1 AS T1 INNER JOIN
PolReporting.Event_M2_T2 AS T2 ON T1.RowID = T2.RowID INNER JOIN
Intouch.ShiftDates_Grouped AS SD ON T1.shiftID = SD.ShiftID
However when I run it I get a message:
I have been having some trouble trying to get the date format YYYYMMDD 00:00:00.00 to convert to MM/DD/YYYY. I am using SQL 2005 and Reporting Services. I attempted several variations to solve this problem as outlined below, with the final working conclusion at the end. I am not saying that this is the BEST or the ONLY way to achieve the solution, but it worked for me.
Code Block
---------------------------------------------------------------------------------------
SELECT DATE_TIME AS DATE1
FROM TRANSLOG
--returns value of 20071027 02:26:24.06
---------------------------------------------------------------------------------------
SELECT CAST(DATE_TIME AS SMALLDATETIME) AS DATE1
FROM TRANSLOG
--returns value of 2007-10-27 02:26:00
---------------------------------------------------------------------------------------
SELECT LEFT(CAST(DATE_TIME AS SMALLDATETIME),11) AS DATE1
FROM TRANSLOG
--returns value of Oct 27 2007
---------------------------------------------------------------------------------------
SELECT CAST(SUBSTRING(DATE_TIME,0,9) AS SMALLDATETIME) AS DATE1
FROM TRANSLOG
--returns value of 2007-10-27 00:00:00
---------------------------------------------------------------------------------------
SELECT CONVERT(VARCHAR(10), DATE_TIME, 103)
FROM TRANSLOG
--although this works with GETDATE(), it does not work with the DATE_TIME field
---------------------------------------------------------------------------------------
SELECT CONVERT(VARCHAR(10), CAST(DATE_TIME AS SMALLDATETIME), 101) AS DATE1
FROM TRANSLOG
--THIS WORKS!!! Displaying 10/27/2007
Hope this helps somebody someday!
-Jody
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!
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 need to find a way to use conditional compilation option to change the connection string in ado.net datasets 2.0.
Debug > connection 1 : test server
Release > connection 2 : real server
How is it possible ?
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