|
And what is your concrete problem? Reading from the database? Setting the text? Please be more specific.
|
|
|
|
|
SeMartens wrote: And what is your concrete problem? Reading from the database? Setting the text? Please be more specific
Both I guess
ML Lingwati
|
|
|
|
|
Take and look this.[^]
I Love T-SQL www.aktualiteti.com
"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.
|
|
|
|
|
Hi Experts
i Want To search fuzzy word search using where clause in sql server 2005
Thank u
|
|
|
|
|
Sharma Dinesh Kumar wrote: i Want To search fuzzy word search using where clause in sql server 2005
Thank u
Take a look at FTS in SQL (to search the "Full Text"), and decide your level of fuzziness. Then search for background information on SoundEx and Levenstein
I are troll
|
|
|
|
|
this is a sample account statement generation for an e-banking software
Plz i want the accounts and customers details to appear even if no transactions exists.
am finding it difficult making that work..i have tried full outer joins and outer joins
any help?
code below
IF @type='RANGE'
BEGIN
print 'Query began'
SELECT (SELECT Openbal FROM Accounts WHERE SUBSTRING(AccNum,9,4)=@AccNum) AS openBal,(SELECT Name FROM ThisCompany) AS CompName,
(SELECT Branch FROM ThisCompany) AS Branch,
(SELECT StreetAdd FROM ThisCompany) AS StreetAddress,
(SELECT AddLine1 FROM ThisCompany) AS AddLine1,
(SELECT AddLine2 FROM ThisCompany) AS AddLine2,
@dt1 as Date1,@dt2 as date2,
(SELECT Tel1 FROM ThisCompany) AS Tel,
(SELECT Website FROM ThisCompany) AS Web,
SUBSTRING(Accounts.AccNum,9,4) AS CustNum,
Accounts.AccType, Accounts.Currency, Customers.FName+' '+
Customers.LName as FName, Customers.CorrespAdd,
Transactions.Dt, Transactions.Debit, Transactions.Credit, Transactions.TranDetails, Transactions.Bal
FROM Accounts FULL OUTER JOIN
Customers ON Accounts.CustNum = Customers.CustNum FULL OUTER JOIN
Transactions ON Accounts.AccNum = Transactions.AccNum
WHERE SUBSTRING(Accounts.AccNUM,9,4)=@AccNum
AND Transactions.Dt BETWEEN DATEADD(dd,1,@dt1) AND DATEADD(dd,1,@dt2)
ORDER BY Transactions.Dt ASC
RETURN
END
|
|
|
|
|
prubyholl wrote: Plz i want the accounts and customers details to appear even if no transactions exists.
Replace the "Where" keyword with "And".
The outer join fails because the where condtion executes after the completion of outer join. Hence you dont get any results.
Whereas when you use an "And" clause , the condition is evaluated while the execution of the outer join.
Also handle the null values gracefully when there is no transaction for a customer.
When you fail to plan, you are planning to fail.
|
|
|
|
|
|
I want to display one row at a time on a gridview from my database table, where i'll be selecting the row by its primary key randomly and some condition using sql server 2005.
please help.
ML Lingwati
|
|
|
|
|
Hi, if I have understood your problem, then you have a table say tblRecords
with key(int, PK) and Value(varchar).
Like
Key Value
-----------------
1 Value1
2 Value2
3 Value3
4 Value4
................
.................
[n] Value[n], where n = any positive number
Now if you fire the following query, it will select only 1 record randomly.
SELECT *
FROM dbo.tblRecords
WHERE [Key] = CAST(RAND()*10 AS INT)
Note- If you have values between 1 to 1000, then it will be RAND()*1000
hope this helps.
Please let me know in case of any concern
Niladri Biswas
|
|
|
|
|
Hey everyone,
I have this budget lines table of which I have the columns CODE, ITEM and AMOUNT.
When my CODE column has this:
CODE
------------------------------
1.1.1 >>>>>> Category 1.1.1
1.1.1.1
1.1.1.2
1.1.1.3
2.2.2 >>>>>> Category 2.2.2
2.2.2.1
2.2.2.2
2.2.2.3
How can I get the subtotals of the amount column of each category?? And if I'm doing it the wrong way "in terms of database design" please tell me how would you do it??
Many thanks guys!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
You could do your sum based on the first 5 characters of the code but that would be a bad idea.
Alternatively, you could change your database structure a little and add a Category table, add a relationship to the budget lines table and only store the final part of the code in your budget lines table.
Category
ID Code
-- ----
1 1.1.1
2 2.2.2
etc
BudgetLine
CategoryCode SubCode
------------ -------
1 1 (gives 1.1.1.1)
1 2 (gives 1.1.1.2)
2 1 (gives 2.2.2.1)
2 2 (gives 2.2.2.2)
I'm sure that won't be perfect for what you need but hopefully it'll give you the right idea.
|
|
|
|
|
Thanks mate, I appreciate your idea and it could be just right but my requirements are more complicated than that, you see, I cant predict how far budget line codes can be nested, some might go deeper than just one level and some just dont branch at all.. Do you still have anymore tricks up your sleeve??
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
I'm not sure if it would work but you could store all of the items in one table and link them to each other, i.e.
ID Code ParentID
-- ---- --------
1 1 NULL
2 1 1 (gives 1.1)
3 1 2 (gives 1.1.1)
3 2 2 (gives 1.1.2)
Using that method you could go to as many or as few levels as required.
|
|
|
|
|
I just found this snapshot from an application.. Any idea what kind of design is adopted??
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
It's impossible to tell how a database is structured just by looking at a screenshot. For all we know, that screen could have taken a long time to pull data in from lots of different tables and munge it around. Having said that, one possible table structure for this would be:
Table: ACC_GROUP
Id Parent Desc
1 NULL Assets
2 1 Current Assets
3 2 Cash on Hand
Table: ACC
Id Group Number Name
1 3 1-1110 Checking Account
2 3 1-1120 Payroll Account
3 2 1-1300 Deposits Paid
From this, you can reproduce that screenshot. Don't forget, you don't have to do everything in one super-complicated SQL query statement. Sometimes it is OK to use cursors, or even (shock horror) to retrieve data and then manipulate it further in code (C#, Java, VB, COBOL, whatever your application happens to use). You won't win any points from the end users for writing clever code. They just want something that works.
But real-world accounting systems are usually more complicated than this so I doubt if that's how it is really done in this particular application. And it's not uncommon to find tables that hold pre-calculated sub-totals at the various levels in order to make displaying total grids like this quick and easy. Ideally you don't duplicate data in a database, but sometimes in the real world it's OK to make that compromise.
|
|
|
|
|
Hi all
am creating table for multiple choice question, each question will have at most 6 choices. so my question is :
is it better to define 6 columns inside that table(one for each column) or just one with special format for it (like using &^& as spertator)?
note: there will be only one answer.
thank you
modified on Tuesday, July 7, 2009 6:57 AM
|
|
|
|
|
I would suggest to have two tables.
Table 1: Question ID and Question Text
Table 2: AnswerID QuestionID and AnswerText
|
|
|
|
|
It's recommended to keep the values in a column atomical; one column/row intersection should hold a single value.
You'd be able to query things a lot easier if you divide it over columns. If things are packed in a single column, chances are that you'd have to resort to parsing the data from that column.
I are troll
|
|
|
|
|
Neither.
The instant you say it's only 6, you'll have a requirement to make it 7.
Use header/detail with QuestionID, then AnswerID/AnswerOption and then you can have 1..N.
Jeremy Likness
http://csharperimage.jeremylikness.com/
|
|
|
|
|
My database holds the complete file path like D:\myfolder\my_file_name.txt & C:\newFile.txt
So, when I sort this, it is sorted according to complete path. Instead, I want to sort it based only on the file name (my_file_name.txt & newFile.txt). Is there any way to write such a query? (If I will implement a sort by myself on file names, it would be slow compared to SQLite sort).
Please suggest.
|
|
|
|
|
I think your design is wrong.
You can store the File Path in 1 column and the corresponding File Names in another column and then perform the sorting on File Names.
In your case, you have to chuck out the File Names from the entire file path and then you have to store it may be in a temporary file and then you have to perform then sorting.
However, the solution is
1) Use a Cursor or While loop and use a split function to split the strings based on '\' character
2) Extract the last string and store it in some temporary table or table variables or you can make your can write a table valued split function.
3) Then you apply the Order by operation.
Hope this helps
Niladri Biswas
|
|
|
|
|
In my earlier post, I have given the idea.
Here is the solution
Say , I have a table tblFileNameSort where I am storing the entire file path
i.e.
FILEPATH
----------
D:\myfolder\my_file_name.txt
C:\newFile.txt
C:\abcFile.txt
E:\bop.txt
I have written a table valued split function
--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
And my stored proc reads like the following
--Created by Niladri Biswas
ALTER PROCEDURE Dbo.SortTxtFile
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- VARIABLE DECLARATION
DECLARE @FILEPATH VARCHAR(50)
DECLARE @FINALSTORAGE TABLE
(
FILENAMES VARCHAR(100)
)
-- STEP 1: DECLARE A CURSOR
DECLARE MYRANDOMCURSOR CURSOR FOR
SELECT FILEPATH
FROM tblFileNameSort
-- STEP 2: OPEN THE CURSOR
OPEN MYRANDOMCURSOR
FETCH MYRANDOMCURSOR INTO @FILEPATH
-- STEP 3: START THE LOGIC
WHILE @@Fetch_Status = 0
BEGIN
-- STEP 4: INSERT THE COMBINED RECORDS INTO TABLE
-- @FINALSTORAGE
INSERT INTO @FINALSTORAGE
SELECT stringval FROM dbo.fnSplit(@FILEPATH,'\')
WHERE stringval LIKE '%.txt'
-- STEP 5: GET THE NEXT RECORD
FETCH MYRANDOMCURSOR INTO @FILEPATH
END
--STEP 6: CLOSE THE CURSOR
CLOSE MYRANDOMCURSOR
--STEP 6: DEALLOCATE THE CURSOR
DEALLOCATE MYRANDOMCURSOR
SELECT * FROM @FINALSTORAGE ORDER BY FILENAMES
END
GO
The output is
FILENAMES
-------------
abcFile.txt
bop.txt
my_file_name.txt
newFile.txt
N.B.~ You must change the database design, as I posted in my earlier reply
Hope this helps.
Niladri Biswas
|
|
|
|
|
Hey Niladri, thanks man for the code. But I am totally illiterate as far as SQL goes. And I am using SQLite so I guess the functions calls etc are different. So, I canot even directly copy your code and use as I dont know where to put what and how to pass parameters etc.. Anyway, I solved my problem by using the Holmes brilliant answer.
many thanks for the help.
|
|
|
|
|
I love being a smartass, try this...
Select *
from Tablename
order by REVERSE(LEFT(REVERSE(path),(CHARINDEX('\',REVERSE(path))-1)))
Not tested but the string massaging works
Never underestimate the power of human stupidity
RAH
|
|
|
|