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!

Build a select statement

Discussion in 'SQL PL/SQL' started by akika, Apr 3, 2017.

  1. akika

    akika Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    New Delhi
    HI,

    I need your assistance to build a select statement..
    is it possible to help ?
    for e.g if a customer 1200 is entitled to 4 number of products then the first 4 article_code and article_desc (from article_tbl) should be displayed
    and dummy column printed for these should be Y else if no entitled its null.

    So depending on the entitle_no the article_code & article_desc should be available for a customer.

    Any suggestion pls how to build this select? Tables structure are attached and output should be like below:

    TBL_ARTICLE.ARTICLE_CODE TBL_ARTICLE.ARTICLE_DESC TBL_PAYMENT.PAYMENT_DATE "PRINTED"
    A02 TABLETS SAMSUNG 01/03/17 Y
    A03 HUAWEI 02/03/17 Y
    A04 LG TV
    A05 COOKER
     

    Attached Files:

  2. androidrobo

    androidrobo Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    chennai
    A select query is used to create subsets of data that you can use to answer specific questions. It can also be used to supply data to other database objects. Once you create a select query, you can use it whenever you need.

    This topic explains how to create a simple select query that searches the data in a single table. You will also learn how to enhance the query by defining record criteria and adding calculated values.
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    748
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    Are you learning SQL ?

    For simple example :
    Code (SQL):
    -- get article of SAMSUNG
    SELECT * FROM tbl_article  ta WHERE ta.article_code = 'A02';

    -- get payments by customer
    SELECT  tc.*,
            tp.artcile_code,
            tp.PAYMENT_DATE,
    FROM tbl_customer tc
    JOIN TBL_PAYMENT tp ON tp.ref_no = tc.ref_no
    WHERE tc.ref_no = '101';

     
    Ask specific questions : sql, db design and etc...
    This will allow you to quickly get answers to their questions.
    The learning process is not easy and all you need to do step by step.

    Maybe helpful ,for education :

    Oracle Dev
    Oracle SQL 101
     
    Last edited: Apr 3, 2017
  4. akika

    akika Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    New Delhi
    Hi,

    Yes i'm new to this oracle.. pl /sql stuffs :(

    would like to do the query based on field 'entitle_no' from tbl_customer.

    For e.g if entitle_no is 1 then it should give article_code A
    if 2 Article Code & desc for A and B
    if 3 Article Code & Desc for A, B and C
    if entitle_no is 0 then no output

    Can a decode be used for that or a select within a decode function?
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,567
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You should use CASE rather than DECODE as it's more flexible and readable:

    ...
    case when entitle_no = 1 then 'A '||desc
    when entitle_no = 2 then 'A '||desc||' B '||desc
    when entitle_no = 3 then 'A '||desc||' B '||desc||' C '||desc
    when entitle_no = 0 then null end article_info,
    ...

    I leave it to you to make the output as you want it.
     
  6. akika

    akika Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    New Delhi
    Hi,
    with case it will be like hardcoding the value in the then condition. Is it possible to make the then condition use a select to retrieve data from another table. As the value in the tbl_article can change from time to time.
    Or this there a better way to achieve it??

    for entitle_no since its in number, im converting it chr(A.entitled_no+64)entitled
    I'm having trouble to make the select between these 2 tables. Can you please help me out?

    For e.g ref 102 is entitled to 4 article, so the output should display A, B, C , D.

    Code (SQL):
    SELECT A.ref_no, A.name1, entitled_no, chr(A.entitled_no+64)due
    FROM tbl_entitle A;
    REF_NO     NAME1           entitled_no     DUE
    ---------- ------------------------------ -
    101        Aki                              1                     A
    102        Yadav                         4                     D
    409        Sha                            NULL                 NULL


    SELECT article_code, article_desc FROM tbl_article;
    ARTICLE_CO ARTICLE_DESC          
    ---------- -------------------------
    A          TABLETS SAMSUNG        
    B          HUAWEI                  
    C          LG TV                  
    D          COOKER                  
    E          CUTLERY SET            
     
     
  7. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    748
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    Hi.
    If you need to change according to some conditions : the structure of fields, data sources , then you need to use dynamic sql
    Try, and make a your decision.Provide this solution here in the forum and we can help you faster.
     
  8. akika

    akika Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    New Delhi
    I've build up below select, please advise how to include in the select the header as
    Article_code, Article_desc, Payment_date, empl_id

    Code (SQL):

    SELECT * FROM (
    SELECT * FROM tbl_article
    WHERE article_code <= (SELECT chr(64+entitle_no)
                        FROM tbl_entitle
                        WHERE ref_no = '101'))
      WHERE article_code NOT IN  (SELECT article_code
                          FROM tbl_payment
                          WHERE ref_no = '101'
                          );
    Table Struc:
    tbl_article : article_code char(1), article_desc varchar2(10)
    tbl_entitle : ref_no number, entitle_no number
    tbl_payment : ref_no number, payment_date date, emplID varchar2(10)
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,567
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You are not returning payment_date or emplID in from your select statement so they will never be in the header. You need to change your query to return those values before they can be displayed.
     
  10. akika

    akika Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    New Delhi
    Which part of select to change that it will include these fields in the header with the above statement?
     
  11. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,567
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You are selecting data only from tbl_article; the other columns you want in your report come from tbl_payment so it has to be included in the FROM clause; this should give you a starting place:

    Code (SQL):
    SELECT * FROM (
    SELECT * FROM tbl_article, tbl_payment
    WHERE article_code <= (SELECT chr(64+entitle_no)
                        FROM tbl_entitle
                        WHERE ref_no = 101))
      WHERE article_code NOT IN  (SELECT article_code
                          FROM tbl_payment
                          WHERE ref_no = 101
      AND ref_no = 101                    
    );
     
  12. akika

    akika Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    New Delhi
    Wen adding the tbl_payment in the select, getting ORA-00918: column ambiguously defined..
     
  13. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,567
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Are the table definitions you supplied correct? There appear to be no commonly named columns between tbl_article and tbl_payment. That is an easy fix, however:

    Code (SQL):
    SELECT * FROM (
    SELECT * FROM tbl_article tba, tbl_payment tbp
    WHERE tba.article_code <= (SELECT chr(64+entitle_no)
                        FROM tbl_entitle
                        WHERE ref_no = 101))
      WHERE tba.article_code NOT IN  (SELECT article_code
                          FROM tbl_payment
                          WHERE ref_no = 101
      AND tbp.ref_no = 101                    
    );
    That should eliminate the error; you'll need to see if it returns the 'correct' results.
     
  14. akika

    akika Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    New Delhi
    Still error, when adding the tbl_payment, getting
    ORA-00904: "TBA"."ARTICLE_CODE": invalid identifier
    But when removing the tbl_payment is working, however i need the payment_date &
     
  15. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,567
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Then take OUT the tba. for that column. It's a fairly simple edit.
     
  16. akika

    akika Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    New Delhi
    Nopes, get
    ORA-00918: column ambiguously defined .. shall the column name be mentioned specificially in the select ??
     
  17. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,567
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Not the 'outer' select but the inner select might need a specific list.
     
  18. akika

    akika Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    New Delhi
    Euh.. still getting the ambiguous error when adding it in inner select... Can you please help?
     
  19. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,567
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your table definitions won't support the output you want to display and your query is using columns that don't exist in the tables you are querying from. Reworking your definitions and re-writing the query I get something that works:

    Code (SQL):
    CREATE TABLE tbl_article(article_code CHAR(1), article_desc varchar2(10));

    CREATE TABLE tbl_entitle(ref_no NUMBER, entitle_no NUMBER);

    CREATE TABLE tbl_payment(ref_no NUMBER, payment_date DATE, article_Code CHAR(1), emplID varchar2(10));

    BEGIN
        FOR i IN 1..1000 loop
            INSERT INTO tbl_entitle VALUES(i, MOD(i, 24));
        END loop;
        FOR i IN 1..1000 loop
            INSERT INTO tbl_article VALUES((SELECT chr(64+entitle_no) FROM tbl_entitle WHERE ref_no = i), 'Fnequasset');
            INSERT INTO tbl_payment VALUES(i, sysdate+i, (SELECT chr(64+entitle_no) FROM tbl_entitle WHERE ref_no = i),'FNING'||rpad(i, 5, '0'));
            END loop;

        commit;
    END;
    /

    SELECT * FROM (
    SELECT * FROM tbl_article
    WHERE article_code <= (SELECT chr(64+entitle_no)
                        FROM tbl_entitle
                        WHERE ref_no = '101'))
      WHERE article_code NOT IN  (SELECT article_code
                          FROM tbl_payment
                          WHERE ref_no = '101'
                          );
    SELECT * FROM (
    SELECT tba.article_code, tba.article_desc, tbp.payment_date, tbp.emplid
    FROM tbl_article tba, tbl_payment tbp
    WHERE tba.article_code <= (SELECT chr(64+entitle_no)
                        FROM tbl_entitle
                        WHERE ref_no = 101)
      AND tba.article_code NOT IN  (SELECT article_code
                          FROM tbl_payment
                          WHERE ref_no = 101)
    AND tbp.article_Code = tba.article_code);
     
    If this is a class assignment then the instructor needs to work on the problem and provide proper table definitions.
     
    akika likes this.
  20. akika

    akika Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    New Delhi
    Thanks David..