Forum Stats

  • 3,876,133 Users
  • 2,267,067 Discussions
  • 7,912,446 Comments

Discussions

Categories

Table- From and To - BETWEEN operator- View to bring all records between from and to

Rajesh234
Rajesh234 Member Posts: 1
edited Jun 28, 2019 11:10AM in SQL & PL/SQL

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

Tagged:
Frank Kulash
· Share on TwitterShare on Facebook

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Jun 28, 2019 9:54AM 
    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.

    Frank Kulash
    · Share on TwitterShare on Facebook
  • Anton Scheffer
    Anton Scheffer Member Posts: 1,950 Gold Trophy
    edited Jun 28, 2019 9:54AM

    Why create a view and not, for instance, use a refcursor?

    · Share on TwitterShare on Facebook
  • BluShadow
    BluShadow Member, Moderator Posts: 42,591 Red Diamond
    edited Jun 28, 2019 9:55AM

    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.

    · Share on TwitterShare on Facebook
  • mathguy
    mathguy Member Posts: 10,920 Black Diamond
    edited Jun 28, 2019 11:10AM

    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?

    · Share on TwitterShare on Facebook