logo
down
shadow

oracle sql 2 columns in one column


oracle sql 2 columns in one column

By : Mitesh Sura
Date : October 17 2020, 08:10 AM
this one helps. This will place a newline character into the output - which is perhaps all you intended by this "Note: FN and LN is in the same row"
code :
select
     ( fn ||chr(10) || ln ) as name
from tbl
select
     case when n.n = 1 then tbl.fn else tbl.ln end as name
from tbl
cross join (select 1 n from dual union all select 2 from dual) n


Share : facebook icon twitter icon
use column to evaluate with another columns oracle

use column to evaluate with another columns oracle


By : Jian Wang
Date : March 29 2020, 07:55 AM
I wish did fix the issue. As Annjawn explains in this answer you can use some of the built in functionality of XMLQuery, specifically .getnumberval(), to fake the formulae. This will be considerably slower than a CASE statement but it is a lot cleaner.
Assuming the table:
code :
create table tmp_test (
   field1 number not null
 , field2 varchar2(1) not null
 , field3 number not null );

insert all
  into tmp_test values (1, '+', 5)
  into tmp_test values (1, '*', 5)
  into tmp_test values (1, '-', 5)
select * from dual;
SQL> select xmlquery(field1 || field2 || field3 returning content
  2                   ).getnumberval() as field4
  3    from tmp_test;

    FIELD4
----------
         6
         5
        -4
create table tmp_test (
   field1 number not null
 , field2 varchar2(1) not null
 , field3 number not null
 , field4 number generated always as (
           xmlquery(field1 || field2 || field3 returning content
                 ).getnumberval()
           ) virtual
    );
SQL> select * from tmp_test;

    FIELD1 F     FIELD3     FIELD4
---------- - ---------- ----------
         1 +          5          6
         1 *          5          5
         1 -          5         -4
oracle select few columns from first table and insert (seq.nextval for one column + already selected columns) into secon

oracle select few columns from first table and insert (seq.nextval for one column + already selected columns) into secon


By : Mohsen
Date : March 29 2020, 07:55 AM
fixed the issue. Will look into that further Your insert statement should have the column-names in the table. Then you should select the values from appropriate sequence and table.
code :
INSERT INTO PRODUCT2 (PRD_ID, PRD_NAME) 
SELECT PRODUCT_SEQ.NEXTVAL, PRD_NAME FROM PRODUCT1
Oracle Query compare 2 columns that both have dates and only display the record if column a less than column b

Oracle Query compare 2 columns that both have dates and only display the record if column a less than column b


By : Alex Runner
Date : March 29 2020, 07:55 AM
seems to work fine I have Oracle data in which I only want to retrieve the records that have a date from a column that is greater than the date of another column date. , You can just compare the dates in WHERE clause:
code :
WHERE CLOSED_D > ETR_D
Two Columns Come in one Column In oracle

Two Columns Come in one Column In oracle


By : Woo
Date : March 29 2020, 07:55 AM
I think the issue was by ths following , You don't need to join the two tables; you can UNION queries against each one:
code :
SELECT TO_CHAR(dr.reasoncode) AS reasoncode, dr.reasoninfo
FROM delayreason dr
UNION ALL
SELECT dsr.reasoncode ||''|| dsr.reasonsubcode, dsr.reasonsubinfo
FROM delaysubreason dsr;
Fetch all columns into one column oracle

Fetch all columns into one column oracle


By : user3687245
Date : March 29 2020, 07:55 AM
it should still fix some issue The following is just to demonstrate a way to achieve what you asked for. (Assuming I didn't misunderstand what you asked for.)
First some preparation.
code :
create table DA (COL1 char(1), COL2 char(1), COL3 char(1));
insert into DA values ('a','b','c');
insert into DA values ('1','2','3');
insert into DA values ('x','y','z');
create table RESULT (ONE_COL char(5));
declare
  L_COL  varchar2(128);
  L_CUR  sys_refcursor;
  L_ONE  char(5);
  L_SQL  varchar2(2000);
--
  cursor C_COLS is
    select COLUMN_NAME
      from USER_TAB_COLUMNS
     where TABLE_NAME = 'DA';
begin
  L_SQL := 'select ';
  open C_COLS;
  fetch C_COLS into L_COL;
  L_SQL := L_SQL || L_COL;
  while C_COLS%found
  loop
    fetch C_COLS into L_COL;
    if C_COLS%found then
      L_SQL := L_SQL || '||';
      L_SQL := L_SQL || '''|''||';
      L_SQL := L_SQL || L_COL;
    end if;
  end loop;
  close C_COLS;
  L_SQL := L_SQL || ' from DA';
  open L_CUR for L_SQL;
  loop
    fetch L_CUR into L_ONE;
    exit when L_CUR%notfound;
    insert into RESULT values (L_ONE);
  end loop;
  close L_CUR;
end;
select * from RESULT;
ONE_C
-----
abc
123
xyz
Related Posts Related Posts :
  • SQL Query - Group consecutive items based on condition
  • Users who work in same department
  • Syntax error near column value Vb
  • Oracle Trigger BEFORE INSERT has No data found
  • What kind of join to use on SQL tables
  • Is there a way to add a constant value dynamically to all records returned in Hive?
  • SQL optimization (inner join or selects)
  • EF 6.x, LINQ-to-SQL and raw SQL clauses
  • Simple SQL Variable Assignment Only Returns One Letter: Why?
  • Converting a custom timestamp to date
  • SQL Server : inserting Player vs Player names in to new table from tblEntrants
  • invalid identifier in sql
  • PL/SQL - I keep getting this error when concatenating: PLS-00306: wrong number or types of arguments in call to '||'
  • Count records only from left side of a LEFT JOIN
  • get everything before a string including itself oracle
  • Format Data from Word Doc to SQL using RegEX
  • Conditional formatting on MAX value row
  • MS-Access : selecting data from two tables and only returning you need
  • SQL Server: optimal indexing strategies for many-to-many join
  • DBgrid column very wide
  • PostgreSQL Group values by category, count and calculate percentage
  • MS Access SQL - Most Recent Record for Each Consultant ID
  • Update table: Summary of previous rows without using cursor or while loop
  • PostgreSQL: built-in function to remove substring starting with certain pattern
  • ORA-00909: invalid number of arguments
  • How to summarize all possible combinations of variables?
  • Select Column within a Column SQL
  • PostgreSQL Inserting 2 relationships at once
  • T sql - How to store results from a dynamic query using EXEC or EXECUTE sp_executesql
  • How do I parse my json into CSV using regex?
  • Reverse foreign key cascading (or how to collect database garbage)
  • SQL Pivot Questions
  • Insert records into a table with a condition in SQL Server 2016
  • display null value using rank functions in oracle sql
  • SQL - Get count of group by column but also select top item of group
  • How to add an array of datarows into an exisitng table inside my database
  • There is no unique constraint matching given keys for referenced table "employee" 1
  • SQL: Unable to SELECT joined column
  • How to find out how much space a SQL Server table uses?
  • Window function to remove specific records from SQL Server dataset
  • How to add a column for each day in sql?
  • Create group column based on the specific rows
  • Not sure if this consistitues a transitive dependency
  • How to compare the values in a column to a long list in SQL Server
  • Preserving data format Decimal(6,5) from vba to sql
  • Oracle Query to rollup QTY by Year- only last 3 years
  • SQL - Calculate 2 columns and view result to another to column
  • Divide or Multiply according to a condition (Improving query)
  • PostgreSQL unnest() with consecutive integers grouped by number
  • SQL to limit output to certain months and years
  • VARCHAR TIME TO GET THE DIFFERENCE
  • SQL conditional constraint on multiple columns being unique
  • Optimize a SQL select query in a loop
  • BTEQ Teradata Import Multiple files into one table
  • Update SQL datetime column with oldest values of another table column?
  • Is INSERT ... SELECT an atomic transaction?
  • SQL query completed successfully but not results
  • SQL sub select returning multiple values
  • Verify condition on two columns
  • SQL conditional field, first match JOIN
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org