|
Actually the max value of A10-50 and A9-50 is A9-50 as you are doing string comparisons and A9 is larger than A1.
What you are trying to do is rather complex unless you can guarantee the format of the data as you need to reformat it for comparison. What you are trying to get is A10-50 and A09-50, then the comparison will work, so you have to split out the alpha at the start, reformat the first number and then reformat the last number (again, A1-9 is greater than A1-10).
It is possible, but not easy - then who said coding should be easy, thats what we get paid for 
Bob
Ashfield Consultants Ltd
|
|
|
|
|
If you need to get the maximum length string, you could simply use a Select Max(Len(StringColumn)) expression.
If you have any custom criteria for getting the maximum sub-string from that comma separated list, I suggest you create a scalar valued function, e.g. GetMax(varchar (nn)). Inside that function you could parse that comma separated list into table rows (you can easily find such functions on the internet) and then apply your maximum criteria.
Hope that helps.
Regards,
Syed Mehroz Alam.
My Blog
My Articles
Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein
|
|
|
|
|
Hi,,
I don't want the Len Of string,,
but i want the max value , like A9-50,A10-50, when get max, will return
A10-50
thanks all.
|
|
|
|
|
There was a very similar question a couple of days ago, with a good suggestion to include another field in your table that just holds the 'numeric' part of your field, to be used in comparisons such as this.
|
|
|
|
|
If you stored your values in the same format
eg. A01-50, A02-50, A09-10, A10-50
Then a max on the column would bring back the A10-50 you wanted.
|
|
|
|
|
The answer Ashfield gave you is correct.
To give you some starting points for reformatting the values (Note, this is not the solution, just ideas you could use):
CREATE TABLE Test12 (
Column1 varchar(50)
)
--
insert into Test12 (Column1) values ('A1-50')
insert into Test12 (Column1) values ('A2-50')
insert into Test12 (Column1) values ('A10-50')
insert into Test12 (Column1) values ('A9-50')
--
SELECT Column1,
CHARINDEX('-', Column1),
SUBSTRING(Column1, 0, CHARINDEX('-', Column1)),
SUBSTRING(SUBSTRING(Column1, 0, CHARINDEX('-', Column1)), 2, 999),
REPLICATE('0', 2 - LEN(SUBSTRING(SUBSTRING(Column1, 0, CHARINDEX('-', Column1)), 2, 999)))
+ SUBSTRING(SUBSTRING(Column1, 0, CHARINDEX('-', Column1)), 2, 999)
FROM Test12
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi guys,
Back with another question 
Create Table TestTable (
TestTableId Int Not Null,
TestTableName Varchar(100),
Primary Key (TestTableId)
);
Now i want to alter the TestTableId column and change it to an Identity Key.
I heard that theres no direct syntax for this as such. But is this possible through SQL Server 2008?
The name is Sandeep
|
|
|
|
|
As far as I know it's not possible in SQL Server 2008 either.
Why not use temp table in the process, like:
- create temp table as you want the table to be
- set IDENTITY_INSERT ON
- copy the data to temp table using INSERT INTO ... SELECT ...
- set IDENTITY_INSERT OFF
- drop the original table
- rename the temp table to orginal table using sp_rename
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
|
The question is so large that it's impossible to answer it in whole. The .Net framework classes for accessing a database provide very much functionality and are quite flexible, so I take it you have good reasons to implement your own mechanism for some of these purposes.
What I would do in your case, I would write down to myself the (current and future) requirements for my custom classes. After that I would create (for example using Class Diagram in Visual Studio) a class model and test my concept against the requirements with different approaches. This would prove the concept to be correct (or you may discover weaknesses and want to change the concept).
Based on your description, few issues I would consider:
- why do I need to open the connections at start-up. If it's for speed, why don't I use connection pooling
- do I need to keep the connection open all the time
- do I need asynchronous database operations
- do different database operations require a common transaction
- what if I need to save several records using one call
- do I need set based operations (deleting multiple records at the same time etc)
Hope this helps you,
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi all,
Am i on the right forum for my questions? If not please lead me to the right one. Thanks a lot.
If Yes,
We are having trouble in creating right format for our Reports/Report Templates using Reporting Services. We used table element in rendering reports data. At first it satisfied our needs but apparently as data gets longer, the whole table we’re being pushed to the next page, leaving the first page blank.
I just want to ask if there’s other work around for this problem.
In our experience using List element is not applicable,because its keep together property is worst than the Table element. We already checked the page size and i think it's not the problem because the problem occurs when there are too many data on the table.
As in design, the whole page is composed of Header which is a sub report, then the whole table itself with groupings and all that stuff.
Can any one have some work around in mind? thanks for the help.
Can any one knows an email address of a particular person where i can ask these questions?
Thanks a lot! I'm not desperate for this one, but i do need an answer.
EWIN
|
|
|
|
|
With Crystal a similar problem occurs because of the report header and report footer. The solution I usually use is to repeat headers on each page and allow grids to be broken apart. This should work with SSRS but I haven't worked with it in a long time.
Need software developed? Offering C# development all over the United States, ERL GLOBAL, Inc is the only call you will have to make.
Happiness in intelligent people is the rarest thing I know. -- Ernest Hemingway
Most of this sig is for Google, not ego.
|
|
|
|
|
Thanks for the reply. But there's no 'Allow Grid or Allow Table to be broken apart' property in SSRS.
|
|
|
|
|
It works fine for me even if I have lot of data rows that can not be accommodated in one page. Are you sure that you have unchecked options like "Insert a page break before/after this table" and "fit table on one page if possible" on the properties window (the one that pops up when you right click a table and select properties)?
Regards,
Syed Mehroz Alam
My Blog
My Articles
Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein
|
|
|
|
|
Yep, I've unchecked those properties.
Maybe it's because of the structure of my page? I used a subreport as its header and the table which is in the body, is composed of header part and ONE Group Row where the data is listed. This group row is composed of another table with a group row also. To make it short, i used nested groupings. And now i think those things are the cause why the whole table is printed on the next page. :c
Still don't know what to do.
|
|
|
|
|
The groups have also a "Page break at Start/End" property. May be you missed unchecking any of such properties from any of the groups/tables.
My Blog
My Articles
Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein
|
|
|
|
|
Those properties are unchecked also. The table work well when the data can be accommodated on one page but when the data exceeds, that's the time it is printed on the next page. I also looked at the KeepTogether properties and see to it that i assigned it false.
|
|
|
|
|
I have 2 tables
a id
x
y
b id amount1 amount2 amount3 a_id
1 5 3 5 x
1 2 4 5 y
I want result like this -->
x 5 3 5 y 2 4 5
how to create query to retrieve this data.can i use pivot .Thank for your help.
|
|
|
|
|
In order to help, you need to provide more information about what you are trying to achieve.
For example: Can table A have more rows. If so, do you want to still add more columns and keep the data on a single row (I believe that this wouldn't be possible). Can table B have several rows per one row in table A, what happens then etc.
Based on the data you provided the following query should provide the result you described, but I think it's hardly what you wanted:
SELECT a1.id, b1.amount1, b1.amount2, b1.amount3,
a2.id, b2.amount1, b2.amount2, b2.amount3
FROM a a1,
b b1,
a a2,
b b2
WHERE a1.id = 'x'
AND b1.a_id = a1.id
AND a2.id = 'y'
AND b2.a_id = a2.id
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
table 'a' is dynamic can insert or delete id
than i can not create static query (like the sample that you show me)
the data can be like this
a id
x
y
z
b id amount1 amount2 amount3 a_id
1 5 3 5 x
1 2 4 5 y
1 7 8 9 z
2 3 1 3 x
2 4 5 6 y
2 8 3 1 z
result is:
x 5 3 5 y 2 4 5 z 7 8 9 --> id = 1 in table b
x 3 1 3 y 4 5 6 z 8 3 1 --> id = 2 in table b
|
|
|
|
|
I don't think that what you're asking for is reasonably possible. This would lead to several problems:
- how the client side knows which columns are fetched from which row
- what if id X in table has 3 rows in table B, but id Y has two rows, what would be the result etc
I would look for alternative solutions for the client side. If the client needs the data in several (undetermine amount) columns based on several rows, fetching the data in such format from the database isn't practical.
Could you for example use XML formatting for the data from the database and then use XML data at client or should you fetch the correct rows from the database as they are and handle all the formatting at the client. The correct approach depends on the requirements, what is the client side actually doing.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi all.
Please guide me thru this. I have a large database with many tables having self references.
for eg:
TestTable:
TestId int,
TestName varchar(100),
ParentTestId int,
Primary Key (TestId),
Foreign Key (ParentTestId) References TestTable(TestId)
I just wanted to query out the tables which are having similar type of relationships within themselves.
Thanks in advance.
The name is Sandeep
|
|
|
|
|
|
thanks for the reply man. but that's not what i asked.
I don't want to select any DATA from any table.
I just wanted to know whether there are any other tables in my database, which have self references within themselves [just like the reference established in TestTable]
I wanted a query which shows those type of tables.
The name is Sandeep
|
|
|
|
|
Hi Xandip,
Just try below query.
and yes run it in SQL Server 2000
select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where Table_Name = 'TestTable'
and Constraint_Type = 'FOREIGN KEY'
Hope it will help u.
Krishnraj
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
