locked
how to run a query with function and view? RRS feed

  • Question

  • User-1965208702 posted

    Hi all,

    hope doing well,

    sir i was using one query which was in sql and working fine.

    in this i am running view and function together.

    but it's not running in oracle.

    here is my query.

    SELECT Leave_id,L.Ldate Code, L.EmpID [Emp ID], E.Emp_Name [Emp Name], Leave_Name [Leave Type],  LDate [From], EndDate [To],Cat_Desc Team ,ls.Leave_Status_text as Status,Flag as Approval,L.Leave_id LeaveDetails_id
    FROM Leave L INNER JOIN vwEmpMain E ON L.EmpID = E.Emp_ID"
     INNER JOIN Leave_Master LM ON L.LeaveType = LM.Leave_Code  Inner join leave_status Ls on Ls.Leave_Status_id = L.Flag WHERE E.Emp_ID in(select Emp_id from  [dbo].[FnFetchEmployees](1)  order by Leave_id desc

    here i am joing table with view in this vwEmpMain is the view and FnfetchEmployees is the function with parameter.

    please help me.

    thanks

    Thursday, September 27, 2012 1:13 AM

Answers

  • User269602965 posted

    1. [dbo].[FnFetchEmployees](1))  you need to display the code for this function so it can be converted to Oracle

    2. Oracle column names may not contain SPACE characters

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 2, 2012 11:25 AM
  • User269602965 posted
    /* FROM is a reserved word, so cannot use as column name       */
    /* [dbo].[FnFetchEmployees](1) needs conversion to Oracle View */
    
    SELECT 
      L.Leave_id           AS Leave_id,
      L.Ldate              AS Code, 
      L.EmpID              AS Emp_ID, 
      vw.Emp_Name          AS Emp_Name,
      LM.Leave_Name        AS Leave_Type,
      L.LDate              AS From_date, 
      L.EndDate            AS To, 
      vw.Cat_Desc          AS Team,
      ls.Leave_Status_text AS Status,
      L.Flag               AS Approval,
      L.Leave_id           AS LeaveDetails_id
    FROM       Leave        L 
    INNER JOIN vwEmpMain    vw ON L.EmpID     = vw.Emp_ID
    INNER JOIN Leave_Master LM ON L.LeaveType = LM.Leave_Code  
    INNER JOIN leave_status Ls ON Ls.Leave_Status_id = L.Flag 
    WHERE 
      vw.Emp_ID IN
                 (SELECT 
                    Emp_id 
                  FROM
                    [dbo].[FnFetchEmployees](1)
                  )
    ORDER BY Leave_id desc

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 2, 2012 6:14 PM

All replies

  • User269602965 posted

    1. many typographical errors found.

    2. all columns must have the table alias when using JOINS

    3. oracle column names may not contain SPACE characters

    4. you cannot call an MSSQL dbo stored procedure from Oracle, convert to oracle PLSQL and call it.

     

    Thursday, September 27, 2012 10:13 AM
  • User-1965208702 posted

    Hi lannie,

    thanks for your response .

    i am new in oracle before i was using sql server so this query was working fine there.

    so i don't know much about that.

    could you please correct that?

    thanks in advance.

    Monday, October 1, 2012 12:19 AM
  • User269602965 posted

    readers here would need your table layouts, entity relationships between tables, and the MS SQL function code for FnFetchEmployees

    to help you clean up your Oracle SQL statement above, understand the JOIN relationships, to know what column names go with which table, etc.

    Two outstanding Oracle references I use all the time.

    http://www.amazon.com/Oracle-Database-11g-SQL-Press/dp/0071498508/ref=sr_1_9?ie=UTF8&qid=1349128403&sr=8-9&keywords=oracle+11g

    http://www.amazon.com/Oracle-Database-11g-Programming-Press/dp/0071494456/ref=sr_1_15?ie=UTF8&qid=1349128403&sr=8-15&keywords=oracle+11g

    Monday, October 1, 2012 5:56 PM
  • User-1965208702 posted

    Hi lannie,

    thanks for your response.

    here i used all alias name to get that it is related to which table.

    here is modified query below:

    SELECT L.Leave_id,L.Ldate Code, L.EmpID [Emp ID], vw.Emp_Name [Emp Name], LM.Leave_Name [Leave Type],  L.LDate [From], L.EndDate [To], vw.Cat_Desc Team ,ls.Leave_Status_text as Status,L.Flag as Approval,L.Leave_id LeaveDetails_id
    FROM Leave L INNER JOIN vwEmpMain vw ON L.EmpID = vw.Emp_ID
     INNER JOIN Leave_Master LM ON L.LeaveType = LM.Leave_Code  Inner join leave_status Ls on Ls.Leave_Status_id = L.Flag WHERE vw.Emp_ID in(select Emp_id from  [dbo].[FnFetchEmployees](1))  order by Leave_id desc

    now please help me.

    thanks,

    Monday, October 1, 2012 10:20 PM
  • User269602965 posted

    1. [dbo].[FnFetchEmployees](1))  you need to display the code for this function so it can be converted to Oracle

    2. Oracle column names may not contain SPACE characters

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 2, 2012 11:25 AM
  • User269602965 posted
    /* FROM is a reserved word, so cannot use as column name       */
    /* [dbo].[FnFetchEmployees](1) needs conversion to Oracle View */
    
    SELECT 
      L.Leave_id           AS Leave_id,
      L.Ldate              AS Code, 
      L.EmpID              AS Emp_ID, 
      vw.Emp_Name          AS Emp_Name,
      LM.Leave_Name        AS Leave_Type,
      L.LDate              AS From_date, 
      L.EndDate            AS To, 
      vw.Cat_Desc          AS Team,
      ls.Leave_Status_text AS Status,
      L.Flag               AS Approval,
      L.Leave_id           AS LeaveDetails_id
    FROM       Leave        L 
    INNER JOIN vwEmpMain    vw ON L.EmpID     = vw.Emp_ID
    INNER JOIN Leave_Master LM ON L.LeaveType = LM.Leave_Code  
    INNER JOIN leave_status Ls ON Ls.Leave_Status_id = L.Flag 
    WHERE 
      vw.Emp_ID IN
                 (SELECT 
                    Emp_id 
                  FROM
                    [dbo].[FnFetchEmployees](1)
                  )
    ORDER BY Leave_id desc

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 2, 2012 6:14 PM