Including sub-queries - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Including sub-queries

Author

Topic

tracy5436
Yak Posting Veteran

50 Posts

Posted - 2008-02-05 : 17:49:16

Hi,

I am writing a query to populate a table. However, this table contains info from different tables and should be grouped by particular primary keys. I have used variables to store the values that I want to pass to the insert query but when I do this, all the rows get updated with the same info. How can I do this ?

My query looks like this :

USE MTB
DECLARE @Y CHAR(4), @M CHAR(2), @DAT CHAR(6)
SELECT
@Y = YEAR(DOCDATE), @M = MONTH(DOCDATE), @DAT = @Y + @M
FROM SOP30200
WHERE DATEDIFF(DAY,SOP30200.DOCDATE,GETDATE()) <= 30 AND SOPTYPE = '3'

USE MTB
DECLARE @NEWCUST NUMERIC(3)
SELECT @NEWCUST = COUNT(DISTINCT RM00101.CUSTNMBR)
FROM RM00101
WHERE DATEDIFF(DAY,RM00101.CREATDDT,GETDATE()) <= 30
GROUP BY RM00101.SALSTERR,RM00101.USERDEF1

USE MTB
DECLARE @COUNTCUS NUMERIC(3)
SELECT @COUNTCUS = COUNT(DISTINCT CUSTNMBR)
FROM RM00101
GROUP BY RM00101.SALSTERR

INSERT INTO DISTEFFE([STOCKLIST CODE], [PROCESS YYYYMM], [ROUTE CODE], [ACCOUNT TYPE], [VISIT FREQUENCY OF ROUTE],[NO. OF ACCOUNTS IN ROUTE],[NO. OF NEW ACCOUNTS]
SELECT
'5555550101',@DAT, LEFT(RM00101.SALSTERR,6), LEFT(RM00101.USERDEF1,3), '4',@COUNTCUS,@NEWCUST
FROM RM00101 INNER JOIN SOP30200 ON RM00101.CUSTNMBR = SOP30200.CUSTNMBR
WHERE DATEDIFF(DAY,SOP30200.DOCDATE,GETDATE()) <= 30 AND SOP30200.SOPTYPE = '3' and SOP30200.LOCNCODE = 'MGD-MAIN'
GROUP BY RM00101.SALSTERR,RM00101.USERDEF1

Thanks
Tracy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-06 : 00:18:20

You need to use these subqueries in SELECT satement to join to main tables and include values in SELECT list. If you want the exact code, can you explein your requirement with some sample data of tables along with structure and expected o/p

tracy5436
Yak Posting Veteran

50 Posts

Posted - 2008-02-06 : 20:08:09

The sub-query using the variable @countcus has the value which I would like to insert into the table INVENTRY, which is a count of the number of customers grouped by route. If you give me an example of including this in the select statement with the query that I included then I could do the others.

Thanks.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2008-02-07 : 06:35:39

Your question does not make much sense. In future please follow these guidelines:

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

The following may help you - if it does not, post the question in a cogent manner.

SELECT '5555550101'
    ,D1.ProcessYYYYMM
    ,D1.RouteCode
    ,D1.AccountType
    ,'4'
    ,ISNULL(D2.COUNTCUS, 0)
    ,ISNULL(D3.NEWCUST, 0)
FROM
(
    SELECT DISTINCT
        STR(YEAR(S1.DOCDATE)) + REPLACE(STR(MONTH(S1.DOCDATE), 2),' ', '0') AS ProcessYYYYMM
        ,LEFT(R1.SALSTERR,6) AS RouteCode
        ,LEFT(R1.USERDEF1,3) AS AccountType
    FROM RM00101 R1
        JOIN SOP30200 S1
            ON R1.CUSTNMBR = S1.CUSTNMBR
                AND DATEDIFF(DAY,S1.DOCDATE,GETDATE()) <= 30
                AND S1.SOPTYPE = '3'
                AND S1.LOCNCODE = 'MGD-MAIN' 
) D1
    LEFT JOIN
    (
        SELECT LEFT(R2.SALSTERR,6) AS RouteCode
            ,COUNT(DISTINCT R2.CUSTNMBR) AS COUNTCUS
        FROM RM00101 R2
        GROUP BY LEFT(R2.SALSTERR,6)
    ) D2
        ON D1.RouteCode = D2.RouteCode
    LEFT JOIN
    (
        SELECT LEFT(R3.SALSTERR,6) AS RouteCode
            ,LEFT(R3.USERDEF1,3) AS AccountType
            ,COUNT(DISTINCT R3.CUSTNMBR) AS NEWCUST 
        FROM RM00101 R3
        WHERE DATEDIFF(DAY,R3.CREATDDT,GETDATE()) <= 30
        GROUP BY LEFT(R3.SALSTERR,6), LEFT(R3.USERDEF1,3)
    ) D3
        ON D1.RouteCode = D3.RouteCode
            AND D1.AcountType = D3.AccountType

tracy5436
Yak Posting Veteran

50 Posts

Posted - 2008-02-07 : 12:42:39

I'm sorry that my question did not make sense to you. My objective is to insert the columns Stocklist Code, Process yyyymm, Route Code, Account Type, Visit frequncy of route, no of accounts in route, no of new accounts, no of accounts covered, no of orders, order amount, average prod/order, cases ordered and cases delivered into the table DISTEFFE. The layout of the disteffe table is as follows :

CREATE TABLE DISTEFFE (
[Stocklist Code] char (12) NOT NULL,
[Process yyyymm] char (6) NOT NULL,
[Route Code] char (6) NOT NULL,
[Account Type] char (3) NOT NULL,
[Visit Frequency of Route] numeric (2),
[No. of Accounts in Route] numeric (4),
[No. of New Accounts] numeric (12,2),
[No. of Accounts Covered] numeric (4),
[No. Of Orders] numeric (5),
[Order Amount] numeric (12,2),
[Average Prod/Order] numeric (5),
[Cases Ordered] numeric(12,2),
[Cases Delivered] numeric(12,2),
CONSTRAINT DISTEFFE PRIMARY KEY ([Stocklist Code],[Process yyyymm], [Route Code], [Account Type])

I used the query which you sent and modified it to insert the data into the DISTEFFE table and also to add the no of accounts covered, no of orders and order amount data into the table, which are all in the D5 subquery. The modified query is now:

INSERT INTO DISTEFFE([STOCKLIST CODE], [PROCESS YYYYMM], [ROUTE CODE], [ACCOUNT TYPE], [VISIT FREQUENCY OF ROUTE],[NO. OF ACCOUNTS IN ROUTE],[NO. OF NEW ACCOUNTS],[NO. OF ACCOUNTS COVERED],[NO. OF ORDERS],[ORDER AMOUNT])

SELECT '5555550101'
,D1.ProcessYYYYMM
,D1.RouteCode
,D1.AccountType
,'4'
,ISNULL(D2.COUNTCUS, 0)
,ISNULL(D3.NEWCUST, 0)
,D5.AccCovered
,D5.NumOrders
,D5.OrdValue
FROM
(
SELECT DISTINCT
STR(YEAR(S1.DOCDATE)) + REPLACE(STR(MONTH(S1.DOCDATE), 2),' ', '0') AS ProcessYYYYMM
,LEFT(R1.SALSTERR,6) AS RouteCode
,LEFT(R1.USERDEF1,3) AS AccountType

FROM RM00101 R1
JOIN SOP30200 S1
ON R1.CUSTNMBR = S1.CUSTNMBR
AND DATEDIFF(DAY,S1.DOCDATE,GETDATE()) <= 30
AND S1.SOPTYPE = '3'
AND S1.LOCNCODE = 'MGD-MAIN'
) D1
LEFT JOIN
(
SELECT DISTINCT
COUNT(S1.CUSTNMBR) AS AccCovered
COUNT(S1.SOPNUMBE) as NumOrders
COUNT(S1.DOCAMNT) As OrdAmount

FROM RM00101 R1
JOIN SOP30200 S1
ON R1.CUSTNMBR = S1.CUSTNMBR
AND DATEDIFF(DAY,S1.DOCDATE,GETDATE()) <= 30
AND S1.SOPTYPE = '3'
AND S1.LOCNCODE = 'MGD-MAIN'
) D5

LEFT JOIN
(
SELECT LEFT(R2.SALSTERR,6) AS RouteCode
,COUNT(DISTINCT R2.CUSTNMBR) AS COUNTCUS
FROM RM00101 R2
GROUP BY LEFT(R2.SALSTERR,6)
) D2
ON D1.RouteCode = D2.RouteCode
LEFT JOIN
(
SELECT LEFT(R3.SALSTERR,6) AS RouteCode
,LEFT(R3.USERDEF1,3) AS AccountType
,COUNT(DISTINCT R3.CUSTNMBR) AS NEWCUST
FROM RM00101 R3
WHERE DATEDIFF(DAY,R3.CREATDDT,GETDATE()) <= 30
GROUP BY LEFT(R3.SALSTERR,6), LEFT(R3.USERDEF1,3)
) D3
ON D1.RouteCode = D3.RouteCode
AND D1.AccountType = D3.AccountType

When I run the query, I receive the following error messages :

Server: Msg 170, Level 15, State 1, Line 31
Line 31: Incorrect syntax near 'COUNT'.
Server: Msg 170, Level 15, State 1, Line 49
Line 49: Incorrect syntax near 'D2'.
Server: Msg 170, Level 15, State 1, Line 59
Line 59: Incorrect syntax near 'D3'.

Can you assist with this ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-07 : 13:13:37

You have not given ON condition for first two LEFT joins

tracy5436
Yak Posting Veteran

50 Posts

Posted - 2008-02-07 : 13:55:44

I have added ON R1.CUSTNMBR = S1.CUSTNMBR after the d5 as shown below :

INSERT INTO DISTEFFE([STOCKLIST CODE], [PROCESS YYYYMM], [ROUTE CODE], [ACCOUNT TYPE], [VISIT FREQUENCY OF ROUTE],[NO. OF ACCOUNTS IN ROUTE],[NO. OF NEW ACCOUNTS],[NO. OF ACCOUNTS COVERED],[NO. OF ORDERS],[ORDER AMOUNT])

SELECT '5555550101'
,D1.ProcessYYYYMM
,D1.RouteCode
,D1.AccountType
,'4'
,ISNULL(D2.COUNTCUS, 0)
,ISNULL(D3.NEWCUST, 0)
,D5.AccCovered
,D5.NumOrders
,D5.OrdValue
FROM
(
SELECT DISTINCT
STR(YEAR(S1.DOCDATE)) + REPLACE(STR(MONTH(S1.DOCDATE), 2),' ', '0') AS ProcessYYYYMM
,LEFT(R1.SALSTERR,6) AS RouteCode
,LEFT(R1.USERDEF1,3) AS AccountType

FROM RM00101 R1
JOIN SOP30200 S1
ON R1.CUSTNMBR = S1.CUSTNMBR
AND DATEDIFF(DAY,S1.DOCDATE,GETDATE()) <= 30
AND S1.SOPTYPE = '3'
AND S1.LOCNCODE = 'MGD-MAIN'
) D1
LEFT JOIN
(
SELECT DISTINCT
COUNT(S1.CUSTNMBR) AS AccCovered
,COUNT(S1.SOPNUMBE) as NumOrders
,COUNT(S1.DOCAMNT) As OrdAmount

FROM RM00101 R1
JOIN SOP30200 S1
ON R1.CUSTNMBR = S1.CUSTNMBR
AND DATEDIFF(DAY,S1.DOCDATE,GETDATE()) <= 30
AND S1.SOPTYPE = '3'
AND S1.LOCNCODE = 'MGD-MAIN'
) D5
ON R1.CUSTNMBR = S1.CUSTNMBR

LEFT JOIN
(
SELECT LEFT(R2.SALSTERR,6) AS RouteCode
,COUNT(DISTINCT R2.CUSTNMBR) AS COUNTCUS
FROM RM00101 R2
GROUP BY LEFT(R2.SALSTERR,6)
) D2
ON D1.RouteCode = D2.RouteCode
LEFT JOIN
(
SELECT LEFT(R3.SALSTERR,6) AS RouteCode
,LEFT(R3.USERDEF1,3) AS AccountType
,COUNT(DISTINCT R3.CUSTNMBR) AS NEWCUST
FROM RM00101 R3
WHERE DATEDIFF(DAY,R3.CREATDDT,GETDATE()) <= 30
GROUP BY LEFT(R3.SALSTERR,6), LEFT(R3.USERDEF1,3)
) D3
ON D1.RouteCode = D3.RouteCode
AND D1.AccountType = D3.AccountType

I receive the error :

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

Ifor
Aged Yak Warrior

700 Posts

Posted - 2008-02-08 : 05:25:00

In your query, obviously neither R1 nor S1 exist outside the D5 derived table (inline view) so:

) D5
ON R1.CUSTNMBR = S1.CUSTNMBR

should probably be:

) D5
ON D1.CUSTNMBR = D5.CUSTNMBR

As the join and filter conditions are exactly the same in D5 as they are in D1 you should ditch D5 and modify D1 to include your counts. Something like:

SELECT '5555550101'
    ,D1.ProcessYYYYMM
    ,D1.RouteCode
    ,D1.AccountType
    ,'4'
    ,ISNULL(D2.COUNTCUS, 0)
    ,ISNULL(D3.NEWCUST, 0)
FROM
(
    SELECT
        STR(YEAR(S1.DOCDATE)) + REPLACE(STR(MONTH(S1.DOCDATE), 2),' ', '0') AS ProcessYYYYMM
        ,LEFT(R1.SALSTERR,6) AS RouteCode
        ,LEFT(R1.USERDEF1,3) AS AccountType
        ,COUNT(S1.CUSTNMBR) AS AccCovered
        ,COUNT(S1.SOPNUMBE) as NumOrders
        ,COUNT(S1.DOCAMNT) As OrdAmount
    FROM RM00101 R1
        JOIN SOP30200 S1
            ON R1.CUSTNMBR = S1.CUSTNMBR
                AND DATEDIFF(DAY,S1.DOCDATE,GETDATE()) <= 30
                AND S1.SOPTYPE = '3'
                AND S1.LOCNCODE = 'MGD-MAIN'
    GROUP BY 
        STR(YEAR(S1.DOCDATE)) + REPLACE(STR(MONTH(S1.DOCDATE), 2),' ', '0')
        ,LEFT(R1.SALSTERR,6)
        ,LEFT(R1.USERDEF1,3)
) D1
    LEFT JOIN
    (
        SELECT LEFT(R2.SALSTERR,6) AS RouteCode
            ,COUNT(DISTINCT R2.CUSTNMBR) AS COUNTCUS
        FROM RM00101 R2
        GROUP BY LEFT(R2.SALSTERR,6)
    ) D2
    ON D1.RouteCode = D2.RouteCode
    LEFT JOIN
    (
        SELECT LEFT(R3.SALSTERR,6) AS RouteCode
            ,LEFT(R3.USERDEF1,3) AS AccountType
            ,COUNT(DISTINCT R3.CUSTNMBR) AS NEWCUST 
        FROM RM00101 R3
        WHERE DATEDIFF(DAY,R3.CREATDDT,GETDATE()) <= 30
        GROUP BY LEFT(R3.SALSTERR,6), LEFT(R3.USERDEF1,3)
    ) D3
    ON D1.RouteCode = D3.RouteCode
        AND D1.AcountType = D3.AccountType

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources