declare alternative value for specific lookup values Microsoft SQL

Question

kind of an odd question, is there a way to lookup a list of values and for the ones that don't exist use an alias and do a lookup on that instead.

matching codes/alias table (so say this is from a table and these are the matching values below)

itemcode	alternatecode
abcd	1200
xxxx	1800
bark	2000
home	2100
sled	5000
tech	7500
bell	6350

so say if this is the data below

itemcode	price
abcd	9.75
xxxx	10.00
bark	11.05
sled	17.12
tech	21.00
2100	15.50
6350	12.75

if I have a query where I only refer to the itemcode in the main column so say like

   SELECT itemcode, price 
   FROM pricing
   WHERE itemcode in ('abcd','xxxx','bark','home','sled','tech','bell')

I would need the outcome to look for the alias and return the main code with the price, outcome below

itemcode	price
abcd	9.75
xxxx	10.00
bark	11.05
sled	17.12
tech	21.00
home	15.50
bell	12.75

is there a way to declare matching ones before hand at the top?

thanks!

and how would I incorporate it into this query below if possible

DECLARE @DefaultCustomerLevel INT = 5
SELECT
I.item_or_pricecode,
COALESCE(PLookup.[uom_code], P.[uom_code]) AS [uom_code],
ISNULL(P.[price], 0) AS [price],
COALESCE(PLookup.[start_date], P.[start_date]) AS [start_date],
COALESCE(PLookup.[price], P.[price]) AS LevelResult
FROM 
(VALUES ('22rgr45dhs'),('35rg4pcpab'),('37rgtf08ab'),('37RGDF10BS'), 
('27MIETE11K')) I([item_or_pricecode])
outer apply
(
select top 1 p.* from
[P2S_DMSI].[Agility].[pricing] P where P.[start_cust_or_group] = 
 '7001364'
                                 AND [cust_shipto_num] = '2'  
                                 AND P.[item_or_pricecode] = I. 
 [item_or_pricecode]
                                 
order by
CASE 
WHEN p.[tran_type] = 'Quot' THEN 1
WHEN p.[default_level]>0    THEN 2
WHEN p.[default_level] = 0 and [tran_type] = '' THEN 3
END, p.start_date desc

) p
OUTER APPLY 
(SELECT TOP 1 * 
 FROM [P2S_DMSI].[Agility].[pricing] P1 
 WHERE ISNULL(P.[tran_type], '') = '' 
   AND P1.[start_cust_or_group] = '' 
   AND P1.[item_or_pricecode] = I.[item_or_pricecode]  
   AND P1.[level_] = COALESCE(NULLIF(P.[default_level], 0),  
 @DefaultCustomerLevel)
 ORDER BY P1.start_date DESC) PLookup;

Answer 1

Start by left joining with the alias table to get the missing item codes; we use a left join because we want the result to contain every row in pricing even if it has no match. Match the itemcode with the alternate.

SELECT 
  *
FROM pricing p
LEFT JOIN alternate_codes ac
  ON ac.alternate_code = p.itemcode

Now we have every row of pricing matched to its alternate.

Now to choose the correct itemcode. If there's an alternate_codes.itemcode defined we should use that, else we use pricing.itemcode. Do this with coalesce to choose the first non-null column.

SELECT
 coalesce(ac.itemcode, p.itemcode) as itemcode,
 price 
FROM pricing p
LEFT JOIN alternate_codes ac
  ON ac.alternate_code = p.itemcode
WHERE coalesce(ac.itemcode, p.itemcode) in ('abcd','xxxx','bark','home','sled','tech','bell')

---

is there a way to declare them at the top like abcd = 1200?

Some databases have variables, but PostgreSQL does not. Even if your database does not variables, I recommend avoiding them. They're non-standard and it's good to learn standard techniques which are applicable to all databases.

In standard SQL you declare data with a values, the table value constructor. For example, values ('abcd', '1200'), ('beef', '4321'). There's several ways to incorporate values into your query.

A temp table is probably simplest. This is a table which exists only for your session. Create a temporary table, insert your data, and use the table as normal.

create temporary table alternate_codes (
  itemcode text not null,
  alternatecode text not null
);

insert into alternate_codes values ('abcd', '1200'), ('beef', '4321');

Then use the table normally. This is probably best if you're going to use the data multiple times in one query.

A Common Table Expression (CTE) or with clause is another good option. This acts like a temp table just for your query.

with alternate_codes(itemcode, alternatecode) as (
  values 
    ('abcd', '1200'), 
    ('beef', '4321')
)
SELECT
 coalesce(ac.itemcode, p.itemcode) as itemcode,
 price 
FROM pricing p
LEFT JOIN alternate_codes ac
  ON ac.alternate_code = p.itemcode
WHERE coalesce(ac.itemcode, p.itemcode) in ('abcd','xxxx','bark','home','sled','tech','bell')

This is most useful for just one query.

Because these codes are necessary to query the data, better would be to add a table and insert these values so everyone can use them. Even better would be to turn your item code fixing query into a view so you can treat the whole query as a table. This will greatly simplify your queries.