Hi,
I am writing a query to return all Provinces from an address field. Our IT department prefer I do not use Parsname. so i am doing it this using a wildcard.
I am using the wildcard LIKE '% ON %' and T0.OINV and T1.INV12
My problem for instance is for example " ON ," sometimes it has " ON," or and for some reason it is not picking up some where it is " ON "
any ideas how better to use a wildcard in this scenerio ?
I am using the following:
SELECT
t0.docnum,
'invoice' as doctype
,T0.[DocDate] as 'Date'
,t2.itemcode as 'item No.'
,t2.dscription as 'Item Description'
,t0.cardcode as 'Customer Code'
,t0.cardname as 'Customer Name'
,T1.[StateS] as 'Ship-To Original Province',
CASE
when t1.States is null and T0.ADDRESS2 LIKE '% AB %' THEN 'AB'
when t1.States is null and T0.ADDRESS2 LIKE '% BC %' THEN 'BC'
when t1.States is null and T0.ADDRESS2 LIKE '% MB %' THEN 'MB'
when t1.States is null and T0.ADDRESS2 LIKE '% NB %' THEN 'NB'
when t1.States is null and T0.ADDRESS2 LIKE '% NL %' THEN 'NL'
when t1.States is null and T0.ADDRESS2 LIKE '% NT %' THEN 'NT'
when t1.States is null and T0.ADDRESS2 LIKE '% NS %' THEN 'NS'
when t1.States is null and T0.ADDRESS2 LIKE '% NU %' THEN 'NU'
when t1.States is null and T0.ADDRESS2 LIKE '% ON %' THEN 'ON' etc, etc, etc
ELSe T1.[StateS]
END AS 'Ship to Province'
--rest of my query......
I am writing a query to return all Provinces from an address field. Our IT department prefer I do not use Parsname. so i am doing it this using a wildcard.
I am using the wildcard LIKE '% ON %' and T0.OINV and T1.INV12
My problem for instance is for example " ON ," sometimes it has " ON," or and for some reason it is not picking up some where it is " ON "
any ideas how better to use a wildcard in this scenerio ?
I am using the following:
SELECT
t0.docnum,
'invoice' as doctype
,T0.[DocDate] as 'Date'
,t2.itemcode as 'item No.'
,t2.dscription as 'Item Description'
,t0.cardcode as 'Customer Code'
,t0.cardname as 'Customer Name'
,T1.[StateS] as 'Ship-To Original Province',
CASE
when t1.States is null and T0.ADDRESS2 LIKE '% AB %' THEN 'AB'
when t1.States is null and T0.ADDRESS2 LIKE '% BC %' THEN 'BC'
when t1.States is null and T0.ADDRESS2 LIKE '% MB %' THEN 'MB'
when t1.States is null and T0.ADDRESS2 LIKE '% NB %' THEN 'NB'
when t1.States is null and T0.ADDRESS2 LIKE '% NL %' THEN 'NL'
when t1.States is null and T0.ADDRESS2 LIKE '% NT %' THEN 'NT'
when t1.States is null and T0.ADDRESS2 LIKE '% NS %' THEN 'NS'
when t1.States is null and T0.ADDRESS2 LIKE '% NU %' THEN 'NU'
when t1.States is null and T0.ADDRESS2 LIKE '% ON %' THEN 'ON' etc, etc, etc
ELSe T1.[StateS]
END AS 'Ship to Province'
--rest of my query......