Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi all,
i have a qvd and in script i need to write a where clause on 1 column with multiple selections. what is the shortest way to write:
load * from test.qvd(qvd)
where
country in ('AU','GB','US','CA')
and currency not in ('USD','EUR','GBP')
please advise the right syntax for the above where clauses
thanks in advance
LOAD *
FROM test.qvd (qvd)
where
(country = 'AU' or country = 'GB' or country ='US' or country ='CA') and
(currency <> 'USD' and currency <>'EUR' and currency <>'GBP')
hope it helps
thanks Alex
but this answer i know but its a long cut.
is there a shortcut code as the list of countries i have is 25 and then teh where cluase really becomes messy
try rhis
load * from test.qvd(qvd)
where match(country,'AU','GB','US','CA') and not match(currency ,'USD','EUR','GBP');
UNfortunately the "in" clause doesn't work ... you can use a workaround that is add two fields to use as flags so for example, on the same row of counties or currencies you need set 1 in the new fields.
(this can be done even with a new table to join)
in this way the final where clause only check new fields values 1 or 0