Very Computer

tab1                            view
(                                 (
        col0            --->      col0
        col1            --->      col1
        col2            --->      col2
        date_added
        date_removed
)

tab2
(
        col0
        colA            --->      colA
        date_added      --->         last_updated    
        date_removed
)                                 )

The corresponding SQL would be

create view
(
        col0,
        col1,
        col2,
        colA,
        last_updated
) as
 select a.col0,
        a.col1,
        a.col2,
        b.colA,
        b.date_added
 from   tab1 a,
        tab2 b
 where
        a.col0 = b.col0 and
        a.date_removed is null and
        b.date_removed is null

Essentially, all the columns in the view match the name of the column
in the table they originally came from except "date_added" , which
has been renamed to "last_updated" .

Here are the results from the following queries:

        select col0, col1, last_updated from view
                sqsh    : returns expected results
                DBI/DBD : returns *nothing*

        select col0, col1, colA from view
                sqsh    : returns expected results
                DBI/DBD : returns expected results

When I create another view and keep the name "date_added" instead
of renaming it, here are the following results:

        select col0, col1, date_removed from view
                sqsh    : returns expected results
                DBI/DBD : returns expected results

        select col0, col1, colA from view
                sqsh    : returns expected results
                DBI/DBD : returns expected results

It is only when the column names from table to view differ,
and then only when using DBI/DBD, that I get no results returned.

Does anyone know what's going on?

----------------------------------------------------------------------
| Jim Hranicky, Senior SysAdmin                   UF/CISE Department |
| E314D CSE Building                            Phone (352) 392-1499 |

----------------------------------------------------------------------
         -  Encryption: its use by criminals is far less  -
         - frightening than its banishment by governments -
                      - Vote for Privacy -
--
----------------------------------------------------------------------
| Jim Hranicky, Senior SysAdmin                   UF/CISE Department |
| E314D CSE Building                            Phone (352) 392-1499 |

[ description of view definition snipped ]

Quote:> It is only when the column names from table to view differ,
> and then only when using DBI/DBD, that I get no results returned.

> Does anyone know what's going on?

Michael
--
Michael Peppler         -||-  Data Migrations Inc.

Int. Sybase User Group  -||-  http://www.isug.com

create view my_view_name as
select a.col0,
       a.col1,
       a.col2,
       b.colA,
       last_updated = b.date_added
from   tab1 a, tab2 b
where  a.col0 = b.col0
and    a.date_removed is null
and    b.date_removed is null

Quote:> Essentially, all the columns in the view match the name of the column
> in the table they originally came from except "date_added" , which
> has been renamed to "last_updated" .

> Here are the results from the following queries:

Is this your problem, or is it that the view is created
coreectly in your database, and you just typed it wrong
in your post?

Larry Coon
University of California