October 7, 2005 at 2:25 pm
Please help. Im trying to update a text column with the following string...
IF @InternalClientID = '' BEGIN SELECT CriteriaField1 AS FileTrackingNumber, CriteriaField2 as GroupNumber, Filename, OrderDate, OrderDueDate AS TargetShipDate, ShipDate, CASE ISNULL(ShipDate,'') WHEN '' THEN '' ELSE CASE substring(CONVERT(varchar, datediff(day, OrderDueDate, ShipDate)), 1, 1) WHEN '0' THEN 'On Time' WHEN '-' THEN 'Early: ' + CONVERT(varchar, datediff(day, OrderDueDate, ShipDate)) + ' day(s)' ELSE 'Late: ' + CONVERT(varchar, datediff(day, OrderDueDate, ShipDate)) + ' day(s)' END END Measurement FROM [digecenter-archive].dbo.tblArchiveData [row] WHERE CONVERT(datetime,OrderDate) BETWEEN @StartDate AND @EndDate ORDER BY CONVERT(datetime,OrderDueDate) for xml auto, elements, xmldata end IF @InternalClientID <> '' BEGIN SELECT CriteriaField1 AS FileTrackingNumber, CriteriaField2 as GroupNumber, Filename, OrderDate, OrderDueDate AS TargetShipDate, ShipDate, CASE ISNULL(ShipDate,'') WHEN '' THEN '' ELSE CASE substring(CONVERT(varchar, datediff(day, OrderDueDate, ShipDate)), 1, 1) WHEN '0' THEN 'On Time' WHEN '-' THEN 'Early: ' + CONVERT(varchar, datediff(day, OrderDueDate, ShipDate)) + ' day(s)' ELSE 'Late: ' + CONVERT(varchar, datediff(day, OrderDueDate, ShipDate)) + ' day(s)' END END Measurement FROM [digecenter-archive].dbo.tblArchiveData [row] WHERE InternalClientID = @InternalClientID AND CONVERT(datetime,OrderDate) BETWEEN @StartDate AND @EndDate ORDER BY CONVERT(datetime,OrderDueDate) for xml auto, elements, xmldata
I keep trying thing like
Update tblreports set sqlquery = 'IF @InternalClientID = '''''' BEGIN SELECT CriteriaField1 AS FileTrackingNumber, CriteriaField2 as GroupNumber, Filename, OrderDate, OrderDueDate AS TargetShipDate, ShipDate, CASE ISNULL(ShipDate,'''''') WHEN '''''' THEN '''''' ELSE CASE substring(CONVERT(varchar, datediff(day, OrderDueDate, ShipDate)), 1, 1) WHEN ''''0'''' THEN ''''On Time'''' WHEN'''-''' THEN ''''Early: '''' + CONVERT(varchar, datediff(day, OrderDueDate, ShipDate)) + '''' day(s)'''' ELSE ''''Late: '''' + CONVERT(varchar, datediff(day, OrderDueDate, ShipDate)) + '''' day(s)'''' END END Measurement FROM [digecenter-archive].dbo.tblArchiveData [row] WHERE CONVERT(datetime,OrderDate) BETWEEN @StartDate AND @EndDate ORDER BY CONVERT(datetime,OrderDueDate) for xml auto, elements, xmldata end IF @InternalClientID <>'''''' BEGIN SELECT CriteriaField1 AS FileTrackingNumber, CriteriaField2 as GroupNumber, Filename, OrderDate, OrderDueDate AS TargetShipDate, ShipDate, CASE ISNULL(ShipDate,'''''') WHEN '''''' THEN '''''' ELSE CASE substring(CONVERT(varchar, datediff(day, OrderDueDate, ShipDate)), 1, 1) WHEN ''''0'''' THEN ''''On Time'''' WHEN '''-''' THEN ''''Early: '''' + CONVERT(varchar, datediff(day, OrderDueDate, ShipDate)) + '''' day(s)'''' ELSE ''''Late: '''' + CONVERT(varchar, datediff(day, OrderDueDate, ShipDate)) + '''' day(s)'''' END END Measurement FROM [digecenter-archive].dbo.tblArchiveData [row] WHERE InternalClientID = @InternalClientID AND CONVERT(datetime,OrderDate) BETWEEN @StartDate AND @EndDate ORDER BY CONVERT(datetime,OrderDueDate) for xml auto, elements, xmldata' Where reportid = 'F2F5A12B-67C1-457E-9443-70DDA666942A'
And Im having the hatrdest time getting it to work. How do you tell SQL to use a literal String when doing an update?
October 7, 2005 at 2:38 pm
if the datatype is text, then you cannot use regular update table set column = 'Sometext'
Please read books online, it will give you the information you need to update this column.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_13_8orl.asp
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply