Sql to Asp Problem.

I'm having a problem getting something to work in ASP that works using TOAD. The code is below. It works in ASP until I get to the union statement, then I get error messages, like this one: Item cannot be found in the collection corresponding to the requested name or ordinal. When I make changes, I'll get a variety of other error messages. Again, this works using toad for oracle, but gets lost in the translation to vb script.

This is an operator log entry database. Some of the log entries are annotated. The connection to the original entry is the entry timestamp. Annotations have an annotation number greater than 0. 0 is used for the original entry and annotations are 1,2,3...
I want to place the word annotation at the start of an annotation entry, have the entry appear just below the original log entry and order the annotations by annotation number 1,2,3... Any help would be appreciated.

The line highlighted in red is causing the problem.

SQL = " SELECT ENTRY_TEXT"
SQL = SQL & " ,SHIFT_DATE"
SQL = SQL & " ,ENTRY_TIME"
SQL = SQL & " , ENTRY_TIMESTAMP"
SQL = SQL & " , ANNOTATION_TIMESTAMP"
SQL = SQL & " , ANNOTATION_NUMBER"
SQL = SQL & " , OPERATOR_POSITION_DESCR"
SQL = SQL & " , ENTRY_TIME "
SQL = SQL & " FROM LOG_ENTRIES"
SQL = SQL & " WHERE SHIFT_DATE = TO_DATE('01272005', 'MMDDYYYY') "
SQL = SQL & " AND SHIFT_ID = 1"
SQL = SQL & " AND LOG_ID = 1"
SQL = SQL & " AND ANNOTATION_NUMBER = 0"
SQL = SQL & " UNION"
SQL = SQL & " SELECT 'ANNOTATION----'||A.ENTRY_TEXT"
SQL = SQL & " ,A.SHIFT_DATE"
SQL = SQL & " ,A.ENTRY_TIME"
SQL = SQL & " , A.ENTRY_TIMESTAMP"
SQL = SQL & " , A.ANNOTATION_TIMESTAMP"
SQL = SQL & " , A.ANNOTATION_NUMBER "
SQL = SQL & " , B.ENTRY_TIME"
SQL = SQL & " FROM LOG_ENTRIES A"
SQL = SQL & " , LOG_ENTRIES B"
SQL = SQL & " WHERE A.SHIFT_DATE = TO_DATE('01272005', 'MMDDYYYY') "
SQL = SQL & " AND A.SHIFT_ID = 1"
SQL = SQL & " AND A.LOG_ID = 1"
SQL = SQL & " AND A.ANNOTATION_NUMBER > 0"
SQL = SQL & " AND B.SHIFT_DATE = A.SHIFT_DATE"
SQL = SQL & " AND B.LOG_ID = A.LOG_ID"
SQL = SQL & " AND B.ENTRY_TIMESTAMP = A.ENTRY_TIMESTAMP"
SQL = SQL & " AND B.ANNOTATION_NUMBER = 0"
SQL = SQL & " ORDER BY 2,7, 5, 6 "

This message means you are referring to a column name that doesn't exist, make sure the column name exists and that it is spelled correctly.

Have you tried this

Not sure if this is what you're looking for, but it should get you close. I didn't test it out so there might be a little tweaking on your end. Let me know how it works.

Dave

'=====================

SQL = " SELECT"
SQL = SQL & " NULL AS Annotation,"
SQL = SQL & " A.SHIFT_DATE AS ShiftDate,"
SQL = SQL & " A.ENTRY_TIME AS EntryTime,"
SQL = SQL & " A.ENTRY_TIMESTAMP AS EntryTimestamp,"
SQL = SQL & " A.ANNOTATION_TIMESTAMP AS AnnotationTimestamp,"
SQL = SQL & " A.ANNOTATION_NUMBER AS AnnotationNumber,"
SQL = SQL & " A.OPERATOR_POSITION_DESCR AS OperatorPositionDescription"
SQL = SQL & " FROM LOG_ENTRIES A"
SQL = SQL & " WHERE A.SHIFT_DATE = '01/27/2005'"
SQL = SQL & " AND A.SHIFT_ID = 1"
SQL = SQL & " AND A.LOG_ID = 1"
SQL = SQL & " AND A.ANNOTATION_NUMBER = 0"

SQL = SQL & " UNION ALL"

SQL = SQL & " SELECT"
SQL = SQL & " B.ENTRY_TEXT AS Annotation,"
SQL = SQL & " B.SHIFT_DATE AS ShiftDate,"
SQL = SQL & " B.ENTRY_TIME AS EntryTime,"
SQL = SQL & " B.ENTRY_TIMESTAMP AS EntryTimestamp,"
SQL = SQL & " B.ANNOTATION_TIMESTAMP AS AnnotationTimestamp,"
SQL = SQL & " B.ANNOTATION_NUMBER AS AnnotationNumber,"
SQL = SQL & " B.OPERATOR_POSITION_DESCR AS OperatorPositionDescription"
SQL = SQL & " FROM LOG_ENTRIES B"
SQL = SQL & " WHERE B.SHIFT_DATE = '01/27/2005'"
SQL = SQL & " AND B.SHIFT_ID = 1"
SQL = SQL & " AND B.LOG_ID = 1"
SQL = SQL & " AND B.ANNOTATION_NUMBER > 0"

SQL = SQL & " ORDER BY AnnotationNumber"