|
Super,
It's now in order !
Thanks
|
|
|
|
|
I'm always reluctant to push the partition/rank solution as someone always seems to come up with a simpler answer.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
A partition/rank solution wouldn't need the join and therefore probably performs better.
The simplest answer isn't always the best.
|
|
|
|
|
SELECT MAX(TheDate), Type, Value FROM MyTable GROUP BY Type, Value
|
|
|
|
|
Hi,
I had mysql installed on my laptop (Windows Vista) but my operating system got corrupted and i had to remove the hard disk and use it as external hard disk.
so currently i have access to the laptop when i connect as external hard disk and the I can go to the Program Files\Mysql folder just like any folder in my external hard.
Now I want to know how can I restore-re-create the database on my new machine using the files of the mysql available on my external hard disk (which use to be my laptop hard disk)?
Thanks,
Jassim
|
|
|
|
|
Change my.ini to point to the correct datadir and restart MySql.
Where's your backup?
Precisely, and that's why you need one. Store it on a USB-key and keep it under your pillow when you go to sleep
Bastard Programmer from Hell
|
|
|
|
|
I know! it's a big mistake not to take a backup huhuhu
listen....
I tried but mysql services was failing to start..
datadir="F:/ProgramData/MySQL/MySQL Server 5.5/Data/"
then I tried putting it in a folder within the new machine with no2 like this:
datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data2/"
and still the same problem..
then I tried to copy the folders for the dbxxx (my database) in the C:/ProgramData/MySQL/MySQL Server 5.5/Data and I got it in the list but i am getting:
1146 Table dosen't exist for every table when i try to open it..
what should I do now?
|
|
|
|
|
this is the error when I tried to start mysql services in the first scenario..
The MySQL service terminated unexpectedly.
|
|
|
|
|
Sounds like the database-file is corrupt. Did it die during a write?
jrahma wrote: what should I do now?
Google for "repair corrupt MySQL" and try everything you find.
Bastard Programmer from Hell
|
|
|
|
|
no it dies during laptop booting only even before windows starts its booting
|
|
|
|
|
Hi,
I have a function like:
if(@a>1)
begin
select * from t1
join t2 on t1.id=t2.id
else
select * from t1
end
this is simple but when I have many situation and more tables to join it grows a lot. I'm looking for a solution that let me depend on the situations make my select query with or without joins.
All the bests,
Agh
|
|
|
|
|
here is the way how to build dynamic query in T-SQL
declare @query as nvarchar(max)
set @query = 'select * from mytable '
if(@ParameterValueIS = 1)
begin
set @query = @query +' where id=1'
end
if(@ParameterValueIS = 2)
begin
set @query = @query +' where id=2'
end
exec (@query)
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
mehrdadov wrote: I'm looking for a solution that let me depend on the situations make my select query with or without joins.
Let me rephrase that, to make sure I understood you correctly; you're looking for something simpeler than a join-statement?
Bastard Programmer from Hell
|
|
|
|
|
mehrdadov wrote: this is simple but when I have many situation and more tables to join it grows a
lot.
Why are you doing this?
As noted in the other reply one can create a query dynamically. A client app could do this as well. But you really shouldn't have "many" of these. It should normally be limited to a very few cases.
|
|
|
|
|
Good Day All
i have the Following string in a Field
'This
is the
reason that i did this>> '
i want to remove spaces in TSQL to this
'This is the reason that i did this>>'
THanks
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa[at]dotnetfunda.com
http://www.Dotnetfunda.com
|
|
|
|
|
here it is
declare @NewLine char(2)
set @NewLine=char(13)+char(10)
select rtrim(ltrim(Replace('This
is the
reason that i did this>> ',@NewLine,'')))
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
You are a Star thanks
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa[at]dotnetfunda.com
http://www.Dotnetfunda.com
|
|
|
|
|
You are welcome.
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
That might not be what you want if the string is 'hello\r\nworld'.
|
|
|
|
|
I wrote a CLR function to do that, compresses whitespace to a single SPACE:
namespace PIEBALD.Lib.LibExt.Compress
{
public static partial class LibExt
{
public static string
Compress
(
this string Victim
)
{
System.Text.StringBuilder result = new System.Text.StringBuilder ( Victim.Length ) ;
bool first = true ;
for ( int i = 0 ; i < Victim.Length ; i++ )
{
if ( !System.Char.IsWhiteSpace ( Victim [ i ] ) )
{
result.Append ( Victim [ i ] ) ;
first = true ;
}
else if ( first )
{
result.Append ( ' ' ) ;
first = false ;
}
}
return ( result.ToString() ) ;
}
}
}
CREATE ASSEMBLY Library FROM 'C:\bin\Library.Sql.dll'
GO
CREATE FUNCTION dbo.Compress(@Source NVARCHAR(MAX)) RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME Library.[PIEBALD.Lib.LibExt.Compress.LibExt].Compress;
GO
|
|
|
|
|
Here's a generic example:
create table t (s sysname)
insert into t select 'this is the reason that I did this. '
update t set s = replace(s, ' ',' ')
where charindex(' ', s) > 0
while 1=1
begin
update t
set s = substring(s, 1, charindex(' ', s, 1)-1) + ' ' + ltrim(substring(s,charindex(' ', s, 1), 8000))
where charindex(' ', s, 1) > 0
if @@rowcount = 0
break
end
select s
from t
|
|
|
|
|
I've been toying with an inventory tracking program for my company for years, but I keep getting stuck on decisions I'm not smart enough to make. I really hate wasted space, and try to optimize table structures to contain only the fields they need, but that means that I'll need a custom table for almost every item in inventory, and that's a nightmare. In this particular dilemma (there are others), I don't know what happens with unused fields when records are stored in a SQL Server database. Do they take up space, or are records compressed when fields are left empty?
For example, we have transformers. They can be substation or distribution transformers, they can be pad-mounted or pole-mounted, they can be oil-filled or dry, they all have primary and secondary voltages, as well as kVA ratings. Any of them can have de-energized tap changers (DETC) which can change their voltage ratios, but which can only be changed while the transformer is off. Substation transformers can also have Load Tap Changers (LTCs), which can be changed on the fly in response to load changes, which can cause the output voltage to vary. If equipped with a LTC, the data collected for a transformer every month has to include how many steps it has moved, and its high and low step values.
My dilemma is that, if I try to create a Transformer table with every possible parameter to be recorded for every transformer, most records will contain a bunch of empty fields. If I try to create a schema that includes a separate table for every variation of transformer type, I'll have to manage a dozen or more different tables, not only in my database, but in the code that accesses it.
From a coding standpoint, a single master Transformer table is far easier to manage, and is the approach I'd prefer to follow. But if 90% of the transformers in use have 40% of their data fields set to null, will they still consume the same storage space as they would if all fields contained data? Will I be taking a performance hit by using the single, universal table structure? Or is it really worth the effort to create different tables for each possible configuration, and to provide customized code to manipulate each table?
Will Rogers never met me.
|
|
|
|
|
Roger Wright wrote: Do they take up space, or are records compressed when fields are left empty
I think this depends on the data type CHAR will use the space allocated but VARCHAR uses the spare required by the data. I'm not 100% sure that is correct as I have had no interest in the issue, I always use VARCHAR unless the field is <5 characters and is required to be filled.
As to your table design, leaving empty fields has little impact on database size but may have different implications. FKs are not valid with empty fields. Maintenance of all those fields can be a bitch.
You could also go down the path of attributes where you have some base types with the fields common to all (or at least the bulk) of items in the machinery type. Then you have a bunch of attributes that can be assigned to each item. Caveat - going too far down this path leads to insanity as you can completely lose control of the attributes.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Great answer (as usual).
The only additional point I'd add is that regardless of data type, if a field is set to NULL, I don't believe there is any 'wasted' space. But your point about CHAR vs VARCHAR is excellent.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Great info, as usual! Thanks!
I think I'll skip the attributes and leave the empty fields; none will be used for foreign keys, in any case. The VARCHAR thing I didn't know, but it makes sense to me.
Will Rogers never met me.
|
|
|
|