Nested SubQuery - SQL Server Forums

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

Nested SubQuery

Author

Topic

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-08-27 : 12:07:56

I'm using this Query provided by the manufacturer of my ERP system:

SELECT  Shmast.fcsono,
        ( Shitem.fshipqty - Shitem.finvqty ) AS qtycanbill       FROM    shmast,
        shitem
        LEFT JOIN inmast ON inmast.fpartno = shitem.fpartno
                            AND inmast.frev = shitem.frev
                            AND inmast.fac = shitem.fac
WHERE   Shitem.fshipno = Shmast.fshipno
        AND fconfirm = 'Y'
        AND ftype <> 'JO'
        AND ftype <> 'VE'
        AND ftype <> 'MI'
        AND shitem.fitemtype <> 'M'
        AND flisinv = 0
        AND fcstatus <> 'C'
        AND LEN(Shitem.IDONO) = 0	
        AND ( Shitem.fshipqty - Shitem.finvqty ) > 0

The bolded portion represents the quantity that has been shipped, but not invoiced. I need to include this in my Query which is below with the placeholder called SHIPPEDNOTINVOICED included.

SELECT  SUM(( SORELS.forderqty - SORELS.finvqty - SHIPPEDNOTINVOICED )
            * SORELS.funetprice) 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 SORELS.FDUEDATE >= CONVERT(DATETIME, '01/01/2002')
        AND FMASTERREL = 0

The relationship between these 2 queries would be

Query #1 shitem.fsokey =
Query #2 (nested) sorels.fsono+sorels.finumber+sorels.frelease

I've been looking at this for an hour and it's making my brain bleed.

karuna
Aged Yak Warrior

582 Posts

Posted - 2008-08-27 : 12:40:38

Just wondering, you can put the first query results in a temp table and use it for the second query ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 13:02:44

Are you using SQL 2005?

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-08-27 : 14:01:24

Thank you for your help, but I'd rather not use a temp table nor a cursor.

I'm using SQL 2000.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 14:15:23

then what you could do is create a UDF

CREATE FUNCTION GETSHIPPEDNOTINVOICEDVALUE
(
@ItemKey varchar(100)
)
RETURNS float AS
BEGIN
DECLARE @RetVal flaot
SELECT  @RetVal=( Shitem.fshipqty - Shitem.finvqty ) AS        FROM    shmast,
        shitem
        LEFT JOIN inmast ON inmast.fpartno = shitem.fpartno
                            AND inmast.frev = shitem.frev
                            AND inmast.fac = shitem.fac
WHERE   shitem.fsokey =@ItemKey
        AND Shitem.fshipno = Shmast.fshipno
        AND fconfirm = 'Y'
        AND ftype <> 'JO'
        AND ftype <> 'VE'
        AND ftype <> 'MI'
        AND shitem.fitemtype <> 'M'
        AND flisinv = 0
        AND fcstatus <> 'C'
        AND LEN(Shitem.IDONO) = 0	
        AND ( Shitem.fshipqty - Shitem.finvqty ) > 0
RETURN @RetVal
END

then use it in your code

SELECT  SUM(( SORELS.forderqty - SORELS.finvqty - dbo.GETSHIPPEDNOTINVOICEDVALUE(sorels.fsono+sorels.finumber+sorels.frelease) )
            * SORELS.funetprice) 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 SORELS.FDUEDATE >= CONVERT(DATETIME, '01/01/2002')
        AND FMASTERREL = 0

karuna
Aged Yak Warrior

582 Posts

Posted - 2008-08-27 : 14:42:30

quote:
Originally posted by DavidChel

Thank you for your help, but I'd rather not use a temp table nor a cursor.

I'm using SQL 2000.

Well, then the other option would to use the first query as a derived table and used it along with second query. May be something like this


SELECT  SUM(( SORELS.forderqty - SORELS.finvqty - SHIPPEDNOTINVOICED )
			* SORELS.funetprice) AS FNBOAMT
FROM    
(
	SELECT  Shmast.fcsono,Shitem.fsokey,
			( Shitem.fshipqty - Shitem.finvqty ) AS qtycanbill
	FROM    shmast, shitem
		LEFT JOIN inmast ON inmast.fpartno = shitem.fpartno
		AND inmast.frev = shitem.frev
		AND inmast.fac = shitem.fac
	WHERE   
		Shitem.fshipno = Shmast.fshipno
	AND fconfirm = 'Y'
	AND ftype <> 'JO'
	AND ftype <> 'VE'
	AND ftype <> 'MI'
	AND shitem.fitemtype <> 'M'
	AND flisinv = 0
	AND fcstatus <> 'C'
	AND LEN(Shitem.IDONO) = 0
) DerivedTable JOIN
SORELS ON DerivedTable.fsokey = (sorels.fsono+sorels.finumber+sorels.frelease)
		JOIN SOMAST ON SOMAST.FSONO = SORELS.FSONO
		JOIN SOITEM ON SOITEM.FSONO = SORELS.FSONO
		AND SOITEM.FINUMBER = SORELS.FINUMBER
WHERE   SOMAST.FSTATUS = 'OPEN'
		AND SORELS.FDUEDATE >= CONVERT(DATETIME, '01/01/2002')
		AND FMASTERREL = 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 14:52:48

but that can cause problem if any of other tables have 1 to many relationships with SORELS

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-08-27 : 16:56:41

Thanks again Visahk. I changed it a bit and got it to work somewhat.

So, here is where I am. Here is the UDF:

CREATE FUNCTION GETSHIPPEDNOTINVOICEDQTY ( @ItemKey VARCHAR(12) )
RETURNS NUMERIC(6, 5)
AS BEGIN
    DECLARE @RetVal NUMERIC(6, 5)
    SELECT  @RetVal = ( Shitem.fshipqty - Shitem.finvqty )
    FROM    shmast,
            shitem
            LEFT JOIN inmast ON inmast.fpartno = shitem.fpartno
                                AND inmast.frev = shitem.frev
                                AND inmast.fac = shitem.fac
    WHERE   shitem.fsokey = @ItemKey
            AND Shitem.fshipno = Shmast.fshipno
            AND fconfirm = 'Y'
            AND ftype <> 'JO'
            AND ftype <> 'VE'
            AND ftype <> 'MI'
            AND shitem.fitemtype <> 'M'
            AND flisinv = 0
            AND fcstatus <> 'C'
            AND LEN(Shitem.IDONO) = 0
            AND ( Shitem.fshipqty - Shitem.finvqty ) > 0
    RETURN @RetVal
   END

This works if I call the following statement:

SELECT  dbo.GETSHIPPEDNOTINVOICEDQTY('280091  1000')

However, if I run it inside the other Select statement:

SELECT  SUM(( SORELS.forderqty - SORELS.finvqty
              - dbo.GETSHIPPEDNOTINVOICEDQTY(sorels.fsono + sorels.finumber
                                               + sorels.frelease) )
            * SORELS.funetprice) 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 SORELS.FDUEDATE >= CONVERT(DATETIME, '01/01/2002')
        AND FMASTERREL = 0

I get the following error:

Server: Msg 8115, Level 16, State 8, Procedure GETSHIPPEDNOTINVOICEDQTY, Line 5
Arithmetic overflow error converting numeric to data type numeric.
Warning: Null value is eliminated by an aggregate or other SET operation.

Can anyone point me in the right direction?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-28 : 03:50:41

change the scale and precision of numeric field. use Numeric(20,5)
instead of Numeric(6,5) in UDF.

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-08-28 : 09:11:31

A couple of things occured to me. Should I edit the UDF to automatically convert a null value to zero?

(( SORELS.forderqty - SORELS.finvqty  -dbo.GETSHIPPEDNOTINVOICEDQTY(sorels.fsono + sorels.finumber
            + sorels.frelease) )
            * SORELS.funetprice) AS FNBOAMT

If forderqty = 10, finvqty = 5 and the value returned from GETSHIPPEDNOTINVOICEDQTY = Null wouldn't that entire line return NULL?

Therefore, wouldn't the line work as

(( SORELS.forderqty - SORELS.finvqty - COALESCE(dbo.GETSHIPPEDNOTINVOICEDQTY(sorels.fsono + sorels.finumber
            + sorels.frelease),0))
            * SORELS.funetprice) AS FNBOAMT

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-28 : 09:37:37

quote:
Originally posted by DavidChel

A couple of things occured to me. Should I edit the UDF to automatically convert a null value to zero?

(( SORELS.forderqty - SORELS.finvqty -dbo.GETSHIPPEDNOTINVOICEDQTY(sorels.fsono + sorels.finumber
+ sorels.frelease) )
* SORELS.funetprice) AS FNBOAMT

If forderqty = 10, finvqty = 5 and the value returned from GETSHIPPEDNOTINVOICEDQTY = Null wouldn't that entire line return NULL?

Therefore, wouldn't the line work as

(( SORELS.forderqty - SORELS.finvqty - COALESCE(dbo.GETSHIPPEDNOTINVOICEDQTY(sorels.fsono + sorels.finumber
+ sorels.frelease),0))
* SORELS.funetprice) AS FNBOAMT

If NULL is causing problem then use COALESCE() or ISNULL() to convert it to 0.

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-08-28 : 09:42:45

quote:
Originally posted by visakh16

change the scale and precision of numeric field. use Numeric(20,5)
instead of Numeric(6,5) in UDF.

Thanks Visahk, I just realized how stupid it is to use 6,5. However, when I switched it to 20,5 I still got this error:

Server: Msg 8115, Level 16, State 8, Procedure GETSHIPPEDNOTINVOICEDQTY, Line 22
Arithmetic overflow error converting numeric to data type numeric.

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-08-28 : 09:45:30

Oops. Nevermind, I'm an idiot. I forgot to change this too:

RETURNS NUMERIC (6, 5)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-28 : 09:50:28

quote:
Originally posted by DavidChel

Oops. Nevermind, I'm an idiot. I forgot to change this too:

RETURNS NUMERIC (6, 5)

is it working now?

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-08-28 : 09:56:18

Yes thank you. Syntactically it works. However the numbers aren't working out the way I thought they would.

I greatly appreciate your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-28 : 10:04:18

quote:
Originally posted by DavidChel

Yes thank you. Syntactically it works. However the numbers aren't working out the way I thought they would.

I greatly appreciate your help.

can you show sample results from both queries and explain waht you're trying to get as final result from them?

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-08-28 : 12:22:34

quote:
Originally posted by visakh16

quote:
Originally posted by DavidChel

Yes thank you. Syntactically it works. However the numbers aren't working out the way I thought they would.

I greatly appreciate your help.

can you show sample results from both queries and explain waht you're trying to get as final result from them?

I think I'm almost there. Is there a way to use a function similar to Min which would accomplish the following?

Min(2,4) = 2

Min(forderqty,finvqty) = 2

In other words, I give it two options and it selects the minimum one?

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-09-02 : 10:15:28

quote:
Originally posted by DavidChel

quote:
Originally posted by visakh16

quote:
Originally posted by DavidChel

Yes thank you. Syntactically it works. However the numbers aren't working out the way I thought they would.

I greatly appreciate your help.

can you show sample results from both queries and explain waht you're trying to get as final result from them?

I think I'm almost there. Is there a way to use a function similar to Min which would accomplish the following?

Min(2,4) = 2

Or

Min(forderqty,finvqty) = 2

In other words, I give it two options and it selects the minimum one?

Is there no way to do something like this min statement?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-02 : 10:19:32

Use a CASE statement.

CASE WHEN a < b THEN a ELSE b END

E 12°55'05.63"
N 56°04'39.26"

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-09-02 : 12:38:08

Yes, I knew that. I was just hoping that there would be a function like I described.

Thanks Peso.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources