Comparing 2 Queries and getting errors.

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

Comparing 2 Queries and getting errors.

Author

Topic

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-08-21 : 09:42:31

I'm trying to change the way we keep track of data at my company.

Here is how far I have gotten and this works:

 DECLARE @DateFrom AS DATETIME

SET @DateFrom = '01/01/2008'
SELECT ( --Original Query

SELECT   sum (
	CASE WHEN SOITEM.FSHIPITEM = 1
	THEN ((SORELS.FORDERQTY - (SORELS.FSHIPBOOK + SORELS.FSHIPBUY + SORELS.FSHIPMAKE)) * SORELS.FUNETPRICE)
	ELSE 0 
	end)
	 AS FNBOAMT
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))
        -   --  New Method 
        (SELECT  
        SUM(
        CASE WHEN dbo.sorels.forderqty = dbo.sorels.finvqty
        THEN 0 
        WHEN (dbo.sorels.forderqty > dbo.sorels.finvqty AND dbo.sorels.finvqty > 0)
        THEN	((dbo.sorels.forderqty - dbo.sorels.finvqty) * dbo.sorels.funetprice)
        ELSE
        ( ( SORELS.FORDERQTY - ( SORELS.FSHIPBOOK + SORELS.FSHIPBUY
                                 + SORELS.FSHIPMAKE ) ) * SORELS.FUNETPRICE ) 
                                 END) AS FNBOAMT
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))

This tells me the difference is $10,939.60.

In order to figure out where I've gone wrong, I want to compare these queries per record. I want to compare the FNBOAMT per line from the first Query to my Query. If I can figure out which fsono's are different I can figure out why by looking at the data. So, this is where I am which of course does not work. What am I doing wrong?

DECLARE @DateFrom AS DATETIME

SET @DateFrom = '03/04/2008'
SELECT * FROM 

--Original

(SELECT  sorels.identity_column,
	sorels.fsono,
	dbo.sorels.fenumber,
	dbo.sorels.frelease,  
	(CASE WHEN SOITEM.FSHIPITEM = 1
	THEN ((SORELS.FORDERQTY - (SORELS.FSHIPBOOK + SORELS.FSHIPBUY + SORELS.FSHIPMAKE)) * SORELS.FUNETPRICE)
	ELSE 0 
	END)
	 AS FNBOAMT
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,  
        (
        CASE WHEN dbo.sorels.forderqty = dbo.sorels.finvqty
        THEN 0 
        WHEN (dbo.sorels.forderqty > dbo.sorels.finvqty AND dbo.sorels.finvqty > 0)
        THEN	((dbo.sorels.forderqty - dbo.sorels.finvqty) * dbo.sorels.funetprice)
        ELSE
        ( ( SORELS.FORDERQTY - ( SORELS.FSHIPBOOK + SORELS.FSHIPBUY
                                 + SORELS.FSHIPMAKE ) ) * SORELS.FUNETPRICE ) 
                                 END) AS FNBOAMT
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.FNBOAMT <> B4.FNBOAMT)

Here are the errors I'm getting. Can I not do this in SQL 2000?

Server: Msg 170, Level 15, State 1, Line 25
Line 25: Incorrect syntax near 'B4'.
Server: Msg 170, Level 15, State 1, Line 50
Line 50: Incorrect syntax near 'Af'.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-21 : 09:45:58

Add
select b4.* from
as first line

Madhivanan

Failing to plan is Planning to fail

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-08-21 : 09:49:40

quote:
Originally posted by madhivanan

Add
select b4.* from
as first line

Madhivanan

Failing to plan is Planning to fail

I appreciate your help, but I still get the same errors.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-21 : 09:51:12

quote:

Originally posted by DavidChel

I'm trying to change the way we keep track of data at my company.

Here is how far I have gotten and this works:

 DECLARE @DateFrom AS DATETIME

SET @DateFrom = '01/01/2008'
SELECT ( --Original Query

SELECT   sum (
	CASE WHEN SOITEM.FSHIPITEM = 1
	THEN ((SORELS.FORDERQTY - (SORELS.FSHIPBOOK + SORELS.FSHIPBUY + SORELS.FSHIPMAKE)) * SORELS.FUNETPRICE)
	ELSE 0 
	end)
	 AS FNBOAMT
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))
        -   --  New Method 
        (SELECT  
        SUM(
        CASE WHEN dbo.sorels.forderqty = dbo.sorels.finvqty
        THEN 0 
        WHEN (dbo.sorels.forderqty > dbo.sorels.finvqty AND dbo.sorels.finvqty > 0)
        THEN	((dbo.sorels.forderqty - dbo.sorels.finvqty) * dbo.sorels.funetprice)
        ELSE
        ( ( SORELS.FORDERQTY - ( SORELS.FSHIPBOOK + SORELS.FSHIPBUY
                                 + SORELS.FSHIPMAKE ) ) * SORELS.FUNETPRICE ) 
                                 END) AS FNBOAMT
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))

DECLARE @DateFrom AS DATETIME

SET @DateFrom = '03/04/2008'
SELECT * FROM 

--Original

(SELECT  sorels.identity_column,
	sorels.fsono,
	dbo.sorels.fenumber,
	dbo.sorels.frelease,  
	(CASE WHEN SOITEM.FSHIPITEM = 1
	THEN ((SORELS.FORDERQTY - (SORELS.FSHIPBOOK + SORELS.FSHIPBUY + SORELS.FSHIPMAKE)) * SORELS.FUNETPRICE)
	ELSE 0 
	END)
	 AS FNBOAMT
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,  
        (
        CASE WHEN dbo.sorels.forderqty = dbo.sorels.finvqty
        THEN 0 
        WHEN (dbo.sorels.forderqty > dbo.sorels.finvqty AND dbo.sorels.finvqty > 0)
        THEN	((dbo.sorels.forderqty - dbo.sorels.finvqty) * dbo.sorels.funetprice)
        ELSE
        ( ( SORELS.FORDERQTY - ( SORELS.FSHIPBOOK + SORELS.FSHIPBUY
                                 + SORELS.FSHIPMAKE ) ) * SORELS.FUNETPRICE ) 
                                 END) AS FNBOAMT
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.FNBOAMT <> B4.FNBOAMT)

Here are the errors I'm getting. Can I not do this in SQL 2000?

Server: Msg 170, Level 15, State 1, Line 25
Line 25: Incorrect syntax near 'B4'.
Server: Msg 170, Level 15, State 1, Line 50
Line 50: Incorrect syntax near 'Af'.

try modifying as above

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-08-21 : 09:58:26

You know, I should have seen the -.

However, if I use the edited query, I still get:

Server: Msg 170, Level 15, State 1, Line 52
Line 52: Incorrect syntax near ')'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-21 : 09:59:54

quote:
Originally posted by DavidChel

You know, I should have seen the -.

However, if I use the edited query, I still get:

Server: Msg 170, Level 15, State 1, Line 52
Line 52: Incorrect syntax near ')'.

remove the last ) in query.

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-08-21 : 10:02:46

Ah, thanks guys. I greatly appreciate it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-21 : 10:03:52

This should be correct one

DECLARE @DateFrom AS DATETIME

SET @DateFrom = '03/04/2008'
SELECT * FROM 
--Original

(
     SELECT  sorels.identity_column,
	sorels.fsono,
	dbo.sorels.fenumber,
	dbo.sorels.frelease,  
	CASE WHEN SOITEM.FSHIPITEM = 1
	THEN ((SORELS.FORDERQTY - (SORELS.FSHIPBOOK + SORELS.FSHIPBUY + SORELS.FSHIPMAKE)) * SORELS.FUNETPRICE)
	ELSE 0 
	END
	 AS FNBOAMT
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,  
        
        CASE WHEN dbo.sorels.forderqty = dbo.sorels.finvqty
        THEN 0 
        WHEN (dbo.sorels.forderqty > dbo.sorels.finvqty AND dbo.sorels.finvqty > 0)
        THEN	((dbo.sorels.forderqty - dbo.sorels.finvqty) * dbo.sorels.funetprice)
        ELSE
        ( ( SORELS.FORDERQTY - ( SORELS.FSHIPBOOK + SORELS.FSHIPBUY
                                 + SORELS.FSHIPMAKE ) ) * SORELS.FUNETPRICE ) 
                                 END AS FNBOAMT
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.FNBOAMT <> B4.FNBOAMT

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-08-21 : 11:27:49

Thanks again. I took the query and added the fields I needed to prove where the first statement was inaccurate. My VP just called me a genius.

I replied that I had some help from some SQL gurus that I know.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-21 : 12:44:06

quote:
Originally posted by DavidChel

Thanks again. I took the query and added the fields I needed to prove where the first statement was inaccurate. My VP just called me a genius.

I replied that I had some help from some SQL gurus that I know.

You're welcome as always

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources