DB2 error on TSQL

Question

I am getting this error: [OLE/DB provider returned message: [IBM][CLI Driver] [DB2 / linuxx8664] SQL0206N "LS_SCHEDULED_DATE" is not valid in the context where used. SQLSTATE=42703) OLE DB error trace [OLE /DB Provider 'MSDASQL' IColumnsInfo: GetColumnsInfo returned 0x80004005: Msg7339, Level 1, State 1, Line 83 OLE DB provider 'MSDASQL' reported an error.

The code I am getting this error is as follows:

IF OBJECT_ID('tempdb..#Temp1') is not null begin drop table #Temp1

select * 

into #Temp1
from openquery(LnkServer,

'
Select 
X.loan_number, 
X.ls_code,
x.ls_actual_completion_date
x.ls_scheduled_completion_date

,max(Case WHEN (ls_code = ''924'' and ls_scheduled_completion_date <> (''01/01/1900'')
             then ls_scheduled_completion_code
               else NULL End) as Ls_scheduled_completion_date

,max(Case WHEN (ls_code = ''926'' and ls_scheduled_completion_date <> (''01/01/1900'')
             then ls_scheduled_completion_code
               else NULL End) as Ls_scheduled_completion_date

,max(Case WHEN (ls_code = ''927'' and ls_scheduled_completion_date <> (''01/01/1900'')
             then ls_scheduled_completion_code
               else NULL End) as Ls_scheduled_completion_date

,max(Case WHEN (ls_code = ''928'' and ls_scheduled_completion_date <> (''01/01/1900'')
             then ls_scheduled_completion_code
               else NULL End) as Ls_scheduled_completion_date

from master x
     inner join(select loan_number, MAX(ls_scheduled_completion_date) as COMPL_DATE
                MAX(ls_actual_completion_date) as APPROVAL_DATE

     Where LS_code in (''924'', ''926'', ''927'', ''928'', ''D08'', ''H38'', ''H79'', ''H42'',
                       ''M40'', ''M29'', ''M10'', ''M40'', ''P31'', ''P49'', ''S17'')

      Group by loan_number order by loan_number) y
      on x.loan_number = y.loan_number
         and x.ls_scheduled_date = y.Compl_date
         and x.ls_actual_completion_date = y.approval_date
      Group by x.loan_number, x.ls_actual_completion_date, ls_scheduled_date

for fetch only with ur')

to format code, select it in the editor and press Control-K. — John Saunders, Jul 12 '10 at 05:25

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

0

Don't you mean LS_SCHEDULED_COMPLETION_DATE?

There doesn't appear to be a LS_SCHEDULED_DATE anywhere in the query other than in the on and group by clauses where I'm assuming the error is occurring.

From IBM's own publib site:

For a SELECT or DELETE statement, the specified column is not a column of any of the tables or views identified in a FROM clause in the statement.

Verify that the names are specified correctly in the SQL statement. For a SELECT statement, ensure that all the required tables are named in the FROM clause.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jul 12 '10 at 05:32

paxdiablo

854,327
234
1,573
1,953

No probs. I like DB2 but it's error messages seem to have a fairly low signal-to-noise ratio :-) – paxdiablo Jul 12 '10 at 05:47
I am also struggling to get this code to write one row per loan with potential of code/dates. In example: loan_number (0000034), ls_code(924), ls_actual_completion_date(2008-05-07 00:00:00, ls_scheduled_completion_date(2008-09-27 00:00:00), STEP924_COMPL_DATE(2008-05-19 00:00:00), STEP926_COMPL_DATE(2008-05-19 00:00:00), STEP927_COMPL_DATE(2008-05-29 00:00:00), STEP928_COMPL_DATE (NULL), ACTUAL_COMPL_DATE(NULL) APPROVAL_DATE(2008-06-01 00:00:00) Of course the column names are across with corresponding dates below the appropriate column. – JMS49 Jul 12 '10 at 05:54
Paxdiablo, I have checked the code and do not see misspelled names. The required tables are in the from clause.. – JMS49 Jul 12 '10 at 06:04
@JMS49, that sounds like a different problem (more to do with SQL than misspelling) and one that is hard to communicate in a small unformatted comment box (at least I'm having a hard time understanding it). I would suggest starting another question for it so it gets a better response than just me looking at it. – paxdiablo Jul 12 '10 at 06:22
I found that the file Master was misspelled within the inner join statement. – JMS49 Jul 12 '10 at 10:40

DB2 error on TSQL

1 Answers1