Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL code compiles but says - Subquery returned more than 1 value when run

Posted on 2011-10-29
28
Medium Priority
?
583 Views
Last Modified: 2012-05-12
Hi Experts,
I have the below SQL code which compiles ok in SQL management studio in Microsodt SQL server 2008 R2 but when I run the code I receive the following error message.  Any ideas what's causing this and how to fix?

Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Msg 512, Level 16, State 1, Line 47
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.


--This Script updates invoice figures from SAP B1 into the SalesAnalysisactual SQL table--

UPDATE Livedb.dbo.salesanalysisactual 
SET    actualcases = Isnull(actualcases, 0) + isnull((SELECT SUM(t2.Quantity) 
                                               FROM 
                     Livedb.dbo.INV1 T2 
                     INNER JOIN 
                     Livedb.dbo.OINV T1
                       ON t2.DocEntry = 
                          t1.DocEntry
                     INNER JOIN 
                     Livedb.dbo.[@Calendar] t5 
                       ON 
                            t1.DocDate >= t5.u_startdate 
                            AND t1.DocDate <= t5.u_enddate 
                                                      INNER JOIN 
Livedb.dbo.OCRD T3 on 

t1.CardCode = t3.CardCode 
left outer join  CRD1 T6 on T1.ShiptoCode = T6.Address 

WHERE  


t1.Docdate = salesanalysisactual.taxdate 
AND 
t5.u_year = salesanalysisactual.YEAR 
AND 
t5.u_period = salesanalysisactual.period 
AND 
t5.u_week = salesanalysisactual.week 
AND t1.CardCode = 
    salesanalysisactual.customer 
 

AND t2.itemcode = 
    salesanalysisactual.partcode 
GROUP  BY t1.Docdate, 
   t5.u_year, 
   t5.u_period, 
   t5.u_week, 
   t1.CardCode, 
   t2.itemcode, 
   ( t6.U_AZU_ICB + t3.U_MAX_SHORT_NAME)), 0 ) 


UPDATE Livedb.dbo.salesanalysisactual 
SET    actualvalue = Isnull(actualvalue, 0) + isnull ((SELECT 
                     SUM( T2.LineTotal ) 
                                
                                               FROM 
                   Livedb.dbo.OINV T1 
                     INNER JOIN 
                    Livedb.dbo.INV1 T2 
                       ON t2.DocEntry = 
                          t1.DocEntry
                     INNER JOIN 
                     Livedb.dbo.[@Calendar] t5 
                       ON 
                            t1.DocDate >= t5.u_startdate 
                            AND t1.DocDate <= t5.u_enddate 
                                                      INNER JOIN 
Livedb.dbo.OCRD T3 on 

T1.CardCode = t3.CardCode 
left outer  JOIN CRD1 T6 on T1.ShiptoCode = T6.Address 

WHERE  
t1.Docdate = salesanalysisactual.taxdate 
AND 
t5.u_year = salesanalysisactual.YEAR 
AND 
t5.u_period = salesanalysisactual.period 
AND 
t5.u_week = salesanalysisactual.week 
AND t1.CardCode = 
    salesanalysisactual.customer 

AND t2.itemcode = 
    salesanalysisactual.partcode 
GROUP  BY t1.Docdate, 
   t5.u_year, 
   t5.u_period, 
   t5.u_week, 
   t1.CardCode, 
   t2.itemcode, 
   ( t6.U_AZU_ICB + t3.U_MAX_SHORT_NAME)),0)

Open in new window

0
Comment
Question by:kevin1983
  • 16
  • 11
28 Comments
 
LVL 2

Expert Comment

by:supengmmer
ID: 37050030
Because the sql result return more than one value(as use 'group by', the results may be more than one group).
For example , you can not  assignment 1, 2, 3..etc to a variable at the same time , how the variable  can know it choose which value?
Thus you should add  'select max..' before 'Isnull(actualcases, 0) + isnull((SELECT SUM(t2.Quantity) ..'

Such as:
 
--This Script updates invoice figures from SAP B1 into the SalesAnalysisactual SQL table--

UPDATE Livedb.dbo.salesanalysisactual 
SET    actualcases = 
(SELECT  MAX(ACTUAL1) FROM     ---------------------ADD
(                              ---------------------ADD
Isnull(actualcases, 0) + isnull((SELECT SUM(t2.Quantity) 
           as ACTUAL1          ---------------------ADD

                                               FROM 
                     Livedb.dbo.INV1 T2 
                     INNER JOIN 
                     Livedb.dbo.OINV T1
                       ON t2.DocEntry = 
                          t1.DocEntry
                     INNER JOIN 
                     Livedb.dbo.[@Calendar] t5 
                       ON 
                            t1.DocDate >= t5.u_startdate 
                            AND t1.DocDate <= t5.u_enddate 
                                                      INNER JOIN 
Livedb.dbo.OCRD T3 on 

t1.CardCode = t3.CardCode 
left outer join  CRD1 T6 on T1.ShiptoCode = T6.Address 

WHERE  


t1.Docdate = salesanalysisactual.taxdate 
AND 
t5.u_year = salesanalysisactual.YEAR 
AND 
t5.u_period = salesanalysisactual.period 
AND 
t5.u_week = salesanalysisactual.week 
AND t1.CardCode = 
    salesanalysisactual.customer 
 

AND t2.itemcode = 
    salesanalysisactual.partcode 
GROUP  BY t1.Docdate, 
   t5.u_year, 
   t5.u_period, 
   t5.u_week, 
   t1.CardCode, 
   t2.itemcode, 
   ( t6.U_AZU_ICB + t3.U_MAX_SHORT_NAME)), 0 ) 
)  TEMP1  )           -----------------------ADD


UPDATE Livedb.dbo.salesanalysisactual 
SET    actualvalue = 
(SELECT  MAX(ACTUAL2) FROM   ----------------------ADD
(                            ----------------------ADD
Isnull(actualvalue, 0) + isnull ((SELECT 
                     SUM( T2.LineTotal ) 
                            AS ACTUAL2   ----------------------ADD
                                
                                               FROM 
                   Livedb.dbo.OINV T1 
                     INNER JOIN 
                    Livedb.dbo.INV1 T2 
                       ON t2.DocEntry = 
                          t1.DocEntry
                     INNER JOIN 
                     Livedb.dbo.[@Calendar] t5 
                       ON 
                            t1.DocDate >= t5.u_startdate 
                            AND t1.DocDate <= t5.u_enddate 
                                                      INNER JOIN 
Livedb.dbo.OCRD T3 on 

T1.CardCode = t3.CardCode 
left outer  JOIN CRD1 T6 on T1.ShiptoCode = T6.Address 

WHERE  
t1.Docdate = salesanalysisactual.taxdate 
AND 
t5.u_year = salesanalysisactual.YEAR 
AND 
t5.u_period = salesanalysisactual.period 
AND 
t5.u_week = salesanalysisactual.week 
AND t1.CardCode = 
    salesanalysisactual.customer 

AND t2.itemcode = 
    salesanalysisactual.partcode 
GROUP  BY t1.Docdate, 
   t5.u_year, 
   t5.u_period, 
   t5.u_week, 
   t1.CardCode, 
   t2.itemcode, 
   ( t6.U_AZU_ICB + t3.U_MAX_SHORT_NAME)),0)
)  TEMP2  )                -----------------------ADD

Open in new window


best regards.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37050173
You need to tell us in English what you are attempting to do or at the very least tell us this:
Are you trying to update one row or many?
How is the resultset in your subquery related to the table you are updating?
0
 

Author Comment

by:kevin1983
ID: 37050280
supengmmer: do you mean replace the ACTUAL1 with Actualcases as ACTUAL1 is not a valid field name. I tried that but it doesnt compile, says line 5 incorrect syntax near +
line 47 incorrect syntax near ' line 55 incorrect syntax near +, line 95 incorrect syntax near '
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:kevin1983
ID: 37050315
acperkins: ok ill try to give more info - im trying to get invoice information from SQL tables that SAP B1 software uses and insert the data into a SQL table named SalesAnalysisActual (used for analysis puposes in other software and needs to be in the salesanalysistable). It could be inserting more than one row of data depeding on how many invoices there are for each customer.

Im trying to insert data into the following fields in the SalesAnalysis table:
actualcases, actualcases
into the correct Year / Period /Week /Day

The script looks at customers that have invoices In SAP B1 software, and uses a table named Calendar which holds dates such as the week, period, year, day etc
The DocDate is the invoice date
CardCode is the customer name

Does this help? or do you need further information?, please can you clarify resultset in your subquery...which part of the code do you need clarifying on what im trying to do?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37050439
Try it this way:
UPDATE  s
SET     actualcases = ISNULL(s.actualcases, 0) + ISNULL(a.Quantity, 0)
FROM    Livedb.dbo.salesanalysisactual s
        INNER JOIN (SELECT  SUM(t2.Quantity) Quantity
                    FROM    Livedb.dbo.INV1 T2
                            INNER JOIN Livedb.dbo.OINV T1 ON t2.DocEntry = t1.DocEntry
                            INNER JOIN Livedb.dbo.[@Calendar] t5 ON t1.DocDate >= t5.u_startdate
                                                                    AND t1.DocDate <= t5.u_enddate
                            INNER JOIN Livedb.dbo.OCRD T3 ON t1.CardCode = t3.CardCode
                            LEFT OUTER JOIN CRD1 T6 ON T1.ShiptoCode = T6.[Address]
                    GROUP BY t1.Docdate,
                            t5.u_year,
                            t5.u_period,
                            t5.u_week,
                            t1.CardCode,
                            t2.itemcode,
                            (t6.U_AZU_ICB + t3.U_MAX_SHORT_NAME)
                   ) a ON t1.Docdate = s.taxdate
                          AND t5.u_year = s.[Year]
                          AND t5.u_period = s.period
                          AND t5.u_week = s.[week]
                          AND t1.CardCode = s.customer
                          AND t2.itemcode = s.partcode


UPDATE  s
SET     actualcases = ISNULL(s.actualcases, 0) + ISNULL(a.Quantity, 0)
FROM    Livedb.dbo.salesanalysisactual s
        INNER JOIN (SELECT  SUM(T2.LineTotal)
                    FROM    Livedb.dbo.OINV T1
                            INNER JOIN Livedb.dbo.INV1 T2 ON t2.DocEntry = t1.DocEntry
                            INNER JOIN Livedb.dbo.[@Calendar] t5 ON t1.DocDate >= t5.u_startdate
                                                                    AND t1.DocDate <= t5.u_enddate
                            INNER JOIN Livedb.dbo.OCRD T3 ON T1.CardCode = t3.CardCode
                            LEFT OUTER  JOIN CRD1 T6 ON T1.ShiptoCode = T6.[Address]
                    GROUP BY t1.Docdate,
                            t5.u_year,
                            t5.u_period,
                            t5.u_week,
                            t1.CardCode,
                            t2.itemcode,
                            (t6.U_AZU_ICB + t3.U_MAX_SHORT_NAME)
                   ) a ON t1.Docdate = s.taxdate
                          AND t5.u_year = s.[Year]
                          AND t5.u_period = s.period
                          AND t5.u_week = s.[week]
                          AND t1.CardCode = s.customer
                          AND t2.itemcode = s.partcode

Open in new window

0
 

Author Comment

by:kevin1983
ID: 37051286
Ok thanks I tried running your suggested code and got the following warning/error message:
Any suggestions?

Msg 4104, Level 16, State 1, Line 18
The multi-part identifier "t1.Docdate" could not be bound.
Msg 4104, Level 16, State 1, Line 19
The multi-part identifier "t5.u_year" could not be bound.
Msg 4104, Level 16, State 1, Line 20
The multi-part identifier "t5.u_period" could not be bound.
Msg 4104, Level 16, State 1, Line 21
The multi-part identifier "t5.u_week" could not be bound.
Msg 4104, Level 16, State 1, Line 22
The multi-part identifier "t1.CardCode" could not be bound.
Msg 4104, Level 16, State 1, Line 23
The multi-part identifier "t2.itemcode" could not be bound.
0
 

Author Comment

by:kevin1983
ID: 37051308
supengmmer: please can you clarify what you mean?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37051549
You are right, let's try that again:
UPDATE  s
SET     actualcases = ISNULL(s.actualcases, 0) + ISNULL(a.Quantity, 0)
FROM    Livedb.dbo.salesanalysisactual s
        INNER JOIN (SELECT  t1.Docdate,
                            t5.u_year,
                            t5.u_period,
                            t5.u_week,
                            t1.CardCode,
                            t2.itemcode,
                            SUM(t2.Quantity) Quantity
                    FROM    Livedb.dbo.INV1 T2
                            INNER JOIN Livedb.dbo.OINV T1 ON t2.DocEntry = t1.DocEntry
                            INNER JOIN Livedb.dbo.[@Calendar] t5 ON t1.DocDate >= t5.u_startdate
                                                                    AND t1.DocDate <= t5.u_enddate
                            INNER JOIN Livedb.dbo.OCRD T3 ON t1.CardCode = t3.CardCode
                            LEFT OUTER JOIN CRD1 T6 ON T1.ShiptoCode = T6.[Address]
                    GROUP BY
							t1.Docdate,
                            t5.u_year,
                            t5.u_period,
                            t5.u_week,
                            t1.CardCode,
                            t2.itemcode,
                            (t6.U_AZU_ICB + t3.U_MAX_SHORT_NAME)
                   ) a ON a.Docdate = s.taxdate
                          AND a.u_year = s.[Year]
                          AND a.u_period = s.period
                          AND a.u_week = s.[week]
                          AND a.CardCode = s.customer
                          AND a.itemcode = s.partcode


UPDATE  s
SET     actualcases = ISNULL(s.actualcases, 0) + ISNULL(a.Quantity, 0)
FROM    Livedb.dbo.salesanalysisactual s
        INNER JOIN (SELECT  t1.Docdate,
                            t5.u_year,
                            t5.u_period,
                            t5.u_week,
                            t1.CardCode,
                            t2.itemcode,
                            SUM(T2.LineTotal)
                    FROM    Livedb.dbo.OINV T1
                            INNER JOIN Livedb.dbo.INV1 T2 ON t2.DocEntry = t1.DocEntry
                            INNER JOIN Livedb.dbo.[@Calendar] t5 ON t1.DocDate >= t5.u_startdate
                                                                    AND t1.DocDate <= t5.u_enddate
                            INNER JOIN Livedb.dbo.OCRD T3 ON T1.CardCode = t3.CardCode
                            LEFT OUTER  JOIN CRD1 T6 ON T1.ShiptoCode = T6.[Address]
                    GROUP BY 
							t1.Docdate,
                            t5.u_year,
                            t5.u_period,
                            t5.u_week,
                            t1.CardCode,
                            t2.itemcode,
                            (t6.U_AZU_ICB + t3.U_MAX_SHORT_NAME)
                   ) a ON a.Docdate = s.taxdate
                          AND a.u_year = s.[Year]
                          AND a.u_period = s.period
                          AND a.u_week = s.[week]
                          AND a.CardCode = s.customer
                          AND a.itemcode = s.partcode

Open in new window

0
 

Author Comment

by:kevin1983
ID: 37052548
ok looks like it almost there now - the code compiled fine, and when I run it I got one warning / error:
Do I need to define the name a somewhere?

Msg 8155, Level 16, State 2, Line 57
No column name was specified for column 7 of 'a'.

I double clicked on the messages and it highlights this line:   ) a ON a.Docdate = s.taxdate
0
 

Author Comment

by:kevin1983
ID: 37052555
(its the code near the end it highlights - 6th line up from the bottom)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37053799
Yes, I forgot an alias.  It should be:
UPDATE  s
SET     actualcases = ISNULL(s.actualcases, 0) + ISNULL(a.Quantity, 0)
FROM    Livedb.dbo.salesanalysisactual s
        INNER JOIN (SELECT  t1.Docdate,
                            t5.u_year,
                            t5.u_period,
                            t5.u_week,
                            t1.CardCode,
                            t2.itemcode,
                            SUM(t2.Quantity) Quantity
                    FROM    Livedb.dbo.INV1 T2
                            INNER JOIN Livedb.dbo.OINV T1 ON t2.DocEntry = t1.DocEntry
                            INNER JOIN Livedb.dbo.[@Calendar] t5 ON t1.DocDate >= t5.u_startdate
                                                                    AND t1.DocDate <= t5.u_enddate
                            INNER JOIN Livedb.dbo.OCRD T3 ON t1.CardCode = t3.CardCode
                            LEFT OUTER JOIN CRD1 T6 ON T1.ShiptoCode = T6.[Address]
                    GROUP BY
							t1.Docdate,
                            t5.u_year,
                            t5.u_period,
                            t5.u_week,
                            t1.CardCode,
                            t2.itemcode,
                            (t6.U_AZU_ICB + t3.U_MAX_SHORT_NAME)
                   ) a ON a.Docdate = s.taxdate
                          AND a.u_year = s.[Year]
                          AND a.u_period = s.period
                          AND a.u_week = s.[week]
                          AND a.CardCode = s.customer
                          AND a.itemcode = s.partcode


UPDATE  s
SET     actualcases = ISNULL(s.actualcases, 0) + ISNULL(a.Quantity, 0)
FROM    Livedb.dbo.salesanalysisactual s
        INNER JOIN (SELECT  t1.Docdate,
                            t5.u_year,
                            t5.u_period,
                            t5.u_week,
                            t1.CardCode,
                            t2.itemcode,
                            SUM(T2.LineTotal) Quantity
                    FROM    Livedb.dbo.OINV T1
                            INNER JOIN Livedb.dbo.INV1 T2 ON t2.DocEntry = t1.DocEntry
                            INNER JOIN Livedb.dbo.[@Calendar] t5 ON t1.DocDate >= t5.u_startdate
                                                                    AND t1.DocDate <= t5.u_enddate
                            INNER JOIN Livedb.dbo.OCRD T3 ON T1.CardCode = t3.CardCode
                            LEFT OUTER  JOIN CRD1 T6 ON T1.ShiptoCode = T6.[Address]
                    GROUP BY 
							t1.Docdate,
                            t5.u_year,
                            t5.u_period,
                            t5.u_week,
                            t1.CardCode,
                            t2.itemcode,
                            (t6.U_AZU_ICB + t3.U_MAX_SHORT_NAME)
                   ) a ON a.Docdate = s.taxdate
                          AND a.u_year = s.[Year]
                          AND a.u_period = s.period
                          AND a.u_week = s.[week]
                          AND a.CardCode = s.customer
                          AND a.itemcode = s.partcode

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37053831
While this solution may execute, you need to be aware of an inherent problem you have and this should provide an answer to your original question:  "Any ideas what's causing this"

What is happening is that your subquery or my derived table solution is more than likely producing duplicate rows and while my solution may execute successfully, there is no guarantee which value is being used to update "actualcases".  You have a GROUP BY on: t1.Docdate,  t5.u_year, t5.u_period, t5.u_week, t1.CardCode,  t2.itemcode, (t6.U_AZU_ICB + t3.U_MAX_SHORT_NAME), but you are only joining using the first 6 columns.  That means that if (t6.U_AZU_ICB + t3.U_MAX_SHORT_NAME) causes two or more rows to be generated, than the update will be random.

To see what I mean do this and it should show you all the duplicates:

SELECT  a.Docdate,
        a.u_year,
        a.u_period,
        a.u_week,
        a.CardCode,
        a.itemcode
FROM    Livedb.dbo.salesanalysisactual s
        INNER JOIN (SELECT  t1.Docdate,
                            t5.u_year,
                            t5.u_period,
                            t5.u_week,
                            t1.CardCode,
                            t2.itemcode,
                            SUM(t2.Quantity) Quantity
                    FROM    Livedb.dbo.INV1 T2
                            INNER JOIN Livedb.dbo.OINV T1 ON t2.DocEntry = t1.DocEntry
                            INNER JOIN Livedb.dbo.[@Calendar] t5 ON t1.DocDate >= t5.u_startdate
                                                                    AND t1.DocDate <= t5.u_enddate
                            INNER JOIN Livedb.dbo.OCRD T3 ON t1.CardCode = t3.CardCode
                            LEFT OUTER JOIN CRD1 T6 ON T1.ShiptoCode = T6.[Address]
                    GROUP BY
							t1.Docdate,
                            t5.u_year,
                            t5.u_period,
                            t5.u_week,
                            t1.CardCode,
                            t2.itemcode,
                            (t6.U_AZU_ICB + t3.U_MAX_SHORT_NAME)
                   ) a ON a.Docdate = s.taxdate
                          AND a.u_year = s.[Year]
                          AND a.u_period = s.period
                          AND a.u_week = s.[week]
                          AND a.CardCode = s.customer
                          AND a.itemcode = s.partcode
GROUP BY
        a.Docdate,
        a.u_year,
        a.u_period,
        a.u_week,
        a.CardCode,
        a.itemcode
HAVING COUNT(*)> 1

SELECT	a.Docdate,
        a.u_year,
        a.u_period,
        a.u_week,
        a.CardCode,
        a.itemcode
FROM    Livedb.dbo.salesanalysisactual s
        INNER JOIN (SELECT  t1.Docdate,
                            t5.u_year,
                            t5.u_period,
                            t5.u_week,
                            t1.CardCode,
                            t2.itemcode,
                            SUM(T2.LineTotal) Quantity
                    FROM    Livedb.dbo.OINV T1
                            INNER JOIN Livedb.dbo.INV1 T2 ON t2.DocEntry = t1.DocEntry
                            INNER JOIN Livedb.dbo.[@Calendar] t5 ON t1.DocDate >= t5.u_startdate
                                                                    AND t1.DocDate <= t5.u_enddate
                            INNER JOIN Livedb.dbo.OCRD T3 ON T1.CardCode = t3.CardCode
                            LEFT OUTER  JOIN CRD1 T6 ON T1.ShiptoCode = T6.[Address]
                    GROUP BY 
							t1.Docdate,
                            t5.u_year,
                            t5.u_period,
                            t5.u_week,
                            t1.CardCode,
                            t2.itemcode,
                            (t6.U_AZU_ICB + t3.U_MAX_SHORT_NAME)
                   ) a ON a.Docdate = s.taxdate
                          AND a.u_year = s.[Year]
                          AND a.u_period = s.period
                          AND a.u_week = s.[week]
                          AND a.CardCode = s.customer
                          AND a.itemcode = s.partcode
GROUP BY
        a.Docdate,
        a.u_year,
        a.u_period,
        a.u_week,
        a.CardCode,
        a.itemcode
HAVING COUNT(*)> 1

Open in new window

0
 

Author Comment

by:kevin1983
ID: 37053909
Ok your latest script did run fine this time and it said it 177 rows affected , 177 rows affected.


I then run your second script to show duplicates and it said
5: (5 row(s) affected)
Msg 208, Level 16, State 1, Line 44
Invalid object name 'Livedb.dbo.INV1'.

So i'm not sure what its done as sounds like its updated 5 rows? - checking data in table at the moment.  I think the following two fields have nothing to join on in any other tables: U_AZU_ICB + t3.U_MAX_SHORT_NAME
so do you suggest changing the Group by clause? - maybe having less fields in the group by if possible, or perhaps breaking it down into 2 separate query's. Do you suggest doing it in another way?
0
 

Author Comment

by:kevin1983
ID: 37054023
Looks like data inserted into 2011 but oddly not week 5 of period 10- the invoices should have fallen within this date range according to the docdate in relation to the calendar tables dates-trying to see exactly where data has been inserted
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37054073
>>5: (5 row(s) affected)<<
If you had clicked on the Results tab you would have seen the 5 duplicate rows that I mentioned.

>>Invalid object name 'Livedb.dbo.INV1'.<<
I have no idea why you are getting this error message in the second query, unless you are on the wrong server.  The query is just a plain SELECT statement, does not update anything and has the same conditions as the UPDATE you managed to run successfully.

>>So i'm not sure what its done as sounds like its updated 5 rows?<<
No, it did not UPDATE anything.  It is just a SELECT statement.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 37054093
On second thoughts the two UPDATE queries can be combined into one.  As in:
UPDATE  s
SET     actualcases = ISNULL(s.actualcases, 0) + ISNULL(a.Quantity, 0),
		actualvalue = ISNULL(s.actualvalue, 0) + ISNULL(a.LineTotal, 0)
FROM    Livedb.dbo.salesanalysisactual s
        INNER JOIN (SELECT  t1.Docdate,
                            t5.u_year,
                            t5.u_period,
                            t5.u_week,
                            t1.CardCode,
                            t2.itemcode,
                            SUM(t2.Quantity) Quantity,
                            SUM( T2.LineTotal) LineTotal
                    FROM    Livedb.dbo.INV1 T2
                            INNER JOIN Livedb.dbo.OINV T1 ON t2.DocEntry = t1.DocEntry
                            INNER JOIN Livedb.dbo.[@Calendar] t5 ON t1.DocDate >= t5.u_startdate
                                                                    AND t1.DocDate <= t5.u_enddate
                            INNER JOIN Livedb.dbo.OCRD T3 ON t1.CardCode = t3.CardCode
                            LEFT OUTER JOIN CRD1 T6 ON T1.ShiptoCode = T6.[Address]
                    GROUP BY
							t1.Docdate,
                            t5.u_year,
                            t5.u_period,
                            t5.u_week,
                            t1.CardCode,
                            t2.itemcode,
                            (t6.U_AZU_ICB + t3.U_MAX_SHORT_NAME)
                   ) a ON a.Docdate = s.taxdate
                          AND a.u_year = s.[Year]
                          AND a.u_period = s.period
                          AND a.u_week = s.[week]
                          AND a.CardCode = s.customer
                          AND a.itemcode = s.partcode

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37054098
But in order to find out what it is truly updating I would suggest you inspect the values in the following SELECT query:

SELECT  a.Docdate,
        a.u_year,
        a.u_period,
        a.u_week,
        a.CardCode,
        a.itemcode,
        a.quantity,
        a.LineTotal
FROM    Livedb.dbo.salesanalysisactual s
        INNER JOIN (SELECT  t1.Docdate,
                            t5.u_year,
                            t5.u_period,
                            t5.u_week,
                            t1.CardCode,
                            t2.itemcode,
                            SUM(t2.Quantity) Quantity
                    FROM    Livedb.dbo.INV1 T2
                            INNER JOIN Livedb.dbo.OINV T1 ON t2.DocEntry = t1.DocEntry
                            INNER JOIN Livedb.dbo.[@Calendar] t5 ON t1.DocDate >= t5.u_startdate
                                                                    AND t1.DocDate <= t5.u_enddate
                            INNER JOIN Livedb.dbo.OCRD T3 ON t1.CardCode = t3.CardCode
                            LEFT OUTER JOIN CRD1 T6 ON T1.ShiptoCode = T6.[Address]
                    GROUP BY
			t1.Docdate,
                            t5.u_year,
                            t5.u_period,
                            t5.u_week,
                            t1.CardCode,
                            t2.itemcode,
                            (t6.U_AZU_ICB + t3.U_MAX_SHORT_NAME)
                   ) a ON a.Docdate = s.taxdate
                          AND a.u_year = s.[Year]
                          AND a.u_period = s.period
                          AND a.u_week = s.[week]
                          AND a.CardCode = s.customer
                          AND a.itemcode = s.partcode

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37054101
And in particular the following duplicates:
SELECT  a.Docdate,
        a.u_year,
        a.u_period,
        a.u_week,
        a.CardCode,
        a.itemcode
FROM    Livedb.dbo.salesanalysisactual s
        INNER JOIN (SELECT  t1.Docdate,
                            t5.u_year,
                            t5.u_period,
                            t5.u_week,
                            t1.CardCode,
                            t2.itemcode,
                            SUM(t2.Quantity) Quantity
                    FROM    Livedb.dbo.INV1 T2
                            INNER JOIN Livedb.dbo.OINV T1 ON t2.DocEntry = t1.DocEntry
                            INNER JOIN Livedb.dbo.[@Calendar] t5 ON t1.DocDate >= t5.u_startdate
                                                                    AND t1.DocDate <= t5.u_enddate
                            INNER JOIN Livedb.dbo.OCRD T3 ON t1.CardCode = t3.CardCode
                            LEFT OUTER JOIN CRD1 T6 ON T1.ShiptoCode = T6.[Address]
                    GROUP BY
							t1.Docdate,
                            t5.u_year,
                            t5.u_period,
                            t5.u_week,
                            t1.CardCode,
                            t2.itemcode,
                            (t6.U_AZU_ICB + t3.U_MAX_SHORT_NAME)
                   ) a ON a.Docdate = s.taxdate
                          AND a.u_year = s.[Year]
                          AND a.u_period = s.period
                          AND a.u_week = s.[week]
                          AND a.CardCode = s.customer
                          AND a.itemcode = s.partcode
GROUP BY
        a.Docdate,
        a.u_year,
        a.u_period,
        a.u_week,
        a.CardCode,
        a.itemcode
HAVING COUNT(*)> 1

Open in new window

0
 

Author Comment

by:kevin1983
ID: 37054389
oh ok I clicked the results shows the following:
2011-10-27 00:00:00.000      2011      10      4      SZI001      1701MDA
2011-10-27 00:00:00.000      2011      10      4      SZI001      1707NDA
2011-10-27 00:00:00.000      2011      10      4      SDP001      2339ISA
2011-10-27 00:00:00.000      2011      10      4      SDP001      2390MSB
2011-10-28 00:00:00.000      2011      10      4      SAI001      1702MQA
Looking at the salesanalysis table I cant see any data inserted into Period 10, week4
0
 

Author Comment

by:kevin1983
ID: 37054430
"On second thoughts the two UPDATE queries can be combined into one.  As in:"
Thanks - this script seems like it has run better and may have done the trick, it says 177 rows afected and has inserted data into 2011 / Period 10  / week 4

"But in order to find out what it is truly updating I would suggest you inspect the values in the following SELECT query:"
I tried running this but says LineTotal in invalid column  - This filed name is not included in the salesanalysis table and it doesnt have the field name quantity. I guess youve mixed up the source and destinantion table field named a bit as destination table doesnt have the same field names.
 But I get the idea of what your were trying to do, would be useful to check exactly what data has been updated then id know for certain its worked
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37054714
I just forgot to include it like I had done on the UPDATE, see here:
SELECT  a.Docdate,
        a.u_year,
        a.u_period,
        a.u_week,
        a.CardCode,
        a.itemcode,
        a.quantity,
        a.LineTotal
FROM    Livedb.dbo.salesanalysisactual s
        INNER JOIN (SELECT  t1.Docdate,
                            t5.u_year,
                            t5.u_period,
                            t5.u_week,
                            t1.CardCode,
                            t2.itemcode,
                            SUM(t2.Quantity) Quantity,
                            SUM( T2.LineTotal) LineTotal
                    FROM    Livedb.dbo.INV1 T2
                            INNER JOIN Livedb.dbo.OINV T1 ON t2.DocEntry = t1.DocEntry
                            INNER JOIN Livedb.dbo.[@Calendar] t5 ON t1.DocDate >= t5.u_startdate
                                                                    AND t1.DocDate <= t5.u_enddate
                            INNER JOIN Livedb.dbo.OCRD T3 ON t1.CardCode = t3.CardCode
                            LEFT OUTER JOIN CRD1 T6 ON T1.ShiptoCode = T6.[Address]
                    GROUP BY
                  t1.Docdate,
                            t5.u_year,
                            t5.u_period,
                            t5.u_week,
                            t1.CardCode,
                            t2.itemcode,
                            (t6.U_AZU_ICB + t3.U_MAX_SHORT_NAME)
                   ) a ON a.Docdate = s.taxdate
                          AND a.u_year = s.[Year]
                          AND a.u_period = s.period
                          AND a.u_week = s.[week]
                          AND a.CardCode = s.customer
                          AND a.itemcode = s.partcode
0
 

Author Comment

by:kevin1983
ID: 37057420
Thanks - had troubling loggin onto experts exchange earlier to reply - you were right the data is a bit random with some customers not including all items and some items doubling up on the quantity. trying to find out where the problem may be
0
 

Author Comment

by:kevin1983
ID: 37062292
The following seem to be the items that are duplicates and causing the figures to double up, but not sure how to resolve.
2011-10-27 00:00:00.000                2011       10           4              SZI001  1701MDA
2011-10-27 00:00:00.000                2011       10           4              SZI001  1707NDA
2011-10-27 00:00:00.000                2011       10           4              STP001 2339ISA
2011-10-27 00:00:00.000                2011       10           4              STP001 2390MSB
2011-10-28 00:00:00.000                2011       10           4              SZI001  1702MQA
0
 

Author Comment

by:kevin1983
ID: 37062296
...used your duplicated query to identify them - cant see anything odd in particular for those items that might explain the reason why
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37062360
>>cant see anything odd in particular for those items that might explain the reason why <<
I mentioned the reason here http:#a37053831.  The reason has to do with your GROUP BY and the addition of (t6.U_AZU_ICB + t3.U_MAX_SHORT_NAME)

>>but not sure how to resolve.<<
I am afraid I cannot help you there, without sitting in front of your computer and knowing all the details.  You could certainly remove that from the GROUP BY clause and the duplicates would go away.  Would that be correct?  I have no idea.
0
 

Author Comment

by:kevin1983
ID: 37062398
ok I appreciate its tricky for you to know without seeing our exact data, i tried removing (t6.U_AZU_ICB + t3.U_MAX_SHORT_NAME) from the group by clause but still getting same results / same duplicates so not sure its them fields that are the issue. I guess we will need to look at it in more detail and try to isolate what could be casing the duplicates but proving to be difficult so far
0
 

Author Closing Comment

by:kevin1983
ID: 37082520
Many thanks for your help on this - I removed all the grouping in your SQL code so that the data is just inserted rather than also organised, we can then group the data in the reporting software instead. - relised this will do fine for what we need.

I guess it may be possible to run a seperate query afterwards on the table to group it.
0
 

Author Comment

by:kevin1983
ID: 37082522
...with grouping removed the data looks correct.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question