March 25, 2010 at 11:24 am
1st post here so be gentle...
I have the following case statement:
MAX(CASE WHEN OptionView.Finish LIKE 'CC%' THEN 'Y' END) AS "CustomFinish"
This statement is working perfectly, however what I really need it to do is return a 'Y' for all records if ANY value in the returned record set is LIKE 'CC%'. The sample data I am working with has a NULL value in the OptionView.Finish field for the last record, but since this particular data set has at least one record in it that meets the 'CC%' requirement, i'd like for it to return a 'Y' in this case.
Any help?
March 25, 2010 at 12:13 pm
From what you've described, your code should still return a 'Y'. See this example:
DECLARE @Table TABLE (Col1 varchar(10))
INSERT INTO @Table
SELECT 'CC' UNION ALL
SELECT 'CC' UNION ALL
SELECT NULL
SELECT MAX(CASE WHEN Col1 LIKE 'CC%' THEN 'Y' END) as 'CustomFinish'
FROM @Table
Can you provide sample data, in the format I've shown above, that demonstrates the problem that you are having?
March 25, 2010 at 12:47 pm
Thanks for the prompt reply John-
Perhaps I wasn't as clear as I should have been. The MAX() portion of my original post is simply to avoid needing to have Optionview.Finish in the Group By clause later on in the query so it can be ignored for purposes of this example. Here is the entire query (which I was hesitant to post in the first place since I'm sure there are clerical errors and generally poor practice all over it :-D):
SELECT
Orders.ordID AS "ordID"
,dbo.fnREP_getOEName_SRI(Orders.ordCreatedby) AS "OE"
,dbo.fnREP_getMaterialHoldStatus_SRI(Orders.ordID) AS "MaterialHold"
,Orders.ordOrderNo AS "OrderNo"
,Orders.ordInfoField1 AS "InfoField1"
,ordtyp.otpCode as "OrderType"
,OrderLines.olnID AS "OrderLineID"
,OrderLines.olnLineNo AS "LineNo"
,OrderLines.olnOutLineNo AS "OutLineNo"
,MAX(CONVERT(VarChar (500), Orders.ordNotes)) AS "OrderNotes"
,Orderlinesaitems.olnsquantity AS "Qty"
,CASE SAItems.pdlID WHEN 40 THEN OrderLines.olnShortDesc
WHEN 41 THEN OrderLines.olnShortDesc
WHEN 42 THEN 'Mouldings'
ELSE OrderLines.olnShortDesc END AS "ProductName"
,SAItems.pdlID AS "ProductLineID"
,SAItems.saiDescription AS "ProductLineDesc"
,Orders.ordOrderDate AS "OrderDate"
,Orders.ordCustRequestDate AS "RequestDate"
,Orders.ordPONumber AS "PONumber"
,Orders.ordDescription AS "JobName"
,Orders.sttID AS "OrderStatus"
,Status.sttValue AS "StatusValue"
,CAST (DATEPART (MONTH,Orders.ordSchedShipDate) AS varchar(2)) + '/' + CAST (DATEPART (DAY,Orders.ordSchedShipDate) AS varchar(2)) + '/' + CAST (DATEPART (YEAR,Orders.ordSchedShipDate) AS varchar(4)) AS "ScheduledShipDate"
--,OptionView.Finish as "FINISH"
--,MAX (CASE WHEN OptionView.Finish LIKE 'CC%' THEN 'Y' END) AS "CustomFinish" --Added by ASJ 3/24/10 to allow for tentative ship date note
,TaxRate.taxtaxrate AS "TaxRate"
,TaxRate.taxState AS "StateTax"
,pay.payDescription AS "PaymentTerms"
,VendorCustomer.venCode AS "CustomerCode"
,VendorCustomer.venCompanyName AS "CustomerName"
,MAX (CASE addresstype.atcAddresstypeCode WHEN 'Ship' THEN Address.addLocationName ELSE '' END) AS "ShipToName"
,MAX (CASE addresstype.atcAddresstypeCode WHEN 'Ship' THEN Address.addAddress1 ELSE '' END) AS "ShipToAddress1"
,MAX (CASE addresstype.atcAddresstypeCode WHEN 'Ship' THEN Address.addAddress2 ELSE '' END) AS "ShipToAddress2"
,MAX (CASE addresstype.atcAddresstypeCode WHEN 'Ship' THEN Address.addcity ELSE '' END) AS "ShipToCity"
,MAX (CASE addresstype.atcAddresstypeCode WHEN 'Ship' THEN Address.addState ELSE '' END) AS "ShipToState"
,MAX (CASE addresstype.atcAddresstypeCode WHEN 'Ship' THEN Address.addzip ELSE '' END) AS "ShipToZip"
,MAX (CASE addresstype.atcAddresstypeCode WHEN 'Ship' THEN Address.shpzCode ELSE '' END) AS "ShipToZone"
,MAX (CASE addresstype.atcAddresstypeCode WHEN 'BILL' THEN Address.addLocationName ELSE '' END) AS "BillToName"
,MAX (CASE addresstype.atcAddresstypeCode WHEN 'BILL' THEN Address.addAddress1 ELSE '' END) AS "BillToAddress1"
,MAX (CASE addresstype.atcAddresstypeCode WHEN 'BILL' THEN Address.addAddress2 ELSE '' END) AS "BillToAddress2"
,MAX (CASE addresstype.atcAddresstypeCode WHEN 'Bill' THEN Address.addcity ELSE '' END) AS "BillToCity"
,MAX (CASE addresstype.atcAddresstypeCode WHEN 'Bill' THEN Address.addState ELSE '' END) AS "BillToState"
,MAX (CASE addresstype.atcAddresstypeCode WHEN 'Bill' THEN Address.addzip ELSE '' END) AS "BillToZip"
,MAX (CASE addresstype.atcAddresstypeCode WHEN 'Bill' THEN address.addPhone ELSE '' END) AS "BillToPhone"
,MAX (CASE addresstype.atcAddresstypeCode WHEN 'Bill' THEN Address.addFax ELSE '' END) AS "BillToFax"
,MAX(olnpSequence) AS "MaxPriceSequenceID"
,MAX(CASE atb.atbCode WHEN 'Customer Job Note' THEN ordav.ordavValue END) AS "CustomerJobNote"
,MAX(CASE atb.atbCode WHEN 'Invoice Date' THEN CONVERT(varchar(10),ordav.ordavValue,101) END) AS "InvoiceDate"
,MAX(CASE atb.atbCode WHEN 'Remake Reason' THEN ordav.ordavValue END) AS "RemakeReason"
,MAX(CASE gps.gpsCode WHEN 'Auto Cust Job Note' THEN gds.gdsDescription END) AS "AutoCustomerJobNote"
,MAX(CASE venatb.atbCode WHEN 'Order Entry Notes' THEN venav.venavValue END) AS "OrderEntryNotes"
,MAX(CASE venatb.atbCode WHEN 'Shipping Instructions' THEN venav.venavValue END) AS "ShippingInstructions"
,MAX(CASE venatb.atbCode WHEN 'Ack Email To' THEN ISNULL(venav.venavValue,' ') END) AS "AckEmailTo"
,MAX(CASE venatb.atbCode WHEN 'Confirmation Email To' THEN ISNULL(venav.venavValue,' ') END) AS "ConfirmationEmailTo"
,MAX(CASE venatb.atbCode WHEN 'Invoice Email To' THEN ISNULL(venav.venavValue,' ') END) AS "InvoiceEmailTo"
,MAX(CASE venatb.atbCode WHEN 'Do Not Need Ack' THEN ISNULL(venav.venavValue,' ') END) AS "DoNotNeedAck"
,MAX(CASE venatb.atbCode WHEN 'Call Before Fax' THEN ISNULL(venav.venavValue,' ') END) AS "CallBeforeFax"
FROM
dbo.Orders
JOIN dbo.OrderTypes AS ordtyp ON ordtyp.otpID = orders.otpID
LEFT JOIN dbo.OrderAddresses ON Orders.ordID = OrderAddresses.ordID
LEFT JOIN dbo.Address ON OrderAddresses.addID = Address.addID
LEFT JOIN dbo.AddressType ON Address.atcID = AddressType.atcID
LEFT JOIN dbo.VendorCustomer ON Orders.venID = VendorCustomer.venID AND Address.venID = VendorCustomer.venID
LEFT JOIN dbo.TaxRate ON Orders.taxID = TaxRate.taxID
LEFT JOIN dbo.PaymentTerms AS pay ON Orders.payID = pay.payID
LEFT JOIN dbo.OrderLines ON Orders.ordID = OrderLines.ordID AND dbo.OrderLines.ltpID =3
LEFT JOIN dbo.OrderLineSAItems ON OrderLines.olnID = OrderLineSAItems.olnID
LEFT JOIN dbo.SAItems ON OrderLineSAItems.SAIID = SAItems.SAIID
LEFT JOIN dbo.SAItemGroupDetails AS saigds ON saigds.saiID = SAItems.saiID
LEFT JOIN dbo.GroupDetails AS gds ON gds.gdsID = saigds.gdsID
LEFT JOIN dbo.Groups AS gps ON gps.gpsID = gds.gpsID
LEFT JOIN dbo.OrderLineOptions ON OrderLines.olnID = OrderLineOptions.olnID
LEFT JOIN dbo.vwOrderLineOptions_SRI OptionView ON orderlines.olnID = OptionView.olnID
LEFT JOIN dbo.OrderLinePricing ON OrderLines.olnID = OrderLinePricing.olnID
LEFT JOIN dbo.OrderAttributeValues AS ordav ON orders.ordID = ordav.ordid
LEFT JOIN dbo.Attributes AS atb ON ordav.atbiD = atb.atbID
LEFT JOIN dbo.VendorCustomerAttributeValues AS venav ON VendorCustomer.venID = venav.venID
LEFT JOIN dbo.Attributes AS venatb ON venav.atbID = venatb.atbID
JOIN dbo.Status on orders.sttID = Status.sttID
WHERE
orders.ordID = @ordID
AND dbo.OrderLines.ltpID =3
GROUP BY
Orders.ordOrderNo
,Orders.ordCreatedby
,ordtyp.otpCode
,OrderLines.olnID
,OrderLines.olnLineNo
,OrderLines.olnShortDesc
,Orders.ordOrderDate
,Orders.ordCustRequestDate
,Orders.ordPONumber
,Orders.ordDescription
,Orders.ordInfoField1
,Orders.sttID
,Orders.ordSchedShipDate
,SAItems.pdlID
,SAItems.saiDescription
,VendorCustomer.venCode
,VendorCustomer.venCompanyName
,Orderlinesaitems.olnsquantity
,TaxRate.taxTaxRate
,TaxRate.taxState
,pay.payDescription
,OrderLines.olnOutLineNo
,orders.ordID
,Status.sttValue
,OptionView.Finish
I have commented out the lines in the SELECT clause that are involved with this particular problem so you can pick them out easier.
Here is a sample result set (with non-important columns removed for privacy purposes):
ordID LineNoFINISH CustomFinish
402012CC-SS-080-V20 Y
402013CC-SS-080-V20 Y
402014CC-SS-080-V20 Y
402015CC-SS-080-V20 Y
402016CC-SS-080-V20 Y
402017CC-SS-080-V20 Y
402018CC-SS-080-V20 Y
402019CC-SS-080-V20 Y
4020110NULL NULL
And here is what I would like the results to look like:
ordID LineNoFINISH CustomFinish
402012CC-SS-080-V20 Y
402013CC-SS-080-V20 Y
402014CC-SS-080-V20 Y
402015CC-SS-080-V20 Y
402016CC-SS-080-V20 Y
402017CC-SS-080-V20 Y
402018CC-SS-080-V20 Y
402019CC-SS-080-V20 Y
4020110NULL Y
Sorry for the poor formatting, but hopefully you get the idea.
March 25, 2010 at 1:02 pm
I didn't try to retrofit this into your query, but this example should show you one way to get the results you are looking for. Basically, this allows you to display the same CustomFinish value for all items in the ordID group.
DECLARE @Table TABLE (ordID int, [LineNo] int, FINISH varchar(25), CustomFinish char(1))
INSERT INTO @Table
SELECT 40201, 2, 'CC-SS-080-V20', 'Y' UNION ALL
SELECT 40201, 3, 'CC-SS-080-V20', 'Y' UNION ALL
SELECT 40201, 4, 'CC-SS-080-V20', 'Y' UNION ALL
SELECT 40201, 5, 'CC-SS-080-V20', 'Y' UNION ALL
SELECT 40201, 6, 'CC-SS-080-V20', 'Y' UNION ALL
SELECT 40201, 7, 'CC-SS-080-V20', 'Y' UNION ALL
SELECT 40201, 8, 'CC-SS-080-V20', 'Y' UNION ALL
SELECT 40201, 9, 'CC-SS-080-V20', 'Y' UNION ALL
SELECT 40201, 10, NULL, NULL
SELECT ordID,
[LineNo],
Finish,
CustomFinish = (SELECT MAX(CustomFinish) FROM @Table WHERE ordID = t.OrdID)
FROM @Table t
March 29, 2010 at 8:35 am
Thanks John,
I think that will work!
March 29, 2010 at 2:52 pm
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply