visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: SQL Compatibility issues :: Problem getting the WHERE clause to work using Oracle for calendar application
By: smccle U.S.A.  Date: 29/08/2003 00:00:00  English  Points: 125 Status: Answered
Quality : Excellent
Like some before me, I have benefitted from the calendar script (I'll refer to it as "diary.asp") that also comes with an add_day.asp and a view_day.asp (fields are: id, dte, and text_field). When I used a MSSQL backend previously, it worked fine, but I'm trying to hook this up to Oracle 8i, and am now getting the following error when trying to run the select statement below (from diary.asp):

SQL: strSql = "SELECT * FROM TB_CALENDAR WHERE month(DTE)= " & month(dtCurViewMonth) & " and year(DTE) = " & year(dtCurViewMonth) & " order by DTE"

Error: Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC driver for Oracle][Oracle]ORA-00904: invalid column name

../diary.asp, line 104

Column name DTE does exist in the table.

Any ideas?

Thanks in advance,
smccle
By: VGR Date: 29/08/2003 06:59:00 English  Type : Comment
Are year() and month() returning numerical values ? If yes, then it's fine as is, if not then I think you miss the single quotes around the litteral string values

In the latter case I would write :

strSql = "SELECT * FROM TB_CALENDAR WHERE month(DTE)= '" & month(dtCurViewMonth) & "' and year(DTE) = '" & year(dtCurViewMonth) & "' order by DTE;"

You also mised the mandatory SQL-compliant closing semicolon

Other from this, my guess is that dtCurViewMonth contains either a quote, or an invalid datetime value that makes the whole SQL phrase invalid.

Too bad your software system doesn't print out the ***real*** SQL phrase as passed to (and refused by) the Oracle DB ;-)
By: smccle Date: 29/08/2003 07:20:00 English  Type : Comment
Thanks for the quick reply. Strange, but I had discovered earlier that adding the semi-colon at the end of the select statement gave me this error (when called through an ASP frontend):

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC driver for Oracle][Oracle]ORA-00911: invalid character

../diary.asp, line 106


Adding the single quotes does not seem to help, either. I am still getting the following:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC driver for Oracle][Oracle]ORA-00904: invalid column name

../diary.asp, line 106


I admit I know only a little about Oracle, and Oracle dates, but I seem to recall reading or hearing that it stores dates differently than they are viewed (i.e., as a numeric value). If so, could this be a date issue, rather than an issue of invalid column name (dte as opposed to month(dte), etc...)? (when I do a select in Toad, I do notice it is stored as 8/6/2003, for example). I tried changing from date to varchar2, and now date gets stored as 6-August-2003.

Thanks again,
smccle
By: kblack05 Date: 29/08/2003 08:00:00 English  Type : Comment
Try using this instead:

strSql = "SELECT * FROM TB_CALENDAR WHERE month(DTE)= ' & month(dtCurViewMonth) & ' and year(DTE) = ' & year(dtCurViewMonth) & ' order by DTE;"

Regards,

~K Black
By: VGR Date: 30/08/2003 18:18:00 English  Type : Comment
this is an invalid front-end string
By: VGR Date: 30/08/2003 18:26:00 English  Type : Comment
try to simplify your query to spot the problem.

strSql = "SELECT * FROM TB_CALENDAR WHERE month(DTE)= " & month(dtCurViewMonth)


By: RedPEN Date: 30/08/2003 18:30:00 English  Type : Comment
1. maybe DTE field doesn't exist - check again at TB_CALENDAR table
2. maybe month() function is case sensitive ... see HELP on oracle
By: joeposter649 Date: 30/08/2003 19:22:00 English  Type : Comment
Do a response.write strSql and cut and paste the result in sqlplus or toad or whatever. Once you get it working in there change your asp code to format strSql like that.
By: smccle Date: 30/08/2003 19:26:00 English  Type : Comment
Thanks for all the feedback. I pretty much tried all the obvious things prior to this, such as verifying the column actually exists, playing around with case sensitivity, etc. However, I wonder if the problem has more to do with the where clause's attempt to split the parse the date column into month(dte) and year(dte)?

SQLStr = "SELECT * FROM TB_EPI_CALENDAR WHERE dte= '" & month(dtCurViewMonth) & "' "

When I execute this select I can actually get the calendar to show now (thanks VGR), but am unable to "see" the text_field data that is stored for that particular date in the calendar. However, when I click on the date I am able to see the data in the view_day.asp edit screen.

The code has already been pasted at this site previously in case it would help to see it in context:

<A HREF="http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_20621924.html">http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_20621924.html</a>

Also, I would really like to figure out how to get this working, but since this is my first time submitting a question, can someone please let me know if I not assessing enough scoring points for this question?

Thanks again for any help.

-smccle
By: smccle Date: 30/08/2003 20:03:00 English  Type : Comment
I do seem to be able to get the correct values from this select:

Response: SELECT * FROM TB_EPI_CALENDAR WHERE month(dte)= '8' and year(dte) = '2003' order by dte

However, I am still getting the same error (see below) and am still unable to view the inserted data for a particular day in the main page (formerly called diary.asp, but I renamed to calendar.asp).

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC driver for Oracle][Oracle]ORA-00904: invalid column name

../calendar.asp, line 122

Note: I realize some of my naming conventions changed around on my second response (i.e., on table names and SQLStr, etc.), but that is not part of this problem since I made sure I was being consistent througout.

Thanks,
smccle
By: VGR Date: 30/08/2003 20:19:00 English  Type : Comment
if the "simplified version" of the query worked out as expected, now add progressively elements until it fails.

Begin with the order by
then add the AND ... clause
By: smccle Date: 31/08/2003 06:33:00 English  Type : Comment
Sorry, I guess I did not make myself clear enough in that last response.

I <i>am</i> able to see that doing the select above returns some values (8 for month, 2003 for year), but no matter how few where clauses I have (including the simple where clause you suggested I use), I still get the same error message (i.e., invalid column name) if I use month(dte) and/or year(dte). In other words, the only way I am able to see the calendar is by using the literal column name "dte" in my where clause--and in that case I can only do one where clause...the one for month OR year, but not both. And, even in doing so, I am unable to view the data that exists for that particular date (which I confirmed exists in the table). Does this make any sense?
By: rdaugherty Date: 03/09/2003 00:39:00 English  Type : Comment
What type of field (schema level) is DTE? It appears as though the YEAR() and MONTH() function are failing because the column type is not appropriate for those functions. Perhaps not but that's just my first thought based on the previous threads.
By: VGR Date: 03/09/2003 04:59:00 English  Type : Answer
I suspect a date format problem. "DTE" has to be a "timestamp", "datetime" or "date" field, or a string ("char", "varchar" etc) containing a compatible format. You may have "locale settings" problems too, depending on the software client you use.
By: VGR Date: 09/09/2003 14:58:00 English  Type : Comment
explanations please ?
By: smccle Date: 10/09/2003 03:31:00 English  Type : Comment
"Oracle supports both date and time, albeit differently from the SQL2 standard. Rather than using two separate entities, date and time, Oracle only uses one, DATE. The DATE type is stored in a special internal format that includes not just the month, day, and year, but also the hour, minute, and second."

From the following resource: <A HREF="http://www-db.stanford.edu/~ullman/fcdb/oracle/or-time.html">http://www-db.stanford.edu/~ullman/fcdb/oracle/or-time.html</a>

So Oracle stores dates in the following format: 28-AUG-03, which is different than the SQL2 standard. However, when you do a select, you see the date as 8/28/2003. I tried using TO_CHAR (see above link for more information on that) to modify the format to work with the calendar code, but had no success, so I ended up discarding Oracle for this calendar altogether and used the Access .mdb that came with the code instead.

Thanks again for all your suggestions.
By: VGR Date: 10/09/2003 04:28:00 English  Type : Comment
Oracle (even 9i) has still ***a lot*** a trouble handling dates, especially DATETIME (new in 9i) : it's completely non-standard, the format is proprietary and obscure, it doesn't recognize "- INTERVAL 1 DAY" etc

so don't regret too much to have abandoned Oracle, even for Access :D [I would have gone for MySql]
By: VGR Date: 10/09/2003 07:00:00 English  Type : Comment

thank you

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 369.068860 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page