I'm currently working on a series of comparison queries, between data from different countries.
The bad thing about this is, that many countries use special symbols such as ^ ~ ¨ ´ ` which basically means I have a tough time making the query effecient.
How would I work about converting Jiñi to Jini, so I can compare properly. Is there any way at all?
Thanks in advance,
Trin
I am comparing two fields one from our legacy table and one in our new table structure that should have identical text data. The new field has an assortment of ANSI characters where the legacy data did not have these. Is there anything I can do that will ignore all ansi character differences? The only route I can think of is just do a replace on each ANSI type on the new column but there are quite a few character types.
View 4 Replies View RelatedHi,
Any suggestions on how to simplify this piece of code:
'locationCount'= CASE dbo.#requestsbyyeartemp.themonth when '1' then (select count(distinct location) from #requestsbyyeartemp where themonth=1)
when '2' then (select count(distinct location) from #requestsbyyeartemp where themonth=2)
when '3' then (select count(distinct location) from #requestsbyyeartemp where themonth=3)
when '4' then (select count(distinct location) from #requestsbyyeartemp where themonth=4)
when '5' then (select count(distinct location) from #requestsbyyeartemp where themonth=5)
when '6' then (select count(distinct location) from #requestsbyyeartemp where themonth=6)
when '7' then (select count(distinct location) from #requestsbyyeartemp where themonth=7)
when '8' then (select count(distinct location) from #requestsbyyeartemp where themonth=8)
when '9' then (select count(distinct location) from #requestsbyyeartemp where themonth=9)
when '10' then (select count(distinct location) from #requestsbyyeartemp where themonth=10)
when '11' then (select count(distinct location) from #requestsbyyeartemp where themonth=11)
when '12' then (select count(distinct location) from #requestsbyyeartemp where themonth=12) end
locationCount is a variable in a temp table. I am looking for ways to make things less repetitive and more efficient.
Thanks
I'm dealing with queries that manages a lot of data consolidation and I'm having to repeat large blocks of code to get the desired result.
I'm wondering if that is any way to make it simple.
My queries looks like this:
Code BlockSELECT
(A + B) AS C,
(D + E) AS F,
(G + H) AS I,
(A + B) * (D + E) AS total1,
(D + E) * (G + H) AS total2,
(A + B) * (G + H) AS total3,
(A + B) * (D + E) * (G + H) AS total
FROM table
Can I somehow declare each part of the expression and make it something like this?
Code Block
SELECT
(A + B) AS C,
(D + E) AS F,
(G + H) AS I,
C * F AS total1,
F * I AS total2,
C * I AS total3,
C * F * I AS total
FROM table
Maybe it seems silly, but each sub-expression, on the real life, includes a lot of CASEs and aggragate functions and my codes are getting complex and confuse.
Since I have lots of code pieces that I repeat more than once, I would like to make it simpler.
tks for any help
In my application I must store over 16000 character in a sql table field . When I split into more than 1 field it gives "unclosed quotation mark" message.
How can I store over 16000 characters to sql table field (only one field) with language specific characters?
Thanks
Hi everybody,
I would like to know if there is any property in sql2000 database to separate lowercase characters from uppercase characters. I mean not to take the values €˜child€™ and €˜Child€™ as to be the same. We are transferring our ingres database into sqlserver. In ingres we have these values but we consider them as different values. Can we have it in sqlserver too?
Hellen
Good day experts,
I wonder if i got an answer for this.
How can i iliminate a letters from a set of integers and characters using a SQL Statement
for ex:
ABC9800468F
is that possible?
is there a function that i can use to iliminate them?
I’m getting ASCII characters in one column of my table. So I want to replace same column value in NON ASCII characters.
Note – values in column must be same
Dear All
I am new to the topic T-SQL.
I am trying to use T-SQL to merge the content of two tables (table1 and table2) into one table making sure there are no duplication.
I wonder if any body can let me have a simple code.
Best Regards
Saad
Hey guys what would be the easiest way to create a report of value changes for particular records from one day to the next..... ?
Any suggestions would be greatly appreciated....
thanks,
Jonathan
Hi there! can anyone help me out??? I need to compare a date from the database and the system date which will be coded in a store procedure in SQL... HELP!!!!!
View 5 Replies View RelatedI have a table with a field with a bit datatype. When I execute the stored procedure line if @bitvalue = 1 begin ...
and the value is passes as 0 the statements beneath the begin execute. What am I doing wrong?
Hi...
I have a problem comparing dates in SQL Server 7.
I want to find results where date1 = date2.
I am having problems becausing SQL is also trying to compare the time which
is included in the field value e.g. 03/04/00 12:01:32
So even if the dates are equal the time may be different so the results are not shown?
I've tried SUBSTRING, and CONVERT,CAST etc.. and still can't get it to work
Any ideas???
Thanks.
Is there a way to compare two similar tables? I'm more interested in finding out if the data content is exactly the same or not between the two tables.
Thanks for your help.
We are converting our project into new version. They have done lot of changes in new version including normalization/denormalization. I need to compare the old and new database.
Do you have any standard script or procedure like SQL Compare software?.
Let me know what are the possibilties we need to check.
Your help appreciated.
Thanks,
Ravi
Good day,
I am receiving garbage data from my other department as follows.
I want:
'12/05/03' for example.
I get:
'//'
'00/00/00'
And other garbage. All of which crashes the transaction because it cannot be converted to Datetime.
What I would like to do is something like this:
IF @v_date > '01/01/50'
BEGIN <-- statement will crash if bad data)
Something to trigger a Boolean (True/False) validation of the data.
I want to know if it is even a date before I run it through my block.
Any thoughts?
I don't want to add a new IF block for every bizarre thing they send me.
Thanks ahead of time.
PS - I guess I could just raise an exception and handle it in a block that way, but am still curious.
Hi friends,
How can I do, in one Trigger, to returns if two columns, one of the Inserted and other of the Deleted, are differents ? I used this example:
If ( select Name from inserted ) !=
( select Name from deleted )
Insert Into Log_System
( Key_Mame, User_Name, Date, Field, Value, Table_Name )
Select 'COD. PROD: ' + Convert( char(10), I.Cod_Prod ),
SUSER_NAME(), GetDate(),
'Name', Convert(Char(255), D.Name ),
Convert(Char(255), I.Name), 'U', 'PRODUCTS'
From Inserted I, Deleted D
But, if one of them is Null, then the comparison result returns FALSE.
Examples:
1 - If in the Table are keeped null and the new value is different of Null
the comparison result returns FALSE.
2 - If in the Table are keeped a value and the new value is null the
comparison result returns FALSE too.
Excuse me, my English is not currently.
Thanks !
Rodney.
Hi,
Assuming a table with a column defined char or varchar.
I have a SQL query like this :
Select * from table1 where column1='Building'
It returns the same result that
Select * from table1 where column1='BUILDING'
It is my understanding SQL Server (verison 7 or 2000, I tried on both) is lower/capital insensitive by default when it is installed. If I want SQL Server to be case-sensitive with my char or varchar columns, where can I set it?
Is it at database level or server level I can find this setting . What is the setting that control it?
Best Regards,
Alain Gagne, Lead DBA
gagnea@msagroup.com
Hello im trying to run a simple query where i compare something simple like BEARING to a field in the database using this query.
Code:
SELECT table1.STK_NO, table1.STK_NO2
FROM table1
WHERE (LEN(CLEANSED_REF)>2)
AND ('BEARING' LIKE '%'+table1.CLEANSED_REF+'%')
Inside the table i can see the CLEANSED_REF field and its value is "000BEARINGBRNG000". When i run the query i get no records returned.
But if i run
Code:
SELECT table1.STK_NO, table1.STK_NO2
FROM table1
WHERE (LEN(CLEANSED_REF)>2)
AND (table1.CLEANSED_REF LIKE '%BEARING%')
The query returns the row i wanted. Can anyone tell me why this wont work the first way, but works the second?
Morning!
Folks, i want some links where i could find healthy stuff regarding New Features and Compliance levels, performance comparisons, TPC tests etc about SQL 2000 and Yukon specially.
I've to submit a document regarding Top database features that shall be used for a Medical-Billing Software in plan.
Howdy!
I have two tables that I am needing to link by the tables date field. In one table the date field is defined as varchar(23). The time for this field is always zeros.
Example: '2005-12-27 00:00:00.000'
The other table is defined as datetime, and it does have the date and time in this field.
Example: 2005-12-27 08:00:35.000
The problem i am having is
2005-12-27 00:00:00.000 does not = 2005-12-27 08:00:35.000.
Because I will never have more than one record on the same date I would like to be able to only compare the date. Example 2005-12-27 = 2005-12-27
Since the fields are 2 different field types, this is giving me a problem. Could someone please help. I have tried everything I know to do.
What I really need is the a way to format the datetime fields date into a string such as '2005-12-27'.
Hopefully what i have written makes sense.
Thanks
I had an application go down yesterday...it was giving a Type Mismatch error and the support team couldn't figure it out.
My suspicion is that a field in the database got populated with a character the vb app didn't like when it renders the information.
I'd like to prove that theory...but the database is large (4gb) and I'm not sure how to do a database comparison to see where the records differ.
Anybody have a script to get me started OR some experience to share?
Thanks,
Alex8675
In management studio, is there a way to compare databases side by side?
Or would anyone know of any freeware out there that would do such a task?
:beer:
thanks in advance.
Hi,
I have a table in sql server 2000.I delete some rows in the table.I would like to know the differences before & after deletion. Is it possible?
I am doing this 2 queries, they get data from the same tables in a very different way. The new way is a lot faster but of course there are problems. There are some discrepancies in the results. Both queries produce results with more than 200.000 rows. Second one has ca. 100 rows less than the first one and some of the results are wrong (most are correct though).
My question is how do I find the differences. I did the left outer join when column a.result != b.result (lines are defined by date and ID so they should be unique).but the join gives me problematic results (some are not different at all, some differences are not in the result).so how do I find the differences between 2 queries, including missing results/rows?
I have an Employee table with 3000 records and an Excel file having themodified data of those emplyoees. Some of the data of Excel may be sameas that of table data but some may differ. EmpId is the unique field.Other than this field, other fields of Excel may have modified data.Ineed to compare the data from SQL Server table with Excel Data.I decided to write a VB Program having two recordsets,one for SQLServer and other for Excel and compare each field's value. If themodified value is found then update that to table. Is there any way tocompare in SQL Server itself?Madhivanan
View 2 Replies View RelatedI am looking for some papers/information that compare relationaldatabases such as oracle, mysql, sql server etc. I am particularlyinterested in their features such as locking mechanisms, integrityconstraints, views... Anyone know where I can find the information?Thanks,Susan
View 3 Replies View RelatedI'm in need of a sql query that I'm not sure is possible. Here is anexample of how it's laid out.employee ID Job class Last Change Date12345 x 2/1/200412345 y 1/15/200412345 z 1/1/2004We know that this person is in job class 'x' because it's the mostrecent change. Is there a way to write a query that will exclude thelines 'y' and 'z' because they are currently incorrect?I would appreciate any help I could get. Thanks
View 1 Replies View RelatedAs many of you know I've been hunting down a SQL CE alternative / embedded DB that is easily portable among handhelds.
It turns out, that without paying for some other DB, there really aren't any good ones... Unless you go GPL, and the interpretation of GPL that most are using is that your code too is GPL even though you may only Dynamically Link to their app. I think this is likely wrong, but none the less I'm back here yet again.
What I'm looking for this time is a definition of how SQL Server CE is implemented, how it differs from a full blown DB, etc, so I can more thourally explain "why SQL CE" is an embedded DB, and how it isn't the full blown SQL Server that has a bad reputation with some.
My guess is:
SQL Server CE is implemented via DLL. There aren't sockets etc to allow you to connect, and it operates more like an advanced hash on the file system.
Please correct me if i'm wrong, or... send me a link where I can further research. Also, if you have another embedded DB as a suggestion, that's free of course, please point it my way.
Thanks!
How would one go about writing an expression such as:
var not in (a to g, s to z) in the expression builder to be evaluted with and iif statment
iif(var not in (a to g, s to z),var,"not in listed")
I would rather not have to write this in the SQL statement as a case statement.
Hi guys,
I'm trying to do a comparison of 2 data sets. Basically what I want is: 'where event date from event number -24 is earlier than the event date for event number -13'
To get the eventdate for the eventno's, I have the following 2 queries:
select eventdate
from caseevent, cases
where eventno = -24
select eventdate
from caseevent, cases
where eventno = -13
So what i'm trying to say is: I want it so that the value of the first query is compared to be LESS than the value of the second query...
Any help please????
Thank you!
I am having a problem with the following expression
=IIf(Fields!client_short_name.Value Like "Capital% ", 1,0)
I am wanting to get a value of 1 if the field has any of the valid values that begin with Capital but it always returns 0. Any ideas?
Hi,
I have table with four fields ID, Date1, Date2,Date3,Date4. Dates are in numeric farmat like 20070927. How can i get largest date (from date1 to date4) on each row. Please suggest query.
Thanks