Error converting varchar to Float

  • 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!

  • It is the RETURN. You are returning a VARCHAR when the value is a FLOAT.

    Jared
    CE - Microsoft

  • 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

  • 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!

  • 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

  • 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/

  • 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

  • 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!

  • 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/

  • 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

  • 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!

  • 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

  • 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/

  • 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');

  • 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