Uer-defined sql question

[Kwang]

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.

[chulett]

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.

[Kwang]

The problem is the first column is not from the table, it is created on the fly.

[ogmios]

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

[chulett]

The problem is the first column is not from the table, it is created on the fly.

Doesn't matter, it's a quoted literal. As noted, are you sure this runs fine outside of DataStage?

[Kwang]

Doesn't matter, it's a quoted literal. As noted, are you sure this runs fine outside of DataStage?[/quote]

Yes. The query runs O.K. in query analyzer.

[ray.wurlod]

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.

I'm surprised that it lets you use MAX() without demanding GROUP BY on the other column.

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

[Kwang]

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