Re: Mysql dateadd query problem - Database Discussion Boards

Re: Trim Function

pmarfleet14-Dec-07 9:22

pmarfleet

14-Dec-07 9:22

mehrdadc48 wrote:
In SQL How can I trim characters from result of select. (for example 0002120 in DB returns as 2120)

What do you mean by 'trim'? In programming terms, this usually refers to removing leading or trailing white space in a sequence of characters. In your example, you appear to be referring to the removal of insignificant digits from the string representation of a number. You also haven't stated which database system you are using. Is it SQL Server, Access, Oracle or something else? If you are trying to remove insignificant digits and are using SQL Server, you could write a function which uses the PATINDEX function to find the position of the first non-zero digit in the string and then uses the SUBSTRING function to return all digits at and after this position.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Re: Trim Function

GuyThiebaut15-Dec-07 11:24

GuyThiebaut

15-Dec-07 11:24

SELECT CAST(CAST('0001234' AS INT) AS VARCHAR(10))

Should also do the trick for you.

Regards

Guy

You always pass failure on the way to success.

TransferText and FileStream Incompatibility... [modified]

new_phoenix14-Dec-07 6:28

new_phoenix

14-Dec-07 6:28

Is it possible to utilize the TransferText function from a database table in MS Access to a TextFile object? I need to be able to write three lines of code to a file and then transfer the records from the database table to the very same file utilizing the TransferText approach because the file needs to be presented in a certain way to the .CSV file. Is this possible?

I would appreciate any assistance you could provide. Here is the code so far:

Set fs = CreateObject("Scripting.FileSystemObject")
     Set a = fs.CreateTextFile(strNewOutputString, True)
     strDatePicker = dteDatePicker.Value
     dteMonth = Month(strDatePicker)
     a.WriteLine ("ACTUALC")
     a.WriteLine (dteMonth & ",,")
     a.WriteLine (dteMonth & ",,")

     'Needs to be able to transfer the text in this format using the "Standard Output" specification format.
     DoCmd.TransferText acExportDelim, "Standard Output", _
         "tblExportCSV", strNewOutputString, False

     a.Close

modified on Friday, December 14, 2007 12:53:54 PM

Getting RANDOM records from MS Access

Nadia Monalisa14-Dec-07 6:15

Nadia Monalisa

14-Dec-07 6:15

Hello,
I am trying to get Random records from MS Access by using following query,

SELECT * FROM myTable ORDER BY rnd(id)

where id is the autonumber primary key of type integer.

Now the problem is, I am always getting same records every time I run the query. How can I get different Random sequence different time ? Would anyone give me a query to take SEED from Current Time's Tick for Rnd function ?

Regards

Re: Getting RANDOM records from MS Access

pmarfleet14-Dec-07 9:26

pmarfleet

14-Dec-07 9:26

A quick Google search returned plenty of results[^] related to your question.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Mysql dateadd query problem

archangel71714-Dec-07 5:11

archangel717

14-Dec-07 5:11

All,

I have been toying with this query for some time now to no avail. I need to run a retention report on sales that canceled within 30, 60, 90 days of the original sale date. My query is below. Could someone please help me in the right direction as to why it doesn't work? The error I get is below the query

<br />
SELECT count(*) FROM db.orders<br />
where date_entered between "2007-08-01" and "2007-08-31" and cancel_date <= date_add('day', 30, date_entered)<br />

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '30, date_entered)' at line 2

Thank you very much!

Re: Mysql dateadd query problem

Mike Dimmick14-Dec-07 13:09

Mike Dimmick

14-Dec-07 13:09

Reference for DATE_ADD in MySQL 6.0[^].

I think the correct syntax would be DATE_ADD(date_entered, INTERVAL 30 DAY).

MySQL is not SQL Server and they do not necessarily use the same syntax for features that both implement. There is a common but very limited standard SQL syntax; many databases support most of SQL-92, but usually not all of it.

DoEvents: Generating unexpected recursion since 1991

Re: Mysql dateadd query problem

archangel71715-Dec-07 12:28

archangel717

15-Dec-07 12:28

Thank you so much! That finally got it working. I knew there was a difference in mysql, but I just couldn't figure it out. Usually its the simple things that fix these problems. Thanks again mate!

SELECT works fine when I execute directly but fails in stored proc

Mel Padden14-Dec-07 0:43

Mel Padden

14-Dec-07 0:43

Hi all. I'm getting a strange error against an Oracle 9i database. I have a stored proc and one of the cursors I'm using has the following SELECT;

<br />
	SELECT ref_no_rev, ref_no FROM<br />
         (<br />
            SELECT REVERSE(ref_no) AS ref_no_rev, ref_no<br />
            FROM (<br />
                  SELECT DISTINCT ref_no<br />
                               FROM itm_table<br />
                              WHERE ref_no IS NOT NULL)<br />
         )<br />
         ORDER BY <br />
         	ref_no_rev<br />

And when I execute it directly against the database from the TOAD editor, it works fine and returns what I want. But when I try to access the cursor in code like so;

<br />
OPEN cur_ref_nos_with_rev;<br />
FETCH cur_ref_nos_with_rev BULK COLLECT INTO col_ref_nos_with_rev;

If falls over on the FETCH, giving me ORA-01001 - Invalid Cursor.

Anybody have any ideas?

Smokie, this is not 'Nam. This is bowling. There are rules.

Re: SELECT works fine when I execute directly but fails in stored proc

Paddy Boyd14-Dec-07 3:38

Paddy Boyd

14-Dec-07 3:38

It's not because you have two fields in your select statement and only one variable in your fetch statement? (Oracle not my strong point).

Re: SELECT works fine when I execute directly but fails in stored proc

Mel Padden14-Dec-07 3:51

Mel Padden

14-Dec-07 3:51

The variable in the FETCH is a strongly typed associative array of rows returned from the query. The table itself contains 500,000+ rows, this query returns about 300,000, so I want to grab it all in one go, do some processing, and then bulk update the database. That's why I'm using the BULK COLLECT. It's part of a proc that writes about 9,000,000 updates, so performance is an issue.

Smokie, this is not 'Nam. This is bowling. There are rules.

Re: SELECT works fine when I execute directly but fails in stored proc

Mel Padden14-Dec-07 4:10

Mel Padden

14-Dec-07 4:10

Doh and thricely doh. It was due to a brain fart in the piece of code preceding the fetch. My fault.

Thanks for trying to help all the same.

Smokie, this is not 'Nam. This is bowling. There are rules.

Re: SELECT works fine when I execute directly but fails in stored proc

Paddy Boyd14-Dec-07 4:12

Paddy Boyd

14-Dec-07 4:12

No bother. Useless advice is a speciality...

Query Issue

rockz...13-Dec-07 23:03

rockz...

13-Dec-07 23:03

I'm writing the query given below which should give 5 rows which has ParentFragmentId as NULL but I'm not getting any row.

SELECT TOP (5) FragmentId, FragmentTitle
FROM Fragments
WHERE (ParentFragmentId = NULL)
ORDER BY CreatedOn DESC

Re: Query Issue

Colin Angus Mackay13-Dec-07 23:14

Colin Angus Mackay

13-Dec-07 23:14

Try:

SELECT TOP (5) FragmentId, FragmentTitle
FROM Fragments
WHERE (ParentFragmentId IS NULL)
ORDER BY CreatedOn DESC

Upcoming FREE developer events:
* Developer Day Scotland

My website | blog

import xls to sqlserver table

sivaram praveen13-Dec-07 19:49

sivaram praveen

13-Dec-07 19:49

hi all,

in my application using vb.net 2003 im trying to import excel sheet to a table existing in sql server 2003, i have searched in net for this but could only find vb.net 2005 results where "sqlbulkcopy" is used but that doesnt work in vb.net 2003

pls help me

Re: import xls to sqlserver table

pmarfleet13-Dec-07 21:42

pmarfleet

13-Dec-07 21:42

This article[^] shows how to query an Excel speadsheet using the Jet OLEDB Provider. Once you have your data in a DataTable, you can iterate through it and run INSERT queries against your SQL Server database. The code is in C# but it should be trivial to convert it to VB.NET.

If your spreadsheet is particularly large, you may want to consider using DTS/SSIS to perform the import and trigger the operation from your .NET code.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Importing NULLs Instead of Actual Values

Bryan Bradford13-Dec-07 9:58

Bryan Bradford

13-Dec-07 9:58

Need some help with an import problem.

Got a database (SQL 2005) with a column that has a type of VARCHAR(100) and I am trying to import data from an Excel file using ADO.NET and stored procedures.

Here are some examples of the values I am tyring to import:
0
81752646
TS:2923137:ABC
WELCOME_HEAD:H:1:2:B
etc...

I am getting the actual values to import for the ones like 0 and 81752646 but for the others it is importing a NULL value every time.

I have done the registry fix for mixed data type and it makes no difference.

Anyone have any idea how to fix this?

Thanks,
Bryan

Re: Importing NULLs Instead of Actual Values

pmarfleet13-Dec-07 11:29

pmarfleet

13-Dec-07 11:29

I guess that you have altered the TypeGuessRows registry setting. Have you included the element IMEX=1 in your connection string? This informs the driver that columns where the data is of an indeterminate data type should be treated as text.

The following text is from KB article 194124:

NOTE: Setting IMEX=1 tells the driver to use Import mode. In this state, the registry setting ImportMixedTypes=Text will be noticed. This forces mixed data to be converted to text. For this to work reliably, you may also have to modify the registry setting, TypeGuessRows=8. The ISAM driver by default looks at the first eight rows and from that sampling determines the datatype. If this eight row sampling is all numeric, then setting IMEX=1 will not convert the default datatype to Text; it will remain numeric.

Adding this setting as well as modifying the registry should produce the desired results.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Re: Importing NULLs Instead of Actual Values

Bryan Bradford14-Dec-07 10:28

Bryan Bradford

14-Dec-07 10:28

I have the TypeGuessRows set to 0. I'll have to go back to the code to see if I'm using the IMEX = 1.

Thanks,
Bryan

Database redesign advice, please.

jimbaloo13-Dec-07 5:59

jimbaloo

13-Dec-07 5:59

I have been asked to redesign the database my company uses for it's web applications. The old database is SQL Server 2000 and I am updating it to 2005.
The old db has many problems, not the least of which is that it simply "evolved". As each new developer worked on it they added tables to do whatever they needed to do instead of finding tables that already contained the data they were operating on. I currently have in excess of 250 tables, a quick search of the pages in the website reveals that at least 60 of these tables are not used. What I need to do is find what data is used and what data is repeated in different tables under different names.

I would appreciate any advice, where to find articles, etc., on how to go about this redesign while minimizing the time I have to spend. Please, point me to where I can find strategies, techniques, etc., to optimize my efforts.
Thanks
Jim

Re: Database redesign advice, please.

Mark Churchill15-Dec-07 18:27

Mark Churchill

15-Dec-07 18:27

Firstly you need to work out how important your existing data it. How much needs to be retained, etc.

Then you can decide whether you are going to be massaging the existing schema (yuck) and keeping all the data, or you have free reign to redesign the entire thing from scratch.

Try making an entity relationship diagram off the existing schema (or business process - as the existing schema is probably crap) and see what is left out or missing.

Then get signoff on the ERD, and enjoy a nice new schema that makes sense. Try and follow some of our tips in our quick reference sheet under Active Record Friendly Databases - it will give you a much wider choice in selecting your data layer tool.

Mark Churchill
Director
Dunn & Churchill
Diamond Binding: Zero to Data Layer in 3 mins

Re: Database redesign advice, please.

jimbaloo17-Dec-07 2:20

jimbaloo

17-Dec-07 2:20

Thanks for the advice Mark. After several days of working on the old database I went to my manager and told him it was going to be too much work to simply rework the old data into a new database so now I get to build from scratch.
What url for Active Record Friendly Databases? I can't find the page.

Re: Database redesign advice, please.

Mark Churchill17-Dec-07 3:30

Mark Churchill

17-Dec-07 3:30

Its in the Diamond Binding Quick Reference Sheet, in the downloads section.[^]

While following those tips isn't mandatory, it will certainly make your object model a lot more intuitive - especially if you use a tool like Diamond Binding to handle your data layer (give it a try!).

Mark Churchill
Director
Dunn & Churchill
Diamond Binding: Zero to Data Layer in 3 mins

Linking databases across servers

Leo Smith13-Dec-07 4:39

Leo Smith

13-Dec-07 4:39

I would like to know how to link a database table into a local view when the primary source is on another server. I am currently using MS-SQL 2005. Can this be done?

Thanks,

Leo T. Smith

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.