SQL 2012 :: Query Returns 13864 On A Varchar Columns
Mar 24, 2015
We have a customer that is running SQL2012 and we are seeing a weird result on a query when we run it on their db. It is based off of a table that has about 30 columns but in this case we only care about 2 of them.
[Number] [varchar](15) NOT NULL
[Person_ID] [varchar](12) NULL
Here is the query we are doing:
Select Number,Person_ID From TableName where LP='ABC123'
The result I get back is the following:
Number:1
Person_ID:13864
The Person_ID should be a result of another table that created that Person_ID but it doesn't exist in that table. So we do not know where that 13864 is coming from. When we open that record through our application it shows Nothing for the Person_ID in that field.
When we do this query on our copy we get back
Number:1
Person_ID:
Which is exactly what we should see as the result.
Could there be a sql server setting that is set on their server that could possibly be given us back 13864 for a NULL value?
View 2 Replies
ADVERTISEMENT
Feb 26, 2014
I know that if I have an nvarchar column I can use an equality like = N'supersqlstring' so it doesn't implicit cast as a varchar, like if I were to do ='supersqlstring'. And then I'll be a big SQL hero and all my stored procedures will run before a millisecond can whisper.
But if I'm comparing an nvarchar column to a varchar column, is it better to cast the varchar 'up' to an nvarchar or cast the nvarchar 'down' to a varchar?
For instance:
cast(a.varchar as nvarchar(100)) = an.nvarchar
or
cast(an.nvarchar as varchar(100)) = a.varchar
Leaving aside non-matching, like (at least I don't think) that SQL considers the varchar n to be equal to the nvarchar ń, what's the best way to handle this?
Pretend for a moment that each column contains a mixed letter and number ID with no accented or wiggly-squiggly Unicode characters; it's just designs clashing.
Is there a performance hitch doing it one way or another? Should I use COLLATE? Should one of the columns be altered?
View 8 Replies
View Related
Aug 19, 2014
i want to obtain two columns in a query but i don´t know how to.ex.
col1 col2
1 A
1 B
2 A
1 C
I need something like this.
col1 col2
1 A, B, C
2 A
Col1 = Table Z
Col2 = Table Y
View 6 Replies
View Related
Aug 5, 2015
I want to know if it is possible to do the following;
I have patients that may have been transferred to different locations(see below)
location_name enter_time
4D04 2/9/15 2:35
4D14 2/9/15 8:44
RECOVERY 3 2/9/15 9:08
4D13 2/9/15 17:36
4D14 2/10/15 2:02
i know i can do a min max to get my first and last values. I want to label the columns something like
1st location, 2nd location, 3rd location, 4th location, discharge location.
there could be 1 location or 20.
is there a way to do this?
i can do a temporary table and then an update query to add the values to those columns.
just not sure how to get the next value and then the next etc.
View 9 Replies
View Related
Mar 26, 2015
I currently have data stored in a temporary table and I would like to transpose the data into a better format. I would like for the query to be dynamic since one of the tables currently has over 500 columns.
The attached file provides an example of the table structure along with sample data. Below the first set of data is the desired final format.
View 2 Replies
View Related
Aug 15, 2007
I created very simple table with 3 columns and one is varchar(max) datatype
When i insert records thru VC++ ADO code i am getting this error
Exception Description Multiple-step OLE DB operation generated errors. Check e
ach OLE DB status value, if available. No work was done. and Error Number:: -2147217887
ODBC Driver: SQL Native Client
SQL server 2005
Table
CREATE TABLE [dbo].[RAVI_TEMP](
[ID] [int] NULL,
[Name] [varchar](max) NULL,
[CITY] [varchar](50) NULL
)
VC++ code
#include "stdafx.h"
#include <string>
#include <strstream>
#include <iomanip>
int main(int argc, char* argv[])
{
try
{
HRESULT hr = CoInitialize(NULL);
_RecordsetPtr pExtRst = NULL;
_bstr_t bstrtDSN, bstrtSQL;
bstrtDSN = L"DSN=espinfo;UID=opsuser;PWD=opsuser;";
bstrtSQL = L"SELECT * FROM RAVI_TEMP";
_variant_t vartValueID,vartValueNAME,vartValueCITY;
_bstr_t bstrtValueID,bstrtValueNAME,bstrtValueCITY;
pExtRst.CreateInstance(__uuidof(Recordset));
hr = pExtRst->Open(bstrtSQL, bstrtDSN, adOpenDynamic, adLockOptimistic, adCmdText);
hr = pExtRst->AddNew();
bstrtValueID = L"1";
vartValueID = bstrtValueID.copy();
bstrtValueNAME = L"RAVIBABUBANDARU";
vartValueNAME = bstrtValueNAME.copy();
bstrtValueCITY = L"Santa Clara";
vartValueCITY = bstrtValueCITY.copy();
pExtRst->GetFields()->GetItem(L"ID")->Value = vartValueID;
pExtRst->GetFields()->GetItem(L"NAME")->Value = vartValueNAME;
pExtRst->GetFields()->GetItem(L"CITY")->Value = vartValueCITY;
pExtRst->Update();
pExtRst->Close();
}
catch(_com_error e)
{
printf("Exception Description %s and Error Number:: %d",(LPTSTR)e.Description(),e.Error());
return e.Error();
}
return 0;
CoUninitialize();
}
if i use regular SQL ODBC driver, no error but its truncating the data
Adv Thanks for your help
View 1 Replies
View Related
Mar 10, 2014
I'm using SQL 2012 express.. and just recently learned how to code.
I wrote a query and keep receiving this error...
Error converting data type varchar to float.
here's the query code
SELECT SUM(cast(lc as float))
FROM [dbo].[LaborCosts]
WHERE ppty = 'ga'
AND PL = 'allctd ktchn expns'
AND ACCT like 'payroll%'
I am trying to sum up the values in column LC, and realized I have unnecessary quotations marks. How can I eliminate the quotations from the column, and only query the numerical values?
View 2 Replies
View Related
Mar 12, 2015
I have the following query that displays 2 values. I want to add a column with the percentage ([Providers With Security]
/ProviderTotal) * 100
SELECT (SELECT COUNT(DISTINCT NPI) FROM HS140_Rpt_Tmp_ForSummary WHERE Market = s.Market) AS ProviderTotal,COUNT(DISTINCT NPI) AS [Providers With Security]
FROM HS140_Rpt_Tmp_ForSummary s
WHERE s.[Security] = 'Yes'
GROUP BY Market
How can I do this?
View 1 Replies
View Related
Feb 20, 2014
Is there a way to query all the columns in a database that have the data type as Integer.
View 9 Replies
View Related
Jul 20, 2005
Is there a function that compares two columns in a row and will returnthe highest of the two values? Something like:Acct Total_Dollars Collected Total_Dollars_Due11233 900.00 1000.00Declare @Value as moneyset @Value=GetHighest(Total_Dollars_Collected,TotalDol lars_Due)Print @ValueThis function will return 1000.00 or the Total_dollars_Due??Is there such a creature???
View 2 Replies
View Related
Apr 21, 2015
Using below script to export the select statement result to .xls
declare @sql varchar(8000)
select @sql = 'bcp "select * from Databases..Table" queryout c:bcpTom.xls -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql
But result is not exporting in seperate tabs, all 4 column details are exporting in single cell.
how to export the data in columns to separate tabs in excel.
View 2 Replies
View Related
Nov 23, 2006
/*Subject: How to build a procedure that returns differentnumbers of columns as a result based on a parameter.You can copy/paste this whole post in SQL Query Analyzeror Management Studio and run it once you've made surethere is no harmful code.Currently we have several stored procedures which finalresult is a select with several joins that returns manycolumns (150 in one case, maybe around 50 the average).We have analyzed our application and found out that mostof the time not all the columns are used. We haveidentified 3 different sets of columns needed indifferent parts of the application.Let's identify and name these sets as:1- simple set, return the employee list for example2- common set, return the employee information (whichinclude the simple set)3- extended set, return the employee information (whichinlude the common set which itself includes the simpleset) + additional information from other tables, maybeeven some SUM aggregates and so on (I don't know forexample, how much sales the employee did so far).So the bigger sets contain the smaller ones. Please keepreading all the way to the bottom to better understandtechnically what we are trying.Here is a code sample of how our current procedureswork. Please note that the passing parameter we can eitherpass a Unique Identifier (PK) to retrieve a single record,or if we pass for example -1 or NULL we retrieve all theemployee records.*/create table a ( apk int primary key, af1 int, af2 int, af3 int, af4int, af5 int, af6 int)create table b ( bpk int primary key, bf1 int, bf2 int, bf3 int, bf4int, bf5 int, bf6 int)create table c ( cpk int primary key, cf1 int, cf2 int, cf3 int, cf4int, cf5 int, cf6 int)create table d ( dpk int primary key, df1 int, df2 int, df3 int, df4int, df5 int, df6 int)insert a values (1,1111,1112,1113,1114,1115,1116)insert a values (2,1211,1212,1213,1214,1215,1216)insert a values (3,1311,1312,1313,1314,1315,1316)insert a values (4,1411,1412,1413,1431,1415,1416)insert a values (5,1511,1512,1513,1514,1515,1516)insert a values (6,1611,1612,1613,1614,1615,1616)insert b values (1,2111,2112,2113,2114,2115,2116)insert b values (2,2211,2212,2213,2214,2215,2216)insert b values (3,2311,2312,2313,2314,2315,2316)insert b values (4,2411,2412,2413,2431,2415,2416)insert b values (5,2511,2512,2513,2514,2515,2516)insert b values (6,2611,2612,2613,2614,2615,2616)insert c values (1,3111,3112,3113,3114,3115,3116)insert c values (2,3211,3212,3213,3214,3215,3216)insert c values (3,3311,3312,3313,3314,3315,3316)insert c values (4,3411,3412,3413,3431,3415,3416)insert c values (5,3511,3512,3513,3514,3515,3516)insert c values (6,3611,3612,3613,3614,3615,3616)insert d values (1,4111,4112,4113,4114,4115,4116)insert d values (2,4211,4212,4213,4214,4215,4216)insert d values (3,4311,4312,4313,4314,4315,4316)insert d values (4,4411,4412,4413,4431,4415,4416)insert d values (5,4511,4512,4513,4514,4515,4516)insert d values (6,4611,4612,4613,4614,4615,4616)gocreate procedure original_proc @pk int asif @pk = -1set @pk = nullselecta.af1, a.af2, a.af3, a.af4, b.bf1, b.bf2, b.bf3, b.bf4, c.cf1, c.cf2,c.cf3, c.cf4, d.df1, d.df2, d.df3, d.df4fromajoin b on a.apk = b.bpkjoin c on b.bpk = c.cpkjoin d on c.cpk = d.dpkwherea.apk = ISNULL(@pk, a.apk)goexec original_proc 1go/*Currently the above SP is a single SP that is basicallyreturning ALL possible needed data. However most of thetime we might need to call and retrieve a simple employeelist.So we thought about modifying the stored procedure byadding an extra parameter that will indicate which setof columns to return.For modifying the stored procedure in order to get avariable name of columns returned and avoidingrepeating code, we built 4 objects: the storedprocedure being called, one table function and 2 views.One table function so that we are able to pass a parameter.The views since they do not accept parameters they arealways joined at least with the inline table function.The stored procedure generates in its body a dynamicSQL statement, where it queries the table function andthe views, depending which set is required. Here is acode sample of our current design (you need to run theprevious code in order for this to work).*/create function _1_set(@pk int)returns tableas return(select a.apk, a.af1, a.af2, a.af3, a.af4, b.bf1, b.bf2from ajoin b on a.apk = b.bpkwhere a.apk = ISNULL(@pk, a.apk))gocreate view _2_set asselect b.bpk, b.bf3, b.bf4, c.cf1, c.cf2from bjoin c on b.bpk = c.cpkgocreate view _3_set asselect c.cpk, c.cf3, c.cf4, d.df1, d.df2, d.df3, d.df4from cjoin d on c.cpk = d.dpkgocreate procedure new_proc @pk int, @set int asdeclare @sql nvarchar(4000)if @pk = -1set @pk = nullset @sql = 'select * from _1_set(@pk) fs 'if @set 1set @sql = @sql + 'join _2_set ss on fs.apk = ss.bpk 'if @set 2set @sql = @sql + 'join _3_set ts on ss.bpk = ts.cpk 'exec sp_executesql @sql, N'@pk int', @pkgoexec new_proc 1, 3go/*For executing the new procedure, we pass parameter 1for the smaller set, 2 for the medium size set or 3for the complete set.For example when we want to retrieve the common setwe pass the Unique Identifier of the employee to theSP and then we pass the type of set we want to useas the second parameter (1 for simple set, 2 forcommon set and 3 for extended set).The SP has the IF and dynamic SQL to add more JOINs.We would like to know what you think of this approachand if you know a simpler way of doing it.For cleaning up the test objects run the following code.*/drop procedure original_procdrop procedure new_procdrop function _1_setdrop view _2_setdrop view _3_setdrop table adrop table bdrop table cdrop table dAs always I would appreciate any feedback, opinion,comments, ideas and suggestions.Thank you
View 9 Replies
View Related
Oct 2, 2014
I've just restored a DB from one server to another. Part of the DB is an assembly used in a Function that unencrypts some data held in a Varbinary(Max) column.
When I execute it on the original server it works fine across all rows of the table, when I execute it against the new server it fails because it's seeing dates in MM/DD/YYYY format, it works if the day of the month is less than 13, but obviously the date would be wrong!
Setting the DATEFORMAT to MDY prior to executing the function has no effect either.
It's the same DLL in the same location, the SQL Server settings as returned by DBCC USEROPTIONS() are identical.
The SQL Server editions and Window OS are the same
New Server
Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
Old Server
Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
I'm thinking the DLL is being affected by some setting either in the Windows OS or a SQL Server setting ...
View 2 Replies
View Related
Nov 29, 2013
I am trying to do a bulk import of data from XML into sQL.
My query returns no errors but no data gets imported.
Here is my XML
?xml version="1.0" encoding="utf-8"?>
<status>
<connection_status>successful</connection_status>
<operation_status>successful</operation_status>
<CustomerDeposits>
[code]....
View 2 Replies
View Related
Aug 14, 2014
From what I've seen, the CheckSum_Agg function appears to returns 0 for even number of repeated values. If so, then what is the practical use of this function for implementing an aggregate checksum across a set of values?
For example, the following work as expected; it returns a non-zero checksum across (1) value or across (2) unequal values.
declare @t table ( ID int );
insert into @t ( ID ) values (-7077);
select checksum_agg( ID ) from @t;
-----------
-7077
declare @t table ( ID int );
insert into @t ( ID ) values (-7077), (-8112);
select checksum_agg( ID ) from @t;
-----------
1035
However, the function appears to returns 0 for an even number of repeated values.
declare @t table ( ID int );
insert into @t ( ID ) values (-7077), (-7077);
select checksum_agg( ID ) from @t;
-----------
0
It's not specific to -7077, for example:
declare @t table ( ID int );
insert into @t ( ID ) values (-997777), (-997777);
select checksum_agg( ID ) from @t;
-----------
0
What's curious is that (3) repeated equal values will return a checksum > 0.
declare @t table ( ID int );
insert into @t ( ID ) values (-997777), (-997777), (-997777);
select checksum_agg( ID ) from @t;
-----------
-997777
But a set of (4) repeated equal values will return 0 again.
declare @t table ( ID int );
insert into @t ( ID ) values (-997777), (-997777), (-997777), (-997777);
select checksum_agg( ID ) from @t;
-----------
0
Finally, a set of (2) uneuqal values repeated twice will return 0 again.
declare @t table ( ID int );
insert into @t ( ID ) values (-997777), (8112), (-997777), (8112);
select checksum_agg( ID ) from @t;
-----------
0
View 0 Replies
View Related
Nov 11, 2011
I have a table that imported as varchar. Most of these columns need to be in a numerical format. How can I convert a table with columns named column0 (needs to be int),column1 (stays varchar), column2(needs to be int), and column 3(needs to be int)?
View 4 Replies
View Related
Sep 22, 2015
I'm attempting to use T-SQL to strictly parse/pull Names from a string field like such: CN=John Doe,OU=xyz,DC=ituy,DC=qwer,DC=org...I would like the ultimate result to be JUST the full name John Doe (pretty much everything after the first = sign and before the first comma. I'm attempting combinations of REPLACE, STUFF, PATINDEX and SUBSTRING, but to no avail.
View 5 Replies
View Related
May 19, 2007
Hi all,
I'm new at this SSIS but have been able to successfully create some simple packages. My situation is that at work we use a column to describe a status of applications. However, this makes for hellacious query because some of those statuses inherintly were one or more statuses previously. Example
Admit = Admit
Accept = Admit then Accept
Withdraw Accept = Admit, Accept, then Withdraw
Decline = Admit then Decline
As you can see inherintly those were all admits at one point. So what I'd like to do is instead of having long queries for example to get all my "Admits", I'd rather query another table that has the following columns as bits:
Admit
Accept
Withdraw
That way I can query the admit column and get all my admits. How can I use SSIS to transform my "Decision" column into those bit columns?
Thanks for any help or suggestions you have.
View 13 Replies
View Related
Feb 13, 2015
comparing UNIQUEIDENTIFIER columns..This query returns several rows where the [ReportId] and [LastRunDate] columns are both NULL:
SELECT [c].[Name],[c].[ItemID],[xl].[ReportID]
, MAX([TimeStart]) [LastRunDate]
FROM [dbo].[Catalog] [c]
LEFT JOIN [dbo].[ExecutionLogStorage] [xl] on [c].[ItemID] = [xl].[ReportID]
WHERE [c].[Type] NOT IN (1,5) -- Not a folder or a data source!
group by [c].[Name],[c].[ItemID],[xl].[ReportID]
order by 4
However, trying to just list catalog reports with no execution history returns 0 rows, but I'm expecting it to return a row for every NULL [ReportId] from the above query:
SELECT *
FROM [dbo].[Catalog]
WHERE [Type] NOT IN (1,5) -- Not a folder or a data source!
AND [ItemID] NOT IN (SELECT [ReportID] FROM [dbo].[ExecutionLogStorage])
I even tried casting [ItemId] and [ReportId] columns in the 2nd query to VARCHAR(255), and still got no rows, but the following queries return 0 rows and 1 row (respectively).
select * from [dbo].[ExecutionLogStorage] where [ReportID] = '0BB2209C-7736-46C8-AD02-4614EBA4F0F1'
select * from [dbo].[Catalog] where [ItemID] = '0BB2209C-7736-46C8-AD02-4614EBA4F0F1'
View 4 Replies
View Related
Jun 4, 2008
Hi guys,
Is there a way to declare a default value of empty string '' for a varchar table column?
Thanks,Kevin
View 4 Replies
View Related
Oct 19, 1999
I have a table on two different servers, the only difference that I can see is that on server A columns first (varchar 32) and last (varchar 32) have ANSI_PADDING set ON and on server B those columns are OFF. No idea why this is true: I didn't specify that the table be set up this way and they both followed similar creation/upgrade paths.
I execute "select last+first from <table>" on server A and the result looks like:
<last1> <first1>
<last2> <first2>
...
On server B I get
<last1><first1>
<last2><first2>
Now the docs say ANSI_PADDING has nothing to do with this behavior; in fact if I copy the data on server B to 2 new columns with ANSI_PADDING ON I get the same results. But that's the *only* thing that was different in syscolumns. What is causing the different output behaviors on these two servers? Thanks.
View 1 Replies
View Related
Jul 20, 2005
I have an application with highly compressable strings (gzip encodingusually does somewhere between 20-50X reduction.) My base 350MBdatabase is mostly made up of these slowly (or even static) strings. Iwould like to compress these so that my disk I/O and memory footprintis greatly reduced.Some databases have the ability to provide a compressedtable, compressed column, or provide a user defined function tocompress an indvidual Field with a user defined function[ala. COMPRESS() and DECOMPRESS() ].I could right a UDF with an extended prodcedure if I need to but I'mwondering if there are any other known methods to do this in MS SQLServer 2000 today?--Frederick Staatsfrederick dot w dot staats at intel dot com (I hate junk mail :-)
View 6 Replies
View Related
May 22, 2008
This is obviously a radical idea but some actually DO want to store linefeeds in varchar columns.
In MySQL I can escape difficult characters for example
INSERT INTO sometable(address) VALUES("23 SomeRoad
SomeTown
SomeCounty");
Does anyone know how to do this in Transact SQL?
View 7 Replies
View Related
Jul 23, 2005
Hi,This is probably an easy question for someone so any help would beappreciated.I have changed the columns in a table that where nvarchar to the samesize of type varchar so halve the space needed for them.I have done this a) becuase this is never going to be an internationalapplication, b) we are running out of space and c) there are 100million rows.I have done this with the alter table statement which seems to work butthe space used in the database hasn't altered.I'm presuming that the way the records are structured within the tablethere is just now more space free inbetween each page???Is there a way or re-shrinking just an individual table and free upsome of the space in there or am i missing the point somewhere?Thanks in advance,Ian
View 4 Replies
View Related
Jan 18, 2007
I have an encrypted column of data that is encrypted by a passphrase. The passphrase was encrypted by a symetric key in a key pair. The passphrase also is stored in a table. I can get the passphrase as needed to encrypt/decrypt the columns. I copied the production database to a new database for development. Subsequently I had to create a new symmetric/asymmetic key pair and recreated my passphrase with the new key pair. Now the passphrase will decrypt a text column but it will not decrypt two other columns which are of type varchar in the database. Here is an example:
DECLARE @pss varchar(30)
EXEC [dbo].[uspPassPhraseGet] @pss OUTPUT
SELECT DISTINCT contactid, uissueid, createdby, created_dt
,CONVERT(varchar(max),DecryptByPassPhrase(@pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.title), 1, CONVERT(varbinary, 23))) as title
,CONVERT(varchar(max),DecryptByPassPhrase(@pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.description), 1, CONVERT(varbinary, 23))) as description
,CONVERT(varchar(max),DecryptByPassPhrase(@pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.shortdesc), 1, CONVERT(varbinary, 23))) as shortdesc,
closed_dt, confidential, statusid, due_dt, deleted_dt,deletedbyid, highrisk, dbo.tbl_msg_app_legislativeinquiry.designator, dbo.tbl_ref_sys_status.description AS statusdesc
FROM dbo.tbl_msg_app_legislativeinquiry INNER JOIN
dbo.tbl_ref_sys_status ON statusid = dbo.tbl_ref_sys_status.ustatusid INNER JOIN
dbo.tbl_gbl_lkp_security ON uissueid = dbo.tbl_gbl_lkp_security.msgid AND
dbo.tbl_msg_app_legislativeinquiry.designator = dbo.tbl_gbl_lkp_security.designator
Like I said I can execute the uspPassPhraseGet stored procedure and I get my passphrase. It will correctly decrypt the dbo.tbl_msg_app_legislativeinquiry.description field which is great but the other two fields will not decrypt. When i copied the database over the encrypted fields do not display the same on the new database. The old database shows a box character followed by a bunch of junk (as expected). The new copied table on the new database shows only a single box (not the same as the original). Is there a known bug with copying a table with varchar fields that are encrypted to a new database? I tried to run a test and got the same result. I also tried to convert the varchar columns to text to see if that solved the problem and it didn't. The description field however is a text type column and it reads exactly as the original. The problem I think is that the Copy Database didn't actually copy my data correctly. How can I get the original encrypted data from the production into my development. I also tried just dropping the table and reimporting the table but that didnt take either. Scratching my head on this one.
View 5 Replies
View Related
Feb 23, 1999
When I was using a simple query using select statement with where clauses, I can get the results. When
I use AND to specify more conditions. It returns no row even though I get the result when query seperately.
What should be the possible cause of this. I am using SQL Server 6.5. Thank you
View 2 Replies
View Related
Mar 15, 2014
-- My first Data
create table #myfirst (id int, city varchar(20))
insert into #myfirst values (500,'Newyork')
insert into #myfirst values (100,'Ediosn')
insert into #myfirst values (200,'Atlanta')
insert into #myfirst values (300,'Greenwoods')
insert into #myfirst values (400,'Hitchcok')
insert into #myfirst values (700,'Walmart')
insert into #myfirst values (800,'Madida')
-- My Second Data
create table #mySecond (id int, city varchar(20),Sector varchar(2))
insert into #mySecond values (1500,'Newyork','MK')
insert into #mySecond values (5500,'Ediosn','HH')
insert into #mySecond values (5060,'The Atlanta','JK')
insert into #mySecond values (7500,'The Greenwoods','DF')
insert into #mySecond values (9500,'Metro','KK')
insert into #mySecond values (3300,'Kilapr','MK')
insert into #mySecond values (9500,'Metro','NH')
--Third Second Data
create table #myThird (id int, city varchar(20),Sector varchar(2))
insert into #myThird values (33,'Walmart','PP')
insert into #myThird values (20,'Ediosn','DD')
select f.*,s.Sector from #myfirst f join #mySecond s on f.city = s.city
/*
idcitySector
500NewyorkMK
100EdiosnHH
*/
i have doubt on two things
1) How Can i compare the City names, by eliminating 'The ' at the beginning (if there is any in second tale city) between first and second
2) after comparing first and second if there is no match found in second them want to compare with third table values for those not found
--i tried below to solve first doubt, it is working but want to know any other wasys to do it
select f.*,s.Sector from #myfirst f join #mySecond s on replace (f.city, 'THE ','')= replace (s.city, 'THE ','')
--Expected results wull be
create table #ExpectResults (id int, city varchar(20),Sector varchar(2))
insert into #ExpectResults values (200,'Atlanta','JK')
insert into #ExpectResults values (100,'Ediosn','HH')
insert into #ExpectResults values (300,'Greenwoods','DF')
insert into #ExpectResults values (500,'Newyork','MK')
insert into #ExpectResults values (700, 'Walmart','PP')
insert into #ExpectResults values (800, 'Madidar','')
[code]....
View 1 Replies
View Related
Sep 14, 2005
I have a string which I need to know where it came from in a database.I don't want to spend time coding this so is there a ready made scriptwhich takes a string as a parameter and searches all the tables whichcontain varchar type columns and searches these columns and indicate whichtables contain that string?Full text search is not enabled.--Tonyhttp://dotNet-Hosting.com - Super low $4.75/month.Single all inclusive features plan with MS SQL Server, MySQL 5, ASP.NET,PHP 5 & webmail support.
View 1 Replies
View Related
Dec 10, 2012
I have written sql query
select INVOICE.InvoiceTypeCode, INVOICE.TarrifHeadNumber,CETSH.GoodsDescription,
INVOICETYPEMASTER.InvoiceTypeName, INVOICEITEMS.ItemQuantity as SumQuantity,
INVOICE.BasicValue ,INVOICE.BasicValue * INVOICE.ExchangeRate +
[Code].....
I am getting different amount 984000.0000 and quantity 9.
View 1 Replies
View Related
Jul 24, 2012
Write a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.
This is what I have so far,
CREATE VIEW InvoiceBasic AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
From Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
[code]...
View 2 Replies
View Related
Apr 25, 2014
I am new to T-SQL development and here's what I am trying to do.
declare @cmd varchar(255)
set @cmd = 'select dbid from sys.sysdatabases where dbid not in (1,2,3,4)'
while (@cmd <=13)
begin
insert into #dbcheck values (@cmd)
Set @cmd = @cmd + 1
end
but it is giving me an error
'Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value 'select dbid from sys.sysdatabases where dbid not in (1,2,3,4)' to data type int.'
Note - #dbcheck has only one column id int
View 3 Replies
View Related
Nov 29, 2012
I am having a query
select INVOICE.TarrifHeadNumber, SUM(INVOICEITEMS.ItemQuantity) From invoiceitems,
invoice Where invoice.invoicenumber = invoiceitems.invoicenumber and
month(InvoiceDate)='11' and year(InvoiceDate)= '2012'
group by INVOICE.TarrifHeadNumber
tarrifheadno SUM(INVOICEITEMS.ItemQuantity)
84195030 9.00
84198910 5.00
84212190 223.00
84569090 247.00
84799040 1138.00
8481-80-1030 137.00
85433000 6177.20
tarrifheadno is unique
Now if i use below query and add invoicetypecode field
select INVOICE.TarrifHeadNumber,CETSH.GoodsDescription, SUM(INVOICEITEMS.ItemQuantity),INVOICE.invoicetype code From invoiceitems,
invoice , cetsh Where invoice.invoicenumber = invoiceitems.invoicenumber and
month(InvoiceDate)='11' and year(InvoiceDate)= '2012' and
cast(CETSH.CETSHNumber as varchar) = INVOICE.TarrifHeadNumber group by INVOICE.TarrifHeadNumber,CETSH.GoodsDescription,in voicetypecode
This query return distinct of 11 records
84195030 84195030
9.00 1
84198910 84198910
5.00 2
84212190 84212190
157.00 1
84212190 84212190
42.00 2
84212190 84212190
24.00 3
84569090 84569090
189.00 1
84569090 84569090
58.00 2
84799040 84799040
166.00 1
84799040 84799040
972.00 2
85433000 85433000
3764.00 1
85433000 85433000
2413.20 2
How to get same 7 records
View 1 Replies
View Related
Dec 13, 2014
Why does this return 64.00000 Hours Worked when if you do the calculation yourself you see that it should only be 16?
Code:
Create Table #One
(
BadgeNum int,
NameOnFile varchar(1000),
hoursworked int
[code]....
View 3 Replies
View Related
