Databases
StackOverflow2013
Postsdbo
POSQL Server: Using Case in FUNCTION
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server: Using Case in FUNCTION
    primarykey
    data
    text
    <p>New to SQL, seasoned VBA-er in need of some assistance.</p> <p>I have a View on transactions that requires to get some different fees of specific types. The fees are in a table where some fields are required and others might be NULL. This way we can get a general fee structure set up and still allow for specific specials.</p> <p>My goal now is to find the best matching fee. This is the fee which:</p> <ol> <li>Matches all required criteria, </li> <li>Matches all optional criteria OR has <code>NULL</code> for them, </li> <li>Has the most matching optional criteria of all fees that pass 1. and 2.</li> </ol> <p>Now I got a version of this code working but as a PROCEDURE. However as I need this to be used in a view I can't use that version and there are no temp tables allowed in a function).</p> <p>After looking on this forum and others I came to a result that still gives 2 errors:<br> 1. ErrorMessage: Incorrect syntax near <code>'@tblTEMP'</code>,<br> 2. ErrorMessage: Incorrect syntax near <code>'BEGIN'</code>.</p> <p>My current script is as follows (using a local variable table):</p> <pre><code>ALTER FUNCTION [dbo].[fnStandardFeeBAK] (@Type_Id BIGINT, @Party_Id BIGINT, @I_A_Id BIGINT, @Grid_Id BIGINT, @Market_Id BIGINT, @Counterparty_Id BIGINT, @Product_Id BIGINT, @DealDate DATETIME) RETURNS TABLE AS BEGIN DECLARE @tblTEMP TABLE (Standard_Fee DECIMAL(38,8), Currency VARCHAR(50), Unit VARCHAR(50), Unit2 VARCHAR(50), MatchScore BIGINT); WITH @tblTEMP AS (SELECT Standard_Fee, V2.Element AS Currency, V1.Element AS Unit, V2.Element+'/'+V1.Element AS Unit2, SF.I_A_Id, SF.Grid_Id, SF.Product_Id, SF.Counterparty_Id, (CASE WHEN SF.I_A_Id = @I_A_Id THEN 1 ELSE 0 END + CASE WHEN SF.Grid_Id = @Grid_Id THEN 1 ELSE 0 END + CASE WHEN SF.Product_Id = @Product_Id THEN 1 ELSE 0 END + CASE WHEN SF.Counterparty_Id = @Counterparty_Id THEN 1 ELSE 0 END) AS MatchScore FROM tblStandard_Fee AS SF LEFT JOIN tblElement AS V1 ON V1.Element_Id = SF.Unit_Id LEFT JOIN tblElement AS V2 ON V2.Element_Id = SF.Currency_Id WHERE SF.Type_Id = @Type_Id AND SF.Party_Id = @Party_Id AND SF.Market_Id = @Market_Id AND SF.Date_From &lt; @DealDate AND (SF.Date_To &gt; @DealDate OR SF.Date_To IS NULL) AND (SF.I_A_Id = @I_A_Id OR SF.I_A_Id IS NULL) AND (SF.Grid_Id = @Grid_Id OR SF.Grid_Id IS NULL) AND (SF.Product_Id = @Product_Id OR SF.Product_Id IS NULL) AND (SF.Counterparty_Id = @Counterparty_Id OR SF.Counterparty_Id IS NULL)) RETURN SELECT Standard_Fee, Currency, Unit, Unit2 FROM @tblTEMP WHERE MatchScore= MAX(MatchScore) END </code></pre> <p>I hope my problem is clear if not let me know.</p> <p>Thank you for making suggestions or even solving my issue with this last bit!</p> <hr> <p>So all of LittleBobbyTables' help combined gave me a working end result. Please dont forget to give him credits for this.</p> <p>Working code:</p> <pre><code>ALTER FUNCTION [dbo].[fnStandardFeeBAK] (@Type_Id BIGINT, @Party_Id BIGINT, @I_A_Id BIGINT, @Grid_Id BIGINT, @Market_Id BIGINT, @Counterparty_Id BIGINT, @Product_Id BIGINT, @DealDate DATETIME) RETURNS @YourTable TABLE ( -- Columns returned by the function Standard_Fee DECIMAL(38,8), Currency VARCHAR(50), Unit VARCHAR(50), Unit2 VARCHAR(50) ) AS BEGIN WITH YourCTE(Standard_Fee, Currency, Unit, Unit2, MatchScore) AS ( SELECT Standard_Fee, V2.Element AS Currency, V1.Element AS Unit, V2.Element + '/' + V1.Element AS Unit2, (CASE WHEN SF.I_A_Id = @I_A_Id THEN 1 ELSE 0 END + CASE WHEN SF.Grid_Id = @Grid_Id THEN 1 ELSE 0 END + CASE WHEN SF.Product_Id = @Product_Id THEN 1 ELSE 0 END + CASE WHEN SF.Counterparty_Id = @Counterparty_Id THEN 1 ELSE 0 END) AS MatchScore FROM tblStandard_Fee AS SF LEFT JOIN tblElement AS V1 ON V1.Element_Id = SF.Unit_Id LEFT JOIN tblElement AS V2 ON V2.Element_Id = SF.Currency_Id --Type_ID, Party_ID and Market_Id are always present, others can have NULL values WHERE SF.Type_Id = @Type_Id AND SF.Party_Id = @Party_Id AND SF.Market_Id = @Market_Id AND SF.Date_From &lt; @DealDate AND (SF.Date_To &gt; @DealDate OR SF.Date_To IS NULL) AND (SF.I_A_Id = @I_A_Id OR SF.I_A_Id IS NULL) AND (SF.Grid_Id = @Grid_Id OR SF.Grid_Id IS NULL) AND (SF.Product_Id = @Product_Id OR SF.Product_Id IS NULL) AND (SF.Counterparty_Id = @Counterparty_Id OR SF.Counterparty_Id IS NULL) GROUP BY Standard_Fee, V1.Element, V2.Element, SF.I_A_Id, SF.Grid_Id, SF.Product_Id, SF.Counterparty_Id ) INSERT @YourTable SELECT Standard_Fee, Currency, Unit, Unit2 FROM YourCTE GROUP BY MatchScore, Standard_Fee, Currency, Unit, Unit2 HAVING MatchScore = (SELECT MAX(MatchScore) FROM YourCTE) RETURN; END </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USK_B
    1. USK_B
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Server: Using Case in FUNCTION
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQL Server: Using Case in FUNCTION
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSQL Server: Using Case in FUNCTION
      1. This table or related slice is empty.
      1. VTDownMod
    1. COI now see I have `SF.I_A_Id, SF.Grid_Id, SF.Product_Id, SF.Counterparty_Id,` still in my code in the first select statement. However even taking that out will not get rid of the error
      singulars
      1. POSQL Server: Using Case in FUNCTION
      1. USK_B
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload