Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2009
    Posts
    2

    Unanswered: correct the sql query

    Hi All,

    I'm new to SQL lang, stuck up with a sql query,
    Three tables say;
    Table1:
    U_Id (pk)
    U_Name

    Table2:
    U_Id (fk)
    U_Code (fk)
    U_Grade
    U_Number
    U_Char

    Table3:
    U_Code (pk)
    U_Grade
    U_Type - value can be 'NUMBER' or 'CHAR'

    I've U_Name, U_code and U_Grade in hand. output, should have the U_ID along with U_Number or U_Char (not both) based on U_Type is 'NUMBER' or 'CHAR' respectively.

    I've the following query written, unable to find the flaw?

    SELECT DISTINCT T1.U_Name,
    IF ( T3.U_Type ) = 'NUMBER'
    BEGIN
    select T2.U_Number as U_Value
    from Table2 T2,
    WHERE T2.U_Code= T3.U_Code
    AND T2.U_Grade= T3.U_Grade
    END
    ELSE
    BEGIN
    select T2.U_Char as U_Value
    from Table2 T2, Table3 T3
    WHERE T2.U_Code= T3.U_Code
    AND T2.U_Grade= T3.U_Grade
    END
    U_Value
    FROM Table1 T1
    Table2 T2
    Table3 T3
    WHERE T1.U_Id = T2.U_Id
    AND T2.U_Code= T3.U_Code
    AND T2.U_Grade= T3.U_Grade
    AND U_Name = 'SomeUser'

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Well, there's no BEGIN-END or IF-THEN-ELSE in SQL. In PL/SQL - yes.

    So, which one of these is it? SQL or PL/SQL?

    If it is SQL, perhaps you could examine use of DECODE and/or CASE and try again.

  3. #3
    Join Date
    Aug 2009
    Posts
    2
    Its PL/SQL

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Are you sure? How much of Oracle did you learn so far? Can you distinguish SQL from PL/SQL? Because, your sample code is ... well, quite badly written.

    You might want to visit Oracle Documentation pages (this is a 10g version link) and check "Most popular" section, especially "SQL Reference", "PL/SQL User's Guide and Reference" as well as "Application Developer's Guide - Fundamentals".

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •