SQL Server 2012 :: Inconsistent Results When Converting To Time?
Jun 5, 2014
I have a lot of rows of hours, set up like this: 0745, 0800, 2200, 1145 and so on (varchar(5), for some reason).
These are converted into a smalldatetime like this:
CONVERT(smalldatetime, STUFF(timestarted, 3, 0, ':')) [this would give output like this - 1900-01-01 11:45:00]
This code has been in place for years...and we stick the date on later from another column.
But recently, it's started to fail for some rows, with "The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value".
My assumption is that new data being added in is junk. If I query for these values and just list them (rather than adding a column to convert them also) that's fine, of course. I've checked all the stuffed (but not yet converted - so 11:45 rather than 1145) output to see if it ISDATE(), and it is. There are no times with hours > 23 or minutes greater than 59 either.
If I add the CONVERT in, we see the error message. But here's the oddity, if I place all of the rows into a holding table, and retry the conversion, there is no error. It's this last bit that is puzzling me. Plus I can't see any errors in the hours data that would cause a conversion problem.
I've put the whole of this into a cursor to try to trap the error rows too, but all processes fine. Why would it fail if NOT in a cursor?
I am getting inconsistent results when BULK INSERTING data from a tab-delimited text file. As part of my testing, I run the same code on the same file again and again, and I get different results every time! I get this on SQL 2005 and SQL 2012 R2.
We have an application that imports data from a spreadsheet. The sheet contains section headers with account numbers and detail rows with transactions by date:
AAAA.1234 /* (account number)*/ 1/1/2015 $150 First Transaction 1/3/2015 $24.233 Second Transaction BBBB.5678 1/1/2015 $350 Third Transaction 1/3/2015 $24.233 Fourth Transaction
My Import program saves this spreadsheet at tab-delimited text, then I use BULK INSERT to bring the data into a generic table full of varchar(255) fields. There are about 90,000 rows in each day's data; after the BULK INSERT about half of them are removed for various reasons.
Next I add a RowID column to the table with the IDENTITY (1,1) property. This gives my raw data unique row numbers.
I then run a routine that converts and copies those records into another holding table that's a copy of the final destination table. That routine parses though the data, assigning the account number in the section header to each detail row. It ends up looking like this:
AAAA.1234 1/1/2015 $150 First Purchase AAAA.1234 1/3/2015 $24.233 Second Purchase BBBB.5678 1/1/2015 $350 Third Purchase BBBB.5678 1/3/2015 $24.233 Fourth Purchase
My technique: I use a cursor to get the starting RowID for each Account Number: I then use the upper and lower RowIDs to do an INSERT into the final table. The query looks like this:
SELECT RowID, SUBSTRING(RowHeader, 6,4) + '.UBC1' AS AccountNumber FROM GenericTable WHERE RowHeader LIKE '____.____%'
Results look like this:
But every time I run the routine, I get different numbers!
Needless to say, my results are not accurate. I get inconsistent results EVERY TIME. Here is my code, with table, field and account names changed for business confidentiality.
TRUNCATE TABLE GenericImportTable; ALTER TABLE GenericImportTable DROP COLUMN RowID; BULK INSERT GenericImportTable FROM 'SERVERGeneralAppnameDataFile.2015.05.04.tab.txt' WITH (FIELDTERMINATOR = ' ', ROWTERMINATOR = '', FIRSTROW = 6) ALTER TABLE GenericImportTable ADD RowID int IDENTITY(1,1) NOT NULL SELECT RowID, SUBSTRING(RowHeader, 6,4) + '.UBC1' AS AccountNumber FROM GenericImportTable WHERE RowHeader LIKE '____.____%'
I need to take a temporary table that has various times stored in a text field (4:30 pm, 11:00 am, 5:30 pm, etc.), convert it to miltary time then cast it as an integer with an update statement kind of like:
Update myTable set MovieTime = REPLACE(CONVERT(CHAR(5),GETDATE(),108), ':', '')
how this can be done while my temp table is in session?
I am trying to calculate the time difference between the value in the row and the min value in the table. So say the min value in the table is 2014-05-29 14:44:17.713. (This is the start time of the test.) Now say the test ends at 2014-05-29 17:10:17.010. There are many rows recorded during that start and end time, for each row created a time stamp is created. I am trying to calculate the elapsed time and have it as a row in the results.
min(timestamp) - timestamp(value in row) = elapsed time for that test where Channel = '273'
Here is the table DDL
CREATE DATABASE SpecTest; USE SpecTest GO
CREATE TABLE [dbo].[Spec1]( [Spec1ID] [int] IDENTITY(1,1) NOT NULL, [Channel] [int] NOT NULL,
I have a linked server named 'Charlie_File' to an Excel Workbook that I set up in SQLServer 2005 Management Studio. The workbook is on my local C drive. Sometimes, I get the results back that I expect when I run the following query;
SELECT * FROM OPENQUERY(Charlie_file, 'SELECT * FROM [Feb$]')
Sometimes, on subsequent runs of the above query, I get the following message;
Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "MSDASQL" for linked server "Charlie_file" reported an error. The provider did not give any information about the error. Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "Charlie_file".
There seems to be about a minute or so of a delay before the query will run correctly on subsequent attempts. Is there a connection issue here where a connection blocks subsequent attempts to select the data within a specific time span?
HiI have an oddity. If I run a piece of SQL:SELECT EmployeeNo, MailToFROM ST_PPS.dbo.Employeewhere AddedOn BETWEEN '01-jan-2006' and '01-feb-2006'AND MailTo NOT IN ( '3', 'x')order by MailToI get the resultsEmployeeNo MailTo----------- ------608384 1606135 1608689 1609095 1607163 1606165 1606472 1608758 1.....for 2594 rowsIf I create a stored procedure with the same SQL:-CREATE PROCEDURE dbo.PPS_testASSELECT EmployeeNo, MailToFROM ST_PPS.dbo.Employeewhere AddedOn BETWEEN '01-jan-2006' and '01-feb-2006'AND MailTo NOT IN ( '3', 'x')order by MailToGOand run it:-EXEC PPS_testI get three extra rowsEmployeeNo MailTo----------- ------607922 NULL606481 NULL605599 NULL606316 1608871 1607427 1608795 1.....for 2597Does anyone know what is happening here? It appears that the clause:-MailTo NOT IN ( '3', 'x')excludes NULL in raw SQL, but includes NULL (correctly I think) in astored procedure.Chloe CrowderThe British Library
I am building a report with a recursive hierarchy for drill-down purposes. The hierarchy is built by querying a SSAS OLAP cube and defining a details grouping for the table/matrix.
Every time I run the report one or more of the leaf members in the recursive hierarchy "jumps" up to the highest level. First I thought that this may be due to the fact that the leafs parents are not part of the returned dataset. However, the queries makes sense and the "offending" members does never contain any data (while the query should return only non empty members) which is why this is a very strange behavior. Furthermore, the "offending" member differs between different executions of the report, despite the fact that the parameters is exactly the same and the cube is untouched between executions.
I am actually pressing "View Report", waiting for the report to execute and when I press "View Report" again, the returned datasets seem to differ, yielding different "offending" members in the report.
When I run the queries individually in the Data-tab in BIDS, the returned datasets are always the same. Execution caching is turned off for the report.
Checking against SSRS's ExecutionLog, the RowCount for consecutive executions with the exact same parameters differ. For example, RowCount: 3094 3080 3079 3088 3087
Why does SSRS behave such inconsistently? Any tips or tricks?
I am running SQL Server 7.0 on NT 4.0. I have created a simple query:
SELECT SUM(month1) As total_month1 FROM eac_manload WHERE project_number = 8800 and dept IN (50,51,52,55,57,60,61,62,63,64,65,68,69)
I first time I run the query I get the correct result. Subsequent times that I run the query the result is 1 record with a Null value. The data has not changed. If I stop MSSQLSERVER and restart the service I get the correct result the first time and the Null value each time thereafter. Anybody out there with any idea of what is going on here? Any help will be appreciated!!
I have a stored procedure (see below), in which I would like to check if the create an identity column and make it a primary key succeeded. I check @@error after the exec statement. This used to pick up an error if the table already had an identity column. It has stopped doing that. Why? And, if this is not the way to capture the error after the exec statement, how do I do it?
CREATE PROCEDURE rasp_test3 /* Written by Judith Farber Abraham this procedure loops thru sysobjects looking for user tables. If a user table, does it have a primary key? If not, add an identity column to table and make it a primary key */ --would like to have sp in main db but use from all three @fixDB nvarchar(50)--the db to which to add PKs
AS Declare @TableName varchar(50) Declare @TableID int Declare @Msg varchar (50) Declare @ColumnName varchar(50) Declare @IndexName varchar(50) Declare @MyCursor nvarchar(500) declare @MyCursorC nvarchar(500) declare @CName sysname --Set @Msg = "********* Finished adding Ident fields *************" /* */ /* do for all user tables ( xtype = u ) */ set @Mycursor = N'Declare SysCursor cursor for select Name, ID from ' + @fixdb +'.dbo.sysobjects where xtype = "u"' execute sp_executesql @mycursor open syscursor Fetch next from SysCursor into @TableName, @TableID /* -1 = no record; -2 = row deleted; 0 = got a row */ While (@@Fetch_status <> -1) Begin If (@@Fetch_status <> -2) Begin /* have a user row (table) */ /* */ set @ColumnName = @TableName + 'ID' set @IndexName = 'PK_' + @columnName
--only add ident and PK if no primary key in table If not exists (Select * from Sysobjects where Parent_obj = @TableID and xtype = 'PK')
--add an identity column to user table and make it a Primary key
EXEC ('ALTER TABLE ' + @tablename + ' ADD ' + @columnName + ' INT IDENTITY CONSTRAINT ' + @IndexName + ' PRIMARY KEY ' ) -- Begin --if error, assume already ident column, so find column name & make PK print @@error if @@error <> 0 print "jerror occured" --set @MycursorC = N'Declare SysCursorC cursor for SELECT c.name --FROM syscolumns c, sysobjects o --WHERE ((c.id = o.id) AND (c.status = 128)) AND (o.name = ' + @tablename + ')' --execute sp_executesql @mycursorC --Open SyscursorC --Fetch next from SysCursorC into @CName --print @cname --close syscursorc --deallocate syscursorc --Exec ('ALTER TABLE ' + @tablename + ' ADD ' + @columnName + ' INT IDENTITY CONSTRAINT ' + @IndexName + ' PRIMARY KEY ' ) --select @cname=c.name --print c.name End
End Fetch next from SysCursor into @TableName, @TableID End --Print @Msg Close SysCursor Deallocate SysCursor Return
I'm testing some code to look up values from my database and update a specific field when certain conditions are met. I'm having trouble with some code that is giving me the results I expect when I submit one set of parameters, but is not finding anything in the database for another set, when I know the data exists.
I put the response.write statements and loops in so I could verify the functionality.
Here's what it produces:
out 452 ret 453 origin 7/29/2006 8:00:00 AM return 7/29/2006 12:05:00 PM OFNO 109 450
Here's what it should produce:
out 452 ret 453 origin 7/29/2006 8:00:00 AM return 7/29/2006 12:05:00 PM OFNO 109 450 RFNO 110 451
If I do this in query analyzer:
Code:
select flightid, flightno, departuretime from flightschedules where flightid > 449 and flightid < 454
this is what I get from the database:
flightid flightno departuretime origin destination 4521092006-07-29 08:00:00 A C 4501092006-07-29 08:00:00 A B 4531102006-07-29 12:05:00 C A 4511102006-07-29 13:15:00 B A
What I'm trying to do is look up the chosen flight, then find the flight with the matching origin/destination (the other flight leg) on the same day.
I can't figure out why it's working for one set of parameters and not for the other.
Firstly may I say that the sproc I am having problems with and the service that calls it is inherited technical debt from an unsupervised contractor. We are not able to go through a rewriting process at the moment so need to live with this if possible.
Background
We have a service written in c# that is processing packages of xml that contain up to 100 elements of goods consignment data. In amongst that element is an identifier for each consignment. This is nvarchar(22) in our table. I have not observed any IDs that are different in length in the XML element.
The service picks up these packages from MSMQ, extracts the data using XPATH and passes the ID into the SPROC in question. This searches for the ID in one of our tables and returns a bool to the service indicating whether it was found or not. If found then we add a new row to another table. If not found then it ignores and continues processing.
Observations
The service seems to be dealing with a top end of around 10 messages a minute... so a max of about 1000 calls to the SPROC per minute. Multi-threading has been used to process these packages but as I am assured, sprocs are threadsafe. It is completing the calls without issue but intermittently it will return FALSE. For these IDs I am observing that they exist on the table mostly (there are the odd exceptions where they are legitimately missing). e.g Yesterday I was watching the logs and on seeing a message saying that an ID had not been found I checked the database and could see that the ID had been entered a day earlier according to an Entered Timestamp.
So the Sproc...
USE [xxxxxxxxxx] GO
SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
[Code]....
So on occasions (about 0.33% of the time) it is failing to get a bit 1 setting in @bFound after the SELECT TOP(1).
The only suggestions I can make have been...
change @pIdentifier nvarchar(25) to nvarchar(22) Trim any potential blanks from either side of both parts of the identifier comparison Change the SELECT TOP(1) to an EXISTS
The only other thought is the two way parameter direction in the C# for the result OUTPUT. Not sure why he did it that way or what the purpose is.
I have been unable to replicate this using a test app and our test databases. Has observed selects failing to find even though the data is there, like this before?
I have a query that joins two large partitioned tables and depending on the values in the where clause, I can get dramatically different performance results.
The first query completed in around 7s and has 47,000 logical reads.
select mo.monitor_id,
mo.site_id,
mo.testtime,
sum(mo.NumBytes),
sum(mo.DNSTime),
sum(mo.ConnectTime),
sum(mo.FirstByteTime),
sum(mo.ContentTime),
sum(mo.RelocTime)
from monitor_raw mr(nolock), monitor_object mo(nolock)
where mr.monitor_id in (5339, 5341, 5342, 943842, 943866)
and mr.testtime between 'Oct 31 2007 3:00:00:000PM' and 'Nov 30 2007 3:00:00:000PM'
and mo.returncode = 200
and mr.site_id in (101,102,105,109,110,112,115,117,119,122,126,151,132,139,129,135,121,138,143,142,159,148,128,171,176,177,178,111,113,116,118,120,127,133,131,130,174,179,185,205,200,202,203,204,210,211,208,209,212,213,216,199,214,224,225,229,230,232,235,241,245,247,250,254,261,267,264,265,266,268,269)
and mr.escalationlevel = 0
and mr.monitor_id = mo.monitor_id
and mr.testtime = mo.testtime
and mr.site_id = mo.site_id group by mo.monitor_id, mo.site_id, mo.testtime
The second query takes 188s to complete and has 1.8m logical reads. The only difference between the two is the value of the monitor_ids in the where clause.
select mo.monitor_id,
mo.site_id,
mo.testtime,
sum(mo.NumBytes),
sum(mo.DNSTime),
sum(mo.ConnectTime),
sum(mo.FirstByteTime),
sum(mo.ContentTime),
sum(mo.RelocTime)
from monitor_raw mr(nolock), monitor_object mo(nolock)
where mr.monitor_id in (152682, 5339, 5341, 5342, 268080)
and mr.testtime between 'Oct 31 2007 3:00:00:000PM' and 'Nov 30 2007 3:00:00:000PM'
and mo.returncode = 200
and mr.site_id in (101,102,105,109,110,112,115,117,119,122,126,151,132,139,129,135,121,138,143,142,159,148,128,171,176,177,178,111,113,116,118,120,127,133,131,130,174,179,185,205,200,202,203,204,210,211,208,209,212,213,216,199,214,224,225,229,230,232,235,241,245,247,250,254,261,267,264,265,266,268,269)
and mr.escalationlevel = 0
and mr.monitor_id = mo.monitor_id
and mr.testtime = mo.testtime
and mr.site_id = mo.site_id group by mo.monitor_id, mo.site_id, mo.testtime
The two tables have clustered indexes on monitor_id, testtime and site_id. Comparing the execution plan, I can see why there is such a difference in performance. The second query performs a clustered index seek on the monitor_object table starting at the lowest monitor_id, testtime & site_id through the highest monitor_id, testtime & site_id. The first query performs a clustered index seek where the monitor_id, testtime and site_id equals the same values from the monitor_raw table.
My question is, how can I force the second query to use the same execution plan as the first so that I can get better performance?
One possible workaround that I could use is to execute five individual queries, one for each monitor_id and then union the results together but this would require significant code changes to my stored procs.
I am getting inconsistent results when BULK INSERTING data from a tab-delimited text file. As part of my testing, I run the same code on the same file again and again, and I get different results every time! I get this on SQL 2005 and SQL 2012 R2.We have an application that imports data from a spreadsheet. The sheet contains section headers with account numbers and detail rows with transactions by date:
AAAA.1234 /* (account number)*/ 1/1/2015 $150 First Transaction 1/3/2015 $24.233 Second Transaction
BBBB.5678 1/1/2015 $350 Third Transaction 1/3/2015 $24.233 Fourth Transaction
My Import program saves this spreadsheet at tab-delimited text, then I use BULK INSERT to bring the data into a generic table full of varchar(255) fields. There are about 90,000 rows in each day's data; after the BULK INSERT about half of them are removed for various reasons. Next I add a RowID column to the table with the IDENTITY (1,1) property. This gives my raw data unique row numbers.
I then run a routine that converts and copies those records into another holding table that's a copy of the final destination table. That routine parses though the data, assigning the account number in the section header to each detail row. It ends up looking like this:
AAAA.1234 1/1/2015 $150 First Purchase AAAA.1234 1/3/2015 $24.233 Second Purchase BBBB.5678 1/1/2015 $350 Third Purchase BBBB.5678 1/3/2015 $24.233 Fourth Purchase
My technique: I use a cursor to get the starting RowID for each Account Number: I then use the upper and lower RowIDs to do an INSERT into the final table. The query looks like this:
SELECT RowID, SUBSTRING(RowHeader, 6,4) + '.UBC1' AS AccountNumber FROM GenericTable WHERE RowHeader LIKE '____.____%'
Results look like this:
But every time I run the routine, I get different numbers! my results are not accurate. I get inconsistent results EVERY TIME.Here is my code, with table, field and account names changed for business confidentiality.This is a high profile project at my company;
TRUNCATE TABLE GenericImportTable; ALTER TABLE GenericImportTable DROP COLUMN RowID; BULK INSERT GenericImportTable FROM 'SERVERGeneralAppnameDataFile.2015.05.04.tab.txt' WITH (FIELDTERMINATOR = ' ', ROWTERMINATOR = ' ', FIRSTROW = 6)
I'm trying to go deeper into SQL Server's XML support. When looking at a recent forum post, I got curious to see if I could achieve the same result with XPath and FLOWR. Here's the data I'm working with:
select data.mynodes.value('@Name', 'varchar(50)') Name from #data cross apply myXMLData.nodes('/order/Product/Drops/Drop[@Number="1"]/Area') data(mynodes) Results: Name 43001PBOX
[Code] .....
Here's my first attempt at a FLWOR expression. Note that doesn't produce the same results.
select myXmlData.query(' for $drop in /order/Product/Drops/Drop for $Name in $drop/Area/@Name where $drop/@Number=1 return data($Name) ') from #data Results: 43001PBOX 43001R001 43001R002 43023PBOX 43023R001 43023R002 43031PBOX 43031R001
How to build a FLOWR-based query that produces the same result as the pure XPath one?
I am using a custom sql query to import data into Tableau. Problem is I need to change the varchar column data in SQL currently returning 18/01/2014 08:35:13 as a format into the date format 05/21/2014 3:55:51 PM before I can use it.
I have a column name DateofRecord and it is nvarchar type..all the values in this column are like this
"04/24/2013' "05/01/2014"...etc...
My requirement is to convert this column into Datetime ?
I tried so many ways using cast and convert functions like cast(dateofrecord,datetime) or like convert(datetime,replace(DateofRecord,'"','''')) ..it didnt worked..
SELECT ROUND ('6.465',2) --- result 6.46 and SELECT ROUND (6.465,2) --- result 6.47 with
It's because you're relying on an implicit conversion from a string to a decimal data type which SQL server will do to 2 decimal places by default...
Alright:
SELECT ROUND (CONVERT(DECIMAL(3,2),'6.465'),2) --- result 6.47 Now please explain this: SELECT ROUND('0.285',2) -- 0.28 SELECT ROUND(0.285,2) -- 0.29 SELECT ROUND (CONVERT(DECIMAL(3,2),'0.285'),2) --- result 0.29 The string value does not seem to be converted to decimal with 2 decimal places.
MS is on the safe side with mentioning the last digit is always an estimate But because the result of the estimate is always the same, I would like to know:
* how is a string value exactly implicitly converted?
* how exactly does the estimation work, that in case of doubt rounds a value up or off?
Within in Visual Studio 2012 solution, I have several projects, one of which is a Database project. I am defining several tables. The one in question is:
CREATE TABLE [dbo].[tblKppHierarchyPcl] ( [ID] NUMERIC(18,0) NOT NULL, [Name] VARCHAR(500), [PartStructureKey] NUMERIC(18,0) NOT NULL, [PartNumber] VARCHAR(500) NOT NULL, [ParentPartNumber] VARCHAR(500) NULL,
[code]...
Error SQL72014: .Net SqlClient Data Provider: Msg 245, Level 16, State 1, Line 76 Conversion.failed when converting the varchar value 'Coolant Quick Disconnect' to data type int.So it has a problem with inserting 'Coolant Quick Disconnect' into the Name column. The Name column is CLEARLY a varchar column but somehow it thinks it's an int column.
I have been trying to convert datetime but keep getting this error(Conversion failed when converting date and/or time from character string.It works just fine if I don't use execute sp_executesql,.
<code> DECLARE @tablename AS nvarchar(max) DECLARE @SQLQuery AS NVARCHAR(MAX) DECLARE @ParameterDefinition AS NVARCHAR(100) DECLARE @startdate datetime
I am working on a code that will convert the query output into a html query output. That is the output of the query will be along with html tags so that we can save it as a html file.
The stored procedure that i have used is downloaded from symantec website and is working fine. Below is the code of that stored procedure.
/****** Object: StoredProcedure [dbo].[sp_Table2HTML] Script Date: 12/21/2011 09:04:30 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_Table2HTML] ( @TABLENAME NVARCHAR(500), @OUTPUT NVARCHAR(MAX) OUTPUT,
[Code] ....
The code works fine and i am able to get the output with html tags. The problem occurs when i insert stylesheet in the code. I tried to enforce styles using a stylesheet for the table returned in my sql code, so that it will look good. below is the stylesheet code that i inserted between <head> and </head> tags.
If I run the procedure without the style sheet code, it works fine. but when i run the procedure with style sheet code i am getting the below errors.
Msg 105, Level 15, State 1, Line 98 Unclosed quotation mark after the character string '</table></body><'. Msg 102, Level 15, State 1, Line 98 Incorrect syntax near '</table></body><'.
[Code] .....
I checked the code and i am not able to find what is the mistake. I tried changing the quotation mark but it didn't worked.
below is my statement to get data from one column (VARCHAR) from table SUPPLY_ITEM_01
SELECT @@SERVERNAME as ServerName,DB_NAME() AS DatabaseName, SUM(CASE WHEN CHARINDEX (CHAR(013) , supplydetail) > 0 THEN 1 ELSE 0 END) AS TotalCHAR013, SUM(CASE WHEN CHARINDEX (CHAR(012), supplydetail ) >0 THEN 1 ELSE 0 END)AS TotalCHAR012, SUM(CASE WHEN CHARINDEX (CHAR(010), supplydetail ) >0 THEN 1 ELSE 0 END) AS TotalCHAR010, SUM(CASE WHEN CHARINDEX (CHAR(009),supplydetail ) >0 THEN 1 ELSE 0 END) AS TotalCHAR009 FROM
[code]...
I need to get result from all the tables and all the columns which has bad data including schemaName, table name and column name in result.
I have created a view thats pulling data from two different tables to combine them into one report.
table 1 lists the client code and table 2 lists the client partner and they're linked by a variable.
When running the report the result shows the client codes with their respective partner however any client codes that didn't have a partner are not displaying in the report and I need all client codes to be displayed even if there's no partner.
Is there a way I can make this display all results and if the client partner doesn't exist for it to still display as 'Null' for the partner but still display the client code?
Script:
SELECT TOP (100) PERCENT C.cltCode AS ClientCode, C.cltSortName AS SortName, C.cltTerminationDate AS [Term date], dbo.vcltAttrib6.ainTVal AS Department, C.objInstID AS ClientID FROM dbo.cdbClient AS C INNER JOIN dbo.vcltAttrib6 ON C.objInstID = dbo.vcltAttrib6.ainObjectInstID GROUP BY C.cltSortName, C.cltTerminationDate, dbo.vcltAttrib6.ainTVal, C.objInstID, C.cltCode ORDER BY ClientID
I want to select all the records, and them them be in alphabetical order first by lastname, then by firstname, then by address. HOWEVER, and this is the tricky part, I want to group names together that have the same address. So, in this example, I want the results to be in this order:
HallC6309 N Olive HallP6309 N Olive <---- grouped with the C record because they have the same address HallE5488 W Catalina <---- back to alphabetical by first name HallJ7222 N Cocopas
I am getting a tab character at the end of my query fields, been trying various things to fix, such as using the replace function below but still i get the tabs!
select CAST(REPLACE(NAMEALIAS,CHAR(9),'')AS CHAR(40)) + ',' as PRODNAME, CAST(REPLACE(ISNULL(GLOBALTRADEITEMNUMBER,0),CHAR(9),'')AS CHAR(18))as EANNO,LTRIM(cast(ISNULL(GLOBALTRADEITEMNUMBER,0) as char(18))) as KONSEAN ,LTRIM(CAST(I.ITEMID AS CHAR(8))) AS PRODCODE,'00' from INVENTTABLE I LEFT JOIN INVENTITEMGROUPITEM IG ON I.ITEMID = IG.ITEMID
I need to get this data into an SQL table in the following form so I can use it to further manipulate the data and update several other tables. I am thinking that UNPIVOT or CROSS APPLY might be the way to go, but am not sure how to code it.
I am doing a Case statement to create a unified field for account type and vendor, in the code below I am receiving the error in the subject, because the account numbers have alpha characters in the string, I need to make them as OTHER if the first 2 left chars are Alpha, I thought how I have ISNUMERIC would do that but I am still getting the error.
I am also including example of how the account_numbers are formatted.
Am converting varchar field to float and summing using group by and next inserting to varchar field(table).
while inserting float value it is converting to exponential ex:1.04177e+006 but if i execute only select statment actual float value will get display ex:1041765.726
My question is why it is converting while inserting ? and how to avoid it.
I have a view I've created which displays client sortname, partner and date added which displays 7 results.
When I add another table to this view to display the Industry it then only gives me 4 results as the other 3 results have no Industry instead of giving me the 7 results and showing the Industry column as empty for the other 3.
Is there a way I can make it show all 7 results and havethe column where the industry is empty display the other results instead of not displaying any results at all for them?
Script: SELECT dbo.cdbClient.cltSortName AS ClientName, dbo.vcltAttrib4.ainTVal AS ClientPartner, dbo.vcltAttrib422.ainDVal AS [Date Added], dbo.cdbAttribInst.ainTVal AS Inudstry FROM dbo.cdbClient LEFT OUTER JOIN dbo.cdbObject ON dbo.cdbClient.cltCategoryID = dbo.cdbObject.objID LEFT OUTER JOIN
[Code] ....
In the above script the cbdAttribInst table has the Industry column I need which is 'ainTVal'...
