SQL Query to Translate a List of Numbers Matched Against Several Ranges, to a List of Values

SQL query to translate a list of numbers matched against several ranges, to a list of values

I am guessing you want to pass that set of numbers as a string and split into into individual numbers. This is harder than you might think, because Oracle doesn't come with a built-in tokenizer. Weird, huh?

There are a number of PL/SQL tokenizer solutions knocking around Das Interwabs. I am using a variant of Anup Pani's implementation, which uses Regex (hence only Oracle 10g or higher). My variant returns an array of numbers which I have declared as a SQL type:

SQL> create or replace type numbers as table of number
  2  /

Type created.

SQL>

This means I can use it as an input to a TABLE() function in a SELECT statement:

SQL> select * from table (str_to_number_tokens('20000, 240004, 375000, 255000'))
  2  /

COLUMN_VALUE
------------
       20000
      240004
      375000
      255000

SQL>

This means I can turn your string of numbers into a table which I can join to in a query, like this:

SQL> select val
  2  from t23
  3       , ( select column_value as i_no
  4           from table (str_to_number_tokens('20000, 240004, 375000, 255000')) ) sq
  5  where t23.year = 2010
  6  and   sq.i_no between t23.r_min and t23.r_max
  7  order by t23.priority
  8  /

       VAL
----------
        82
        50
        52

SQL>

How to find a ranges of sequential numbers without gaps in a table

You need to identify groups that are the same. There is a trick to this, which is a difference of row numbers.

select min(id) as fromid, max(id) as toid, type
from (select t.*,
             (row_number() over (partition by type order by id) -
              row_number() over (partition by type, badvalue order by id)
             ) as grp
      from table t
     ) grp
where badvalue = 0
group by grp, type;

There is a nuance here, because you only seem to want rows where "bad value" is 0. Note that this condition goes in the outer select, so it doesn't interfere with the row_number() calculations.

SQL query to get overlapping entries between two big lists of ranges

You can easily express this in SQL as a join. For partial overlaps, this would be:

select a.*, b.*
from a join
     b
     on a.start < b.end and a.end > b.start;

However, this will be slow, because it will be doing a nested loop comparison. So, although concise, this won't necessarily be much faster.

Augmenting an SQL query to translate an id for a human-readable value

If you basically need to do a look up in another table you can insert another JOIN in your query just like below:

SELECT DISTINCT x.*, acc.name
FROM 'the_database.the_salesforce_table.Lead' x INNER JOIN
     ( SELECT id, MAX(_sdc_sequence) AS seq, MAX(_sdc_batched_at) AS batch 
       FROM 'the_database.the_salesforce_table.Lead'
       GROUP BY id
     ) xx
     ON x.id = xx.id AND x._sdc_sequence = xx.seq AND
        x._sdc_batched_at = xx.batch
LEFT JOIN <account_table> acc ON acc.id = x.convertedaccountid

Please let me know if this helps you

Split column to multiple rows

And the correct answer is.

select Number, substr(
Site, 
instr(','||Site,',',1,seq),
instr(','||Site||',',',',1,seq+1) - instr(','||Site,',',1,seq)-1)  Site
from Sitetable,(select level seq from dual connect by level <= 100) seqgen
where instr(','||Site,',',1,seq) > 0

UPDATE multiple rows with different values in one query in MySQL

You can do it this way:

UPDATE table_users
    SET cod_user = (case when user_role = 'student' then '622057'
                         when user_role = 'assistant' then '2913659'
                         when user_role = 'admin' then '6160230'
                    end),
        date = '12082014'
    WHERE user_role in ('student', 'assistant', 'admin') AND
          cod_office = '17389551';

I don't understand your date format. Dates should be stored in the database using native date and time types.

SQL how to convert row with date range to many rows with each date

Using some sample data...

create table data (begindate datetime, enddate datetime, data int);
insert data select 
 '20130101', '20130104', 7 union all select
 '20130105', '20130106', 9;

The Query: (Note: if you already have a numbers/tally table - use it)

select dateadd(d,v.number,d.begindate) adate, data
  from data d
  join master..spt_values v on v.type='P'
       and v.number between 0 and datediff(d, begindate, enddate)
order by adate;

Results:

|                       COLUMN_0 | DATA |
-----------------------------------------
| January, 01 2013 00:00:00+0000 |    7 |
| January, 02 2013 00:00:00+0000 |    7 |
| January, 03 2013 00:00:00+0000 |    7 |
| January, 04 2013 00:00:00+0000 |    7 |
| January, 05 2013 00:00:00+0000 |    9 |
| January, 06 2013 00:00:00+0000 |    9 |

---

Alternatively you can generate a number table on the fly (0-99) or as many numbers as you need

;WITH Numbers(number) AS (
  select top(100) row_number() over (order by (select 0))-1
  from sys.columns a
  cross join sys.columns b
  cross join sys.columns c
  cross join sys.columns d
  )
select dateadd(d,v.number,d.begindate) adate, data
  from data d
  join Numbers v on v.number between 0 and datediff(d, begindate, enddate)
order by adate;

SQL Fiddle Demo

How can I pull a list of ID's from a SQL table as a comma-separated values string?

In addition to @OMG Ponies method, you could also try this COALESCE trick from:

Using COALESCE to Build Comma-Delimited Strings

declare @string nvarchar(255)

select @string = coalesce(@string + ', ', '') + cast(prodid as nvarchar(5))
from products

---

Related Topics