Recent content by jgerstb

From the SQL reference - Volume 5 The following example illustrates the use of ESCAPE: To look for the pattern ‘95%’ in a string such as ‘Result is 95% effective’, if Result is the field to be checked, use: WHERE Result LIKE ‘%95Z%%’ ESCAPE ‘Z’ This clause finds the value ‘95%’.

If you need to find the string following the last the _ you could try to search from the end of a known length string like this (not pretty) SELECT CASE WHEN SUBSTRING (my_string FROM 16 FOR 1) = '_' THEN TRIM(SUBSTRING (my_string FROM 17)) WHEN SUBSTRING...

I noticed typeofuse, what does it mean? According to the manual - Data Dictionary - Release V2R5.1 ** This field is not used.

How about this to get the last 3 characters from a column SELECT SUBSTRING(TRIM(column) FROM CHARACTER_LENGTH(TRIM(column)) -2 FOR 3) from Table If column is CHAR(10) and contains "WEST " TRIM removes trailing spaces CHARACTER_LENGTH returns 4 subtract 2 from CHARACTER_LENGTH =...

I believe it is AND first and then OR. But I would use parens to make it clear to anyone reading the code what conditions should be ORed

If you are going to replace a view as you stated in your original post aren't you going to run into problems keeping it relevant for concurent users? BILL LOGS on the following fires: replace view EXAMPLE AS SELECT * FROM TABLE A WHERE CONDITION = 1; BOB LOGS on the following fires...

You could do something like CREATE VIEW myview AS SELECT stuff FROM mytable WHERE (USER = 'bill' AND other conditions) OR (USER = 'bob' AND other conditions) OR etc etc for each user Define the view one time then replace as new users need to be added. This way all users can use the...

The solution I offered does not mix OLAP and aggregate functions. I tested it and it works. The GROUP BY in this case names the grouping where the CSUM should break. GROUP BY works this way for OLAP functions. It will return all rows with a sequence number which will be reset when phone...

The a names the derived table. query should look like this SELECT a.name ,a.phone ,a.blah1 ,a.blah2 ,a.transaction ,a.blah3 ,a.blah4 FROM (SELECT name ,phone ,blah1 ,blah2 ,transaction ,blah3...

GROUP BY works on all the values in your select list. If you have 10 columns in your select list you must either group by all 10 or perform an aggregate operation (MIN, MAX,SUM, AVG, COUNT) on the ones not included. See my previous post.

SELECT DISTINCT PHONE, NAME, BLAH1, BLA2H,TRANSACTION,...... FROM TABLE1 TABLE2 TABLE3 TABLE4 WHERE STATE = 'TX' AND TYPE = 'BUS' AND TRANSACTION = 'TRANTYP'; will work if you want distinct instances of all the columns selected, that is all the rows with a given phone number...

If I understand your problem this might work. I have added a sequence number that breaks on phone, transaction then selects the record for phone, transaction that has sequence number 1. This should give you 1 record for each phone, transaction combination. THis assumes that you truly do not care...

Sorry, I put an extra ")" in the substr. SELECT junk_acct_no FROM junk_tbl WHERE substr(junk_acct_no,chars(junk_acct_no),1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ') AND substr(junk_acct_no,chars(junk_acct_no) -1,1) IN ('0', '1', '2', '3', '4', '5', '6'...

Here is a solution. It assumes no leading or embedded spaces in your junk_acct_no value.If junk_acct_no will always fill all 20 characters with a value other than blank, remove ' ' from the in list. If junk_acct_no will be a consistent length shorter than 20 you can trim the value and remove '...

How about something like this. SELECT SUM(CASE WHEN day_of_week BETWEEN 2 AND 6 THEN 1 ELSE 0 END) as working_days FROM sys_calendar.calendar WHERE calendar_date BETWEEN '2003-01-01' AND '2003-01-31' AND calendar_date NOT IN ('2003-01-01') Counting days Monday thru Friday and supply...