Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.9K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 398 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
Table- From and To - BETWEEN operator- View to bring all records between from and to

I have a table TABLE1 where i have 2 columns ValueFrom and ValTo with SIGN option is BT. Now i need to design a View having one column which constitutes of only column.
TABLE1:
SETNAME SIGN OPTION VALFROM VALTO
ABC I BT 500700 500710
VIEW1
COLUMN1
500700
500701
500702
500703
500704
500705
500706
500707
500708
500709
500710
Answers
-
Rajesh234 wrote:I have a table TABLE1 where i have 2 columns ValueFrom and ValTo with SIGN option is BT. Now i need to design a View having one column which constitutes of only column.TABLE1:SETNAME SIGN OPTION VALFROM VALTOABC I BT 500700 500710VIEW1COLUMN1500700500701500702500703500704500705500706500707500708500709500710
How do I ask a question on the forums?
Please provide details for #5 - #9 inclusive.
-
Why create a view and not, for instance, use a refcursor?
-
So you want a query that can generate rows?
Something like...
SQL> ed
Wrote file afiedt.buf 1 with table1 as (
2 select 'ABC' as setname, 'I' as sgn, 'BT' as opt, 500700 as valfrom, 500710 as valto from dual union all
3 select 'CDE' as setname, 'I' as sgn, 'BT' as opt, 1 as valfrom, 5 as valto from dual
4 )
5 select setname, valfrom+level-1 as column1
6 from table1
7 connect by setname = prior setname
8 and level <= (valto-valfrom)+1
9* and prior sys_guid() is not null
SQL> /SET COLUMN1
--- ----------
ABC 500700
ABC 500701
ABC 500702
ABC 500703
ABC 500704
ABC 500705
ABC 500706
ABC 500707
ABC 500708
ABC 500709
ABC 500710
CDE 1
CDE 2
CDE 3
CDE 4
CDE 516 rows selected.You haven't explained what the significance of SIGN or OPTION are in your data, so I've ignored those.
Also I assumed that SETNAME was a key.
-
Writing code to solve interesting tasks is fun, and it's good practice. That works when the task itself is clearly defined.
Helping people formulate a clear task is a very different kind of activity. It can also be fun, and it's also good practice. The best programmers, in my opinion, are those who are able to interact with non-technical people, to identify a meaningful, correct and complete statement of the task that must be performed.
So, let me engage in this other kind of activity.
Your input data has a column SET. You say you want to write a view with A SINGLE COLUMN. That doesn't make a lot of sense. Don't you have several sets in the table? If you do, that means one of two things:
Either that you must write a view that will have the correct values FOR EVERY SET, and in that case the view must also have a column to identify the set (otherwise the view is useless)
Or that you want to write a PARAMETERIZED view, where you give the set as an input.
So, which of these two is a better statement of what you need to write? (Or neither - in which case, please clarify.)
Then, let me try to guess... the SIGN column tells you if the values will be in I or D order, meaning Increasing or Decreasing. I assume if that's the case, the "from" value will be lower than the "to" value in the Increasing case, and "from" will be greater than "to" in the Decreasing case. What I just said also proves that the SIGN column is unnecessary, and in the worst cases it will simply contradict what can be read directly from the "from" and "to" columns. (Of course, this only makes sense if my guess about the SIGN column is correct.)
Then, the OPTION column tells you what kind of condition you must use. BT might mean "between". What other options are there? Perhaps GT, GE, LT, LE, EQ, NE? Some of these don't make a lot of sense; NE (meaning not equal) - fine, not equal to 38; what does that mean? List all the positive integers that are NOT equal to 38? Obviously that is not possible. So, if my guess about the OPTION column is correct (that it tells you what comparison operators you must use), please explain that in full details. What values are possible, and what do they all mean?