topic Re: Quick SQL Question in ODS and Base Reporting

Quick SQL Question

OS2Rules — Tue, 13 Nov 2012 19:41:33 GMT

Hi All:

A quick question about how to code an SQL:

I have a prefix value (a data set name) in one table, and a fully qualified name (including the member name and extension) in a second table.

Is there a way to code a SQL to match these? I thought to use something like:

where a.prefix =: b.dsn

but this will not work.

I am not concerned whether the fully qualified name contains multiple sub directories, as long as the prefix matches.

Thanks in advance.

LarryWorley — Tue, 13 Nov 2012 19:47:36 GMT

Try like operator with multi-character wild card "%'

where b.dsn like trim(a.prefix)||'%'

Haikuo — Tue, 13 Nov 2012 19:49:27 GMT

colon operator is not working in Proc SQL, you probably need to use scan():

where a.prefix=scan(b.dsn,1)

Haikuo

Astounding — Tue, 13 Nov 2012 20:04:33 GMT

I suspect LarryWorley hit the nail on the head, but just in case this should be worth a shot:

where trim(a.prefix) = substr(b.dsn, 1, length(a.prefix))