Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Recent content by jgerstb

  1. jgerstb

    Wild Cards question.

    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%’.
  2. jgerstb

    Question on String Function

    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...
  3. jgerstb

    What is typeofuse col in dbc.dbqlobjtbl

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

    Substring from right?

    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 =...
  5. jgerstb

    AND OR precedence

    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
  6. jgerstb

    Conditional SQL based on user.

    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...
  7. jgerstb

    Conditional SQL based on user.

    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...
  8. jgerstb

    Droping Duplicate data

    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...
  9. jgerstb

    Droping Duplicate data

    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...
  10. jgerstb

    Droping Duplicate data

    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.
  11. jgerstb

    Droping Duplicate data

    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...
  12. jgerstb

    Droping Duplicate data

    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...
  13. jgerstb

    Wildcard characters

    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'...
  14. jgerstb

    Wildcard characters

    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 '...
  15. jgerstb

    Number of working days between two date fields

    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...
See more

Part and Inventory Search

Back
Top