I have a table:
batch_date numeric (8,0)
batch_nmbr numeric (5,0)
batch_ctr numeric (4,0)
col4...
col5...
col6...
I am trying to select data using something like:
select col1, col2
FROM
tablename
WHERE
(BATCH_DATE +
BATCH_NMBR +
BATCH_CTR) >= (:BATCH-DATE
+ :BATCH-NMBR
+ :BATCH-CTR)
this is a mssql cursor embedded in a microfocus cobol program.
I have successfully concatenated and converted all three fields into one field as Follow:
 SELECT   CONVERT(varchar(10),CAST(cc2.ProbationLengthYears AS INT)) + ' Year(s)' + ' ' +   CONVERT(varchar(10),CAST(cc2.ProbationLengthMonths AS INT)) + ' Month(s)' + ' ' +   CONVERT(varchar(10),CAST(cc2.ProbationLengthDays AS INT)) + ' Day(s)'
 The result is:
  2 Year(s) 0 Month(s) 0 Day(s)
Ideally I would like the result to be as follow because the probation months and days are 0:
I have the following 2 fields that are sourced from an Excel spreadsheet
DocNumber - a 10 digit number PostingRow - a number between 1 and 999
I would like to produce a new column that is a concatenation of these two fields, but the PostingRow needs to be a 3 digit number eg. 1000256153-001 ....
This may seem like a simple question to be asking but I’m not the most experienced working with DTS loads and can't understand (don't know) why my load is failing.
I am trying to load in a text file comma separated into a table I have created.
As far as I can se it should be working but it gives me the error:
The number of failing rows exceeds the maximum specified. TransformCopy 'DTSTransformation_4'conversion error: General conversion failure on column pair (source column 'Col004(DBTYPE_STR), destination column 'Result1' (DBTYPE_NUMERIC)).
Help.
Can't figure out what’s going on so any ideas would be useful.
I have a table on SQL with numeric field (18,3), when create a cursoradaptor the become the a numeric field accept only int Value not allow to accept decimal. How to enable the field to accept decimal.
I am attempting to concatenate two numeric type fields together with character data and the query is adding them together. I am assuming I need to convert the ints to a string type but would appreciate some info on the best way to do this...I am sure it's something simple but am not finding much on the web about it.
SELECT vehFacID + '-' + vehID AS vehNew FROM Vehicles
I've generated a report using Reporting Services, and some of my fields are numeric with the value zero. On generating the report this is shown as 0.00 and not 0. Is this something that is easily fixed?
If I create an index on a field in SQL Server, what will be the most efficient (fastest) field type to index a field? (This field will be a "Pointer" to a child table that will contain a list of codes, and their description.) Would a Numeric field be quicker than a VarChar field? VarChar would make it easier for a Human to decipher the raw records. (For example, if I used a numeric the code would be 42 or 47, while the VarChar could be 'savings' or 'checking'.) Basically I will have the following "Master" table: FieldType --------- IDInt NameVarChar StatusInt -or- VarChar Customer_TypeInt -or- VarChar If Customer_Type is a code that can be looked up in another table, and I index that field, would I want the "Code" to be an Int or VarChar? SQL: Select * From Master Where Customer_Type = <42> or <'savings'> My Where clause would depend on the field type. Thank you, Bryan
I have 2 varchar fields on MS 2005 table First field is date and format is 080118(YYMMDD) and second is salary field like 00002000(positive) and 00002000- (negative) how can I move them to date and numeric fields on another table....
I'm attempting to use the isnull function to convert null values in a column to a blank. It works on the alpha field but not the numeric and I was wondering what can be done for numeric field conversion.
when I run below query I got Error of Arithmetic overflow error converting numeric to data type numeric declare @a numeric(16,4)
set @a=99362600999900.0000
The 99362600999900 value before numeric is 14 and variable that i declared is of 16 length. Then why this error is coming ? When I set Length 18 then error removed.
I'm getting the above when trying to populate a variable. The values in question are : @N = 21 @SumXY = -1303765191530058.2251000000 @SumXSumY = -5338556963168643.7875000000
When I run, SELECT (@N * @SumXY) - (@SumXSumY * @SumXSumY) in QA I get the result OK which is -28500190448996439680147097583285.072256 ie 32 places to left of decimal and 6 to the right When I try the following ie to populate a variable with that value I get the error - SELECT R2Top = (@N * @SumXY) - (@SumXSumY * @SumXSumY)@R2Top is NUMERIC (38, 10)
I have the Excel Connection Manager and Source to read the contents from an Excel file. For some reason couple of numeric fields from the Excel worksheet are brought over as nulls even though they have a value of 300 and 150. I am not sure why this is happening. I looked into the format of the fields and they are set to General in Excel, I tried setting them to numeric and that did not help.
All the other content from the excel file is coming thru except for the 2 numeric fields.
I tried to bring the contents from the excel source to a text file in csv format and for some reason the 2 numeric fields came out as blank.
Any inputs on getting this addressed will be much appreciated.
I need to concatenate two date fields so they appear in a drop-down list like this: 8:00 AM - 10:00 AM I'm using MS SQL 2005 and my query looks like this: SELECT ClinicTimesID, ClinicID, (CTStartTime + ' - ' + CTEndTime) AS TimeSlot FROM Clinics_Times WHERE (ClinicID = 1) and I get this error: Msg 242, Level 16, State 3, Line 1 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. Can I strip out the date so that only the time appears or is it even possible to concatenate these fields? If so, how? Thanks!
Hello all, I'm trying to combine two columns of data into a third column using a formula on the thrid column. Each of the columns could contain nulls and each of the columns could contain padding after or before the data. I'm trying to use the following formula yet SQL is throwing an error. Can someone provide another set of eyes to check this out? ISNULL(LTRIM(RTRIM([user_Define_4a])),’’) + ISNULL(LTRIM(RTRIM([user_Define_1])),’’) Thanks
I am completely new to SQL and I have some syntax questions. I am trying to concatenate 4 fields and some padded constants to form a new key field to perform joins. The result should be a twelve character field without spaces. My problem is that the current fields use spaces as place holders and I need to replace the spaces with ‘0’.
1st ‘0’ (constant) 2nd, 3rd, and 4th, from [RD_ID] (without the suffix) 5th and 6th from [RD_ID] suffix or replace spaces with ‘00’ 7th 1 or 2 from [RDWY_ID] 8th Z from [MLGE_TYPE] or replace space with ‘0’ 9th 1 – 9 from [OVLAP_MLGE_CD] or replace space with ‘0’ 10th ‘S’ (constant) 11th ‘0’ (constant) 12th ‘0’ (constant)
Results should resemble 0001CQ100S00 or 000100100S00
Col1 Col2 Col3 --------------------------------------------------------------------------- Andrews S 93845877712 P Sylvia 9999876669 J Bill K 7657654677 L
I need the output like this Col1 Col2 Col3 -------------------------------------------------------------------------- AndrewsS 93845877712 P Sylvia 99999876669 J BillK 76576546677 L
The character on the left of Col2 has to be joined to Col1 and character on the right of col2 has to be joined to Col3. Can anybody suggest a query for this.
I am using ODBC to connect SQL Server 2000 + SP4 server runnig on Windows 2003 standard edition Server +SP1. But, sporadically my application server connectivity to DB fails and i receive the following error messages
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (WrapperWrite()). [Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation.) In D:de.cpp 702 Apr 12 2006 20:34:47 [Microsoft][ODBC SQL Server Driver]Communication link failure) In D:de.cpp 702 Apr 12 2006 20:34:47 20071017 08:23:10 TID (00000ff0) Sev (3) Err (0) Msg (Read failure. General SQL error.
Strange thing is that i receive this error only sporadically, Please Please advice !
Hi All, I've been trying to create a dynamic query using the 'Like' clause and '%'. my code snippet looks like this: while (@@FETCH_STATUS = 0)begin set @likeString = @likeString + ' item_Text LIKE ''%'+@word+'%'' OR ' fetch next from theLike into @word end
-- strip off last ORset @likeString = ltrim(rtrim(substring(@likeString, 0, (len(@likeString) - 3))))-- ================================================-- create query to find keywords in the index and store in temp table-- ================================================set @query = 'INSERT into #resulttable (itemcount, item_id) SELECT COUNT(d.item_id), d.item_id FROM tp_index_details AS d INNER JOIN tp_index ON d.idx_id = tp_index.idx_id 'set @query = @query +' WHERE (d.idx_id IN (SELECT idx_id FROM tp_index AS i WHERE ( 'set @query = rtrim(@query) + @likeStringset @query = @query + ' ) AND (subscription_id = 1000))) GROUP BY d.item_id ORDER BY d.item_id DESC' The problem is the @query string gets truncated. My question is how to get the quotes around '%' variables to work in a string? Thanks for any help! regards Davej
Hi all - I have posted inquiries on this rather vexing issue before, so I apologize in advance for revisting this. I am trying to create the code to add the parameters for two CheckBoxLists together. One CheckBoxList allows users to choose a group of Customers by Area Code, the other "CBL" allows users to select Customers by a type of Category that these Customers are grouped into. When a user selects Customers via one or the other CBL, I have no problems. If, however, the user wants to get all the Customers from one or more Area Codes who ALSO may or may not be members of one or more Categories; I have had trouble trying to create the proper SQL. What I have so far:Protected Sub btn_CustomerSearchCombined_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_CustomerSearchCombined.Click Dim CSC_SqlString As String = "SELECT Customers.CustomerID, Customers.CustomerName, Customers.CategoryID, Customers.EstHours, Customers.Locality, Category.Category FROM Customers INNER JOIN Category ON Customers.CategoryID = Category.CategoryID WHERE " Dim ACItem As ListItem Dim CATItem As ListItem For Each ACItem In cbl_CustomersearchAREA.Items If ACItem.Selected Then CSC_SqlString &= "Customers.AreaCodeID = '" & ACItem.Value & "' OR " End If Next CSC_SqlString &= "' AND " <-- this is the heart of my problem, I believe For Each CATItem In cbl_CustomersearchCAT.Items If CATItem.Selected Then CSC_SqlString &= "Customers.CategoryID = '" & CATItem.Value & "' OR " End If Next CSC_SqlString = Left(CSC_SqlString, Len(CSC_SqlString) - 4) CSC_SqlString &= "ORDER By Categories.Category" sql_CustomersearchGrid.SelectCommand = CSC_SqlString End SubAny help on this is much appreciated, many thanks --
Hi, I'm trying to mak emy query dynamic such that depending upon certain conditions, the conditions in the WHERE clause change. I have my SP as shown below and it doesn't seem to work correctly this way and seems like it is not even taking it as a condition. Please advise on what is going wrong here. I'm building the @Condition variable dynamically and appending it to the where clause below. Any help wud be greatly appreciated.
Thanks
CREATE PROCEDURE dbo.CabsSchedule_ViewSchedule ( @SiteCode smallint = 0, @YearMonth int = NULL, @ByYearMonth bit = 1 ) AS
DECLARE @tempYearMonth int DECLARE @Condition varchar(1000) SET @tempYearMonth = 0
IF @YearMonth IS NULL OR @YearMonth = 0 BEGIN EXECUTE CabsSchedule_GetYearMonth @tempYearMonth, @YearMonth OUTPUT END
IF @ByYearMonth = 0 BEGIN DECLARE @Year int DECLARE @Month int SET @Year = CAST(SUBSTRING(CAST(@YearMonth AS VARCHAR(6)),1,4) AS INT) SET @Month = CAST(SUBSTRING(CAST(@YearMonth AS VARCHAR(6)),5,2) AS INT) SET @Condition = ' DATEPART ([month], FullDate) = ' + CAST(@Month AS VARCHAR(2)) + ' AND DATEPART ([year], FullDate) = ' + CAST(@Year AS VARCHAR(4)) + ' AND ' END ELSE BEGIN SET @Condition = ' YearMonth = ' + CAST(@YearMonth AS VARCHAR(6)) + ' AND ' END
SELECT BillPeriod = CASE WHEN BillPeriod = 32 THEN 'NB' WHEN BillPeriod = 33 THEN 'Holiday' ELSE Convert(nVarChar(7), BillPeriod) END, WorkDay = CASE WHEN WorkDay = -1 THEN '' WHEN WorkDay = 0 THEN 'Holiday' ELSE Convert(nVarchar(7), WorkDay) END, JulianDate, CalendarDay, CalDayBillRcvd, Remarks, FullDate FROMdbo.CabsSchedule WHERE YearMonth = @YearMonth AND SiteCode = @SiteCode GO
