|
I assume mysql has similar sorts of functions but I have not had the pleasure, but in T-SQL / SQL Server you could do:
select ID = right('0000000' + convert(varchar(20),ID),7) from MyTable -- ID can be int or varchar
or
select ID = right('0000000' + ID,7) from MyTable -- OK only when ID is varchar.
Likewise you could put this into an update statement if for example you are in the process of converting your ID column from type int to varchar/text etc.
|
|
|
|
|
Helllo
I have a problem while updating data in Sql table,
i have a table named City it contains CityID,CityName,CityShortName
i have created a stored procedure
PROCEDURE [dbo].[UpdateCity]
@CityID int,@CityName nvarchar(50),@CityShortName nvarchar(50)
AS
BEGIN
Declare @ReturnVal nvarchar(20)
SET NOCOUNT ON;
SET XACT_ABORT ON
Begin Try
IF EXISTS (SELECT CityName From City WHERE CityName =@CityName)
BEGIN
SET @ReturnVal ='Already Exists!'
--Return @ReturnVal
END
ELSE
BEGIN
Update City Set CityName=@CityName,CityShortName=@CityShortName
where CityID=@CityID
Set @ReturnVal ='Updated SuccessFully'
--Return @ReturnVal
End
end try
but when i m just editing Shortname its not updating , but its working perfect when i m editing either cityname or cityname and shortname,,
i know that i m doing mistake but where i can't found
second thng how can i show message updated successfully or already exist as i m using asp gridview
Please help me
Thanx
|
|
|
|
|
Here is the source of your problem:
eraser950 wrote: IF EXISTS (SELECT CityName From City WHERE CityName =@CityName)
BEGIN
SET @ReturnVal ='Already Exists!'
--Return @ReturnVal
END
When you do not change the city name, that if clause returns true, and hence you return 'Already Exists!'.
Try:
IF EXISTS (SELECT CityName From City WHERE CityName =@CityName AND CityID<>@CityID)
|
|
|
|
|
|
Thnx its working perfect kindly tell me is this good approach or theres any other method to do this?
second how can show message as i m usng sqldatasource(GridView) to update and delete
|
|
|
|
|
Hello,
FOr example i have the following table
class name
1 m
1 n
1 a
1 b
1 c
2 d
2 e
2 f
I need the following result:
class name
1 m,n,a,b,c
2 d,e,f
I mean grouping by class and all the names in one row
|
|
|
|
|
Use Cursor
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
modified on Monday, September 20, 2010 9:16 AM
|
|
|
|
|
michaelgr1 wrote: all the names in one row
Do you mean "in one field"?
If using Sql Server, you can write a custom aggregation function. There's an example somewhere on MSDN or the help.
It may not be the best solution to this problem, but it's good to know how to do it.
|
|
|
|
|
I need it in a field.
How can i do it? can you show me please?
BTW, I have only read only access to the DB
|
|
|
|
|
Use the pivot command. You can find more information here[^].
|
|
|
|
|
You can use xml path
declare @tmp table (class int, name char)
insert into @tmp values (1, 'm')
insert into @tmp values (1, 'n')
insert into @tmp values (1, 'a')
insert into @tmp values (1, 'b')
insert into @tmp values (1, 'c')
insert into @tmp values (2, 'd')
insert into @tmp values (2, 'e')
insert into @tmp values (2, 'f')
select class,
replace(replace((
select replace(name,' ','*') as 'data()'
from @tmp t2
where t1.class = t2.class
for xml path('')),' ',','),'*',' ') as name
from @tmp t1
group by class
|
|
|
|
|
I hate XML  but it is an excellent answer!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I find using STUFF to be slightly easier to read;
SELECT class,
STUFF(
(
SELECT
',' + name
FROM @tmp t2
WHERE t2.class = t1.class
FOR XML PATH('')
), 1, 1, '') AS data
FROM @tmp t1
GROUP BY class
|
|
|
|
|
I'd like to STUFF all xml and it's derivatives where the sun don't shine.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi All,
I am looking for query which needs two group by..
table looks like---
falconid priority affecteditem region
1 1 textbook ny
2 1 database ln
3 2 textbook ln
4 1 database zu
5 2 coin ny
6 2 textbook zu
now i want to result sorted by affected item and then count(*) according to region base in set like so that i can count how many ticket opened region vise..
affecteditem ln zu ny
textbook 2 1 0
database 1 1 0
coin 0 0 1
Thanks,
Abhishek
|
|
|
|
|
Look at using a PIVOT
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
|
Hi,
Apologize if you did not understand my problem..I am describing it again..
I have table which having four column,falconid,priority,affected item and region.
falconid is primary key,priority shows how much critical is problem,affected item shows which application is affecting and user is facing problem,region shows the different time zone
multiple user is raising the tickets from different zone with respect to affected item.that all will save in single table. like below..
falconid priority affecteditem region
1 1 textbook ny
2 1 database ln
3 2 textbook ln
4 1 database zu
5 2 coin ny
6 2 textbook zu
Now I want to calculate at the end of the day,that how many ticket(count*) has been raise for the particular affected item from which zone.. in table there are 3 ticket for textbook but from different region
I want to calculate the number of ticket raised from different region on affected item wise..like below
affecteditem ln zu ny
textbook 1 1 1
database 1 1 0
coin 0 0 1
|
|
|
|
|
I understand, you want to PIVOT priority for each region by affecteditem. You need a PIVOT query, it is not a simple thing therefore I directed you to an ARTICLE that will help explain and guide you through creating the PIVOT query. There are also some excellent examples in BOL that you might want to work through.
I could write the query for you but I already know how to do these and you need to learn, so!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi Rah,
Thanks for the article,I am trying but as I am not the database resource so it will take the time.. I am learning now how pivote works.... but it is urgent for me as i have to demo of project tomarrow,, can u provide me the query on urgent basis.....
Thanks in advance,
Abhishek
|
|
|
|
|
create table #table (affected item varchar, LN int, NY int, Zu int)
insert(table)
select
Affected item,SUM( case region when 'LN" then 1 else 0) LN,SUM( case region when "NY" then 1 else 0) NY,SUM( case region when 'ZU" then 1 else 0) ZU
from tablename groupby affected item
please advice if i am wrong?
Thanks,
Abhishek
|
|
|
|
|
I rried to post a little bit ago but that must have bombed off. I'm using Access 2007 in query design mode
At this point I have gotten to where I show the 24 test records for Material Inventory in the correct storage yards. The SQL statement is:
SELECT MatInv.MatInvID, Location.LocationName, MatInv.LocationID, MatInv.Units, MatInv.CostCenterID, MatInv.Condition, MatInv.TasksID, MatInv.MaterialsID, MatInv.CostCenterID, Materials.MaterialsID, Materials.Material
FROM Location INNER JOIN (Materials INNER JOIN MatInv ON Materials.MaterialsID = MatInv.MaterialsID) ON Location.LocationID = MatInv.LocationID;
Now I need to show where two 2 items of material in each storage yard came from a DIFFERENT CostCenterID than the CostCenterID of the storage yard. The storage yards are costed based upon the lease they are loacted on. There can be several hundred wells for each lease- all with the same CostCenterID. SOMETIMES, material from one well on one lease (one CostCenterID) may be stored in another leases storage yard (another CostCenterID). I need to be able to track it and point it out.
So I add in the table Well and relate the fields Well.CostCenterID to MatInv.CostCenterID and I get 36000+ records. Not the original 24 test records. So obviously the Query designer isn't what I need so I am trying to manipulate things in SQL view- STILL without any luck. Trying to follow info on Access help that is just about useless. Current SQL statement is:
SELECT Well.CostCenterID, Well.Well, MatInv.MatInvID, Location.LocationName, MatInv.LocationID, MatInv.Units, MatInv.CostCenterID, MatInv.Condition, MatInv.TasksID, MatInv.MaterialsID, MatInv.CostCenterID, Materials.MaterialsID, Materials.Material
FROM Well INNER JOIN (Location INNER JOIN (Materials INNER JOIN MatInv ON Materials.MaterialsID = MatInv.MaterialsID) ON Location.LocationID = MatInv.LocationID) ON Well.CostCenterID = MatInv.CostCenterID;
Any assisance is appreciated,
Larry
|
|
|
|
|
This article may help.
Caveat - I use SQL Server not Access
I find it easier to lay out my own joins so they are more human readable. Start with the minimum tables to get the minimum result, then expand on it.
Get a list of material and their storage location with cost center
Get a list of wells and their cost centres
get the relationship between well and material, compare cost centers
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I liked the link- useful. Right on, I had started back with just two tables and worked my way up from there. I finally decided to add another ID field to one of the tables (breaking normalcy rules) but it helped make it an easier sql select statement. I only had to change a small bit of code to make sure that everything gets saved properly to the tables. So it works now- thanks.
Larry
|
|
|
|
|
lemarshall wrote: (breaking normalcy rules)
These should be known as guidlines as they are subject to reality and business requirement. Having said that I alway looks very closely at a design that does not conform to these guidelines, it is usually wrong somewhere.
You got it fixed - always satisfying.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
