Counting in Multiple Queries

[IBSkippy]

I'm relatively new to BO, so I have a question concerning truncating fields and linking queries.&nbsp;&nbsp;I have a field (we'll call it F1) that comes in as four possible formats: 123456 78, 123456-78, 78-123456, C123456.&nbsp;&nbsp;I also have a field (F2) that is of the form 123456.&nbsp;&nbsp;I am attempting to link these two fields together so I can use the Count function on it.&nbsp;&nbsp;<br><br>The basic idea is to count the number of records that appear in both queries.&nbsp;&nbsp;I have been able to truncate F1 into a format matching F2, but I can't link them together.&nbsp;&nbsp;Any suggestions?&nbsp;&nbsp;<br><br>Thanks in advance!!

 

[yaffle]

How about using a sub-query to return all your truncated F1 values, than have the main query select the count of F2 values in the list provided by the sub-query.

 

[IBSkippy]

The only problem with doing a subquery is performing the truncation in SQL.&nbsp;&nbsp;I haven't found a way to account for all four possible formats except on the report level with a nested &quot;if&quot; statement.&nbsp;&nbsp;Any suggestions on how to recognize and appropriately manipulate the data in the SQL statement?<br><br>Thanks for your help!

 

[jnicho02]

You could code your join as a single statement.....<br><FONT FACE=monospace>SUBSTRING(F1,charindex(F1,F2),len(F2)) = F2</font><br><br>...E.g, this would do<br>substring(&quot;123456 78&quot;,1,6) = &quot;123456&quot;<br>substring(&quot;123456-78&quot;,1,6) = &quot;123456&quot;<br>substring(&quot;78-123456&quot;,4,6) = &quot;123456&quot;<br>substring(&quot;C123456&quot;,2,6) = &quot;123456&quot;<br><br>is &quot;X123456X&quot; a valid match? or does the 123456 have to be at the start or end of the field. If so then the query will be different.<br> <p> <br><a href=mailto: > </a><br><a href=

http://www.geocities.com/jnicho02/>my

home</a><br>

 

[IBSkippy]

X123456X is a valid match, but it would count as C123456.&nbsp;&nbsp;Thank you for the tip.&nbsp;&nbsp;This will probably work, if I can allow 123456 be any 6 digit number.&nbsp;&nbsp;Any tips for how to do pattern matching in SQL for Oracle?&nbsp;&nbsp;I've attempted stuff like [0-9][0-9][0-9][0-9][0-9][0-9][0-9], but to no avail (it works for Informix....).&nbsp;&nbsp;&nbsp;I need 123456 to be any number in the range from 000000 to 999999, but it will be in character format instead of numeric.<br><br>Thanks again for all help!!!