Re: Does updating view updates the table? - Database Discussion Boards

Re: a function to return grade from mark that stored in a table

Mark Churchill15-Dec-07 20:29

15-Dec-07 20:29

Thats your algorithm, its really up to you. In Australia we say a High Distinction as above 85%, Distinction 75-85 etc etc.

You'll need to ask your client for this one.

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

Re: a function to return grade from mark that stored in a table

sepel15-Dec-07 20:38

sepel

15-Dec-07 20:38

very thanks.

sepel

Getting EF Beta 3 Working on Vista x64

martin_hughes15-Dec-07 7:11

martin_hughes

15-Dec-07 7:11

I thought I'd post this, just in case anyone is struggling to get Entity Framework Beta 3 working in 64-Bit environments.

Microsoft's line is that the design time EF Beta 3 is not supported officially, yet, on x64 platforms. This leads to all manner of billy, including the csdl, msdl and ssdl mapping files not being generated.

The work around is incredibly simple:

Copy:
Microsoft.Data.Entity.Build.Tasks.dll
Microsoft.Data.Entity.targets

from %windir%\Microsoft.NET\Framework64\v3.5 to %windir\Microsoft.NET\Framework\v3.5

and then rebuild the project (F6).

"On one of my cards it said I had to find temperatures lower than -8. The numbers I uncovered were -6 and -7 so I thought I had won, and so did the woman in the shop. But when she scanned the card the machine said I hadn't.

"I phoned Camelot and they fobbed me off with some story that -6 is higher - not lower - than -8 but I'm not having it."
-Tina Farrell, a 23 year old thicky from Levenshulme, Manchester.

Date Picker with SSRS2005 not working when called in VS 2005 [modified]

MSinha14-Dec-07 23:39

MSinha

14-Dec-07 23:39

Hi All,
I am using SSRS2005 to generate the report. I have used Date Picker control. Date picker is working at runtime when I run directly, i.e. http//server ip/reports. But, When I call this report in VS 2005 (C# with ASP .Net 2.0) with Forms Authentication, date picker control refreshes the page and not working.
!!Any Help Please!!

modified on Monday, December 17, 2007 2:17:09 AM

Does updating view updates the table?

NetBot14-Dec-07 19:00

NetBot

14-Dec-07 19:00

Hi all,

I am facing a helllot of problems with views..
I created a view as

create view viewname as select * from tabel1;

but when i run an update query on the view , it updates it in the tabel1 tooooo Confused | :confused:

any help guys????

Re: Does updating view updates the table?

pmarfleet14-Dec-07 21:59

pmarfleet

14-Dec-07 21:59

A view provides a way of looking at the data in a table or tables. A view holds no data in its own right, it simply represents a cached SQL SELECT statement that returns data from the underlying tables. A simple view like yours is updateable, because each row in the view can be mapped back to a row in the underlying table tabel1. This means that you can run insert/update/delete queries on the view as if it was a real table.

BTW, it's table, not tabel.

Paul Marfleet

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

Re: Does updating view updates the table?

NetBot14-Dec-07 22:54

NetBot

14-Dec-07 22:54

pmarfleet wrote:
BTW, it's table, not tabel.

oh... sorry it was a typo.

So how do i create a view such that the actual data is not altered.
I mean if i wan`t to test some queries on data. This is an ideal situation in online databases $Shucks | :-\$

Re: Does updating view updates the table?

pmarfleet15-Dec-07 6:43

pmarfleet

15-Dec-07 6:43

newbie.net wrote:
So how do i create a view such that the actual data is not altered.
I mean if i wan`t to test some queries on data.

I don't understand. How can you test an UPDATE query if you're not able to update any data?

In a typical software development setup, you would have a development environment that is separate from your production environment. Testing of code can be performed in the development environment without compromising the running of the production environment. Your development environment should replicate your production environment as closely as possible. This way, when you move your application from development to production it should behave predictably.

Paul Marfleet

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

Re: Does updating view updates the table?

Mike Dimmick15-Dec-07 6:31

Mike Dimmick

15-Dec-07 6:31

My understanding was that views are not updatable in Microsoft SQL Server, unless you use an INSTEAD OF trigger to perform the updates on the base table.

Please post the exact version of the exact database server software that you're using with your questions. It saves a lot of time.

DoEvents: Generating unexpected recursion since 1991

Re: Does updating view updates the table?

pmarfleet15-Dec-07 9:46

pmarfleet

15-Dec-07 9:46

Mike Dimmick wrote:
My understanding was that views are not updatable in Microsoft SQL Server, unless you use an INSTEAD OF trigger to perform the updates on the base table.

This isn't the case. I was able to run an update query on a simple view. The MSDN documentation states:

INSERT, UPDATE, and DELETE statements must also meet certain qualifications before they can reference an updatable view. UPDATE and INSERT statements can reference a view only if the view is updatable and the UPDATE or INSERT statement is written so that it modifies data in only one of the base tables referenced in FROM clause of the view. A DELETE statement can reference only a view that is updatable; the view must also reference exactly one table in its FROM clause.

AFAIK, a view is updateable if it returns rows of data that can be directly mapped back to rows of data in the underlying table that contains the fields being updated.

Paul Marfleet

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

Trim Function

mehrdadc4814-Dec-07 7:56

mehrdadc48

14-Dec-07 7:56

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

Best wishes

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

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