|
You have several approaches to this, for example:
- use REGEXP_INSTR to find nonnumeric characters. Use this inside DECODE to make the decision if to convert or not
- create a small function which returns number. Inside this, use TO_NUMBER and catch the potential error and ignore it. In case of an error, return NULL (or whatever is desired)
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
CREATE OR REPLACE FUNCTION business_date (start_date DATE,
Days2Add NUMBER) RETURN DATE IS
Counter NATURAL := 0;
CurDate DATE := start_date;
DayNum POSITIVE;
SkipCntr NATURAL := 0;
BEGIN
WHILE Counter < Days2Add LOOP
CurDate := CurDate+1;
DayNum := TO_CHAR(CurDate, 'D');
IF DayNum BETWEEN 2 AND 6 THEN
Counter := Counter + 1;
ELSE
SkipCntr := SkipCntr + 1;
END IF;
END LOOP;
RETURN start_date + Counter + SkipCntr;
END business_date;
/
|
|
|
|
|
If you mean the usage it could be something like (or wherever you want to use it):
SELECT business_date(SYSDATE, 20) FROM DUAL
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
I have some question about design my database.
I want to design a database for a Advertisement web site.
there are some ads in my site such as:car,shirt,mobile,house,etc...
any ads saved by a member of site and belong to it.
any ads visited by some member .
any ads have it's property .for example car has model,color.mobile has weight,have cam,have tollbooths.
house has infrastructure ,stage and ....
in FUTURE many ads may add to my site with their specific properties.
i want know should i store all of my ads in my table or save them separately?
sepel
|
|
|
|
|
I would recommend you have a Member table and an Advertisement table on with a memberid field which is the member's id who uploaded the advert.
A VisitHistory table that contains the Advert id, member id and maybe datetime with foreign keys to the member and Advertisement tables.
A table with main categories like cars, mobiles, houses etc and then a table with subcategories with a parent id on it which will be the foreign key to the categories table that filters the subcategories for each category. That way you can have all these main categories with all their subcategories and easily add new ones if you want diffirent kinds of adds.
This is obviously high level without any indexes or anything but I hope you get the idea.
Hope this helps.
|
|
|
|
|
Use inheritance in your database, so you can add different subclasses of Advert later.
From our docs:
In this example, a ServiceProduct and a StockProduct table are defined. Both primary key columns
are also declared as foreign keys referencing the Product.Id column. These relationships guarantee
that for every ServiceProduct or StockProduct record there will be a corresponding Product record
with the same Id. Effectively these two tables inherit from the product table...
|
|
|
|
|
Hi,
I have the following query
SELECT iAccountID,iViolatorID,Count(iViolatorID) FROM tbTempQNotificationRequest (NOLOCK)
WHERE IsProcessed = 0 AND sdtStatusDate >= @sdtCurrentDate AND sdtStatusDate < @sdtCurrentDate + 1
GROUP BY iAccountID,vcGroupByValue,iViolatorID
Is it possible say when I group by the result set return
iAccount|iViolatorID|Count
1-------|1----------|3
2-------|2----------|5
I would like to have a fourth column say AccountNo and I want that it should contain comma seperated values of AccountNo against the group by columns.e.g.
iAccount|iViolatorID|Count|AcccountNo
1-------|1----------|3----|X12,X23,X34
2-------|2----------|5----|X45,X56,X65,X32,X98
I hope my question is clear and understandable
|
|
|
|
|
One way to do this is to create a function where you pass in an id that returns a comma seperated string of account numbers for that ID.
Then your query would look something like
SELECT iAccountID, iViolatorID, Count(iViolatorID), GetAccountNumbers(iViolatorID) AS AccountNos
FROM tbTempQNotificationRequest
This will slow down the query a bit but it'll work.
Hope this helps.
|
|
|
|
|
Hi,
Thanks for the answer. You know i could call a function but the thing is this query is a part of function being called from stored procedure. My scenario is complex and calling another function will effect the procedure performance further as currently procedure takes 5 seconds to execute. So I was looking for something if we could accomplish it through SQL. Though thanks anyways.
Regards,
|
|
|
|
|
I have FirstName and LastName in one of my table. I am displaying as FirstName + LastName. I would like to apply LIKE to the FirstName + LastName. This is what I have tried.
SELECT
(FirstName + LastName) AS DisplayName
FROM
MyTable
WHERE DisplayName LIKE '%Name%'Then I tried
SELECT
(FirstName + LastName) as DisplayName
FROM
MyTable
WHERE
FirstName + LastName LIKE '%Name%'
OR
SELECT
(FirstName + LastName) as DisplayName
FROM
MyTable
WHERE
FirstName LIKE '%Name%' OR LastName LIKE '%Name%'These two worked, but I am not sure that this is the correct approach. Is there a better way to do this?
|
|
|
|
|
I would not have thought of using the 2nd option.
The 3rd one is the most appropriate if only because the where clause will take advantage of any indexes where the concatenated string will not. This may be negated by using the like operator but I'm not sure.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: because the where clause will take advantage of any indexes where the concatenated string will not
Yeah, I see the good point here. Thanks for the help.
|
|
|
|
|
The third option is definitely the best because the second will create a new string each comparison. If you're only going to be doing a simple '%Name%' check, then go with the third.
|
|
|
|
|
Hello,
I have a column name DollarAmount in the Excel Sheet. The format of the cells in this column have currency format with 3 decimal places. It means the data is somewhat this way: $15.900, $22.634, .......
I have to load this data into the SQL Server table using SSIS.
I tried to move into a table by giving datatype Currency and in the DataConversion Transformation also I converted the excel column into Currency Format. I had to use DataConversion as I have many other columns also.
Now, my question is that I am able to get the data as 15.900, but the $ sign is missing. It is necessary for me to have a dollar sign also, but even after trying few datatypes in SQL Server table as well as Data Conversion Transformation in SSIS, I am not able to get the $ sign also passed from my excel spreadsheet.
I guess it is not that tough, but I am not getting the right spot. Can anyone of you help me achieve this?
Thank you.
|
|
|
|
|
I'm not sure if this is your actual problem but as far as I know money and smallmoney datatypes in SQL Server store the numerical value for the money without currency symbol. SQL Server has several currency symbols it understands in strings (such as '$15900') and knows how to remove it from the string if converted to money.
If you want to get the currency symbol back when you fetch data from the database, you should cast the value of a money field to varchar and add the relevant currency symbol.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
I'm using VB.Net, ADO.Net and MySQL. I want to be able to delete a row in one dataset, sourced from a database table, then detect the deleted row, merge it into a dataset sourced from a table (with identical schema in another (Archive) database. I have no problems otherwise with updating tables but any approach I try to this question doesn't work. I'm sure it's really very simple but.............
|
|
|
|
|
What's the exact problem you have (perhaps post some code explaining the problem area)?
Are you getting some errors or do you have a piece of code which doesn't do what's expected?
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
It was really a question of how to approach the problem. I didn't see any point in including any of the several versions of code that I tried. In the night, it suddenly came to me what the problem was - I was deleting a row, then trying to add insert that row in another table. The fact that its rowstate was Deleted ensured it could never happen. Having realised this, I have been able to find a viable solution - probably not the most elegant, but it works. Thanks for caring!
|
|
|
|
|
Okay. If it's working and manageable, don't fix it 
Main thing you got it solved.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
I have a need to execute a query that pulls all data from one table regardless of whether matching data (matched by a foreign key) is present in a second table. This would be a classic LEFT JOIN query except that if a matching record is found in the RIGHT table (table2), then I need to pull only the latest such record from that table.
In particular, I need to pull one field, called VE (maybe more in the future) from the RIGHT table, but only from the latest record in the RIGHT table that matches the data in the LEFT table. The basic scenario I have is an object described in the LEFT table and the results of a process performed on that object in the RIGHT table. There may be more than one process result for that object in the right table, but only the latest one matters to me for the purposes of this query.
To solve this problem I used the following query based on the advice of someone from Code Project, but it didn't quite work out as planned. The ID in this query is an autonumber field that I'm using to retrieve the latest record.
SELECT Details.*, Results.VE
FROM Details LEFT JOIN [SELECT Results.VE, Results.SerialNumber, Max(Results.ID) From Results Group By Results.VE, Results.SerialNumber]. AS Results ON Details.SN = Results.SerialNumber);
It worked for the most part, but there were 7 more records in this result table from this query than are present in the 'LEFT' table. It appears that the duplications were occuring in the subquery, such that if there was an object that had two (or more) process results with different VE values, then the duplication occurred. For other duplications, this query correctly retrieved only the last record.
I appreciate any help.
Thanks.
|
|
|
|
|
Most likely the reason is in your group by section. You group on two columns so you'll get as many rows as there are valid combinations present. But then on your join you use only one column (SerialNumber).
If you want the latest record based on maximum ID and the maximum of id should be fetched based on serialnumber, it may be simpler to use correlated scalar at select portion (there may be typos):
SELECT Details.*,
(SELECT Results.VE
From Results r1
WHERE Details.SN = r1.SerialNumber
AND ID = (SELECT MAX(ID)
FROM Results r2
WHERE Details.SN = r1.SerialNumber)
FROM Details;Hope this helps,
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Thanks. Your explanation about the GROUP BY helped me to understand why I was getting some duplicates, but the solution does not work; the result set was so huge that I had to cancel the attempt to execute the query.
|
|
|
|
|
Few questions:
- do you want to get as many rows in the output as there are rows in Details-table (no where conditions)?
- how many rows there are in details table?
- how many rows there are in Results table?
- is SerialNumber indexed in Results-table?
- is ID indexed in results table?
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Mika,
1. Ultimately, no, I want to retrieve rows from the Details table based on certain conditions, say date range or whether one of the fields in the table is equal to (OR LIKE) a certain parameter. But, I thought that retrieving all rows from the Details table (and no more than the total number of rows) was the simplest condition.
2. The sample database I have-and perhaps a small database at that-has 10080 rows in the Details table.
3. 10500 rows. Nearly all objects in the Details table have undergone the process whose results are recorded in the results table (sometimes more than once).
4. No
5. Yes, ID is indexed in the results table.
Thanks.
|
|
|
|
|
Okay,
So the performance is affected because you fetch so many records (not a real world situation, but although a good test case).
Anyway, since the scalar is correlated, it's executed as many times as there are rows in the result (in this case 10080 times). If the result is limited, it will greately affect performance.
Another thing. Try adding a new index with for column SerialNumber (or SerialNumber, ID). Leave the existing ID index as it is. I would guess that you'll see quite different performance.
Also you could have an index on Details.SN if not already indexed.
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
