March 6, 2012 at 8:25 am
Hi All,
I am trying to modify a create view that was done by a previous developer in mySQL, he converted it to SQL Server, but when I try to view the data, I get the following error:
Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to BIGINT.
The view runs with no errors as do the functions it calls, but I cannot view the data. Here is where I think the issues is because this is the only BIGINT I can find in the query.
Here is the line of code for the function call in the code:
dbo.ComputeSizeDescription(cat.ItemForm,st.Width,st.Length)
These are the datatypes: ItemForm - smallint, Width - decimal, Length - decimal
And here are the 2 functions it is calling:
--ComputeSizeDescription
CREATE FUNCTION dbo.ComputeSizeDescription
(
@itemForm INT,
@width FLOAT,
@len FLOAT
) RETURNS VARCHAR(128)
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @descr varchar(128)
SELECT @descr = CASE @itemForm
-- @Width Only Items
WHEN 2 THEN @Width + '\"'
WHEN 3 THEN @Width + '\"'
-- Len Only Items
WHEN 4 THEN dbo.ToFeetAndInches(@len)
-- @Width & Len Items
WHEN 5 THEN @Width + '\" x ' + @Width + '\"'
WHEN 6 THEN @Width + '\" x ' + @Width + '\"'
WHEN 10 THEN @Width + '\" x ' + @Width + '\"'
ELSE @Width + '\" Item Form Value not given \"'
END
return @descr
END
--ToFeetandInches
CREATE FUNCTION ToFeetAndInches
(
@len FLOAT
)
RETURNS varchar(64)
AS
BEGIN
DECLARE @feet FLOAT, @inches FLOAT
SELECT @feet = @len / 12;
SELECT @inches = @len - (@feet*12)
return @feet +'\"' + @inches + '\"'
END
Any help would be very appreciated, the previous developer is not available and I am new to this and assume this is something simple, but I am missing it. I need to get this done ASAP, please help!
Thanks!
March 6, 2012 at 8:41 am
It is the RETURN. You are returning a VARCHAR when the value is a FLOAT.
Jared
CE - Microsoft
March 6, 2012 at 8:46 am
Try this:
--ComputeSizeDescription
CREATE FUNCTION dbo.ComputeSizeDescription
(
@itemForm INT,
@width FLOAT,
@len FLOAT
) RETURNS VARCHAR(128)
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @descr varchar(128)
SELECT @descr = CASE @itemForm
-- @Width Only Items
WHEN 2 THEN CAST(@Width AS VARCHAR(128)) + '"'
WHEN 3 THEN CAST(@Width AS VARCHAR(128)) + '"'
-- Len Only Items
WHEN 4 THEN dbo.ToFeetAndInches(@len)
-- @Width & Len Items
WHEN 5 THEN CAST(@Width AS VARCHAR(128)) + '" x ' + CAST(@Width AS VARCHAR(128)) + '"'
WHEN 6 THEN CAST(@Width AS VARCHAR(128)) + '" x ' + CAST(@Width AS VARCHAR(128)) + '"'
WHEN 10 THEN CAST(@Width AS VARCHAR(128)) + '" x ' + CAST(@Width AS VARCHAR(128)) + '"'
ELSE CAST(@Width AS VARCHAR(128)) + '" Item Form Value not given "'
END
return @descr
END
GO
--ToFeetandInches
CREATE FUNCTION ToFeetAndInches
(
@len FLOAT
)
RETURNS varchar(64)
AS
BEGIN
DECLARE @feet FLOAT, @inches FLOAT
SELECT @feet = @len / 12;
SELECT @inches = @len - (@feet*12)
return CAST(@feet AS VARCHAR(64)) +'"' + CAST(@inches AS VARCHAR(64)) + '"'
END
GO
SELECT dbo.ComputeSizeDescription(2,10.3,22.89)
Jared
CE - Microsoft
March 6, 2012 at 8:55 am
Thanks Jared!
That fixed that error and now I am getting another one...
Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Is there anyway to narrow down where that error is coming from? The view completes successfully, but when I try to view the data, I get this error..
Here is the view if this is helpful:
USE [SteelPlus]
GO
/****** Object: View [dbo].[salesdtl_v] Script Date: 03/05/2012 21:50:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--DROP VIEW salesdtl_v ;
CREATE VIEW [dbo].[salesdtl_v] AS
SELECT st.Transaction_No AS SalesOrder_No,
st.Line_No AS Line_No,
st.MaterialSource_Type AS MaterialSource_Type,
(case when (st.MaterialSource_Type = 'B') then 'BUYOUT' else 'STOCK' end) AS MaterialSourceValue,
st.PickingGroup_ID AS PickingGroup_ID,
(select wp.PickingGroupDescription from SteelPlus.dbo.warehousepickinggroup wp
join salestransactionhdr sth ON ((wp.PickingGroup_ID = st.PickingGroup_ID) and (wp.Warehouse_No = sth.Warehouse_No))) AS PickingGroupDescription,
st.CustomerPartNumber_ID AS CustomerPartNumber_ID,
st.Item_ID AS Item_ID,
st.ItemDescription AS LineDescription,
(select i.ItemDescription from item i where (i.Item_ID = st.Item_ID)) AS ItemMasterFileDescription,
st.UnitWeight AS UnitWeight,
st.OrderQuantity AS OrderQuantity,
st.OrderQuantity_UOM AS OrderQuantity_UOM,
st.ShippedQuantity AS ShippedQuantity,
st.UnpostedInvoiceQuantity AS UnpostedInvoiceQuantity,
st.QuantityInUnitPriceUnitOfMeasure AS QuantityInUnitPriceUnitOfMeasure,
st.StandardSize_No AS StandardSize_No,
st.Length AS Length,
(case when (st.StandardSize_No > 0) then 'STANDARD SIZE' else 'NON-STANDARD SIZE' end) AS Size_Type,
(case when (st.StandardSize_No > 0) then (select stds.SizeDescription from standardsize stds where (st.StandardSize_No = stds.StandardSize_No))
else dbo.ComputeSizeDescription(cat.ItemForm,st.Width,st.Length) end) AS SizeDescription,dbo.getItemFormValue(cat.ItemForm) AS ItemFormValue,
dbo.getItemTypeValue(cat.ItemType) AS ItemTypeValue,
st.Width AS Width,
st.Length AS Length2,
(case when (cat.ItemForm in (2,3,5,6,10)) then st.ThicknessOrOutsideDiameter else 0 end) AS Thickness,
(case when (cat.ItemForm = 4) then st.ThicknessOrOutsideDiameter else 0 end)AS OutsideDiameter,
st.BillQuantity AS BillQuantity,
st.BillQuantity_UOM AS BillQuantity_UOM,
st.BillQuantityWidth AS BillQuantityWidth,
st.BillQuantityLength AS BillQuantityLength,
(case when (cat.ItemForm in (2,3,5,6,10)) then st.BillQuantityThicknessOrOd else 0 end) AS BillQuantityThickness,
(case when (cat.ItemForm = 4) then st.BillQuantityThicknessOrOd else 0 end) AS BillQuantityOutsideDiameter,
st.UnitCost AS UnitCost,
st.UnitCost_UOM AS UnitCost_UOM,
(case when (st.MarkupOrMargin_Type = 'R') then 'MARKUP' else 'MARGIN' end) AS MarkupOrMargin_Type,
(case when (st.MarkupOrMargin_Type = 'M') then st.BasePriceMarkupOrMargin else 0 end) AS BasePriceMargin,
(case when (st.MarkupOrMargin_Type = 'R') then st.BasePriceMarkupOrMargin else 0 end) AS BasePriceMarkup,
st.BaseUnitPrice AS BaseUnitPrice,
st.BaseUnitPrice_UOM AS BaseUnitPrice_UOM,
st.CuttingChargeSchedule_ID AS CuttingChargeSchedule_ID,
(select p.ScheduleDescription from CuttingChargeSchedule p where (p.Schedule_ID = st.CuttingChargeSchedule_ID)) AS CuttingChargeDescription,
st.CuttingChargeQuantity AS CuttingChargeQuantity,
st.CuttingChargeUnitPrice AS CuttingChargeUnitPrice,
st.CuttingChargeSetupAmount AS CuttingChargeSetupAmount,
st.UnitPrice AS UnitPrice,st.UnitPrice_UOM AS UnitPrice_UOM,
st.ExtendedPrice AS ExtendedPrice,
st.Category_ID AS Category_ID,
cat.CategoryName AS CategoryName,
st.TotalLineWeight AS TotalLineWeight,
st.ExtendedCost AS ExtendedCost,
sth.Transaction_Date AS Transaction_Date,
sth.Customer_ID AS Customer_ID,
(select c.CustomerName from customer c where (c.Customer_ID = sth.Customer_ID)) AS CutomerName,
(case when (sth.Transaction_Type = 'C') then 'CLOSED' else 'OPEN' end) AS Status,
sth.ActualInsideRep_ID AS ActualInsideRep_ID,
(select s.RepName from salesrep s
where (s.Rep_ID = sth.ActualInsideRep_ID)) AS ActualInsideRepName,
sth.AssignedInsideRep_ID AS AssignedInsideRep_ID,
(select s.RepName from salesrep s
where (s.Rep_ID = sth.AssignedInsideRep_ID)) AS AssignedInsideRepName,
sth.AssignedOutsideRep1_ID AS AssignedOutsideRep1_ID,
(select s.RepName from salesrep s
where (s.Rep_ID = sth.AssignedOutsideRep1_ID)) AS AssignedOutsideRep1Name,
sth.AssignedOutsideRep2_ID AS AssignedOutsideRep2_ID,
(select s.RepName from salesrep s
where (s.Rep_ID = sth.AssignedOutsideRep2_ID)) AS AssignedOutsideRep2Name,
sth.Branch_No AS Branch_No,
(select b.BranchName from branch b
where (b.Branch_No = sth.Branch_No)) AS BranchName,
sth.Warehouse_No AS Warehouse_No,
(select w.WarehouseName from warehouse w
where (w.Warehouse_No = sth.Warehouse_No)) AS WarehouseName,
sth.JobName AS JobName,
(case when (sth.BillOnWeight_Type = 'T')
then 'THEORETICAL' else 'ACTUAL' end) AS BillOnWeight_Type,
sth.ShippingAddress_Type AS ShippingAddress_Type,
(case when (sth.ShippingAddress_Type = 3)
then 'SEPCIFIED ADDRESS' when (sth.ShippingAddress_Type = 2)
then 'STORED ADDRESS' else 'WILL CALL' end) AS ShippingAddressTypeValue,
sth.ShippingAddress_No AS ShippingAddress_No,
sth.ShippingAddressName AS ShippingAddressName,
sth.ShippingAddressLine1 AS ShippingAddressLine1,
sth.ShippingAddressLine2 AS ShippingAddressLine2,
sth.ShippingAddressLine3 AS ShippingAddressLine3,
sth.ShippingCity AS ShippingCity,
sth.ShippingState AS ShippingState,
sth.ShippingCountry AS ShippingCountry,
sth.ShippingZipOrPostalCode AS ShippingZipOrPostalCode,
sth.BackordersAllowed_Flag AS BackordersAllowed_Flag,
sth.Ship_Date AS Ship_Date,
sth.BillingAddressName AS BillingAddressName,
sth.BillingAddressLine1 AS BillingAddressLine1,
sth.BillingAddressLine2 AS BillingAddressLine2,
sth.BillingAddressLine3 AS BillingAddressLine3,
sth.BillingCity AS BillingCity,
sth.BillingState AS BillingState,
sth.BillingCountry AS BillingCountry,
sth.BillingZipOrPostalCode AS BillingZipOrPostalCode,
sth.UDF1 AS UDF1,
sth.UDF2 AS UDF2,
sth.UDF3 AS UDF3,
sth.UDF4 AS UDF4,
sth.UDF5 AS UDF5,
sth.UDF6 AS UDF6,
sth.UDF7 AS UDF7,
sth.UDF8 AS UDF8,
sth.PaymentTerms_ID AS PaymentTerms_ID,
(select c.TermsDescription from customerpaymentterms c
where (c.Terms_ID = sth.PaymentTerms_ID)) AS PaymentTermsDescription,
sth.SalesTaxCode1_ID AS SalesTaxCode1_ID,
(select s.JurisdictionDescription from salestaxjurisdiction s
where (s.Jurisdiction_ID = sth.SalesTaxCode1_ID)) AS SalesTaxCode1Description,
sth.SalesTaxRate1 AS SalesTaxRate1,
sth.MaximumTax1Amount AS MaximumTax1Amount,
sth.SalesTaxCode2_ID AS SalesTaxCode2_ID,
(select s.JurisdictionDescription
from salestaxjurisdiction s
where (s.Jurisdiction_ID = sth.SalesTaxCode2_ID)) AS SalesTaxCode2Description,
sth.SalesTaxRate2 AS SalesTaxRate2,
sth.MaximumTax2Amount AS MaximumTax2Amount,
sth.SalesTaxCode3_ID AS SalesTaxCode3_ID,
(select s.JurisdictionDescription from salestaxjurisdiction s
where (s.Jurisdiction_ID = sth.SalesTaxCode3_ID))
AS SalesTaxCode3Description,
sth.SalesTaxRate3 AS SalesTaxRate3,
sth.MaximumTax3Amount AS MaximumTax3Amount,
sth.SalesTaxCode4_ID AS SalesTaxCode4_ID,
(select s.JurisdictionDescription from salestaxjurisdiction s
where (s.Jurisdiction_ID = sth.SalesTaxCode4_ID))
AS SalesTaxCode4Description,
sth.SalesTaxRate4 AS SalesTaxRate4,
sth.MaximumTax4Amount AS MaximumTax4Amount,
sth.SalesTaxCode5_ID AS SalesTaxCode5_ID,
(select s.JurisdictionDescription from salestaxjurisdiction s
where (s.Jurisdiction_ID = sth.SalesTaxCode5_ID))
AS SalesTaxCode5Description,
sth.SalesTaxRate5 AS SalesTaxRate5,
sth.MaximumTax5Amount AS MaximumTax5Amount,
sth.ReleasedCreditAmount AS ReleasedCreditAmount,
sth.CustomerPurchaseOrderNumber AS CustomerPurchaseOrderNumber,
sth.TaxableTransaction_Flag AS TaxableTransaction_Flag,
sth.ShippingMethod_No AS ShippingMethod_No,
(select s.MethodDescription from shippingmethod s
where (s.ShippingMethod_No = sth.ShippingMethod_No))
AS ShippingMethodDescription,
sth.ShippingMethod_No AS ShippingRoute_ID,
(select s.MethodDescription from shippingMethod s
where (s.Route_ID = sth.ShippingMethod_No))
AS ShippingRouteName,
sth.BuyerContact_No AS BuyerContact_No,
sth.BuyerName AS BuyerName,
sth.BuyerEmailAddress AS BuyerEmailAddress,
sth.BuyerCountry AS BuyerCountry,
sth.BuyerPhoneNumber AS BuyerPhoneNumber,
sth.BuyerExtension AS BuyerExtension,
sth.BuyerFaxNumber AS BuyerFaxNumber,
sth.QuotePrintCount AS QuotePrintCount,
sth.SalesOrderPrintCount AS SalesOrderPrintCount,
sth.LastSalesOrderPrintedByUser_ID AS LastSalesOrderPrintedByUser_ID,
sth.LastSalesOrderPrinted_Date AS LastSalesOrderPrinted_Date,
sth.LastSalesOrderPrinted_Time AS LastSalesOrderPrinted_Time,
sth.PickListPrintCount AS PickListPrintCount,
sth.LastPickListPrintedByUser_ID AS LastPickListPrintedByUser_ID,
sth.LastPickListPrinted_Date AS LastPickListPrinted_Date,
sth.LastPickListPrinted_Time AS LastPickListPrinted_Time,
sth.ShipperPrintCount AS ShipperPrintCount,
sth.LastShipperPrintedByUser_ID AS LastShipperPrintedByUser_ID,
sth.LastShipperPrinted_Date AS LastShipperPrinted_Date,
sth.LastShipperPrinted_Time AS LastShipperPrinted_Time,
sth.AcknowledgementPrintCount AS AcknowledgementPrintCount,
sth.LastAcknowledgementPrintedByUser_ID AS
LastAcknowledgementPrintedByUser_ID,
sth.LastAcknowledgementPrinted_Date AS LastAcknowledgementPrinted_Date,
sth.LastAcknowledgementPrinted_Time AS LastAcknowledgementPrinted_Time,
sth.SalesOrderOnCreditHold_Flag AS SalesOrderOnCreditHold_Flag,
sth.CrHoldReasonPastDue_Flag AS CrHoldReasonPastDue_Flag,
sth.CrHoldReasonCrLimit_Flag AS CrHoldReasonCrLimit_Flag,
sth.CrHoldReasonCrCheckExpired_Flag AS CrHoldReasonCrCheckExpired_Flag,
sth.CrHoldReasonCustAlwaysOnHold_Flag AS
CrHoldReasonCustAlwaysOnHold_Flag,
sth.CreditHoldReleaseCount AS CreditHoldReleaseCount,
sth.LastReleasedFromCrHoldByUser_ID AS LastReleasedFromCrHoldByUser_ID,
sth.LastReleasedFromCrHold_Date AS LastReleasedFromCrHold_Date,
sth.LastReleasedFromCrHold_Time AS LastReleasedFromCrHold_Time,
(st.ExtendedPrice - st.ExtendedCost) AS ProfitAmount,
((st.ExtendedPrice - st.ExtendedCost) / st.ExtendedCost) * 100 AS MarginPercentage,
st.OpenClosed_Type AS OpenClosed_Type,
(case when (st.OpenClosed_Type = 'C') then 'CLOSED' else 'OPEN' end) AS OpenClosedValue,
cat.CategoryGroup1_ID AS CategoryGroup1_ID,
(select c.GroupDescription from categorygroup c where (c.Group_ID = cat.CategoryGroup1_ID)) AS CatgeoryGroup1Description,
cat.CategoryGroup2_ID AS CategoryGroup2_ID,
(select c.GroupDescription from categorygroup c where (c.Group_ID = cat.CategoryGroup2_ID)) AS CatgeoryGroup2Description,
cat.CategoryGroup3_ID AS CategoryGroup3_ID,
(select c.GroupDescription from categorygroup c where (c.Group_ID = cat.CategoryGroup3_ID)) AS CatgeoryGroup3Description,
cat.CategoryGroup4_ID AS CategoryGroup4_ID,
(select c.GroupDescription from categorygroup c where (c.Group_ID = cat.CategoryGroup4_ID)) AS CatgeoryGroup4Description,
cat.CategoryGroup5_ID AS CategoryGroup5_ID,
(select c.GroupDescription from categorygroup c where (c.Group_ID = cat.CategoryGroup5_ID)) AS CatgeoryGroup5Description,
sth.Territory_ID AS Territory_ID,
(select s.TerritoryDescription from territory s where (s.Territory_ID = sth.Territory_ID)) AS TerritoryDescription,
(select t.TerritoryGroup1_ID from territory t where (t.Territory_ID = sth.Territory_ID)) AS TerritoryGroup1_ID,
(select tg.GroupDescription from territorygroup tg join territory t ON ((tg.TerritoryGroup_ID = t.TerritoryGroup1_ID) and (t.Territory_ID = sth.Territory_ID))) AS Territory1Description,
(select t.TerritoryGroup2_ID from territory t where (t.Territory_ID = sth.Territory_ID)) AS TerritoryGroup2_ID,
(select tg.GroupDescription from territorygroup tg join territory t ON ((tg.TerritoryGroup_ID = t.TerritoryGroup2_ID) and (t.Territory_ID = sth.Territory_ID))) AS Territory2Description,
(select t.TerritoryGroup3_ID from territory t where (t.Territory_ID = sth.Territory_ID)) AS TerritoryGroup3_ID,
(select tg.GroupDescription from territorygroup tg join territory t ON ((tg.TerritoryGroup_ID = t.TerritoryGroup3_ID) and (t.Territory_ID = sth.Territory_ID))) AS Territory3Description,
(select t.TerritoryGroup4_ID from territory t where (t.Territory_ID = sth.Territory_ID)) AS TerritoryGroup4_ID,
(select tg.GroupDescription from territorygroup tg join territory t ON ((tg.TerritoryGroup_ID = t.TerritoryGroup4_ID) and (t.Territory_ID = sth.Territory_ID))) AS Territory4Description,
(select t.TerritoryGroup5_ID from territory t where (t.Territory_ID = sth.Territory_ID)) AS TerritoryGroup5_ID,
(select tg.GroupDescription from territorygroup tg join territory t ON ((tg.TerritoryGroup_ID = t.TerritoryGroup5_ID) and (t.Territory_ID = sth.Territory_ID))) AS Territory5Description
--,
--(select c.CustomerType1_ID from customer c where (c.Customer_ID = sth.Customer_ID)) AS CustomerType1_ID,
--(select ct.CustomerTypeDescription from customertype ct join customer c ON ((ct.CustomerType_ID = c.CustomerType1_ID) and (c.Customer_ID = sth.Customer_ID))) AS CustomerType1Description,
--(select c.CustomerType2_ID from customer c where (c.Customer_ID = sth.Customer_ID)) AS CustomerType2_ID,
--(select ct.CustomerTypeDescription from customertype ct join customer c ON ((ct.CustomerType_ID = c.CustomerType2_ID) and (c.Customer_ID = sth.Customer_ID))) AS CustomerType2Description,
--(select c.CustomerType3_ID from customer c where (c.Customer_ID = sth.Customer_ID)) AS CustomerType3_ID,
--(select ct.CustomerTypeDescription from customertype ct join customer c ON ((ct.CustomerType_ID = c.CustomerType3_ID) and (c.Customer_ID = sth.Customer_ID))) AS CustomerType3Description,
--(select c.CustomerType4_ID from customer c where (c.Customer_ID = sth.Customer_ID)) AS CustomerType4_ID,
--(select ct.CustomerTypeDescription from customertype ct join customer c ON ((ct.CustomerType_ID = c.CustomerType4_ID) and (c.Customer_ID = sth.Customer_ID))) AS CustomerType4Description,
--(select c.CustomerType5_ID from customer c where (c.Customer_ID = sth.Customer_ID)) AS CustomerType5_ID,
--(select ct.CustomerTypeDescription from customertype ct join customer c ON((ct.CustomerType_ID = c.CustomerType5_ID) and (c.Customer_ID = sth.Customer_ID))) AS CustomerType5Description
from ((salestransactiondtl st
join salestransactionhdr sth on((st.Transaction_No = sth.Transaction_No)))
join category cat on((st.Category_ID = cat.Category_ID)))
where (sth.Transaction_Type in ('S','C'));
GO
Things are commented out because the database changed, so just ignore that part for now.
Thanks!
March 6, 2012 at 9:02 am
that's a bit much for me to take on. It is really "dirty" and complex. The error means that something in the view is returning more than one value when it can only compare to 1. Ex:
SELECT *
FROM foo
WHERE columnA = (SELECT someColumn FROM dopple WHERE x > 4)
The condition that columnA = can only return 1 value for the statement to run. If that sub-query (SELECT someColumn FROM dopple WHERE x > 4) returns more than 1 value, it fails. There HAS to be a much better way to rewrite this view, and if I were you I would take the time to understand it and rewrite it.
Jared
CE - Microsoft
March 6, 2012 at 9:32 am
HOLY!!!!!!!!!
There are over 30 subselects and somewhere around 5-6 scalar functions. Even if you got that to work you might as well go out for lunch while it runs.
That is going to take at least a week to clean up. I would suggest hiring a consultant to work through this. There is no way you will get enough help on a forum to straighten this thing out.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 6, 2012 at 9:42 am
Sean Lange (3/6/2012)
HOLY!!!!!!!!!There are over 30 subselects and somewhere around 5-6 scalar functions. Even if you got that to work you might as well go out for lunch while it runs.
That is going to take at least a week to clean up. I would suggest hiring a consultant to work through this. There is no way you will get enough help on a forum to straighten this thing out.
Almost done 🙂 Take a look at this and adjust as needed. Notice how I got rid of all of the sub-queries. Do the same for the remainder.
--DROP VIEW salesdtl_v ;
CREATE VIEW [dbo].[salesdtl_v] AS
SELECT st.Transaction_No AS SalesOrder_No,
st.Line_No AS Line_No,
st.MaterialSource_Type AS MaterialSource_Type,
case
when st.MaterialSource_Type = 'B' then 'BUYOUT'
else 'STOCK'
end AS MaterialSourceValue,
st.PickingGroup_ID AS PickingGroup_ID,
wp.PickingGroupDescription AS PickingGroupDescription,
st.CustomerPartNumber_ID AS CustomerPartNumber_ID,
st.Item_ID AS Item_ID,
st.ItemDescription AS LineDescription,
i.ItemDescription AS ItemMasterFileDescription,
st.UnitWeight AS UnitWeight,
st.OrderQuantity AS OrderQuantity,
st.OrderQuantity_UOM AS OrderQuantity_UOM,
st.ShippedQuantity AS ShippedQuantity,
st.UnpostedInvoiceQuantity AS UnpostedInvoiceQuantity,
st.QuantityInUnitPriceUnitOfMeasure AS QuantityInUnitPriceUnitOfMeasure,
st.StandardSize_No AS StandardSize_No,
st.Length AS Length,
case
when st.StandardSize_No > 0 then 'STANDARD SIZE'
else 'NON-STANDARD SIZE'
end AS Size_Type,
case
when st.StandardSize_No > 0 then (select stds.SizeDescription from standardsize stds where (st.StandardSize_No = stds.StandardSize_No))
else dbo.ComputeSizeDescription(cat.ItemForm,st.Width,st.Length)
end AS SizeDescription,
dbo.getItemFormValue(cat.ItemForm) AS ItemFormValue,
dbo.getItemTypeValue(cat.ItemType) AS ItemTypeValue,
st.Width AS Width,
st.Length AS Length2,
case
when cat.ItemForm in (2,3,5,6,10) then st.ThicknessOrOutsideDiameter
else 0
end AS Thickness,
case
when cat.ItemForm = 4 then st.ThicknessOrOutsideDiameter
else 0
end AS OutsideDiameter,
st.BillQuantity AS BillQuantity,
st.BillQuantity_UOM AS BillQuantity_UOM,
st.BillQuantityWidth AS BillQuantityWidth,
st.BillQuantityLength AS BillQuantityLength,
case
when cat.ItemForm in (2,3,5,6,10) then st.BillQuantityThicknessOrOd
else 0
end AS BillQuantityThickness,
case
when cat.ItemForm = 4 then st.BillQuantityThicknessOrOd
else 0
end AS BillQuantityOutsideDiameter,
st.UnitCost AS UnitCost,
st.UnitCost_UOM AS UnitCost_UOM,
case
when st.MarkupOrMargin_Type = 'R' then 'MARKUP'
else 'MARGIN'
end AS MarkupOrMargin_Type,
case
when st.MarkupOrMargin_Type = 'M' then st.BasePriceMarkupOrMargin
else 0
end AS BasePriceMargin,
case
when st.MarkupOrMargin_Type = 'R' then st.BasePriceMarkupOrMargin
else 0
end AS BasePriceMarkup,
st.BaseUnitPrice AS BaseUnitPrice,
st.BaseUnitPrice_UOM AS BaseUnitPrice_UOM,
st.CuttingChargeSchedule_ID AS CuttingChargeSchedule_ID,
p.ScheduleDescription AS CuttingChargeDescription,
st.CuttingChargeQuantity AS CuttingChargeQuantity,
st.CuttingChargeUnitPrice AS CuttingChargeUnitPrice,
st.CuttingChargeSetupAmount AS CuttingChargeSetupAmount,
st.UnitPrice AS UnitPrice,st.UnitPrice_UOM AS UnitPrice_UOM,
st.ExtendedPrice AS ExtendedPrice,
st.Category_ID AS Category_ID,
cat.CategoryName AS CategoryName,
st.TotalLineWeight AS TotalLineWeight,
st.ExtendedCost AS ExtendedCost,
sth.Transaction_Date AS Transaction_Date,
sth.Customer_ID AS Customer_ID,
c.CustomerName AS CutomerName,
case
when sth.Transaction_Type = 'C' then 'CLOSED'
else 'OPEN'
end AS [Status],
sth.ActualInsideRep_ID AS ActualInsideRep_ID,
sr.RepName AS ActualInsideRepName,
sth.AssignedInsideRep_ID AS AssignedInsideRep_ID,
sr2.RepName AS AssignedInsideRepName,
sth.AssignedOutsideRep1_ID AS AssignedOutsideRep1_ID,
sr3.RepName AS AssignedOutsideRep1Name,
sth.AssignedOutsideRep2_ID AS AssignedOutsideRep2_ID,
sr4.RepName AS AssignedOutsideRep2Name,
sth.Branch_No AS Branch_No,
bn.BranchName AS BranchName,
sth.Warehouse_No AS Warehouse_No,
wn.WarehouseName AS WarehouseName,
sth.JobName AS JobName,
case
when sth.BillOnWeight_Type = 'T' then 'THEORETICAL'
else 'ACTUAL'
end AS BillOnWeight_Type,
sth.ShippingAddress_Type AS ShippingAddress_Type,
case
when sth.ShippingAddress_Type = 3 then 'SEPCIFIED ADDRESS'
when sth.ShippingAddress_Type = 2 then 'STORED ADDRESS'
else 'WILL CALL'
end AS ShippingAddressTypeValue,
sth.ShippingAddress_No AS ShippingAddress_No,
sth.ShippingAddressName AS ShippingAddressName,
sth.ShippingAddressLine1 AS ShippingAddressLine1,
sth.ShippingAddressLine2 AS ShippingAddressLine2,
sth.ShippingAddressLine3 AS ShippingAddressLine3,
sth.ShippingCity AS ShippingCity,
sth.ShippingState AS ShippingState,
sth.ShippingCountry AS ShippingCountry,
sth.ShippingZipOrPostalCode AS ShippingZipOrPostalCode,
sth.BackordersAllowed_Flag AS BackordersAllowed_Flag,
sth.Ship_Date AS Ship_Date,
sth.BillingAddressName AS BillingAddressName,
sth.BillingAddressLine1 AS BillingAddressLine1,
sth.BillingAddressLine2 AS BillingAddressLine2,
sth.BillingAddressLine3 AS BillingAddressLine3,
sth.BillingCity AS BillingCity,
sth.BillingState AS BillingState,
sth.BillingCountry AS BillingCountry,
sth.BillingZipOrPostalCode AS BillingZipOrPostalCode,
sth.UDF1 AS UDF1,
sth.UDF2 AS UDF2,
sth.UDF3 AS UDF3,
sth.UDF4 AS UDF4,
sth.UDF5 AS UDF5,
sth.UDF6 AS UDF6,
sth.UDF7 AS UDF7,
sth.UDF8 AS UDF8,
sth.PaymentTerms_ID AS PaymentTerms_ID,
cpt.TermsDescription AS PaymentTermsDescription,
sth.SalesTaxCode1_ID AS SalesTaxCode1_ID,
stj1.JurisdictionDescription AS SalesTaxCode1Description,
sth.SalesTaxRate1 AS SalesTaxRate1,
sth.MaximumTax1Amount AS MaximumTax1Amount,
sth.SalesTaxCode2_ID AS SalesTaxCode2_ID,
stj2.JurisdictionDescription AS SalesTaxCode2Description,
sth.SalesTaxRate2 AS SalesTaxRate2,
sth.MaximumTax2Amount AS MaximumTax2Amount,
sth.SalesTaxCode3_ID AS SalesTaxCode3_ID,
stj3.JurisdictionDescription AS SalesTaxCode3Description,
sth.SalesTaxRate3 AS SalesTaxRate3,
sth.MaximumTax3Amount AS MaximumTax3Amount,
sth.SalesTaxCode4_ID AS SalesTaxCode4_ID,
stj4.JurisdictionDescription AS SalesTaxCode4Description,
sth.SalesTaxRate4 AS SalesTaxRate4,
sth.MaximumTax4Amount AS MaximumTax4Amount,
sth.SalesTaxCode5_ID AS SalesTaxCode5_ID,
stj5.JurisdictionDescription AS SalesTaxCode5Description,
sth.SalesTaxRate5 AS SalesTaxRate5,
sth.MaximumTax5Amount AS MaximumTax5Amount,
sth.ReleasedCreditAmount AS ReleasedCreditAmount,
sth.CustomerPurchaseOrderNumber AS CustomerPurchaseOrderNumber,
sth.TaxableTransaction_Flag AS TaxableTransaction_Flag,
sth.ShippingMethod_No AS ShippingMethod_No,
smd.MethodDescription AS ShippingMethodDescription,
sth.ShippingMethod_No AS ShippingRoute_ID,
srn.MethodDescription AS ShippingRouteName,
sth.BuyerContact_No AS BuyerContact_No,
sth.BuyerName AS BuyerName,
sth.BuyerEmailAddress AS BuyerEmailAddress,
sth.BuyerCountry AS BuyerCountry,
sth.BuyerPhoneNumber AS BuyerPhoneNumber,
sth.BuyerExtension AS BuyerExtension,
sth.BuyerFaxNumber AS BuyerFaxNumber,
sth.QuotePrintCount AS QuotePrintCount,
sth.SalesOrderPrintCount AS SalesOrderPrintCount,
sth.LastSalesOrderPrintedByUser_ID AS LastSalesOrderPrintedByUser_ID,
sth.LastSalesOrderPrinted_Date AS LastSalesOrderPrinted_Date,
sth.LastSalesOrderPrinted_Time AS LastSalesOrderPrinted_Time,
sth.PickListPrintCount AS PickListPrintCount,
sth.LastPickListPrintedByUser_ID AS LastPickListPrintedByUser_ID,
sth.LastPickListPrinted_Date AS LastPickListPrinted_Date,
sth.LastPickListPrinted_Time AS LastPickListPrinted_Time,
sth.ShipperPrintCount AS ShipperPrintCount,
sth.LastShipperPrintedByUser_ID AS LastShipperPrintedByUser_ID,
sth.LastShipperPrinted_Date AS LastShipperPrinted_Date,
sth.LastShipperPrinted_Time AS LastShipperPrinted_Time,
sth.AcknowledgementPrintCount AS AcknowledgementPrintCount,
sth.LastAcknowledgementPrintedByUser_ID AS
LastAcknowledgementPrintedByUser_ID,
sth.LastAcknowledgementPrinted_Date AS LastAcknowledgementPrinted_Date,
sth.LastAcknowledgementPrinted_Time AS LastAcknowledgementPrinted_Time,
sth.SalesOrderOnCreditHold_Flag AS SalesOrderOnCreditHold_Flag,
sth.CrHoldReasonPastDue_Flag AS CrHoldReasonPastDue_Flag,
sth.CrHoldReasonCrLimit_Flag AS CrHoldReasonCrLimit_Flag,
sth.CrHoldReasonCrCheckExpired_Flag AS CrHoldReasonCrCheckExpired_Flag,
sth.CrHoldReasonCustAlwaysOnHold_Flag AS
CrHoldReasonCustAlwaysOnHold_Flag,
sth.CreditHoldReleaseCount AS CreditHoldReleaseCount,
sth.LastReleasedFromCrHoldByUser_ID AS LastReleasedFromCrHoldByUser_ID,
sth.LastReleasedFromCrHold_Date AS LastReleasedFromCrHold_Date,
sth.LastReleasedFromCrHold_Time AS LastReleasedFromCrHold_Time,
st.ExtendedPrice - st.ExtendedCost AS ProfitAmount,
((st.ExtendedPrice - st.ExtendedCost) / st.ExtendedCost) * 100 AS MarginPercentage,
st.OpenClosed_Type AS OpenClosed_Type,
case
when st.OpenClosed_Type = 'C' then 'CLOSED'
else 'OPEN'
end AS OpenClosedValue,
cat.CategoryGroup1_ID AS CategoryGroup1_ID,
cat1.GroupDescription AS CatgeoryGroup1Description,
cat.CategoryGroup2_ID AS CategoryGroup2_ID,
cat2.GroupDescription AS CatgeoryGroup2Description,
cat.CategoryGroup3_ID AS CategoryGroup3_ID,
cat3.GroupDescription AS CatgeoryGroup3Description,
cat.CategoryGroup4_ID AS CategoryGroup4_ID,
cat4.GroupDescription AS CatgeoryGroup4Description,
cat.CategoryGroup5_ID AS CategoryGroup5_ID,
cat5.GroupDescription AS CatgeoryGroup5Description,
sth.Territory_ID AS Territory_ID,
(select s.TerritoryDescription from territory s where (s.Territory_ID = sth.Territory_ID)) AS TerritoryDescription,
(select t.TerritoryGroup1_ID from territory t where (t.Territory_ID = sth.Territory_ID)) AS TerritoryGroup1_ID,
(select tg.GroupDescription from territorygroup tg join territory t ON ((tg.TerritoryGroup_ID = t.TerritoryGroup1_ID) and (t.Territory_ID = sth.Territory_ID))) AS Territory1Description,
(select t.TerritoryGroup2_ID from territory t where (t.Territory_ID = sth.Territory_ID)) AS TerritoryGroup2_ID,
(select tg.GroupDescription from territorygroup tg join territory t ON ((tg.TerritoryGroup_ID = t.TerritoryGroup2_ID) and (t.Territory_ID = sth.Territory_ID))) AS Territory2Description,
(select t.TerritoryGroup3_ID from territory t where (t.Territory_ID = sth.Territory_ID)) AS TerritoryGroup3_ID,
(select tg.GroupDescription from territorygroup tg join territory t ON ((tg.TerritoryGroup_ID = t.TerritoryGroup3_ID) and (t.Territory_ID = sth.Territory_ID))) AS Territory3Description,
(select t.TerritoryGroup4_ID from territory t where (t.Territory_ID = sth.Territory_ID)) AS TerritoryGroup4_ID,
(select tg.GroupDescription from territorygroup tg join territory t ON ((tg.TerritoryGroup_ID = t.TerritoryGroup4_ID) and (t.Territory_ID = sth.Territory_ID))) AS Territory4Description,
(select t.TerritoryGroup5_ID from territory t where (t.Territory_ID = sth.Territory_ID)) AS TerritoryGroup5_ID,
(select tg.GroupDescription from territorygroup tg join territory t ON ((tg.TerritoryGroup_ID = t.TerritoryGroup5_ID) and (t.Territory_ID = sth.Territory_ID))) AS Territory5Description
from salestransactiondtl st
inner join salestransactionhdr sth
on st.Transaction_No = sth.Transaction_No
inner join category cat
on st.Category_ID = cat.Category_ID
INNER JOIN SteelPlus.dbo.warehousepickinggroup wp
ON st.PickingGroup_ID = wp.PickingGroup_ID
and sth.Warehouse_No = wp.Warehouse_No
LEFT JOIN item i
ON st.Item_ID = i.Item_ID
LEFT JOIN CuttingChargeSchedule p
ON st.CuttingChargeSchedule_ID = p.Schedule_ID
LEFT JOIN customer c
ON sth.Customer_ID = c.Customer_ID
LEFT JOIN customerpaymentterms cpt
ON sth.PaymentTerms_ID = cpt.Terms_ID
LEFT JOIN salestaxjurisdiction stj1
ON sth.SalesTaxCode1_ID = stj1.Jurisdiction_ID
LEFT JOIN salestaxjurisdiction stj2
ON sth.SalesTaxCode2_ID = stj2.Jurisdiction_ID
LEFT JOIN salestaxjurisdiction stj3
ON sth.SalesTaxCode3_ID = stj3.Jurisdiction_ID
LEFT JOIN salestaxjurisdiction stj4
ON sth.SalesTaxCode4_ID = stj4.Jurisdiction_ID
LEFT JOIN salestaxjurisdiction stj5
ON sth.SalesTaxCode5_ID = stj5.Jurisdiction_ID
LEFt JOIN salesrep sr
ON sth.ActualInsideRep_ID = sr.rep_id
LEFt JOIN salesrep sr2
ON sth.AssignedInsideRep_ID = sr.rep_id
LEFT JOIN salesrep sr3
ON sth.AssignedOutsideRep1_ID= sr3.Rep_ID
LEFT JOIN salesrep sr4
ON sth.AssignedOutsideRep2_ID = sr4.Rep_ID
LEFT JOIN branch bn
ON sth.Branch_No = bn.Branch_No
LEFT JOIN warehouse wn
ON sth.Warehouse_No = wn.Warehouse_No
LEFT JOIN shippingmethod smd
ON sth.ShippingMethod_No = smd.ShippingMethod_No
LEFT JOIN from shippingMethod srn
ON sth.ShippingMethod_No = srn.Route_ID
LEFT JOIN categorygroup cat1
on cat.CategoryGroup1_ID = st.Category_ID
LEFT JOIN categorygroup cat2
on cat.CategoryGroup2_ID = st.Category_ID
LEFT JOIN categorygroup cat3
on cat.CategoryGroup3_ID = st.Category_ID
LEFT JOIN categorygroup cat4
on cat.CategoryGroup4_ID = st.Category_ID
LEFT JOIN categorygroup cat5
on cat.CategoryGroup5_ID = st.Category_ID
where sth.Transaction_Type in ('S','C');
GO
Jared
CE - Microsoft
March 6, 2012 at 10:11 am
Funny thing is, a consultant did create that! I was hired to create reports based off that view. They have created a non-normalized table based off this view so their customers can create their own reports without having to understand SQL.
That consultant is MIA basically and I have been trying to figure out what he has done under a very tight deadline (I am not a DBA). So thank you very much for your help, you are my new best friend Jared!
I will work on this and hopefully get it figured out soon!
thanks!
March 6, 2012 at 10:15 am
adiehl (3/6/2012)
Funny thing is, a consultant did create that! I was hired to create reports based off that view. They have created a non-normalized table based off this view so their customers can create their own reports without having to understand SQL.That consultant is MIA basically and I have been trying to figure out what he has done under a very tight deadline (I am not a DBA). So thank you very much for your help, you are my new best friend Jared!
I will work on this and hopefully get it figured out soon!
thanks!
That's funny!!! It seems that consultant didn't understand sql either. Fortunately Jared seems to have plenty of spare time on his hands lately.
Depending on the size of the tables under this those scalar functions could still be a performance problem but reports are notorious for taking longer than most things anyway so maybe it is acceptable to business.
If the performance is a problem once you get the query straightened out post back and we will find you some ways to make that faster.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 6, 2012 at 10:30 am
Sean Lange (3/6/2012)
adiehl (3/6/2012)
Funny thing is, a consultant did create that! I was hired to create reports based off that view. They have created a non-normalized table based off this view so their customers can create their own reports without having to understand SQL.That consultant is MIA basically and I have been trying to figure out what he has done under a very tight deadline (I am not a DBA). So thank you very much for your help, you are my new best friend Jared!
I will work on this and hopefully get it figured out soon!
thanks!
That's funny!!! It seems that consultant didn't understand sql either. Fortunately Jared seems to have plenty of spare time on his hands lately.
Depending on the size of the tables under this those scalar functions could still be a performance problem but reports are notorious for taking longer than most things anyway so maybe it is acceptable to business.
If the performance is a problem once you get the query straightened out post back and we will find you some ways to make that faster.
Sometimes too much time! I would consider creating a job to periodically move this data into a data warehouse, if the business does not require real-time reports. This way the query doesn't have to run each time. Remember that a view is simply a saved query. That's it.
Jared
CE - Microsoft
March 6, 2012 at 10:36 am
Thanks for all the help, if we have more issues maybe my company can hire Jared! The other consultant was specifically for Talend and I'm not sure that he was the best in that either as we are having problems with things he created in there too. Now I just need to find a Talend forum with really helpful people and maybe I can finally start to create reports!
March 6, 2012 at 10:38 am
adiehl (3/6/2012)
Thanks for all the help, if we have more issues maybe my company can hire Jared! The other consultant was specifically for Talend and I'm not sure that he was the best in that either as we are having problems with things he created in there too. Now I just need to find a Talend forum with really helpful people and maybe I can finally start to create reports!
I'll do consulting 🙂 Me likey money lol
Jared
CE - Microsoft
March 6, 2012 at 11:45 am
I like earning extra cash on the side as well.
One thing I would also look at changing are the UDFs. I would rewrite them as in-line table valued functions that return a single row table and use them in the FROM clause using CROSS APPLY. Check out this blog post for why, http://www.sqlservercentral.com/blogs/lynnpettis/2009/05/07/comparing-hardcoded-functions-in-line-tvf-s-and-scalar-functions/
March 6, 2012 at 1:55 pm
Well I got this working, but it looks like the data isn't correct 🙁
the first 1000 rows are all the same...
Any ideas?
Here is the view, same as yours Jared, just with the last part modified also.
any help would be appreciated, I was hoping to get this completed today
--DROP VIEW salesdtl_v ;
CREATE VIEW [dbo].[salesdtl_v] AS
SELECT st.Transaction_No AS SalesOrder_No,
st.Line_No AS Line_No,
st.MaterialSource_Type AS MaterialSource_Type,
case
when st.MaterialSource_Type = 'B' then 'BUYOUT'
else 'STOCK'
end AS MaterialSourceValue,
st.PickingGroup_ID AS PickingGroup_ID,
wp.PickingGroupDescription AS PickingGroupDescription,
st.CustomerPartNumber_ID AS CustomerPartNumber_ID,
st.Item_ID AS Item_ID,
st.ItemDescription AS LineDescription,
i.ItemDescription AS ItemMasterFileDescription,
st.UnitWeight AS UnitWeight,
st.OrderQuantity AS OrderQuantity,
st.OrderQuantity_UOM AS OrderQuantity_UOM,
st.ShippedQuantity AS ShippedQuantity,
st.UnpostedInvoiceQuantity AS UnpostedInvoiceQuantity,
st.QuantityInUnitPriceUnitOfMeasure AS QuantityInUnitPriceUnitOfMeasure,
st.StandardSize_No AS StandardSize_No,
st.Length AS Length,
case
when st.StandardSize_No > 0 then 'STANDARD SIZE'
else 'NON-STANDARD SIZE'
end AS Size_Type,
case
when st.StandardSize_No > 0 then (select stds.SizeDescription from standardsize stds where (st.StandardSize_No = stds.StandardSize_No))
else dbo.ComputeSizeDescription(cat.ItemForm,st.Width,st.Length)
end AS SizeDescription,
dbo.getItemFormValue(cat.ItemForm) AS ItemFormValue,
dbo.getItemTypeValue(cat.ItemType) AS ItemTypeValue,
st.Width AS Width,
st.Length AS Length2,
case
when cat.ItemForm in (2,3,5,6,10) then st.ThicknessOrOutsideDiameter
else 0
end AS Thickness,
case
when cat.ItemForm = 4 then st.ThicknessOrOutsideDiameter
else 0
end AS OutsideDiameter,
st.BillQuantity AS BillQuantity,
st.BillQuantity_UOM AS BillQuantity_UOM,
st.BillQuantityWidth AS BillQuantityWidth,
st.BillQuantityLength AS BillQuantityLength,
case
when cat.ItemForm in (2,3,5,6,10) then st.BillQuantityThicknessOrOd
else 0
end AS BillQuantityThickness,
case
when cat.ItemForm = 4 then st.BillQuantityThicknessOrOd
else 0
end AS BillQuantityOutsideDiameter,
st.UnitCost AS UnitCost,
st.UnitCost_UOM AS UnitCost_UOM,
case
when st.MarkupOrMargin_Type = 'R' then 'MARKUP'
else 'MARGIN'
end AS MarkupOrMargin_Type,
case
when st.MarkupOrMargin_Type = 'M' then st.BasePriceMarkupOrMargin
else 0
end AS BasePriceMargin,
case
when st.MarkupOrMargin_Type = 'R' then st.BasePriceMarkupOrMargin
else 0
end AS BasePriceMarkup,
st.BaseUnitPrice AS BaseUnitPrice,
st.BaseUnitPrice_UOM AS BaseUnitPrice_UOM,
st.CuttingChargeSchedule_ID AS CuttingChargeSchedule_ID,
p.ScheduleDescription AS CuttingChargeDescription,
st.CuttingChargeQuantity AS CuttingChargeQuantity,
st.CuttingChargeUnitPrice AS CuttingChargeUnitPrice,
st.CuttingChargeSetupAmount AS CuttingChargeSetupAmount,
st.UnitPrice AS UnitPrice,st.UnitPrice_UOM AS UnitPrice_UOM,
st.ExtendedPrice AS ExtendedPrice,
st.Category_ID AS Category_ID,
cat.CategoryName AS CategoryName,
st.TotalLineWeight AS TotalLineWeight,
st.ExtendedCost AS ExtendedCost,
sth.Transaction_Date AS Transaction_Date,
sth.Customer_ID AS Customer_ID,
c.CustomerName AS CutomerName,
case
when sth.Transaction_Type = 'C' then 'CLOSED'
else 'OPEN'
end AS [Status],
sth.ActualInsideRep_ID AS ActualInsideRep_ID,
sr.RepName AS ActualInsideRepName,
sth.AssignedInsideRep_ID AS AssignedInsideRep_ID,
sr2.RepName AS AssignedInsideRepName,
sth.AssignedOutsideRep1_ID AS AssignedOutsideRep1_ID,
sr3.RepName AS AssignedOutsideRep1Name,
sth.AssignedOutsideRep2_ID AS AssignedOutsideRep2_ID,
sr4.RepName AS AssignedOutsideRep2Name,
sth.Branch_No AS Branch_No,
bn.BranchName AS BranchName,
sth.Warehouse_No AS Warehouse_No,
wn.WarehouseName AS WarehouseName,
sth.JobName AS JobName,
case
when sth.BillOnWeight_Type = 'T' then 'THEORETICAL'
else 'ACTUAL'
end AS BillOnWeight_Type,
sth.ShippingAddress_Type AS ShippingAddress_Type,
case
when sth.ShippingAddress_Type = 3 then 'SEPCIFIED ADDRESS'
when sth.ShippingAddress_Type = 2 then 'STORED ADDRESS'
else 'WILL CALL'
end AS ShippingAddressTypeValue,
sth.ShippingAddress_No AS ShippingAddress_No,
sth.ShippingAddressName AS ShippingAddressName,
sth.ShippingAddressLine1 AS ShippingAddressLine1,
sth.ShippingAddressLine2 AS ShippingAddressLine2,
sth.ShippingAddressLine3 AS ShippingAddressLine3,
sth.ShippingCity AS ShippingCity,
sth.ShippingState AS ShippingState,
sth.ShippingCountry AS ShippingCountry,
sth.ShippingZipOrPostalCode AS ShippingZipOrPostalCode,
sth.BackordersAllowed_Flag AS BackordersAllowed_Flag,
sth.Ship_Date AS Ship_Date,
sth.BillingAddressName AS BillingAddressName,
sth.BillingAddressLine1 AS BillingAddressLine1,
sth.BillingAddressLine2 AS BillingAddressLine2,
sth.BillingAddressLine3 AS BillingAddressLine3,
sth.BillingCity AS BillingCity,
sth.BillingState AS BillingState,
sth.BillingCountry AS BillingCountry,
sth.BillingZipOrPostalCode AS BillingZipOrPostalCode,
sth.UDF1 AS UDF1,
sth.UDF2 AS UDF2,
sth.UDF3 AS UDF3,
sth.UDF4 AS UDF4,
sth.UDF5 AS UDF5,
sth.UDF6 AS UDF6,
sth.UDF7 AS UDF7,
sth.UDF8 AS UDF8,
sth.PaymentTerms_ID AS PaymentTerms_ID,
cpt.TermsDescription AS PaymentTermsDescription,
sth.SalesTaxCode1_ID AS SalesTaxCode1_ID,
stj1.JurisdictionDescription AS SalesTaxCode1Description,
sth.SalesTaxRate1 AS SalesTaxRate1,
sth.MaximumTax1Amount AS MaximumTax1Amount,
sth.SalesTaxCode2_ID AS SalesTaxCode2_ID,
stj2.JurisdictionDescription AS SalesTaxCode2Description,
sth.SalesTaxRate2 AS SalesTaxRate2,
sth.MaximumTax2Amount AS MaximumTax2Amount,
sth.SalesTaxCode3_ID AS SalesTaxCode3_ID,
stj3.JurisdictionDescription AS SalesTaxCode3Description,
sth.SalesTaxRate3 AS SalesTaxRate3,
sth.MaximumTax3Amount AS MaximumTax3Amount,
sth.SalesTaxCode4_ID AS SalesTaxCode4_ID,
stj4.JurisdictionDescription AS SalesTaxCode4Description,
sth.SalesTaxRate4 AS SalesTaxRate4,
sth.MaximumTax4Amount AS MaximumTax4Amount,
sth.SalesTaxCode5_ID AS SalesTaxCode5_ID,
stj5.JurisdictionDescription AS SalesTaxCode5Description,
sth.SalesTaxRate5 AS SalesTaxRate5,
sth.MaximumTax5Amount AS MaximumTax5Amount,
sth.ReleasedCreditAmount AS ReleasedCreditAmount,
sth.CustomerPurchaseOrderNumber AS CustomerPurchaseOrderNumber,
sth.TaxableTransaction_Flag AS TaxableTransaction_Flag,
sth.ShippingMethod_No AS ShippingMethod_No,
smd.MethodDescription AS ShippingMethodDescription,
sth.ShippingMethod_No AS ShippingRoute_ID,
srn.MethodDescription AS ShippingRouteName,
sth.BuyerContact_No AS BuyerContact_No,
sth.BuyerName AS BuyerName,
sth.BuyerEmailAddress AS BuyerEmailAddress,
sth.BuyerCountry AS BuyerCountry,
sth.BuyerPhoneNumber AS BuyerPhoneNumber,
sth.BuyerExtension AS BuyerExtension,
sth.BuyerFaxNumber AS BuyerFaxNumber,
sth.QuotePrintCount AS QuotePrintCount,
sth.SalesOrderPrintCount AS SalesOrderPrintCount,
sth.LastSalesOrderPrintedByUser_ID AS LastSalesOrderPrintedByUser_ID,
sth.LastSalesOrderPrinted_Date AS LastSalesOrderPrinted_Date,
sth.LastSalesOrderPrinted_Time AS LastSalesOrderPrinted_Time,
sth.PickListPrintCount AS PickListPrintCount,
sth.LastPickListPrintedByUser_ID AS LastPickListPrintedByUser_ID,
sth.LastPickListPrinted_Date AS LastPickListPrinted_Date,
sth.LastPickListPrinted_Time AS LastPickListPrinted_Time,
sth.ShipperPrintCount AS ShipperPrintCount,
sth.LastShipperPrintedByUser_ID AS LastShipperPrintedByUser_ID,
sth.LastShipperPrinted_Date AS LastShipperPrinted_Date,
sth.LastShipperPrinted_Time AS LastShipperPrinted_Time,
sth.AcknowledgementPrintCount AS AcknowledgementPrintCount,
sth.LastAcknowledgementPrintedByUser_ID AS
LastAcknowledgementPrintedByUser_ID,
sth.LastAcknowledgementPrinted_Date AS LastAcknowledgementPrinted_Date,
sth.LastAcknowledgementPrinted_Time AS LastAcknowledgementPrinted_Time,
sth.SalesOrderOnCreditHold_Flag AS SalesOrderOnCreditHold_Flag,
sth.CrHoldReasonPastDue_Flag AS CrHoldReasonPastDue_Flag,
sth.CrHoldReasonCrLimit_Flag AS CrHoldReasonCrLimit_Flag,
sth.CrHoldReasonCrCheckExpired_Flag AS CrHoldReasonCrCheckExpired_Flag,
sth.CrHoldReasonCustAlwaysOnHold_Flag AS
CrHoldReasonCustAlwaysOnHold_Flag,
sth.CreditHoldReleaseCount AS CreditHoldReleaseCount,
sth.LastReleasedFromCrHoldByUser_ID AS LastReleasedFromCrHoldByUser_ID,
sth.LastReleasedFromCrHold_Date AS LastReleasedFromCrHold_Date,
sth.LastReleasedFromCrHold_Time AS LastReleasedFromCrHold_Time,
st.ExtendedPrice - st.ExtendedCost AS ProfitAmount,
((st.ExtendedPrice - st.ExtendedCost) / st.ExtendedCost) * 100 AS MarginPercentage,
st.OpenClosed_Type AS OpenClosed_Type,
case
when st.OpenClosed_Type = 'C' then 'CLOSED'
else 'OPEN'
end AS OpenClosedValue,
cat.CategoryGroup1_ID AS CategoryGroup1_ID,
cat1.GroupDescription AS CatgeoryGroup1Description,
cat.CategoryGroup2_ID AS CategoryGroup2_ID,
cat2.GroupDescription AS CatgeoryGroup2Description,
cat.CategoryGroup3_ID AS CategoryGroup3_ID,
cat3.GroupDescription AS CatgeoryGroup3Description,
cat.CategoryGroup4_ID AS CategoryGroup4_ID,
cat4.GroupDescription AS CatgeoryGroup4Description,
cat.CategoryGroup5_ID AS CategoryGroup5_ID,
cat5.GroupDescription AS CatgeoryGroup5Description,
sth.Territory_ID AS Territory_ID,
ter.TerritoryDescription AS TerritoryDescription,
ter.TerritoryGroup1_ID AS TerritoryGroup1_ID,
tg.GroupDescription AS Territory1Description,
ter2.TerritoryGroup2_ID AS TerritoryGroup2_ID,
tg2.GroupDescription AS Territory2Description,
ter3.TerritoryGroup1_ID AS TerritoryGroup3_ID,
tg3.GroupDescription AS Territory3Description,
ter4.TerritoryGroup1_ID AS TerritoryGroup4_ID,
tg4.GroupDescription AS Territory4Description,
ter5.TerritoryGroup1_ID AS TerritoryGroup5_ID,
tg5.GroupDescription AS Territory5Description
from salestransactiondtl st
inner join salestransactionhdr sth
on st.Transaction_No = sth.Transaction_No
inner join category cat
on st.Category_ID = cat.Category_ID
INNER JOIN SteelPlus.dbo.warehousepickinggroup wp
ON st.PickingGroup_ID = wp.PickingGroup_ID
and sth.Warehouse_No = wp.Warehouse_No
LEFT JOIN item i
ON st.Item_ID = i.Item_ID
LEFT JOIN CuttingChargeSchedule p
ON st.CuttingChargeSchedule_ID = p.Schedule_ID
LEFT JOIN customer c
ON sth.Customer_ID = c.Customer_ID
LEFT JOIN customerpaymentterms cpt
ON sth.PaymentTerms_ID = cpt.Terms_ID
LEFT JOIN salestaxjurisdiction stj1
ON sth.SalesTaxCode1_ID = stj1.Jurisdiction_ID
LEFT JOIN salestaxjurisdiction stj2
ON sth.SalesTaxCode2_ID = stj2.Jurisdiction_ID
LEFT JOIN salestaxjurisdiction stj3
ON sth.SalesTaxCode3_ID = stj3.Jurisdiction_ID
LEFT JOIN salestaxjurisdiction stj4
ON sth.SalesTaxCode4_ID = stj4.Jurisdiction_ID
LEFT JOIN salestaxjurisdiction stj5
ON sth.SalesTaxCode5_ID = stj5.Jurisdiction_ID
LEFt JOIN salesrep sr
ON sth.ActualInsideRep_ID = sr.rep_id
LEFt JOIN salesrep sr2
ON sth.AssignedInsideRep_ID = sr.rep_id
LEFT JOIN salesrep sr3
ON sth.AssignedOutsideRep1_ID= sr3.Rep_ID
LEFT JOIN salesrep sr4
ON sth.AssignedOutsideRep2_ID = sr4.Rep_ID
LEFT JOIN branch bn
ON sth.Branch_No = bn.Branch_No
LEFT JOIN warehouse wn
ON sth.Warehouse_No = wn.Warehouse_No
LEFT JOIN shippingmethod smd
ON sth.ShippingMethod_No = smd.ShippingMethod_No
LEFT JOIN shippingMethod srn
ON sth.ShippingMethod_No = srn.Route_ID
LEFT JOIN territory ter
On ter.territory_ID = sth.Territory_ID
LEFT JOIN territorygroup tg
On tg.territorygroup_ID = ter.territorygroup1_ID
And ter.territory_ID = sth.territory_ID
LEFT JOIN territory ter2
On ter.territory_ID = sth.Territory_ID
LEFT JOIN territorygroup tg2
On tg.territorygroup_ID = ter.territorygroup1_ID
And ter.territory_ID = sth.territory_ID
LEFT JOIN territory ter3
On ter.territory_ID = sth.Territory_ID
LEFT JOIN territorygroup tg3
On tg.territorygroup_ID = ter.territorygroup1_ID
And ter.territory_ID = sth.territory_ID
LEFT JOIN territory ter4
On ter.territory_ID = sth.Territory_ID
LEFT JOIN territorygroup tg4
On tg.territorygroup_ID = ter.territorygroup1_ID
And ter.territory_ID = sth.territory_ID
LEFT JOIN territory ter5
On ter.territory_ID = sth.Territory_ID
LEFT JOIN territorygroup tg5
On tg.territorygroup_ID = ter.territorygroup1_ID
And ter.territory_ID = sth.territory_ID
LEFT JOIN categorygroup cat1
on cat.CategoryGroup1_ID = st.Category_ID
LEFT JOIN categorygroup cat2
on cat.CategoryGroup2_ID = st.Category_ID
LEFT JOIN categorygroup cat3
on cat.CategoryGroup3_ID = st.Category_ID
LEFT JOIN categorygroup cat4
on cat.CategoryGroup4_ID = st.Category_ID
LEFT JOIN categorygroup cat5
on cat.CategoryGroup5_ID = st.Category_ID
where sth.Transaction_Type in ('S','C');
March 6, 2012 at 1:58 pm
Well I got this working, but it looks like the data isn't correct [Sad]
the first 1000 rows are all the same...
Any ideas?
Here is the view, same as yours Jared, just with the last part modified also.
any help would be appreciated, I was hoping to get this completed today
Pretty sparse on the details. What does that mean the "data isn't correct"??? Wrong row counts, incorrect values, etc....
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply