No spool space error,when running SP in BTEQ.

Database
Enthusiast

No spool space error,when running SP in BTEQ.

Hi,

We are using some big SP in our projects.We have spacing issue on running those SP's in both teradata studio and BTEQ.We are getting No Spool space error in BTEQ command and teradata studio.We are googled about this issue in forum,they are suggest us to  run the query in BTEQ Terminal ,but still issue in not solved.can anyone help us in this issue........it's urgent!!!!!

We have attached the screen shot.

6 REPLIES
Enthusiast

Re: No spool space error,when running SP in BTEQ.

Hi

There are some points you need to consider to avoid "no more spool space"

- Verify if the SQL you are using has been written correctly. It means you use the proper  PI, PPI, NUSI (in case it appplies). In addition, you may verify if you can create tmp tables in order to resolve the complex queries and then try to join the results by creating a new table, for instance.

- On the other hand, you may ask your DBA if he/she could validate the spool space assigned to the user that executes the process through bteq or assistant.

Regards.

Re: No spool space error,when running SP in BTEQ.

Hi Keerthi,

Check for the skew factor of the tables involved  and collect stats for secondary indexes (if any).

Enthusiast

Re: No spool space error,when running SP in BTEQ.

Hi ashok_paswan,

We have checked the skew factor for single customer table, we are getting below results,

T001_Test_Invoice_Raw 2482.03369140625 0.10130965576335008

We have set both index (PI and NUSI) key for above table.It's contain more than 20M records.We are getting spool space error particulary this table. There is any chance to reduce this problem.

Enthusiast

Re: No spool space error,when running SP in BTEQ.

What does your stored procedure do?  Does it perform aggregation?  Are you joining tables?  have you run explains on the underlying code?

Enthusiast

Re: No spool space error,when running SP in BTEQ.

And how much spool is currently allocated to that User ID?

Enthusiast

Re: No spool space error,when running SP in BTEQ.

Hi,

We fetch data from one table and stored that results to global temporary table and we perform some aggregation function and union all the results.Using that result update that into new table.

Allocated space is as follow as for user 

Permanent Space : 3700.9999996572733

Spool Space : 2700.999999638647

Temporary Space : 1000.9999997355044

The sample code as follow as,


REPLACE PROCEDURE "Test"."Test" (

IN "tblName" VARCHAR(200),

IN "getYear" VARCHAR(10),

IN "getClientId" VARCHAR(100))

BEGIN 

DECLARE query VARCHAR(50000);

DECLARE columns VARCHAR(30000);

SET columns=' InvoiceId '||

', BillToAccountNumber ' ||

', InvoiceDate ' ||

', InvoiceNumber ' ||

', OriginalAmountDue ' ||

', ShipmentDate ' ||

', BundleNumber ' ||

', NumberOfPieces ' ||

', ExpressOrGroundTrackingID ' ||

', OriginalCustomerReference ' ||

', OriginalRef#2 ' ||

', OriginalRef#3_PONumber ' ||

', OriginalDepartmentReferenceDescription ' ||

', ActualWeightAmount ' ||

', ActualWeightUnits ' ||

', RatedWeightAmount ' ||

', RatedWeightUnits ' ||

', ZoneCode ' ||

', ServiceType ' ||

', StoreId ' ||

', RecipientName ' ||

', RecipientCompany ' ||

', RecipientAddressLine1 ' ||

', RecipientAddressLine2 ' ||

', RecipientCity ' ||

', RecipientState ' ||

', RecipientZipCode ' ||

', RecipientCountry ' ||

', ShipperCompany ' ||

', ShipperName ' ||

', ShipperAddressLine1 ' ||

', ShipperAddressLine2 ' ||

', ShipperCity ' ||

', ShipperState ' ||

', ShipperZipCode ' ||

', ShipperCountry ' ||

', DimLength ' ||

', DimWidth ' ||

', DimHeight ' ||

', DimDivisor ' ||

', NetChargeAmount ' ||

', TransportationChargeAmount ' ||

', TrackingIDChargeDescription1 ' ||

', TrackingIDChargeAmount1 ' ||

', TrackingIDChargeDescription2 ' ||

', TrackingIDChargeAmount2 ' ||

', TrackingIDChargeDescription3 ' ||

', TrackingIDChargeAmount3 ' ||

', TrackingIDChargeDescription4 ' ||

', TrackingIDChargeAmount4 ' ||

', TrackingIDChargeDescription5 ' ||

', TrackingIDChargeAmount5 ' ||

', TrackingIDChargeDescription6 ' ||

', TrackingIDChargeAmount6 ' ||

', TrackingIDChargeDescription7 ' ||

', TrackingIDChargeAmount7 ' ||

', TrackingIDChargeDescription8 ' ||

', TrackingIDChargeAmount8 ' ||

', TrackingIDChargeDescription9 ' ||

', TrackingIDChargeAmount9 ' ||

', TrackingIDChargeDescription10 ' ||

', TrackingIDChargeAmount10 ' ||

', TrackingIDChargeDescription11 ' ||

', TrackingIDChargeAmount11 ' ||

', TrackingIDChargeDescription12 ' ||

', TrackingIDChargeAmount12 ' ||

', TrackingIDChargeDescription13 ' ||

', TrackingIDChargeAmount13 ' ||

', TrackingIDChargeDescription14 ' ||

', TrackingIDChargeAmount14 ' ||

', TrackingIDChargeDescription15 ' ||

', TrackingIDChargeAmount15 ' ||

', TrackingIDChargeDescription16 ' ||

', TrackingIDChargeAmount16 ' ||

', TrackingIDChargeDescription17 ' ||

', TrackingIDChargeAmount17 ' ||

', TrackingIDChargeDescription18 ' ||

', TrackingIDChargeAmount18 ' ||

', TrackingIDChargeDescription19 ' ||

', TrackingIDChargeAmount19 ' ||

', TrackingIDChargeDescription20 ' ||

', TrackingIDChargeAmount20 ' ||

', TrackingIDChargeDescription21 ' ||

', TrackingIDChargeAmount21 ' ||

', TrackingIDChargeDescription22 ' ||

', TrackingIDChargeAmount22 ' ||

', TrackingIDChargeDescription23 ' ||

', TrackingIDChargeAmount23 ' ||

', TrackingIDChargeDescription24 ' ||

', TrackingIDChargeAmount24 ' ||

', TrackingIDChargeDescription25 ' ||

', TrackingIDChargeAmount25 ' ||

', TotalOfPositive ' ||

', TotalOfNegative ';

DELETE FROM Testtemptable;

set query=' INSERT INTO Testtemptable('||columns||')

SELECT '||columns||' FROM '||getTableName||' WHERE EXTRACT(YEAR FROM cast(Invoicedate as date format ''yyyymmdd''))='||getYear||' ';

CALL DBC.SYSEXECSQL(QUERY);

UPDATE T202_Test_Dashboard 

FROM(SELECT Src.ServType,Src.MONTH_2 As Month_5,Src.NetAmount,Src.Typ,getClientId as ClientId,Src.ChargeType,Src.AccNumber  

FROM(SELECT FreightTbl2.ServType AS ServType,FreightTbl2.NetAmount,FreightTbl2.MONTH_2,FreightTbl2.Typ AS Typ,FreightTbl2.ChargeType,FreightTbl2.AccountNumber AS AccNumber

FROM

(SELECT ChargeDescriptionAccTbl1.ChargeDescription AS ServType,SUM(ChargeDescriptionAccTbl1.NetAmount) AS NetAmount 

,ChargeDescriptionAccTbl1.MONTH_3 AS Month_2,'Accessorial' AS Typ,'FRTWithAcc' AS ChargeType,ChargeDescriptionAccTbl1.AccountNumber

FROM

(

------Description1

SELECT 

TrackingIDChargeDescription1 AS ChargeDescription,

SUM(CAST(COALESCE(OREPLACE(TrackingIDChargeAmount1,' ',''),0.00) AS NUMERIC(32,2))) AS "NetAmount",

EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')) AS MONTH_3,

BillToAccountNumber AS AccountNumber

FROM Testtemptable

GROUP BY TrackingIDChargeDescription1,EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')),BillToAccountNumber

UNION 

------Description2

SELECT 

TrackingIDChargeDescription2 AS ChargeDescription,

SUM(CAST(COALESCE(OREPLACE(TrackingIDChargeAmount2,' ',''),0.00) AS NUMERIC(32,2))) AS "NetAmount",

EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')) AS MONTH_3,

BillToAccountNumber AS AccountNumber

FROM Testtemptable

GROUP BY TrackingIDChargeDescription2,EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')),BillToAccountNumber

UNION 

--Description3

SELECT 

TrackingIDChargeDescription3 AS ChargeDescription,

SUM(CAST(COALESCE(OREPLACE(TrackingIDChargeAmount3,' ',''),0.00) AS NUMERIC(32,2))) AS "NetAmount",

EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')) AS MONTH_3,

BillToAccountNumber AS AccountNumber

FROM Testtemptable

GROUP BY TrackingIDChargeDescription3,EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')),BillToAccountNumber

UNION 

------Description4

SELECT 

TrackingIDChargeDescription4 AS ChargeDescription,

SUM(CAST(COALESCE(OREPLACE(TrackingIDChargeAmount4,' ',''),0.00) AS NUMERIC(32,2))) AS "NetAmount",

EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')) AS MONTH_3,

BillToAccountNumber AS AccountNumber

FROM Testtemptable

GROUP BY TrackingIDChargeDescription4,EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')),BillToAccountNumber

UNION 

---- --Description5

SELECT 

TrackingIDChargeDescription5 AS ChargeDescription,

SUM(CAST(COALESCE(OREPLACE(TrackingIDChargeAmount5,' ',''),0.00) AS NUMERIC(32,2))) AS "NetAmount",

EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')) AS MONTH_3,

BillToAccountNumber AS AccountNumber

FROM Testtemptable

GROUP BY TrackingIDChargeDescription5,EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')),BillToAccountNumber

UNION 

---- --Description6

SELECT 

TrackingIDChargeDescription6 AS ChargeDescription,

SUM(CAST(COALESCE(OREPLACE(TrackingIDChargeAmount6,' ',''),0.00) AS NUMERIC(32,2))) AS "NetAmount",

EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')) AS MONTH_3,

BillToAccountNumber AS AccountNumber

FROM Testtemptable

GROUP BY TrackingIDChargeDescription6,EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')),BillToAccountNumber

UNION 

------Description7

SELECT 

TrackingIDChargeDescription7 AS ChargeDescription,

SUM(CAST(COALESCE(OREPLACE(TrackingIDChargeAmount7,' ',''),0.00) AS NUMERIC(32,2))) AS "NetAmount",

EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')) AS MONTH_3,

BillToAccountNumber AS AccountNumber

FROM Testtemptable

GROUP BY TrackingIDChargeDescription7,EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')),BillToAccountNumber

UNION 

---- --Description8

SELECT 

TrackingIDChargeDescription8 AS ChargeDescription,

SUM(CAST(COALESCE(OREPLACE(TrackingIDChargeAmount8,' ',''),0.00) AS NUMERIC(32,2))) AS "NetAmount",

EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')) AS MONTH_3,

BillToAccountNumber AS AccountNumber

FROM Testtemptable

GROUP BY TrackingIDChargeDescription8,EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')),BillToAccountNumber

UNION 

--Description9

SELECT 

TrackingIDChargeDescription9 AS ChargeDescription,

SUM(CAST(COALESCE(OREPLACE(TrackingIDChargeAmount9,' ',''),0.00) AS NUMERIC(32,2))) AS "NetAmount",

EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')) AS MONTH_3,

BillToAccountNumber AS AccountNumber

FROM Testtemptable

GROUP BY TrackingIDChargeDescription9,EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')) ,BillToAccountNumber

UNION 

---- --Description10

SELECT 

TrackingIDChargeDescription10 AS ChargeDescription,

SUM(CAST(COALESCE(OREPLACE(TrackingIDChargeAmount10,' ',''),0.00) AS NUMERIC(32,2))) AS "NetAmount",

EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')) AS MONTH_3,

BillToAccountNumber AS AccountNumber

FROM Testtemptable

GROUP BY TrackingIDChargeDescription10,EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')),BillToAccountNumber

UNION 

---- --Description11

SELECT 

TrackingIDChargeDescription11 AS ChargeDescription,

SUM(CAST(COALESCE(OREPLACE(TrackingIDChargeAmount11,' ',''),0.00) AS NUMERIC(32,2))) AS "NetAmount",

EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')) AS MONTH_3,

BillToAccountNumber AS AccountNumber

FROM Testtemptable

GROUP BY TrackingIDChargeDescription11,EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')),BillToAccountNumber

UNION

---- --Description12

SELECT 

TrackingIDChargeDescription12 AS ChargeDescription,

SUM(CAST(COALESCE(OREPLACE(TrackingIDChargeAmount12,' ',''),0.00) AS NUMERIC(32,2))) AS "NetAmount",

EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')) AS MONTH_3,

BillToAccountNumber AS AccountNumber

FROM Testtemptable

GROUP BY TrackingIDChargeDescription12,EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')),BillToAccountNumber

UNION 

---- --Description13

SELECT 

TrackingIDChargeDescription13 AS ChargeDescription,

SUM(CAST(COALESCE(OREPLACE(TrackingIDChargeAmount13,' ',''),0.00) AS NUMERIC(32,2))) AS "NetAmount",

EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')) AS MONTH_3,

BillToAccountNumber AS AccountNumber

FROM Testtemptable

GROUP BY TrackingIDChargeDescription13,EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')),BillToAccountNumber

UNION

---- --Description14

SELECT 

TrackingIDChargeDescription14 AS ChargeDescription,

SUM(CAST(COALESCE(OREPLACE(TrackingIDChargeAmount14,' ',''),0.00) AS NUMERIC(32,2))) AS "NetAmount",

EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')) AS MONTH_3,

BillToAccountNumber AS AccountNumber

FROM Testtemptable

GROUP BY TrackingIDChargeDescription14,EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')),BillToAccountNumber

UNION

---- --Description15

SELECT 

TrackingIDChargeDescription15 AS ChargeDescription,

SUM(CAST(COALESCE(OREPLACE(TrackingIDChargeAmount15,' ',''),0.00) AS NUMERIC(32,2))) AS "NetAmount",

EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')) AS MONTH_3,

BillToAccountNumber AS AccountNumber

FROM Testtemptable

GROUP BY TrackingIDChargeDescription15,EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')),BillToAccountNumber

UNION 

---- --Description16

SELECT 

TrackingIDChargeDescription16 AS ChargeDescription,

SUM(CAST(COALESCE(OREPLACE(TrackingIDChargeAmount16,' ',''),0.00) AS NUMERIC(32,2))) AS "NetAmount",

EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')) AS MONTH_3,

BillToAccountNumber AS AccountNumber

FROM Testtemptable

GROUP BY TrackingIDChargeDescription16,EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')),BillToAccountNumber

UNION

---- --Description17

SELECT 

TrackingIDChargeDescription17 AS ChargeDescription,

SUM(CAST(COALESCE(OREPLACE(TrackingIDChargeAmount17,' ',''),0.00) AS NUMERIC(32,2))) AS "NetAmount",

EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')) AS MONTH_3,

BillToAccountNumber AS AccountNumber

FROM Testtemptable

GROUP BY TrackingIDChargeDescription17,EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')),BillToAccountNumber

UNION 

---- --Description18

SELECT 

TrackingIDChargeDescription18 AS ChargeDescription,

SUM(CAST(COALESCE(OREPLACE(TrackingIDChargeAmount18,' ',''),0.00) AS NUMERIC(32,2))) AS "NetAmount",

EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')) AS MONTH_3,

BillToAccountNumber AS AccountNumber

FROM Testtemptable

GROUP BY TrackingIDChargeDescription18,EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')),BillToAccountNumber

UNION

---- --Description19

SELECT 

TrackingIDChargeDescription19 AS ChargeDescription,

SUM(CAST(COALESCE(OREPLACE(TrackingIDChargeAmount19,' ',''),0.00) AS NUMERIC(32,2))) AS "NetAmount",

EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')) AS MONTH_3,

BillToAccountNumber AS AccountNumber

FROM Testtemptable

GROUP BY TrackingIDChargeDescription19,EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')),BillToAccountNumber

UNION

---- --Description20

SELECT 

TrackingIDChargeDescription20 AS ChargeDescription,

SUM(CAST(COALESCE(OREPLACE(TrackingIDChargeAmount20,' ',''),0.00) AS NUMERIC(32,2))) AS "NetAmount",

EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')) AS MONTH_3,

BillToAccountNumber AS AccountNumber

FROM Testtemptable

GROUP BY TrackingIDChargeDescription20,EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')),BillToAccountNumber

UNION

---- --Description21

SELECT 

TrackingIDChargeDescription21 AS ChargeDescription,

SUM(CAST(COALESCE(OREPLACE(TrackingIDChargeAmount21,' ',''),0.00) AS NUMERIC(32,2))) AS "NetAmount",

EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')) AS MONTH_3,

BillToAccountNumber AS AccountNumber

FROM Testtemptable

GROUP BY TrackingIDChargeDescription21,EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')),BillToAccountNumber

UNION

---- --Description22

SELECT 

TrackingIDChargeDescription22 AS ChargeDescription,

SUM(CAST(COALESCE(OREPLACE(TrackingIDChargeAmount22,' ',''),0.00) AS NUMERIC(32,2))) AS "NetAmount",

EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')) AS MONTH_3,

BillToAccountNumber AS AccountNumber

FROM Testtemptable

GROUP BY TrackingIDChargeDescription22,EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')),BillToAccountNumber

UNION

---- --Description23

SELECT 

TrackingIDChargeDescription23 AS ChargeDescription,

SUM(CAST(COALESCE(OREPLACE(TrackingIDChargeAmount23,' ',''),0.00) AS NUMERIC(32,2))) AS "NetAmount",

EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')) AS MONTH_3,

BillToAccountNumber AS AccountNumber

FROM Testtemptable

GROUP BY TrackingIDChargeDescription23,EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')),BillToAccountNumber

UNION 

---- --Description24

SELECT 

TrackingIDChargeDescription24 AS ChargeDescription,

SUM(CAST(COALESCE(OREPLACE(TrackingIDChargeAmount24,' ',''),0.00) AS NUMERIC(32,2))) AS "NetAmount",

EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')) AS MONTH_3,

BillToAccountNumber AS AccountNumber

FROM Testtemptable

GROUP BY TrackingIDChargeDescription24,EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')),BillToAccountNumber

UNION 

---- --Description25

SELECT 

TrackingIDChargeDescription25 AS ChargeDescription,

SUM(CAST(COALESCE(OREPLACE(TrackingIDChargeAmount25,' ',''),0.00) AS NUMERIC(32,2))) AS "NetAmount",

EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')) AS MONTH_3,

BillToAccountNumber AS AccountNumber

FROM Testtemptable

GROUP BY TrackingIDChargeDescription25,EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')),BillToAccountNumber

) AS ChargeDescriptionAccTbl1

WHERE

(ChargeDescriptionAccTbl1.ChargeDescription='Fuel Surcharge' OR ChargeDescriptionAccTbl1.ChargeDescription='Address Correction' 

OR ChargeDescriptionAccTbl1.ChargeDescription='Declared Value' )

GROUP BY ChargeDescriptionAccTbl1.ChargeDescription,ChargeDescriptionAccTbl1.MONTH_3,ChargeDescriptionAccTbl1.AccountNumber)AS FreightTbl2

UNION

SELECT ServiceType AS ServiceTypess,

SUM(CAST(COALESCE(OREPLACE(TransportationChargeAmount,' ',''),0.00) AS NUMERIC(32,2))-

ABS(CAST(COALESCE(OREPLACE(TotalOfNegative,' ',''),0.00) AS NUMERIC(32,2)))) AS NetAmount 

,EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')) AS MONTH_4

,'Freight' AS Typess,'FRT' AS ChargeTypess

,BillToAccountNumber AS AccountNumber

FROM Testtemptable

GROUP BY ServiceType,EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')),BillToAccountNumber

UNION

SELECT ServiceType AS ServiceTypess,

SUM(CAST(COALESCE(OREPLACE(TransportationChargeAmount,' ',''),0.00) AS NUMERIC(32,2))-

ABS(CAST(COALESCE(OREPLACE(TotalOfNegative,' ',''),0.00) AS NUMERIC(32,2)))+CAST(COALESCE(OREPLACE(TotalOfPositive,' ',''),0.00) AS NUMERIC(32,2))) AS NetAmount

,EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')) AS MONTH_4

,'Freight' AS Typess,'FRTWithAcc' AS ChargeTypess

,BillToAccountNumber AS AccountNumber 

FROM Testtemptable

GROUP BY ServiceType,EXTRACT(MONTH FROM cast(Invoicedate as date format 'yyyymmdd')),BillToAccountNumber)src

WHERE src.ServType IS NOT NULL)tbl1

SET  GrandMonthAndAccountLevelTotalForServiceLevel=cast(tbl1.NetAmount as varchar(200))

WHERE T202_ChargeDescription_Dashboard.ChargeDescription=tbl1.ServType 

AND CAST(T202_ChargeDescription_Dashboard.Month_1 as numeric(32,0))=CAST(tbl1.Month_5 as numeric(32,0))

AND T202_ChargeDescription_Dashboard.ChargeType=tbl1.ChargeType

AND cast(T202_ChargeDescription_Dashboard.client_id as numeric(32,0))=cast(tbl1.ClientId as numeric(32,0))

AND T202_ChargeDescription_Dashboard.ChargeGroup=tbl1.typ

AND CAST(T202_ChargeDescription_Dashboard.AccountNumber as numeric(32,0))=CAST(tbl1.AccNumber as numeric(32,0));

END;