|
select ('973' + contact_moblle) AS mobile_number from persons where contact_moblle is not null and contact_moblle != '' and len(contact_moblle) = 8 and left(contact_moblle, 1) = '3' GROUP BY contact_moblle order by file_no
Hope this will help. Check this and let me know the outcome
-Shenbaga Murugan Paramasivapandian
I hate computers and I just mess them up with my code!
|
|
|
|
|
Try this
;With CTE AS
(
Select
Rn = Row_Number() Over(Partition By ('973' + contact_moblle) Order By file_no)
,('973' + contact_moblle) AS mobile_number
from persons
where contact_moblle is not null
and contact_moblle != ''
and len(contact_moblle) = 8
and left(contact_moblle, 1) = '3'
)
Select mobile_number
From CTE
Where Rn = 1
Hope this helps
Niladri Biswas
|
|
|
|
|
Hi
Can anyone show me how to convert strings that start with Mc[x] to Mc[X]? (e.g. convert Mcgovern to McGovern, Mcdonald to McDonald) in Oracle (or just SQL code)?
I tried using the regexp_replace function in Oracle, but can't quite figure out how to upper case the third character.
Thanks
|
|
|
|
|
Though not elegant, I was able to do the following:
select regexp_replace(d_name,'Mc[a-z]', 'Mc' || upper(substr(d_name, 3, 1))) n from
(
select 'Mcfix' d_name from dual
);
Thanks
|
|
|
|
|
I would like to hear some opinions from experienced developers who have worked with Access and SQL Server Express.
I am thinking of building a desktop based room reservation system as a personal project. However, I am not sure what would be the best approach.
I am thinking of the following options:
1) Using MS Access for both the front-end and back-end.
2) Using C# for front-end and MS Access as back-end.
3) Using C# for front-end and MS SQL Server Express as back-end.
It would be a packaged software, so I'm wondering if MS Access would be stable enough.
I haven't worked with MS Access long enough, so I would like to find out from other experienced developers whether MS Access backend would be sufficient, and what are some of the recommended steps to take to prevent database corruption - maybe backup, compact and repair every time program closes; what else?
Thank you.
|
|
|
|
|
If you hope to sell it, don't use Access.
|
|
|
|
|
Thanks for the response. I did a bit more research, and found out about MS SQL CE, and also SQLite. For now, I will test out SQLite since deployment seems to be easy.
|
|
|
|
|
As always, it depends. Are you intending to put a lot of logic into the database (stored procedures)? Or are you intending to put as little logic into the database as possible, and just use it as a relatively dumb data store? I'm not suggesting either option is better than the other, but that decision might influence your choice of database. Or you could look at it the other way round - support for stored procs is better in some databases than in others so if you make the decision about the database first it may place limitations on how you design the application.
You could also use this as an opportunity to think about how you might build a system which is database agnostic, or at least ask yourself the question, how easy would it be to upgrade from MS Access to SQL Server six months from now if I find Access can't cut it?
It's not clear when you say this is a "personal" project whether that means it is just a learning exercise or whether you are considering possible commercial applications at some point (which is what I read into your statement about it being a "packaged" solution).
|
|
|
|
|
I am taking this chance to improve my development skill. At some point, I would like to sell it as a product. This is more of a side project, to be done after work or during the weekends.
I am looking at light-weight database. The application would be a standalone, single-user. For support of multi-user, I intend to use configuration to specify the database connection. So, the database should be able to support multi-user environment, within a LAN -> which I am looking at a client/server architecture.
This will be a data-entry, reporting application, so MS Access would be ideal. However, I do not have experience using the developer edition and compiled an executable with the Access run-time included in the package, which user can run without impacting the existing Access operation on their PC.
Hence, looking at a C# front-end and a suitable light weight database as data store. Stored procedure would be nice to have.
|
|
|
|
|
Stan Moong wrote: reporting application
This can cause severe problems with MS Access. When you calculate sums, averages etc. on joins of sub queries, the performance will be unacceptably slow even with few data. In such cases, go for MS SQL Server (Express edition is OK too).
|
|
|
|
|
If you are thinking of SQLite,which is an embedded DBS,you may also want to check out VistaDB[^] from Gibraltar software.
I have been using for several years and have found it both reliable and very fast, with a small footprint. It has a reasonable management interface, can import from Access and SQLServer, and can export to XML.
|
|
|
|
|
Thank you very much for the information. I will check it out!
|
|
|
|
|
If the database will grow very big use something besides MS Access. Access is good for small stuff but personal preferance would be something else. Note I have used both Access and SQL Server as backends. I have used Access as the front end for a SQL Server backend. Stick with a different backend then Access if possible. Using C# as your front end will prevent a few security problems.
Good luck and happy learning.
|
|
|
|
|
Thank you.
Yup, I am looking at the C# as front-end, and a light-weight database for data store.
|
|
|
|
|
I still use Access for small, single user applications, but I've found that using Access for
Multi-User applications is not so hot.
My 2 cents. 
Good luck. 
|
|
|
|
|
Thank you.
I will be using C# as front-end, and a light-weight database for the data store.
|
|
|
|
|
I would go for MySQL and C# as a final product.
Why not prototype with Access - for single user apps the form design etc is easy and fast.
Then when you are happy with what you are doing go for C# and MySQL - MySQL because it is open source and supported by Oracle.
I would avoid Access as a backend database if you can as MySQL is built specifically for the sort of thing you are trying to do(and Access most definitely is not...).
Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)
|
|
|
|
|
Yes, that is a good idea to get a prototype up, and also see if any design flaw. Thanks for the idea.
|
|
|
|
|
Stan Moong wrote: 3) Using C# for front-end and MS SQL Server Express as back-end.
I would choose this anyday. Access database is bound to get corrupted with multi-user access.
|
|
|
|
|
C Sharp and Ms access is best for your project.
or choose your db according to length of db.
|
|
|
|
|
Hi, I've been looking for an answer for quite a while but no luck, I'm sure u guys can help me out.
I have 3 tables
Roles > MId PK
Members > RId PK
Members_Roles (MId, RId)
I'm new to this so would appreciate ur help.
Regards,
K
|
|
|
|
|
You are going to have to post a real question in order to get an answer. However this[^] might be a starting point for you. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
modified 4-Oct-11 15:02pm.
|
|
|
|
|
try this...........
select m.* from roles r, member m, member_roles p
where r.mid = p.rid and m.rid = p.rid
|
|
|
|
|
For an article I want to write I need a large sample database.
A bit like Northwind but with many records.
I could have based in on Northwind but I'd like to be able to use it (for comparison) on any database and not being in violation of Microsoft licenses.
I therefore wrote a script that create a few tables from scratch and a lot of data into it.
If you're mad enough to try it (you got the source below).
This is pretty much textbook to speak to database beginners but hopefully with enough relationship to make interesting queries.
It will create 5 classic tables (record count in the box):
+- Client -+ +--Order----+
| 100,000 |<---| 500,000 |
+----------+ +-----------+
| ^
| |
v |
+- Occupation-+ |
| 330 | |
+-------------+ |
|
|
|
+- Product -+ +--OrderLine--+
| 1,554 |<----------| 3,137,991 |
+-----------+ +-------------+
I hope you appeciate this fantastic Ascii-Art
I am trying to have a shortish script (a few kilobytes) that can quickly generate enough data to show the impact of indexes.
The script takes 6 minutes to run on my laptop
I worked hard on faking meaningful data.
This is one the first client looks like:
ClientId 1
FirstName: Jimmy
MiddleName: Lawrence
LastName: Mcpherson
Gender: M
DateOfBirth: 24:28.8
CreditRisk: 2
XCode: AZ3 1KN
OccupationId: 228
TelephoneNumber: (123) 822 - 3268
Street1: 468 Old Barnes Street
Street2: 2nd Floor
City: Sacramento
ZipCode: 83319
Longitude: 38.46202086
Latitude: 120.999543
Notes: In aute sunt et ea sed ex tempor. Et ex non eu anim ad. Id velit aliqua. Elit id do qui quis. Sed fugiat ex. Sunt ipsum ut eu ad sint. Enim magna animus lorem eu eu culpa. Sint nostrud irure ipsum amet amet. Sed do mollit irure nisi. Elit ut sint est ea culpa. Sit aute enim culpa deserunt ad ea. Eu velit et ex minim ad. Eu do enim non ad ad. Exclamo amet est. Nulla enim ex. Do ad ea ullamco ex. Magna excepteur ipsum ad non sunt eu non. Animus ut elit. Ut et est aute. Do cillum non ad sunt do enim. Ex non ea ea. Et id id sint duis. In in et. Id duis ipsum. Aute velit ut. Amet enim velit elit. Id occaecat ea. Sunt minim exclamo. Do ad et id id ipsum lorem. Qui qui do sunt ad. In exclamo do. Ipsum ad sed. Et ex ea. Et ullamco sunt nisi ex. Sunt eu quis lorem. Amet id sit ipsum. Non aute eu nisi. Ad et quis sed. Eu veniam nisi duis in sint non. Laboris sunt elit aute. Aliqua elit ad do. Culpa et ea. Esse in elit id. Ea ex elit. Est mollit ea nisi esse qui. Ad nisi in labore. Non ad veniam sed.
And this is the first product
ProductId: 1
Name: White fleece fabric
Price: 5.50
Active: 1
Stock: 319.740
If you got a minute or two (or 6) I am available for comments.
Would you format the database differently.
Is there enough data for proper real case scenarios?
http://pastebin.com/md3Sbr55[^]
|
|
|
|
|
Try Google for a large test database. I have seen various ones over the years but cannot remember where.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
