|
use need something like
DROP FUNCTION IF EXISTS sortString;
DELIMITER |
CREATE FUNCTION sortString(inString TEXT) RETURNS TEXT
BEGIN
DECLARE delim CHAR(1) DEFAULT ','; -- delimiter
DECLARE strings INT DEFAULT 0; -- number of substrings
DECLARE forward INT DEFAULT 1; -- index for traverse forward thru substrings
DECLARE backward INT; -- index for traverse backward thru substrings, position in calc. substrings
DECLARE remain TEXT; -- work area for calc. no of substrings
-- swap areas TEXT for string compare, INT for numeric compare
DECLARE swap1 TEXT; -- left substring to swap
DECLARE swap2 TEXT; -- right substring to swap
SET remain = inString;
SET backward = LOCATE(delim, remain);
WHILE backward != 0 DO
SET strings = strings + 1;
SET backward = LOCATE(delim, remain);
SET remain = SUBSTRING(remain, backward+1);
END WHILE;
IF strings < 2 THEN RETURN inString; END IF;
REPEAT
SET backward = strings;
REPEAT
SET swap1 = SUBSTRING_INDEX(SUBSTRING_INDEX(inString,delim,backward-1),delim,-1);
SET swap2 = SUBSTRING_INDEX(SUBSTRING_INDEX(inString,delim,backward),delim,-1);
IF swap1 > swap2 THEN
SET inString = TRIM(BOTH delim FROM CONCAT_WS(delim
,SUBSTRING_INDEX(inString,delim,backward-2)
,swap2,swap1
,SUBSTRING_INDEX(inString,delim,(backward-strings))));
END IF;
SET backward = backward - 1;
UNTIL backward < 2 END REPEAT;
SET forward = forward +1;
UNTIL forward + 1 > strings
END REPEAT;
RETURN inString;
END |
DELIMITER ;
-- example call:
SET @Xstr = "The,quick,brown,fox,jumped,over,the,lazy,dog";
SET @Ystr = "9,8,7,6,5,4,3,2,1";
SET @Zstr = "7,8,9,6,5,2,1,4,3";
SET @str = "3,2,3,4,2,3";
SELECT sortString(@str) AS s1,sortString(@Xstr) AS s2,sortString(@Ystr) AS s3,sortString(@Zstr) AS s4;
|
|
|
|
|
Your Query is Full of
>
They will not Compile in SQL
Thanks
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
the sample is of MySQL, you need to write the code for MS SQL
|
|
|
|
|
I got a Solution
create FUNCTION [dbo].[fnSplit](
@sInputList VARCHAR(8000) -- List of delimited items
, @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item int)
BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
END
IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
go
create FUNCTION GetSortedString
(
@inputString varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
declare @outputString varchar(max)
set @outputString = ''
select @outputString = ltrim(rtrim(str(item))) + ',' + @outputString from dbo.fnSplit(@inputString,',') order by item desc
if SUBSTRING(@outputString,len(@outputString),1) = ','
begin
set @outputString = SUBSTRING(@outputString,1,len(@outputString)-1)
end
return @outputString
END
GO
and Tested it like this
select dbo.GetSortedString('2,1,4,5,3,6')
Thanks
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Hi,
The steps are:
1) Use a table valued split function to split the comma delimited strings
2) Then sort the table values
3) Loop thru each values of the table and make a customised delimited
string .
The above steps are depicted below
STEP 1:
The split function goes like this
-- Created by NILADRI BISWAS
ALTER FUNCTION [dbo].[fnSplit]
(@oldstring as varchar(100),@delimeter as varchar(1))
RETURNS @mytab table(counter int,stringval varchar(100))
AS
Begin
Declare @newstring as varchar(100)
Declare @pos as int
Declare @i as int
Declare @c as int
set @newstring = '';
set @i = 1
set @c = 0
set @pos = CHARINDEX(@delimeter, @oldstring)
WHILE (@i != 0)
Begin
set @c = @c +1
insert into @mytab(counter,stringval) values(@c,@newstring + Substring(@oldstring,0, @pos))
set @oldstring = Substring(@oldstring,@pos+1,len(@oldstring))
set @pos = CHARINDEX(@delimeter, @oldstring)
set @i = @pos;
if (@i = 0)
Begin
set @i = 0;
set @c = @c +1
insert into @mytab(counter,stringval) values(@c,@newstring + @oldstring)
End
End
return
End
Create a stored proc like this
-- Created by NILADRI BISWAS
ALTER PROCEDURE dbo.SortedList
-- Add the parameters for the stored procedure here
(@DelimitedValues AS VARCHAR(50))
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @OUTPUT VARCHAR(30)
DECLARE @TEMPOUTPUT VARCHAR(30)
SET @OUTPUT = ''
SET @TEMPOUTPUT = ''
DECLARE @MYCURSOR CURSOR
SET @MYCURSOR = CURSOR FOR
STEP 2:
SELECT STRINGVAL FROM dbo.fnSplit(@DelimitedValues,',')
ORDER BY RIGHT(REPLICATE('0', 11) + stringval, 10)
STEP 3:
OPEN @MYCURSOR
FETCH NEXT
FROM @MYCURSOR INTO @TEMPOUTPUT
WHILE @@FETCH_STATUS = 0
BEGIN
SET @OUTPUT = @OUTPUT + @TEMPOUTPUT + ','
FETCH NEXT
FROM @MYCURSOR INTO @TEMPOUTPUT
END
CLOSE @MYCURSOR
DEALLOCATE @MYCURSOR
SET @OUTPUT = SUBSTRING(@OUTPUT,0,LEN(@OUTPUT))
PRINT @OUTPUT
END
Hope this helps
Niladri Biswas
modified on Monday, June 29, 2009 11:52 AM
|
|
|
|
|
Thank its Works like wonders
Resolved
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Niladri Biswas
modified on Monday, June 29, 2009 11:45 PM
|
|
|
|
|
Cool
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
I am using MS Access data base here i have a problem with Joining of the tables which giving me a error like operator syntax error. i came to know that we have to use paranthesis to join tables in MS Access, but i don't know how to use them.
Here i am posting my SQL query can any body give me solution for this. This is query is working fine in SQL server but i want it in MS Access.
select * from students a
left join subdetails b on a.bid=b.bid
left join subjects c on b.subid=c.subid
left join details d on c.subid=d.subid and a.hno=d.hno
where d.hno=1234 and b.bid=2 and b.semid=1
Please help me.
Thanks & Regards,
Anil Chelasani
|
|
|
|
|
Use query designer to construct the query then switch to SQL view.
You can then copy and paste resulting SQL.
Note that Access tends to put in redundant brackets but they don't cause any problem.
Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis
|
|
|
|
|
Thanks.
Thanks & Regards,
Anil Chelasani
|
|
|
|
|
Try this
SELECT * FROM
(
( STUDENTS AS A LEFT JOIN SUBDETAILS AS B ON A.BID=B.BID)
LEFT JOIN SUBJECTS C ON B.SUBID=C.SUBID
)
LEFT JOIN DETAILS D ON C.SUBID=D.SUBID
WHERE D.HNO=1234
AND B.BID=2
AND B.SEMID=1
AND A.HNO=D.HNO
N.B.~ If you want to perform more than one left join in MS Access, you should use parenthesis in the From clause.
Hope this helps
Niladri Biswas
|
|
|
|
|
Thanks this is working fine but when i am trying to check second condition in the ON that is like
LEFT JOIN DETAILS D ON C.SUBID=D.SUBID AND C.HNO = A.HNO
it is again giving the error.
can you please tell me how to check for second condition in the "ON".
Thanks & Regards,
Anil Chelasani
|
|
|
|
|
Here's the SQL generated by the query designer.
Two versions: first with fixed values, second using parameters.
Note the redundant parentheses (esp in WHERE) - and the layout is pretty poor.
SELECT a.*
FROM ((Student AS a LEFT JOIN Subdetails AS b ON a.bid = b.bid)
LEFT JOIN Subjects AS c ON b.subid = c.subid)
LEFT JOIN details AS d ON c.subid = d.subid
WHERE (((b.bid)=2) AND ((b.semid)=1) AND ((d.hno)=1234) AND ((d.hno)=[a].[hno]));
PARAMETERS inBid Long, inSemid Long, inHno Long;
SELECT a.*
FROM ((Student AS a LEFT JOIN Subdetails AS b ON a.bid = b.bid)
LEFT JOIN Subjects AS c ON b.subid = c.subid)
LEFT JOIN details AS d ON c.subid = d.subid
WHERE (((b.bid)=[inBid]) AND ((b.semid)=[inSemid]) AND ((d.hno)=[inHno]) AND ((d.hno)=[a].[hno]));
Looking at your original post I suspect this is a problems
left join details d on c.subid=d.subid and a.hno=d.hno
The tables on both sides of the and must be the same i.e. c=d or a=d - cannot have c=d and a=d in same join clause.
Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis
|
|
|
|
|
it worked fine. Thanks for ur time and consideration.
Thanks & Regards,
Anil Chelasani
|
|
|
|
|
|
Float
------
- Approximate data-type
- Not all values in the data type range can be precisely represented.
- Float is better for percentage calculations
Numeric/Decimal datatypes
------------------
- Fixed precision and scalable.
- The values ranges from - 10^38 +1 through 10^38 - 1 when maximum
precision is used.
- Appropriate in many places where money/currency is involved
Money Data Type
---------------
- Money is compatible for currency with dollar and cent format.
- The datatype is accurate to 10 thousand times of the monetary unit.
- If the field is capable enough to store large aggregates rather than just the largest value, it is recommended to go with Money data type.
- Also with money, you have the choice to use thousand of separators.
Coming to your question directly, Do you use FLOAT or NUMERIC for financial transaction?
If it is mathematical calculations of currency or any aggregation of the same(e.g. Add,Multiply etc. which will be in fraction), or any salary field is involved, go with money datatype as opposed to the other two datatypes.
Hope this helps
Niladri Biswas
|
|
|
|
|
Thanks but float has more digit, see reference in original post.
Also I have seen in financial apps where float is used, also NUMERIC(19,5)
What's your opinion?
dev
|
|
|
|
|
Money Data Type
---------------
- Money is compatible for currency with dollar and cent format.
- The datatype is accurate to 10 thousand times of the monetary unit.
- If the field is capable enough to store large aggregates rather than just the largest value, it is recommended to go with Money data type.
- Also with money, you have the choice to use thousand of separators.
Coming to your question directly, Do you use FLOAT or NUMERIC for financial transaction?
If it is mathematical calculations of currency or any aggregation of the same(e.g. Add,Multiply etc. which will be in fraction), or any salary field is involved, go with money datatype as opposed to the other two datatypes.
Niladri Biswas
|
|
|
|
|
yes, in some financial apps I used before (for reputable banks), numeric and float
now this app i'm doing is compat with all MySQL, M$SQL and oracle, so don't like "money".
I'm just thinking, say if I use NUMERIC(19,5), mean 5 digits after the decimal. Over say 10k transaction in say one account, USD 0.000001 x 10k = USD 0.01 = one cent USD
It's still appreciable in my opinion and I'd be more comfortable with say NUMERIC(19,7) ... but really I have seen NUMERIC(19,5) in real app.
What's your take I'm asking for an opinion.
dev
|
|
|
|
|
SQL Server considers each specific combination of precision and scale
as a different data type in case of Numeric data types.
That implies that for FLOAT(8) 99.99 and 99.999999 are same data types
though SQL SERVER interpretes NUMERIC(4,2) and NUMERIC(6,6) as different
data types (e.g. 99.99 and 99.999999 are different types )
In lieu of the above fact I would like to go with FLOAT
Niladri Biswas
|
|
|
|
|
|
Thanks very much Niladri. Sorry about the late reply.
dev
|
|
|
|
|
Hi,
I am currently using ACCESS 2003 but I am planning to develop a GUI using .NET (Visual C#). I have been told from other forums here in msdn that the best option is to use SQL Server instead of ACCESS 2003 because I am planning to use this database from many computers on the asme LAN at the same time. The DB is located on a network drive.
I saw other forums that someone say that SQL Server Express is limited to 10 users only and 4G bytes of data. Is this true?
What are the limitations? I am planning to have around 30 persons using this DB, is this possible? The data is not big, it is around 100M bytes ...
Thanks for your time
|
|
|
|
|
kikeman wrote: I saw other forums that someone say that SQL Server Express is limited to 10 users only and 4G bytes of data. Is this true?
The figures that you have given are roughly correct, in fact I think that you may be high with the users figure. (I thought that it was nearer to 4). It may also be true (it is for other M$ Express versions) that you cannot use it in production.
However, the Express version is still valid to develop against, and in most cases will port to one of the production versions with no problems.
You may find this link[^] useful
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|