locked
deploy report with if else condition RRS feed

  • Question

  • I have a report that i need to publish but i need your support since i have issues, my case as below

    i have 4 parameters as below:

    @Fileidfrom

    @fileidto

    @loaddatefrom

    @loaddateto

    and i need to do the below:

    If(@loaddatefrom is null AND @loaddateto IS NULL)
    begin
    SELECT * from Contents_ALL_View where FileID BETWEEN @fileidFROM AND @FILEIDTO

    end

    else if (@FileIDFROM is null AND @FILEIDTO IS NULL)

    begin

    select * from Contents_ALL_View where convert(date,Load_Date_Time) between @loaddatefrom AND @loaddateto

    thats is..but when i do this, it keep give me error that the parameters already defined

    appreciate your support

    Wednesday, February 28, 2018 9:56 AM

Answers

  • you dont need if else condition at all

    all you need is just this query and a single dataset

    SELECT BL.Bank,BL.ICA,f.File_Name,f.Load_Date_Time,f.Settlement_Date,fcv.[FileID],[Switch_Serial_Number],[Acquirer_Issuer] ,[Processor_ID] ,[Transaction_Date_Time],[Transaction_Date_Time_String],LEFT(pan,6) as BIN ,[PAN]    ,[Processing_Code],[Trace_Number],[MCC],[POS_Entry_Mode],[Reference_Number],[Acquirer_ID],[Terminal_ID],[Response_Code]
    ,[Product_Code],[Advice_Reason_Code],[ISIS_Agreement_Code],[Authorization_ID],[Transaction_Currency_Code],[Transaction_Implied_Decimal] ,[Local_Completed_Amount],[Completed_Amount_DB_CR],[Local_Cash_Back_Amount],[Cash_Back_Amount_DB_CR] ,[Local_Fees],[Local_Fees_DB_CR],[Settlement_Currency_Code],[Settlement_Implied_Decimal]      ,[Settlement_Conversion_Rate] ,[Settlement_Completed_Amount],[Settlement_Completed_Amount_DB_CR],[Interchange_Fees]      ,[Interchange_Fees_DB_CR],[Switch_Fees_Currency_Code],[Switch_Fee_Implied_Decimal],[Switch_Fees],[Switch_Fees_DB_CR]   ,[Positive_ID_Indicator],[Cross_Border_Indicator],[Cross_Border_Currency_Indicator],[Visa_International_Service_Assessment_Fee_Indicator]
    FROM [MasterCardSettlement].[dbo].[T464_FIle_Contents_ALL_View] FCV  JOIN [MasterCardSettlement].[dbo].[T464_Files] F
      ON FCV.FileID=f.ID  LEFT OUTER JOIN [MasterCardSettlement].[dbo].[BIN_LIST] BL  ON BL.BIN=LEFT(FCV.PAN,6)
    where (FileID BETWEEN @_fileidFROM AND @_FILEIDTO 
    OR (@_fileidFROM IS NULL AND @_FILEIDTO IS NULL))
    AND (Convert(date,Load_Date_time) BETWEEN @_START_LOAD_DATE AND @_END_LOAD_DATE
    OR (@_START_LOAD_DATE IS NULL AND @_END_LOAD_DATE IS NULL))
    AND Acquirer_Issuer='A'
    ORDER BY BL.Bank


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by EL-ABD Tuesday, March 6, 2018 8:35 AM
    Wednesday, February 28, 2018 1:03 PM
  • Dear Visakh16,

    but the where condition doesn't meet my requirment, as i need to do this scenario,

    if i entered the fileidfrom and fileidto only without the other parameteres it gives me the required result and so on if i entered only both parameteres (startloaddate and endloaddate) without the other parameteres , then it should give me data.

    parameters should be optional

    Option A: 

    FileID BETWEEN @_fileidFROM AND @_FILEIDTO

    Option B:

    Convert(date,Load_Date_time) BETWEEN @_START_LOAD_DATE AND @_END_LOAD_DATE

    It is optional

    please refer to the OR conditions

    It causes those paramaters to get ignored if they're NULL

    where (FileID BETWEEN @_fileidFROM AND @_FILEIDTO 
    OR (@_fileidFROM IS NULL AND @_FILEIDTO IS NULL))
    AND (Convert(date,Load_Date_time) BETWEEN @_START_LOAD_DATE AND @_END_LOAD_DATE
    OR (@_START_LOAD_DATE IS NULL AND @_END_LOAD_DATE IS NULL))
    AND Acquirer_Issuer='A'
    ORDER BY BL.Bank

    Only when you pass them some valid values, parameters will be applied for filtering


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by EL-ABD Tuesday, March 6, 2018 8:35 AM
    Wednesday, February 28, 2018 1:17 PM
  • Hi EL_ABD,

    You could try to use CONVERT(VARCHAR(10),Load_Date_time , 111) in your above query to change your datetime in table. You could refer to this link for details.

    Best Regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by EL-ABD Tuesday, March 6, 2018 8:35 AM
    Thursday, March 1, 2018 1:58 AM

All replies

  • Can you please post some screenshots ? Not exactly getting where you are getting the error.

    -------------------------------------------------------------------
    Please Mark This As Answer if it solved your issue 
    Please Vote This As Helpful if it helps to solve your issue so that other forum members can benefit from it

    Yuvraj Singh Bais
    -------------------------------------------------------------------

    Wednesday, February 28, 2018 9:59 AM
  • I have a report that i need to publish but i need your support since i have issues, my case as below

    i have 4 parameters as below:

    @Fileidfrom

    @fileidto

    @loaddatefrom

    @loaddateto

    and i need to do the below:

    If(@loaddatefrom is null AND @loaddateto IS NULL)
    begin
    SELECT * from Contents_ALL_View where FileID BETWEEN @fileidFROM AND @FILEIDTO

    end

    else if (@FileIDFROM is null AND @FILEIDTO IS NULL)

    begin

    select * from Contents_ALL_View where convert(date,Load_Date_Time) between @loaddatefrom AND @loaddateto

    thats is..but when i do this, it keep give me error that the parameters already defined

    appreciate your support

    post your full code

    It seems like you're declaring the parameters again inside the procedure. This is not required as parameters will already be defined in the procedure definition


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, February 28, 2018 10:18 AM

  • If(@_START_Load_Date is null AND @_END_LOAD_DATE IS NULL)
    begin
    SELECT BL.Bank,BL.ICA,f.File_Name,f.Load_Date_Time,f.Settlement_Date,fcv.[FileID],[Switch_Serial_Number],[Acquirer_Issuer] ,[Processor_ID] ,[Transaction_Date_Time],[Transaction_Date_Time_String],LEFT(pan,6) as BIN ,[PAN]    ,[Processing_Code],[Trace_Number],[MCC],[POS_Entry_Mode],[Reference_Number],[Acquirer_ID],[Terminal_ID],[Response_Code]
    ,[Product_Code],[Advice_Reason_Code],[ISIS_Agreement_Code],[Authorization_ID],[Transaction_Currency_Code],[Transaction_Implied_Decimal] ,[Local_Completed_Amount],[Completed_Amount_DB_CR],[Local_Cash_Back_Amount],[Cash_Back_Amount_DB_CR] ,[Local_Fees],[Local_Fees_DB_CR],[Settlement_Currency_Code],[Settlement_Implied_Decimal]      ,[Settlement_Conversion_Rate] ,[Settlement_Completed_Amount],[Settlement_Completed_Amount_DB_CR],[Interchange_Fees]      ,[Interchange_Fees_DB_CR],[Switch_Fees_Currency_Code],[Switch_Fee_Implied_Decimal],[Switch_Fees],[Switch_Fees_DB_CR]   ,[Positive_ID_Indicator],[Cross_Border_Indicator],[Cross_Border_Currency_Indicator],[Visa_International_Service_Assessment_Fee_Indicator]
    FROM [MasterCardSettlement].[dbo].[T464_FIle_Contents_ALL_View] FCV  JOIN [MasterCardSettlement].[dbo].[T464_Files] F
      ON FCV.FileID=f.ID  LEFT OUTER JOIN [MasterCardSettlement].[dbo].[BIN_LIST] BL  ON BL.BIN=LEFT(FCV.PAN,6)
    where FileID BETWEEN @_fileidFROM AND @_FILEIDTO AND Acquirer_Issuer='A'
    ORDER BY BL.Bank
    end

    else if (@_FileIDFROM is null AND @_FILEIDTO IS NULL)
    BEGIN
    SELECT BL.Bank,BL.ICA,f.File_Name,f.Load_Date_Time,f.Settlement_Date,fcv.[FileID],[Switch_Serial_Number],[Acquirer_Issuer] ,[Processor_ID],[Transaction_Date_Time],[Transaction_Date_Time_String],LEFT(pan,6) as BIN,[PAN]      ,[Processing_Code] ,[Trace_Number],[MCC],[POS_Entry_Mode],[Reference_Number],[Acquirer_ID],[Terminal_ID],[Response_Code],[Product_Code],[Advice_Reason_Code],[ISIS_Agreement_Code],[Authorization_ID],[Transaction_Currency_Code],[Transaction_Implied_Decimal],[Local_Completed_Amount],[Completed_Amount_DB_CR],[Local_Cash_Back_Amount],[Cash_Back_Amount_DB_CR],[Local_Fees],[Local_Fees_DB_CR],[Settlement_Currency_Code],[Settlement_Implied_Decimal]      ,[Settlement_Conversion_Rate],[Settlement_Completed_Amount],[Settlement_Completed_Amount_DB_CR],[Interchange_Fees]   ,[Interchange_Fees_DB_CR],[Switch_Fees_Currency_Code],[Switch_Fee_Implied_Decimal],[Switch_Fees],[Switch_Fees_DB_CR]  ,[Positive_ID_Indicator],[Cross_Border_Indicator],[Cross_Border_Currency_Indicator],[Visa_International_Service_Assessment_Fee_Indicator]
    FROM [MasterCardSettlement].[dbo].[T464_FIle_Contents_ALL_View] FCV  JOIN [MasterCardSettlement].[dbo].[T464_Files] F
      ON FCV.FileID=f.ID  LEFT OUTER JOIN [MasterCardSettlement].[dbo].[BIN_LIST] BL  ON BL.BIN=LEFT(FCV.PAN,6)
    where convert(date,Load_Date_Time) between @_START_LOAD_DATE AND @_END_LOAD_DATE AND Acquirer_Issuer='A'
    ORDER BY BL.Bank
    end
    Wednesday, February 28, 2018 12:51 PM
  • i tried to create two datasets one for each query , but i need to do the following, when i enter the fileidfrom and fileidto, then go to execute dataset1 

    when i enter @_START_LOAD_DATE AND @_END_LOAD_DATE then it run dataset2

    how can i do that

    Wednesday, February 28, 2018 12:53 PM
  • you dont need if else condition at all

    all you need is just this query and a single dataset

    SELECT BL.Bank,BL.ICA,f.File_Name,f.Load_Date_Time,f.Settlement_Date,fcv.[FileID],[Switch_Serial_Number],[Acquirer_Issuer] ,[Processor_ID] ,[Transaction_Date_Time],[Transaction_Date_Time_String],LEFT(pan,6) as BIN ,[PAN]    ,[Processing_Code],[Trace_Number],[MCC],[POS_Entry_Mode],[Reference_Number],[Acquirer_ID],[Terminal_ID],[Response_Code]
    ,[Product_Code],[Advice_Reason_Code],[ISIS_Agreement_Code],[Authorization_ID],[Transaction_Currency_Code],[Transaction_Implied_Decimal] ,[Local_Completed_Amount],[Completed_Amount_DB_CR],[Local_Cash_Back_Amount],[Cash_Back_Amount_DB_CR] ,[Local_Fees],[Local_Fees_DB_CR],[Settlement_Currency_Code],[Settlement_Implied_Decimal]      ,[Settlement_Conversion_Rate] ,[Settlement_Completed_Amount],[Settlement_Completed_Amount_DB_CR],[Interchange_Fees]      ,[Interchange_Fees_DB_CR],[Switch_Fees_Currency_Code],[Switch_Fee_Implied_Decimal],[Switch_Fees],[Switch_Fees_DB_CR]   ,[Positive_ID_Indicator],[Cross_Border_Indicator],[Cross_Border_Currency_Indicator],[Visa_International_Service_Assessment_Fee_Indicator]
    FROM [MasterCardSettlement].[dbo].[T464_FIle_Contents_ALL_View] FCV  JOIN [MasterCardSettlement].[dbo].[T464_Files] F
      ON FCV.FileID=f.ID  LEFT OUTER JOIN [MasterCardSettlement].[dbo].[BIN_LIST] BL  ON BL.BIN=LEFT(FCV.PAN,6)
    where (FileID BETWEEN @_fileidFROM AND @_FILEIDTO 
    OR (@_fileidFROM IS NULL AND @_FILEIDTO IS NULL))
    AND (Convert(date,Load_Date_time) BETWEEN @_START_LOAD_DATE AND @_END_LOAD_DATE
    OR (@_START_LOAD_DATE IS NULL AND @_END_LOAD_DATE IS NULL))
    AND Acquirer_Issuer='A'
    ORDER BY BL.Bank


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by EL-ABD Tuesday, March 6, 2018 8:35 AM
    Wednesday, February 28, 2018 1:03 PM
  • Dear Visakh16,

    but the where condition doesn't meet my requirment, as i need to do this scenario,

    if i entered the fileidfrom and fileidto only without the other parameteres it gives me the required result and so on if i entered only both parameteres (startloaddate and endloaddate) without the other parameteres , then it should give me data.

    parameters should be optional

    Option A: 

    FileID BETWEEN @_fileidFROM AND @_FILEIDTO

    Option B:

    Convert(date,Load_Date_time) BETWEEN @_START_LOAD_DATE AND @_END_LOAD_DATE

    Wednesday, February 28, 2018 1:15 PM
  • Dear Visakh16,

    but the where condition doesn't meet my requirment, as i need to do this scenario,

    if i entered the fileidfrom and fileidto only without the other parameteres it gives me the required result and so on if i entered only both parameteres (startloaddate and endloaddate) without the other parameteres , then it should give me data.

    parameters should be optional

    Option A: 

    FileID BETWEEN @_fileidFROM AND @_FILEIDTO

    Option B:

    Convert(date,Load_Date_time) BETWEEN @_START_LOAD_DATE AND @_END_LOAD_DATE

    It is optional

    please refer to the OR conditions

    It causes those paramaters to get ignored if they're NULL

    where (FileID BETWEEN @_fileidFROM AND @_FILEIDTO 
    OR (@_fileidFROM IS NULL AND @_FILEIDTO IS NULL))
    AND (Convert(date,Load_Date_time) BETWEEN @_START_LOAD_DATE AND @_END_LOAD_DATE
    OR (@_START_LOAD_DATE IS NULL AND @_END_LOAD_DATE IS NULL))
    AND Acquirer_Issuer='A'
    ORDER BY BL.Bank

    Only when you pass them some valid values, parameters will be applied for filtering


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by EL-ABD Tuesday, March 6, 2018 8:35 AM
    Wednesday, February 28, 2018 1:17 PM
  • i tried your query but when i select fileid from and fileid to it retrieve data, but when i select start load date and end load date , it doesn't retrieve any data

    Wednesday, February 28, 2018 1:41 PM
  • i tried your query but when i select fileid from and fileid to it retrieve data, but when i select start load date and end load date , it doesn't retrieve any data

    May be because you dont have any data within the period

    I can only guess as I dont know your data , neither can see it because you didnt post it yet

    Does date values have timepart too?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Wednesday, February 28, 2018 1:49 PM
    Wednesday, February 28, 2018 1:49 PM
  • yes, the table has column load date time with this format

    7/2/2014 9:30:55 AM


    • Edited by EL-ABD Wednesday, February 28, 2018 2:09 PM
    Wednesday, February 28, 2018 1:57 PM
  • could you please advise the right date format to set in 
    START_LOAD_DATE and END_LOAD_DATE
    Wednesday, February 28, 2018 2:49 PM
  • Hi EL_ABD,

    You could try to use CONVERT(VARCHAR(10),Load_Date_time , 111) in your above query to change your datetime in table. You could refer to this link for details.

    Best Regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by EL-ABD Tuesday, March 6, 2018 8:35 AM
    Thursday, March 1, 2018 1:58 AM