PDA

View Full Version : VBA code just need one line...?



Barendrecht8
02-28-2017, 07:41 AM
Hi guys,

Grtz from the Netherlands.

I have a questions regarding a select case issue.


SELECT (CASE WHEN ep.PropName = 'Class_01' THEN 'Assortiment 1 (Selectiecode)'
WHEN ep.PropName = 'Class_02' THEN 'Assortiment 2 (KAM Artikel)'
WHEN ep.PropName = 'Class_03' THEN 'Assortiment 3 (Productieprint)'
WHEN ep.PropName = 'Class_04' THEN 'Assortiment 4 (Voorraadgestuurd)'
WHEN ep.PropName = 'Class_05' THEN 'Assortiment 5 (Balans)'
WHEN ep.PropName = 'Class_06' THEN 'Assortiment 6 (Produktieorders)'
WHEN ep.PropName = 'Class_07' THEN 'Assortiment 7 (Assortment 7)'
WHEN ep.PropName = 'Class_08' THEN 'Assortiment 8 (Assortment 8)'
WHEN ep.PropName = 'Class_09' THEN 'Assortiment 9 (Assortment 9)'
WHEN ep.PropName = 'Class_10' THEN 'Assortiment 10 (MRP)'
WHEN ep.PropName = 'ConfigurationClass' THEN 'Configuratieklasse'
WHEN ep.PropName = 'NetWeight' THEN '(nto: niet van toepassing)'
ELSE ep.PropName END) AS FieldName, (CASE WHEN ep.CaptionTermID = 4138 THEN 1102
WHEN ep.CaptionTermID = 222 THEN 1162
ELSE ep.CaptionTermID
END) AS TermId, (CASE
WHEN ep.propname IN ('IsAssembled','IsBackToBackOrder','IsBatchItem','IsDiscount','IsExplode',' IsOutsourcedItem') THEN (CASE ed.oldvalue WHEN 1 THEN 'True' ELSE 'False' END)
WHEN ep.propname = 'CommissionMethod' THEN (CASE WHEN ed.oldvalue = 'F' THEN 'Vast bedrag' WHEN ed.oldvalue = 'M' THEN 'Marge %' WHEN ed.oldvalue = 'S' THEN 'Verkoopprijs' ELSE ed.oldvalue END)
WHEN ep.propname = 'Condition' THEN (CASE WHEN ed.oldvalue = 'A' THEN 'Actief' WHEN ed.oldvalue = 'B' THEN 'Geblokkeerd' WHEN ed.oldvalue = 'D' THEN 'Vervallen' WHEN ed.oldvalue = 'F' THEN 'Toekomstig' WHEN ed.oldvalue = 'E' THEN 'Non-actief' ELSE ed.oldvalue END)
WHEN ep.propname = 'ItemType' THEN (CASE WHEN ed.oldvalue = 'V' THEN 'Verkoop' WHEN ed.oldvalue = 'I' THEN 'Intern' WHEN ed.oldvalue = 'T' THEN 'Tekst' WHEN ed.oldvalue = 'N' THEN 'Vervallen' ELSE ed.oldvalue END)
WHEN ep.propname = 'OrderPolicyCode' THEN (CASE WHEN ed.oldvalue = 'L' THEN 'Klantordergestuurd' WHEN ed.oldvalue = 'F' THEN 'Vast' WHEN ed.oldvalue = 'P' THEN 'Periodiek' WHEN ed.oldvalue = 'R' THEN 'Bestelniveau' ELSE ed.oldvalue END)
WHEN ep.propname = 'status' THEN (CASE WHEN ed.oldvalue = 'P' THEN 'Gepland' WHEN ed.oldvalue = 'D' THEN 'Development' WHEN ed.oldvalue = 'I' THEN 'Piot' WHEN ed.oldvalue = 'C' THEN 'Commercially available' WHEN ed.oldvalue = 'W' THEN 'Withdraw' ELSE ed.oldvalue END)
WHEN ep.propname = 'Type' THEN (CASE WHEN ed.oldvalue = 'S' THEN 'Standaard' WHEN ed.oldvalue = 'P' THEN 'Phantom' WHEN ed.oldvalue = 'B' THEN 'Bulkuitgifte' WHEN ed.oldvalue = 'L' THEN 'Arbeidsuur' WHEN ed.oldvalue = 'M' THEN 'Machine-uur' WHEN ed.oldvalue = 'C' THEN 'Contract' WHEN ed.oldvalue = 'R' THEN 'Redencodes' ELSE ed.oldvalue END)
WHEN ep.propname = 'ValuationMethod' THEN (CASE WHEN ed.oldvalue = 'V' THEN 'Vaste verrekenprijs' WHEN ed.oldvalue = 'C' THEN 'Vaste verrekenprijs (Classic)' WHEN ed.oldvalue = 'G' THEN 'Gemiddelde inkoopprijs' WHEN ed.oldvalue = 'F' THEN 'FIFO' WHEN ed.oldvalue = 'L' THEN 'LIFO' WHEN ed.oldvalue = 'T' THEN 'Werkelijke kostprijsberekening' WHEN ed.oldvalue = 'A' THEN 'Bijgewerkte GIP' ELSE ed.oldvalue END)
ELSE ed.oldvalue
END) AS OValue, (CASE
WHEN ep.propname IN ('IsAssembled','IsBackToBackOrder','IsBatchItem','IsDiscount','IsExplode',' IsOutsourcedItem') THEN (CASE ed.newvalue WHEN 1 THEN 'True' ELSE 'False' END)
WHEN ep.propname = 'CommissionMethod' THEN (CASE WHEN ed.newvalue = 'F' THEN 'Vast bedrag' WHEN ed.newvalue = 'M' THEN 'Marge %' WHEN ed.newvalue = 'S' THEN 'Verkoopprijs' ELSE ed.newvalue END)
WHEN ep.propname = 'Condition' THEN (CASE WHEN ed.newvalue = 'A' THEN 'Actief' WHEN ed.newvalue = 'B' THEN 'Geblokkeerd' WHEN ed.newvalue = 'D' THEN 'Vervallen' WHEN ed.newvalue = 'F' THEN 'Toekomstig' WHEN ed.newvalue = 'E' THEN 'Non-actief' ELSE ed.newvalue END)
WHEN ep.propname = 'ItemType' THEN (CASE WHEN ed.newvalue = 'V' THEN 'Verkoop' WHEN ed.newvalue = 'I' THEN 'Intern' WHEN ed.newvalue = 'T' THEN 'Tekst' WHEN ed.newvalue = 'N' THEN 'Vervallen' ELSE ed.newvalue END)
WHEN ep.propname = 'OrderPolicyCode' THEN (CASE WHEN ed.newvalue = 'L' THEN 'Klantordergestuurd' WHEN ed.newvalue = 'F' THEN 'Vast' WHEN ed.newvalue = 'P' THEN 'Periodiek' WHEN ed.newvalue = 'R' THEN 'Bestelniveau' ELSE ed.newvalue END)
WHEN ep.propname = 'status' THEN (CASE WHEN ed.newvalue = 'P' THEN 'Gepland' WHEN ed.newvalue = 'D' THEN 'Development' WHEN ed.newvalue = 'I' THEN 'Piot' WHEN ed.newvalue = 'C' THEN 'Commercially available' WHEN ed.newvalue = 'W' THEN 'Withdraw' ELSE ed.newvalue END)
WHEN ep.propname = 'Type' THEN (CASE WHEN ed.newvalue = 'S' THEN 'Standaard' WHEN ed.newvalue = 'P' THEN 'Phantom' WHEN ed.newvalue = 'B' THEN 'Bulkuitgifte' WHEN ed.newvalue = 'L' THEN 'Arbeidsuur' WHEN ed.newvalue = 'M' THEN 'Machine-uur' WHEN ed.newvalue = 'C' THEN 'Contract' WHEN ed.newvalue = 'R' THEN 'Redencodes' ELSE ed.newvalue END)
WHEN ep.propname = 'ValuationMethod' THEN (CASE WHEN ed.newvalue = 'V' THEN 'Vaste verrekenprijs' WHEN ed.newvalue = 'C' THEN 'Vaste verrekenprijs (Classic)' WHEN ed.newvalue = 'G' THEN 'Gemiddelde inkoopprijs' WHEN ed.newvalue = 'F' THEN 'FIFO' WHEN ed.newvalue = 'L' THEN 'LIFO' WHEN ed.newvalue = 'T' THEN 'Werkelijke kostprijsberekening' WHEN ed.newvalue = 'A' THEN 'Bijgewerkte GIP' ELSE ed.newvalue END)
ELSE ed.newvalue
END) AS NValue, h.fullname AS FullName, ed.created AS Date, Comment FROM ebcdatalog ed
INNER JOIN ebcprops ep ON ed.property = ep.propid
LEFT OUTER JOIN humres h ON ed.creator = h.res_id
LEFT OUTER JOIN ItemAccounts i ON ed.DataKey = CAST(i.ID AS varchar(36))
WHERE ((ed.datakey = 'I4433.DTT.010' AND (ed.component = 0xD2C366F6262D2C4A96B808DC0B1FD785)) OR (i.ItemCode = 'I4433.DTT.010' AND ed.component = 0x7C6B3262291F9C468A3106F0CD71F9B1)) ORDER BY ed.created DESC, ep.PropName


This code provides me this document in Excel:

18515


I just want to see the lines with column name: TermId : 5120

Is this possible??

JBeaucaire
02-28-2017, 08:26 AM
Do you need to change the query? Can you not simply filter the results?


Rows(1).AutoFilter 2, 5120

Barendrecht8
02-28-2017, 08:38 AM
Do you need to change the query? Can you not simply filter the results?


Rows(1).AutoFilter 2, 5120



Hi Thanks!

I really do not now how to do this? Where should I ad this filter?

JBeaucaire
02-28-2017, 08:49 AM
You only shared part of your macro, where the query was being formed. The snippet I offered would go at the end of the macro, where you are viewing the results in the worksheet.

Aflatoon
03-01-2017, 04:13 AM
If you only want TermId 5120, you can replace this part:
(CASE WHEN ep.CaptionTermID = 4138 THEN 1102
WHEN ep.CaptionTermID = 222 THEN 1162
ELSE ep.CaptionTermID
END) AS TermId,


with just this:

ep.CaptionTermID As TermId

and then add a section to the WHERE clause at the end that just has (ep.CaptionTermID = 5120)