Skip to main content
Database Administrators

Return to Answer

deleted 4 characters in body
Source Link
Akina
Akina
  • 20.3k
  • 2
  • 19
  • 22

Maybe simply

CREATE FUNCTION [dbo].[fnct_rpt_ActiveHC_WithoutGrp_tarun1]
    (
    @adid1 VARCHAR(25),
    @number INT
    )
RETURNS TABLE
AS
RETURN  
    SELECT *
    FROM OD_SAP.dbo.ty_rpt_ActiveHC_WithoutGrp a
    JOIN [BI_Control].[dbo].[tbl_BI_AccessMatrix_ADIDcc] b ON a.CostCode = b.cc
    JOIN [BI_Control].[dbo].[tbl_BI_AccessMatrixMaster] C ON b.ADID = c.ADID AND C.IsActive = 1
    WHERE @adid1 NOT IN ('tachakr','BEULA','NI345519') 
       OR (b.ADID = @adid1 AND b.ReportID = @number)

?

Maybe simply

CREATE FUNCTION [dbo].[fnct_rpt_ActiveHC_WithoutGrp_tarun1]
    (
    @adid1 VARCHAR(25),
    @number INT
    )
RETURNS TABLE
AS
RETURN  
    SELECT *
    FROM OD_SAP.dbo.ty_rpt_ActiveHC_WithoutGrp a
    JOIN [BI_Control].[dbo].[tbl_BI_AccessMatrix_ADIDcc] b ON a.CostCode = b.cc
    JOIN [BI_Control].[dbo].[tbl_BI_AccessMatrixMaster] C ON b.ADID = c.ADID AND C.IsActive = 1
    WHERE @adid1 NOT IN ('tachakr','BEULA','NI345519') 
       OR (b.ADID = @adid1 AND b.ReportID = @number)

?

Maybe simply

CREATE FUNCTION [dbo].[fnct_rpt_ActiveHC_WithoutGrp_tarun1]
    (
    @adid1 VARCHAR(25),
    @number INT
    )
RETURNS TABLE
AS
RETURN  
    SELECT *
    FROM OD_SAP.dbo.ty_rpt_ActiveHC_WithoutGrp a
    JOIN [BI_Control].[dbo].[tbl_BI_AccessMatrix_ADIDcc] b ON a.CostCode = b.cc
    JOIN [BI_Control].[dbo].[tbl_BI_AccessMatrixMaster] C ON b.ADID = c.ADID AND C.IsActive = 1
    WHERE @adid1 IN ('tachakr','BEULA','NI345519') 
       OR (b.ADID = @adid1 AND b.ReportID = @number)

?

edited body
Source Link
Akina
Akina
  • 20.3k
  • 2
  • 19
  • 22

Maybe simply

CREATE FUNCTION [dbo].[fnct_rpt_ActiveHC_WithoutGrp_tarun1]
    (
    @adid1 varcharVARCHAR(25),
    @number intINT
    )
RETURNS TABLE
AS
RETURN  
    SELECT *
    FROM OD_SAP.dbo.ty_rpt_ActiveHC_WithoutGrp a
    JOIN [BI_Control].[dbo].[tbl_BI_AccessMatrix_ADIDcc] b ON a.CostCode = b.cc
    JOIN [BI_Control].[dbo].[tbl_BI_AccessMatrixMaster] C ON b.ADID = c.ADID AND C.IsActive = 1
    whereWHERE @adid1 notNOT inIN ('tachakr','BEULA','NI345519') 
       OR (b.ADID = @adid1 AND b.ReportID = @number)

?

Maybe simply

CREATE FUNCTION [dbo].[fnct_rpt_ActiveHC_WithoutGrp_tarun1]
    (
    @adid1 varchar(25),
    @number int
    )
RETURNS TABLE
AS
RETURN  
    SELECT *
    FROM OD_SAP.dbo.ty_rpt_ActiveHC_WithoutGrp a
    JOIN [BI_Control].[dbo].[tbl_BI_AccessMatrix_ADIDcc] b ON a.CostCode = b.cc
    JOIN [BI_Control].[dbo].[tbl_BI_AccessMatrixMaster] C ON b.ADID = c.ADID AND C.IsActive = 1
    where @adid1 not in ('tachakr','BEULA','NI345519') 
       OR (b.ADID = @adid1 AND b.ReportID = @number)

?

Maybe simply

CREATE FUNCTION [dbo].[fnct_rpt_ActiveHC_WithoutGrp_tarun1]
    (
    @adid1 VARCHAR(25),
    @number INT
    )
RETURNS TABLE
AS
RETURN  
    SELECT *
    FROM OD_SAP.dbo.ty_rpt_ActiveHC_WithoutGrp a
    JOIN [BI_Control].[dbo].[tbl_BI_AccessMatrix_ADIDcc] b ON a.CostCode = b.cc
    JOIN [BI_Control].[dbo].[tbl_BI_AccessMatrixMaster] C ON b.ADID = c.ADID AND C.IsActive = 1
    WHERE @adid1 NOT IN ('tachakr','BEULA','NI345519') 
       OR (b.ADID = @adid1 AND b.ReportID = @number)

?

Source Link
Akina
Akina
  • 20.3k
  • 2
  • 19
  • 22

Maybe simply

CREATE FUNCTION [dbo].[fnct_rpt_ActiveHC_WithoutGrp_tarun1]
    (
    @adid1 varchar(25),
    @number int
    )
RETURNS TABLE
AS
RETURN  
    SELECT *
    FROM OD_SAP.dbo.ty_rpt_ActiveHC_WithoutGrp a
    JOIN [BI_Control].[dbo].[tbl_BI_AccessMatrix_ADIDcc] b ON a.CostCode = b.cc
    JOIN [BI_Control].[dbo].[tbl_BI_AccessMatrixMaster] C ON b.ADID = c.ADID AND C.IsActive = 1
    where @adid1 not in ('tachakr','BEULA','NI345519') 
       OR (b.ADID = @adid1 AND b.ReportID = @number)

?