1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to display the literal as a separte column

Discussion in 'SQL PL/SQL' started by expora, Oct 24, 2011.

  1. expora

    expora Active Member

    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    80
    for example suppose that you want to create a result that detailes employee names and thire job .titels linked by the litieral string ‘is a ‘ .when you place the literal ‘is a ’ between the cloumn names in the select list , the ruselt produced a separate cloumn contining the literal string.
    BLEASE CHECK THE BELOW EXAMPLE.
    Code (SQL):
    SELECT ENAME ,IS A’,JOB
    FROM EMP;
    ------------------------------------
    ENAME      'ISA JOB
    ---------- ---- ---------
    SMITH      IS A CLERK
    ALLEN      IS A SALESMAN
    WARD       IS A SALESMAN
    JONES      IS A MANAGER
    MARTIN     IS A SALESMAN
    BLAKE      IS A MANAGER
    CLARK      IS A MANAGER
    SCOTT      IS A ANALYST
    KING       IS A PRESIDENT
    TURNER     IS A SALESMAN
    ADAMS      IS A CLERK

    ENAME      '
    ISA JOB
    ---------- ---- ---------
    JAMES      IS A CLERK
    FORD       IS A ANALYST
    MILLER     IS A CLERK
     
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: How to display the letiral as a separte column

    You didn't go far enough with your explanation -- you can do this with joins as well:

    Code (SQL):
    SQL> SELECT e.ename, 'IS AN OBNOXIOUS', e.job, 'IN DISMAL', d.loc, 'AND IS SEVERELY OVERPAID AT', sal, 'SCHNECKNOOBERS'
      2  FROM emp e, dept d
      3  WHERE d.deptno = e.deptno;
     
    ENAME      'ISANOBNOXIOUS' JOB       'INDISMAL LOC           'ANDISSEVERELYOVERPAIDAT'          SAL 'SCHNECKNOOBER
    ---------- --------------- --------- --------- ------------- --------------------------- ---------- --------------
    SMITH      IS AN OBNOXIOUS CLERK     IN DISMAL DALLAS        AND IS SEVERELY OVERPAID AT        800 SCHNECKNOOBERS
    ALLEN      IS AN OBNOXIOUS SALESMAN  IN DISMAL CHICAGO       AND IS SEVERELY OVERPAID AT       1600 SCHNECKNOOBERS
    WARD       IS AN OBNOXIOUS SALESMAN  IN DISMAL CHICAGO       AND IS SEVERELY OVERPAID AT       1250 SCHNECKNOOBERS
    JONES      IS AN OBNOXIOUS MANAGER   IN DISMAL DALLAS        AND IS SEVERELY OVERPAID AT       2975 SCHNECKNOOBERS
    MARTIN     IS AN OBNOXIOUS SALESMAN  IN DISMAL CHICAGO       AND IS SEVERELY OVERPAID AT       1250 SCHNECKNOOBERS
    BLAKE      IS AN OBNOXIOUS MANAGER   IN DISMAL CHICAGO       AND IS SEVERELY OVERPAID AT       2850 SCHNECKNOOBERS
    CLARK      IS AN OBNOXIOUS MANAGER   IN DISMAL NEW YORK      AND IS SEVERELY OVERPAID AT       2450 SCHNECKNOOBERS
    SCOTT      IS AN OBNOXIOUS ANALYST   IN DISMAL DALLAS        AND IS SEVERELY OVERPAID AT       3000 SCHNECKNOOBERS
    KING       IS AN OBNOXIOUS PRESIDENT IN DISMAL NEW YORK      AND IS SEVERELY OVERPAID AT       5000 SCHNECKNOOBERS
    TURNER     IS AN OBNOXIOUS SALESMAN  IN DISMAL CHICAGO       AND IS SEVERELY OVERPAID AT       1500 SCHNECKNOOBERS
    ADAMS      IS AN OBNOXIOUS CLERK     IN DISMAL DALLAS        AND IS SEVERELY OVERPAID AT       1100 SCHNECKNOOBERS
     
    ENAME      'ISANOBNOXIOUS' JOB       'INDISMAL LOC           'ANDISSEVERELYOVERPAIDAT'          SAL 'SCHNECKNOOBER
    ---------- --------------- --------- --------- ------------- --------------------------- ---------- --------------
    JAMES      IS AN OBNOXIOUS CLERK     IN DISMAL CHICAGO       AND IS SEVERELY OVERPAID AT        950 SCHNECKNOOBERS
    FORD       IS AN OBNOXIOUS ANALYST   IN DISMAL DALLAS        AND IS SEVERELY OVERPAID AT       3000 SCHNECKNOOBERS
    MILLER     IS AN OBNOXIOUS CLERK     IN DISMAL NEW YORK      AND IS SEVERELY OVERPAID AT       1300 SCHNECKNOOBERS
     
    14 ROWS selected.
     
    SQL>
     
    Sadik likes this.
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Re: How to display the letiral as a separte column

    The question is not clear. Do you want to rename separate field or concatenate the literal ? The following query may help you.

    Code (SQL):

    SQL> SELECT ENAME ,'IS A' LITERAL,JOB  FROM  EMP;

    ENAME      LITERAL JOB
    ---------- ------- ---------
    SMITH      IS A    CLERK
    ALLEN      IS A    SALESMAN
    WARD       IS A    SALESMAN
    JONES      IS A    MANAGER
    MARTIN     IS A    SALESMAN
    BLAKE      IS A    MANAGER
    CLARK      IS A    MANAGER
    KING       IS A    PRESIDENT
    TURNER     IS A    SALESMAN
    ADAMS      IS A    CLERK
    JAMES      IS A    CLERK
    FORD       IS A    ANALYST

    12 ROWS selected.

    SQL> SELECT ENAME ,'IS A'||JOB JOB FROM  EMP;

    ENAME      JOB
    ---------- -------------
    SMITH      IS ACLERK
    ALLEN      IS ASALESMAN
    WARD       IS ASALESMAN
    JONES      IS AMANAGER
    MARTIN     IS ASALESMAN
    BLAKE      IS AMANAGER
    CLARK      IS AMANAGER
    KING       IS APRESIDENT
    TURNER     IS ASALESMAN
    ADAMS      IS ACLERK
    JAMES      IS ACLERK
    FORD       IS AANALYST

    12 ROWS selected.

    SQL>
     
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: How to display the letiral as a separte column

    It isn't a question, he's trying to advertise for his blog to teach beginners the intricacies of SQL. I personally think this is the wrong place to be shilling and promoting such a blog.
     
  5. expora

    expora Active Member

    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    80
    Dear Members I'm Sure From My Examples If You Want Check Please Take A Copy And Past In SQL Editor And Replay, I Will Complete My Posts Still I Didn’t Finish My Blog Please Check Dates Of Posts. Also It A Blog You Know What Mean Blog I Think If You Know The Meaning Of Blog You Will Not Replay Like That
    Good luck
    And I’m sorry for disturbing for all who replay by criticism method
    Please if you see any error in any post in my blog please write a comment there and post
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    From your blog:

    Code (SQL):
    SQL> CREATE TABLE newemp AS SELECT empno, ename, deptno FROM emp WHERE deptno = 10;
     
    TABLE created.
     
    SQL> INSERT INTO newemp
    2 SELECT empno,ename,deptno
    3 FROM emp
    4 WHERE deptno=10;
     
    3 ROWS created.
     
    SQL> SAVEPOINT inserting_finish;
     
    SAVEPOINT created.
     
    SQL>
    SQL> ROLLBACK INSERTING_FINISH
    2 /
    ROLLBACK INSERTING_FINISH
    *
    ERROR at line 1:
    ORA-02181: invalid OPTION TO ROLLBACK WORK
     
    SQL>
    SQL> UPDATE EMP
    2 SET job='PROGRAMMER', SAL=2500
    3 WHERE ENAME='SMITH';
    SET job='PROGRAMMER', SAL=2500
    *
    ERROR at line 2:
    ORA-12899: VALUE too LARGE FOR COLUMN "BING"."EMP"."JOB" (actual: 10, maximum:
    9)
     
    SQL>
    SQL> CREATE TABLE TRAINING
    2 (T_NO NUMBER(4) CONSTRAINT TRAINING_T_NO_PK PRIMARY_KEY,T_NAME VARACHAR2(25) CONSTRAINT TRAINING_T_NAME _NN NOT_NULL);
    (T_NO NUMBER(4) CONSTRAINT TRAINING_T_NO_PK PRIMARY_KEY,T_NAME VARACHAR2(25) CONSTRAINT TRAINING_T_NAME _NN NOT_NULL)
    *
    ERROR at line 2:
    ORA-02253: CONSTRAINT specification NOT allowed here
     
    SQL>
    Your blog posts this:

    Code (SQL):

    SELECT ename,job
    FROM emp
    WHERE ename LIKE ‘c%’;
    Ename job
    ---------- ---------
    Clark manager
     
    In actuality the result is this:

    Code (SQL):
    SQL> SELECT ename,job
    2 FROM emp
    3 WHERE ename LIKE 'c%';
     
    no ROWS selected
     
    SQL>
    since the names are in all capital letters. Likewise this example:

    Code (SQL):
    SQL> SELECT ename
    2 FROM emp
    3 WHERE ename LIKE '_a%';
     
    no ROWS selected
     
    SQL>