Hi all,
I wrote a user-defined sql query in a oledb stage:
select 'N' as fund_fl, max(dim_dt_id) as dim_dt_id
from dim_dt
where dt_1 is not null
union
select 'Y', max(dim_dt_id)
from dim_dt
where dt_2 is not null
I got the error msg: OLE DB provider error: Column does not exist.
How to handle the generated column -- fund_fl? I really want the column to be there as part of the output.
Thank you in advance.
Uer-defined sql question
Moderators: chulett, rschirm, roy
Uer-defined sql question
Kwang
Don't use that particular stage, but if it's anything like OCI...
As long as you are sure that all of the columns being selected do in fact exist, try it without the 'as' statements in the first query of your union. It will populate your output using whatever column names are defined on the 'Columns' tab, so they really aren't needed.
As long as you are sure that all of the columns being selected do in fact exist, try it without the 'as' statements in the first query of your union. It will populate your output using whatever column names are defined on the 'Columns' tab, so they really aren't needed.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Try executing your SQL query in the database's own "tool", it may give you a better indication of the error. I don't see anything wrong with it a first sight (it may just be a typo in the column names).
Ogmios
Ogmios
Last edited by ogmios on Tue Feb 03, 2004 12:51 pm, edited 1 time in total.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Re: Uer-defined sql question
I'm surprised that it lets you use MAX() without demanding GROUP BY on the other column.Kwang wrote:Hi all,
I wrote a user-defined sql query in a oledb stage:
select 'N' as fund_fl, max(dim_dt_id) as dim_dt_id
from dim_dt
where dt_1 is not null
union
select 'Y', max(dim_dt_id)
from dim_dt
where dt_2 is not null
I got the error msg: OLE DB provider error: Column does not exist.
How to handle the generated column -- fund_fl? I really want the column to be there as part of the output.
Thank you in advance.
That said, is it the case that columns dim_dt_id, dt_1 and dt_2 all exist on the dim_dt table? (I ask this because of the exact nature of the error message.)
As an experiment, can you try the same query in an ODBC stage? There may be some hiccup in the OLEDB provider for your particular database, or in the way that the OLEDB stage presents the query to it.
Finally, try quoted identifiers. It may seem a little strange, but I've seen it solve a different, though possibly related problem.
Code: Select all
select 'N' as fund_fl, max("dim_dt_id") as dim_dt_id
from "dim_dt"
where "dt_1" is not null
union
select 'Y', max("dim_dt_id")
from "dim_dt"
where "dt_2" is not null
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Re: Uer-defined sql question
I figured it out. My new query statement is as the following:
select 'N' as 'date_fl', max(dim_dt1) as 'dim_dt_id'
from dim_dt
where dim_dt1 is not null
union
select 'Y', max(dim_dt2)
from dim_dt
where dim_dt2 is not null
The only difference is I used quotes around my column names.
Thank you for everybody's input.
Kathy
select 'N' as 'date_fl', max(dim_dt1) as 'dim_dt_id'
from dim_dt
where dim_dt1 is not null
union
select 'Y', max(dim_dt2)
from dim_dt
where dim_dt2 is not null
The only difference is I used quotes around my column names.
Thank you for everybody's input.
Kathy
Kwang