InValid Column Name 1

[JRCharlie]

Hi
I Have a table named schedule that holds all the orders shipped and not shipped, Also I have a view Named Schedule_view Puling all the orders from Schedule that have not been Shipped and both the table and the View have a Column Named RevisionNo this in CPP 12.
In My Application When I Pull all the orders from the View I Get this error
'ERROR [HY000] [PSQL][ODBC Client Interface][LNA][PSQL][SQL Engine]Error in expression: View1Tab1 . RevisionNO "
"ERROR [42S22] [PSQL][ODBC Client Interface][LNA][PSQL][SQL Engine]Invalid column name: 'RevisionNO'.
But when pulling from the Table I get No Error.
Why is this So.
Thanks

 

[mirtheil]

What's your view look like?

Mirtheil

http://www.mirtheil.com

 

[JRCharlie]

Thanks Mirtheil for your reply
See the atachment for the fields that Consistes the View, i didn't post it here because it is Huge.
This is the query I Used to create the view.
CREATE VIEW "SCHEDULE_View" AS
Select * From SCHEDULE
WHERE REQUIRED<>'00000000'
AND BVRES_00 = ''
AND WHSE=01
AND (SHIPPED = 0 OR SHIPPED IS NULL)
AND (shpreqComp=0 OR shpreqComp IS NULL)

 

[mirtheil]

What happens if you run the Select part of the view by itself. Does that work?
If you create the following view, can you access the RevisionNo field?

Create View ViewTest as
Select RevisionNo From SCHEDULE
WHERE REQUIRED<>'00000000'
AND BVRES_00 = ''
AND WHSE='01'
AND (SHIPPED = 0 OR SHIPPED IS NULL) 
AND (shpreqComp=0 OR shpreqComp IS NULL)

What tool are you using to execute the queries? If you're not using the Pervasive COntrol Center (PCC), please try that tool.


Mirtheil

http://www.mirtheil.com

 

[JRCharlie]

When I run The Query on Both PCC And also tried on Flyspeed SQL and the column Shows.
If I run the query from the Appliction the column does not show. I also tried renaming the column on the table , the same thing happens.

 

[mirtheil]

So, the view works correctly in the PCC and Flyspeed SQL?
If the view is working outside of the application (in PCC and Flyspeed SQL), it seems that the application might be the issue.
What's the application where it fails? If it's something you're writing, please post the code that sets up the query and runs it.

Mirtheil

http://www.mirtheil.com

 

[JRCharlie]

If I Use SelectString the same query used to create the view the column does not show.
If Selectstrin2 is used, I get the error that was originaly posted and it Fails at dr=.ExecuteReader

 Private Sub Read_Data()
        Dim wbcon As New OdbcConnection
        Dim cmd As New OdbcCommand
        Dim dr As OdbcDataReader
        Dim dt As New DataTable
        Dim SelectString As String = "Select * From SCHEDULE WHERE REQUIRED<>'00000000' AND BVRES_00 = '' AND WHSE=01 AND (SHIPPED = 0 OR SHIPPED IS NULL) AND (shpreqComp=0 OR shpreqComp IS NULL)"
        Dim SelectString2 As String = "Select SCHEDULE_View.* From  SCHEDULELOG.SCHEDULE_View"
        Try
            wbcon = Main.CW_Live_WB
            wbcon.Open()
            cmd = New OdbcCommand
            With cmd
                .CommandType = CommandType.Text
                .CommandText = SelectString2
                .Connection = wbcon
                dr = .ExecuteReader
                dt.Load(dr)
            End With
            dv_BVProgram = New DataView(dt)
        Catch ex As Exception
            MsgBox("Programming.Read_Data " & ex.Message)
        End Try
        wbcon.Close()
    End Sub

 

[mirtheil]

Does it still work if you use:

Dim SelectString2 As String = "Select * From  SCHEDULE_View"

By specifying "SCHEDULELOG" on the query, you are changing the database being used. Is it possible that you have two databases and one doesn't have the field you are looking for?

On another note, if your view is a "select *", you won't gain any performance using the view. If the standard SELECT works, that might be a better option.

Mirtheil

http://www.mirtheil.com

 

[JRCharlie]

I specified "SCHEDULELOG" on the query because I have two databases in the same Engine.
Instead of creating two Connection strings I was refering the Database by Spesifying it on the query.
There is only one SCHEDULELOG Database.
But by reading your last comment I created a new DSN for each DataBase and created a Connection String for each,and removed the database name from the Query and that fixed the Problem.
Just Curious now I dont understand what you meant by not gaining any performance using the view, can you elaberate.

 

[mirtheil]

Glad it's working.
A "View" is just a stored select statement. When running in the engine, whether you call a View or a Select, the engine runs the same code. The view isn't pre-compiled or cached. Also, a view usually limits the columns to "hide" non-necessary columns.
For example, if you run the following:

create table t1 (f1 int, f2 char(10), f3 char(10), f4 int);
insert into t1 values (1,'test value', '1',1);
insert into t1 values (2,'2', '2',2);
create view v1 as 
select f1, f4 from t1 where f2 = 'test value';
select * from t1;
select * from v1;

You'll get this:

select * from t1
         f1   f2           f3                    f4
===========   ==========   ==========   ===========
          1   test value   1                      1
          2   2            2                      2

select * from v1
         f1            f4
===========   ===========
          1             1

Mirtheil

http://www.mirtheil.com

 

[JRCharlie]

Thanks For Demostration.
The Schedule Table holds all the Orders.
While the view only holds the Orders ready for production but not shipped.