Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Can't find the error.

Author  Topic 

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-08-28 : 15:13:41
I've been looking at this for 2 hours. I really despise the crappy errors that Query Analyzer throws.

Anyway, this works syntactically: 

DECLARE @DateFrom AS DATETIME

SET @DateFrom = '01/01/2002'
SELECT  B4.*,
        AF.NewFNBOAMT,
        AF.SHIPPEDNOTINVOICED
FROM    --Original
        ( SELECT    sorels.identity_column,
                    sorels.fsono,
                    dbo.sorels.fenumber,
                    dbo.soitem.fshipitem,
                    dbo.sorels.frelease,
                    dbo.sorels.forderqty,
                    dbo.sorels.finvqty,
                    DBO.SORELS.FUNETPRICE,
                    ( SORELS.FSHIPBOOK + SORELS.FSHIPBUY + SORELS.FSHIPMAKE ) AS SHIPPED,
                    ( CASE WHEN SOITEM.FSHIPITEM = 1
                           THEN ( ( SORELS.FORDERQTY - ( SORELS.FSHIPBOOK
                                                         + SORELS.FSHIPBUY
                                                         + SORELS.FSHIPMAKE ) )
                                  * SORELS.FUNETPRICE )
                           ELSE 0
                      END ) AS oldFNBOAMT
          FROM      SORELS
                    JOIN SOMAST ON SOMAST.FSONO = SORELS.FSONO
                    JOIN SOITEM ON SOITEM.FSONO = SORELS.FSONO
                                   AND SOITEM.FINUMBER = SORELS.FINUMBER
          WHERE     SOMAST.FSTATUS = 'OPEN'
                    AND FMASTERREL = 0
                    AND somast.forderdate >= CONVERT(DATETIME, @DateFrom)
        ) B4
        INNER JOIN --  New Method 
        ( SELECT    sorels.identity_column,
                    sorels.fsono,
                    dbo.sorels.fenumber,
                    dbo.sorels.frelease,
                    COALESCE(dbo.GETSHIPPEDNOTINVOICEDQTY(sorels.fsono + sorels.finumber + sorels.frelease),
                                 0) AS SHIPPEDNOTINVOICED,
                    ( SORELS.forderqty - SORELS.finvqty
                      - COALESCE(dbo.GETSHIPPEDNOTINVOICEDQTY(sorels.fsono + sorels.finumber + sorels.frelease),
                                 0)) * SORELS.funetprice  AS NewFNBOAMT
          FROM      SORELS
                    JOIN SOMAST ON SOMAST.FSONO = SORELS.FSONO
                    JOIN SOITEM ON SOITEM.FSONO = SORELS.FSONO
                                   AND SOITEM.FINUMBER = SORELS.FINUMBER
          WHERE     SOMAST.FSTATUS = 'OPEN'
                    AND FMASTERREL = 0
                    AND somast.forderdate >= CONVERT(DATETIME, @DateFrom)
        ) Af ON b4.IDENTITY_column = Af.identity_column
WHERE   Af.newFNBOAMT <> B4.oldFNBOAMT


When I tried to edit the part bolded to the following I get these errors. 

DECLARE @DateFrom AS DATETIME

SET @DateFrom = '01/01/2002'
SELECT  B4.*,
        AF.NewFNBOAMT,
       AF.SHIPPEDNOTINVOICED
FROM    --Original
        ( SELECT    DBO.sorels.identity_column,
                    DBO.sorels.fsono,
                    dbo.sorels.fenumber,
                    dbo.soitem.fshipitem,
                    dbo.sorels.frelease,
                    dbo.sorels.forderqty,
                    dbo.sorels.finvqty,
                    DBO.SORELS.FUNETPRICE,
                    ( SORELS.FSHIPBOOK + SORELS.FSHIPBUY + SORELS.FSHIPMAKE ) AS SHIPPED,
                    ( CASE WHEN SOITEM.FSHIPITEM = 1
                           THEN ( ( SORELS.FORDERQTY - ( SORELS.FSHIPBOOK
                                                         + SORELS.FSHIPBUY
                                                         + SORELS.FSHIPMAKE ) )
                                  * SORELS.FUNETPRICE )
                           ELSE 0
                      END ) AS oldFNBOAMT
          FROM      SORELS
                    JOIN SOMAST ON SOMAST.FSONO = SORELS.FSONO
                    JOIN SOITEM ON SOITEM.FSONO = SORELS.FSONO
                                   AND SOITEM.FINUMBER = SORELS.FINUMBER
          WHERE     SOMAST.FSTATUS = 'OPEN'
                    AND FMASTERREL = 0
                    AND somast.forderdate >= CONVERT(DATETIME, @DateFrom)
        ) B4
        INNER JOIN --  New Method 
        ( SELECT    sorels.identity_column,
                    sorels.fsono,
                    dbo.sorels.fenumber,
                    dbo.sorels.frelease,
                    COALESCE(dbo.GETSHIPPEDNOTINVOICEDQTY(sorels.fsono
                                                          + sorels.finumber
                                                          + sorels.frelease),
                             0) AS SHIPPEDNOTINVOICED,
                    SORELS.forderqty - SORELS.finvqty
                    - ( CASE WHEN ( DBO.SORELS.fshipbook + DBO.SORELS.fshipbuy
                                    + dbo.sorelsfshipmake )
                                  - dbo.sorels.finvqty > COALESCE(dbo.GETSHIPPEDNOTINVOICEDQTY(sorels.fsono + sorels.finumber + sorels.frelease), 0)
                             THEN 
                             COALESCE(dbo.GETSHIPPEDNOTINVOICEDQTY(sorels.fsono + sorels.finumber + sorels.frelease),0)
                             ELSE 
                             ( SORELS.fshipbook + SORELS.fshipbuy
                                    + dbo.sorelsfshipmake )
                                  - dbo.sorels.finvqty
                        END ) * SORELS.funetprice AS NewFNBOAMT
          FROM      SORELS
                    JOIN SOMAST ON SOMAST.FSONO = SORELS.FSONO
                    JOIN SOITEM ON SOITEM.FSONO = SORELS.FSONO
                                   AND SOITEM.FINUMBER = SORELS.FINUMBER
          WHERE     SOMAST.FSTATUS = 'OPEN'
                    AND FMASTERREL = 0
                    AND somast.forderdate >= CONVERT(DATETIME, @DateFrom)) Af ON b4.IDENTITY_column = Af.identity_column
WHERE   Af.newFNBOAMT <> B4.oldFNBOAMT


Server: Msg 107, Level 16, State 2, Line 4
The column prefix 'dbo' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 4
The column prefix 'dbo' does not match with a table name or alias name used in the query.


Anybody?

sodeep
Master Smack Fu Yak Hacker

7174 Posts
Posted - 2008-08-28 : 15:18:23
You need to remove dbo as sorels is already owner.

quote:
Originally posted by DavidChel

I've been looking at this for 2 hours. I really despise the crappy errors that Query Analyzer throws.

Anyway, this works syntactically: 

DECLARE @DateFrom AS DATETIME

SET @DateFrom = '01/01/2002'
SELECT  B4.*,
        AF.NewFNBOAMT,
        AF.SHIPPEDNOTINVOICED
FROM    --Original
        ( SELECT    sorels.identity_column,
                    sorels.fsono,
                    dbo.sorels.fenumber,
                    dbo.soitem.fshipitem,
                    dbo.sorels.frelease,
                    dbo.sorels.forderqty,
                    dbo.sorels.finvqty,
                    DBO.SORELS.FUNETPRICE,
                    ( SORELS.FSHIPBOOK + SORELS.FSHIPBUY + SORELS.FSHIPMAKE ) AS SHIPPED,
                    ( CASE WHEN SOITEM.FSHIPITEM = 1
                           THEN ( ( SORELS.FORDERQTY - ( SORELS.FSHIPBOOK
                                                         + SORELS.FSHIPBUY
                                                         + SORELS.FSHIPMAKE ) )
                                  * SORELS.FUNETPRICE )
                           ELSE 0
                      END ) AS oldFNBOAMT
          FROM      SORELS
                    JOIN SOMAST ON SOMAST.FSONO = SORELS.FSONO
                    JOIN SOITEM ON SOITEM.FSONO = SORELS.FSONO
                                   AND SOITEM.FINUMBER = SORELS.FINUMBER
          WHERE     SOMAST.FSTATUS = 'OPEN'
                    AND FMASTERREL = 0
                    AND somast.forderdate >= CONVERT(DATETIME, @DateFrom)
        ) B4
        INNER JOIN --  New Method 
        ( SELECT    sorels.identity_column,
                    sorels.fsono,
                    dbo.sorels.fenumber,
                    dbo.sorels.frelease,
                    COALESCE(dbo.GETSHIPPEDNOTINVOICEDQTY(sorels.fsono + sorels.finumber + sorels.frelease),
                                 0) AS SHIPPEDNOTINVOICED,
                    ( SORELS.forderqty - SORELS.finvqty
                      - COALESCE(dbo.GETSHIPPEDNOTINVOICEDQTY(sorels.fsono + sorels.finumber + sorels.frelease),
                                 0)) * SORELS.funetprice  AS NewFNBOAMT
          FROM      SORELS
                    JOIN SOMAST ON SOMAST.FSONO = SORELS.FSONO
                    JOIN SOITEM ON SOITEM.FSONO = SORELS.FSONO
                                   AND SOITEM.FINUMBER = SORELS.FINUMBER
          WHERE     SOMAST.FSTATUS = 'OPEN'
                    AND FMASTERREL = 0
                    AND somast.forderdate >= CONVERT(DATETIME, @DateFrom)
        ) Af ON b4.IDENTITY_column = Af.identity_column
WHERE   Af.newFNBOAMT <> B4.oldFNBOAMT


When I tried to edit the part bolded to the following I get these errors. 

DECLARE @DateFrom AS DATETIME

SET @DateFrom = '01/01/2002'
SELECT  B4.*,
        AF.NewFNBOAMT,
       AF.SHIPPEDNOTINVOICED
FROM    --Original
        ( SELECT    DBO.sorels.identity_column,
                    DBO.sorels.fsono,
                    dbo.sorels.fenumber,
                    dbo.soitem.fshipitem,
                    dbo.sorels.frelease,
                    dbo.sorels.forderqty,
                    dbo.sorels.finvqty,
                    DBO.SORELS.FUNETPRICE,
                    ( SORELS.FSHIPBOOK + SORELS.FSHIPBUY + SORELS.FSHIPMAKE ) AS SHIPPED,
                    ( CASE WHEN SOITEM.FSHIPITEM = 1
                           THEN ( ( SORELS.FORDERQTY - ( SORELS.FSHIPBOOK
                                                         + SORELS.FSHIPBUY
                                                         + SORELS.FSHIPMAKE ) )
                                  * SORELS.FUNETPRICE )
                           ELSE 0
                      END ) AS oldFNBOAMT
          FROM      SORELS
                    JOIN SOMAST ON SOMAST.FSONO = SORELS.FSONO
                    JOIN SOITEM ON SOITEM.FSONO = SORELS.FSONO
                                   AND SOITEM.FINUMBER = SORELS.FINUMBER
          WHERE     SOMAST.FSTATUS = 'OPEN'
                    AND FMASTERREL = 0
                    AND somast.forderdate >= CONVERT(DATETIME, @DateFrom)
        ) B4
        INNER JOIN --  New Method 
        ( SELECT    sorels.identity_column,
                    sorels.fsono,
                    dbo.sorels.fenumber,
                    dbo.sorels.frelease,
                    COALESCE(dbo.GETSHIPPEDNOTINVOICEDQTY(sorels.fsono
                                                          + sorels.finumber
                                                          + sorels.frelease),
                             0) AS SHIPPEDNOTINVOICED,
                    SORELS.forderqty - SORELS.finvqty
                    - ( CASE WHEN ( DBO.SORELS.fshipbook + DBO.SORELS.fshipbuy
                                    + dbo.sorelsfshipmake )
                                  - dbo.sorels.finvqty > COALESCE(dbo.GETSHIPPEDNOTINVOICEDQTY(sorels.fsono + sorels.finumber + sorels.frelease), 0)
                             THEN 
                             COALESCE(dbo.GETSHIPPEDNOTINVOICEDQTY(sorels.fsono + sorels.finumber + sorels.frelease),0)
                             ELSE 
                             ( SORELS.fshipbook + SORELS.fshipbuy
                                    + dbo.sorelsfshipmake )
                                  - dbo.sorels.finvqty
                        END ) * SORELS.funetprice AS NewFNBOAMT
          FROM      SORELS
                    JOIN SOMAST ON SOMAST.FSONO = SORELS.FSONO
                    JOIN SOITEM ON SOITEM.FSONO = SORELS.FSONO
                                   AND SOITEM.FINUMBER = SORELS.FINUMBER
          WHERE     SOMAST.FSTATUS = 'OPEN'
                    AND FMASTERREL = 0
                    AND somast.forderdate >= CONVERT(DATETIME, @DateFrom)) Af ON b4.IDENTITY_column = Af.identity_column
WHERE   Af.newFNBOAMT <> B4.oldFNBOAMT


Server: Msg 107, Level 16, State 2, Line 4
The column prefix 'dbo' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 4
The column prefix 'dbo' does not match with a table name or alias name used in the query.


Anybody?
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-08-28 : 15:31:48
Thank you Sodeep. I think I need to learn something here.

Forgive my ignorance, but why does the dbo. matter?

Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-08-28 : 15:35:20
Secondly, when I change the code to this: 

DECLARE @DateFrom AS DATETIME

SET @DateFrom = '01/01/2002'
SELECT  B4.*,
        AF.NewFNBOAMT,
       AF.SHIPPEDNOTINVOICED
FROM    --Original
        ( SELECT    sorels.identity_column,
                    sorels.fsono,
                    sorels.fenumber,
                    soitem.fshipitem,
                    sorels.frelease,
                    sorels.forderqty,
                    sorels.finvqty,
                    SORELS.FUNETPRICE,
                    ( SORELS.FSHIPBOOK + SORELS.FSHIPBUY + SORELS.FSHIPMAKE ) AS SHIPPED,
                    ( CASE WHEN SOITEM.FSHIPITEM = 1
                           THEN ( ( SORELS.FORDERQTY - ( SORELS.FSHIPBOOK
                                                         + SORELS.FSHIPBUY
                                                         + SORELS.FSHIPMAKE ) )
                                  * SORELS.FUNETPRICE )
                           ELSE 0
                      END ) AS oldFNBOAMT
          FROM      SORELS
                    JOIN SOMAST ON SOMAST.FSONO = SORELS.FSONO
                    JOIN SOITEM ON SOITEM.FSONO = SORELS.FSONO
                                   AND SOITEM.FINUMBER = SORELS.FINUMBER
          WHERE     SOMAST.FSTATUS = 'OPEN'
                    AND FMASTERREL = 0
                    AND somast.forderdate >= CONVERT(DATETIME, @DateFrom)
        ) B4
        INNER JOIN --  New Method 
        ( SELECT    sorels.identity_column,
                    sorels.fsono,
                    sorels.fenumber,
                    sorels.frelease,
                    COALESCE(GETSHIPPEDNOTINVOICEDQTY(sorels.fsono
                                                          + sorels.finumber
                                                          + sorels.frelease),
                             0) AS SHIPPEDNOTINVOICED,
                    (SORELS.forderqty - SORELS.finvqty
                    - ( CASE WHEN ( SORELS.fshipbook + SORELS.fshipbuy
                                    + sorelsfshipmake )
                                  - sorels.finvqty > COALESCE(GETSHIPPEDNOTINVOICEDQTY(sorels.fsono + sorels.finumber + sorels.frelease), 0)
                             THEN 
                             COALESCE(GETSHIPPEDNOTINVOICEDQTY(sorels.fsono + sorels.finumber + sorels.frelease),0)
                             ELSE 
                             ( SORELS.fshipbook + SORELS.fshipbuy
                                    + sorelsfshipmake )
                                  - sorels.finvqty
                        END ) * SORELS.funetprice) AS NewFNBOAMT
          FROM      SORELS
                    JOIN SOMAST ON SOMAST.FSONO = SORELS.FSONO
                    JOIN SOITEM ON SOITEM.FSONO = SORELS.FSONO
                                   AND SOITEM.FINUMBER = SORELS.FINUMBER
          WHERE     SOMAST.FSTATUS = 'OPEN'
                    AND FMASTERREL = 0
                    AND somast.forderdate >= CONVERT(DATETIME, @DateFrom)) Af ON b4.IDENTITY_column = Af.identity_column
WHERE   Af.newFNBOAMT <> B4.oldFNBOAMT


I still get the following error: 

Server: Msg 195, Level 15, State 10, Line 39
'GETSHIPPEDNOTINVOICEDQTY' is not a recognized function name.


I assume I’m getting that error because I don’t have dbo.getshippednotinvoicedqty. I replace that and get: 

DECLARE @DateFrom AS DATETIME

SET @DateFrom = '01/01/2002'
SELECT  B4.*,
        AF.NewFNBOAMT,
       AF.SHIPPEDNOTINVOICED
FROM    --Original
        ( SELECT    sorels.identity_column,
                    sorels.fsono,
                    sorels.fenumber,
                    soitem.fshipitem,
                    sorels.frelease,
                    sorels.forderqty,
                    sorels.finvqty,
                    SORELS.FUNETPRICE,
                    ( SORELS.FSHIPBOOK + SORELS.FSHIPBUY + SORELS.FSHIPMAKE ) AS SHIPPED,
                    ( CASE WHEN SOITEM.FSHIPITEM = 1
                           THEN ( ( SORELS.FORDERQTY - ( SORELS.FSHIPBOOK
                                                         + SORELS.FSHIPBUY
                                                         + SORELS.FSHIPMAKE ) )
                                  * SORELS.FUNETPRICE )
                           ELSE 0
                      END ) AS oldFNBOAMT
          FROM      SORELS
                    JOIN SOMAST ON SOMAST.FSONO = SORELS.FSONO
                    JOIN SOITEM ON SOITEM.FSONO = SORELS.FSONO
                                   AND SOITEM.FINUMBER = SORELS.FINUMBER
          WHERE     SOMAST.FSTATUS = 'OPEN'
                    AND FMASTERREL = 0
                    AND somast.forderdate >= CONVERT(DATETIME, @DateFrom)
        ) B4
        INNER JOIN --  New Method 
        ( SELECT    sorels.identity_column,
                    sorels.fsono,
                    sorels.fenumber,
                    sorels.frelease,
                    COALESCE(DBO.GETSHIPPEDNOTINVOICEDQTY(sorels.fsono
                                                          + sorels.finumber
                                                          + sorels.frelease),
                             0) AS SHIPPEDNOTINVOICED,
                    (SORELS.forderqty - SORELS.finvqty
                    - ( CASE WHEN ( SORELS.fshipbook + SORELS.fshipbuy
                                    + sorelsfshipmake )
                                  - sorels.finvqty > COALESCE(DBO.GETSHIPPEDNOTINVOICEDQTY(sorels.fsono + sorels.finumber + sorels.frelease), 0)
                             THEN 
                             COALESCE(DBO.GETSHIPPEDNOTINVOICEDQTY(sorels.fsono + sorels.finumber + sorels.frelease),0)
                             ELSE 
                             ( SORELS.fshipbook + SORELS.fshipbuy
                                    + sorelsfshipmake )
                                  - sorels.finvqty
                        END ) * SORELS.funetprice) AS NewFNBOAMT
          FROM      SORELS
                    JOIN SOMAST ON SOMAST.FSONO = SORELS.FSONO
                    JOIN SOITEM ON SOITEM.FSONO = SORELS.FSONO
                                   AND SOITEM.FINUMBER = SORELS.FINUMBER
          WHERE     SOMAST.FSTATUS = 'OPEN'
                    AND FMASTERREL = 0
                    AND somast.forderdate >= CONVERT(DATETIME, @DateFrom)) Af ON b4.IDENTITY_column = Af.identity_column
WHERE   Af.newFNBOAMT <> B4.oldFNBOAMT


This works syntactically. However, what am I missing about the importance of being the database owner?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2008-08-28 : 15:41:33
dbo isn't necessarily the database owner.

Typically, developers just use dbo as the object owners, so dbo.ObjectName works great. But some people setup users to own the objects, so you need to use UserName.ObjectName.

So the three-part naming convention is: DatabaseName.ObjectOwner.ObjectName where ObjectOwner is typically dbo. You can leave off DatabaseName in almost all cases.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2008-08-28 : 15:41:55
So the question you need to answer is who owns your objects?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2008-08-28 : 17:26:12
Just to follow up on what Tara said, because GETSHIPPEDNOTINVOICEDQTY is a function SQL required that you prefix it with the owner. You can use the ObjectOwner.ObjectName when joining to the tables, but when you are specifying the column list you do not specify the Owner. Presumably because each object name needs to be unique for a particular query, whether the actual name is unique or by using an alias.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site