Case When STatement

Please check whether this would help
 

WITH CombinedLocCodes AS (
    SELECT "LocCode", "DocEntry"
    FROM PCH1
    WHERE EXISTS (
        SELECT 1 
        FROM VPM2 A1 
        WHERE A1."Invtype" = 18 AND PCH1."DocEntry" = A1."DocEntry"
    )
    UNION ALL
    SELECT "LocCode", "DocEntry"
    FROM RPC1
    WHERE EXISTS (
        SELECT 1 
        FROM VPM2 A1 
        WHERE A1."Invtype" = 19 AND RPC1."DocEntry" = A1."DocEntry"
    )
)
SELECT 
    T1."TransType", 
    T1."Account", 
    SUM(T1."Debit") AS "Debit", 
    SUM(T1."Credit") AS "Credit", 
    CASE 
        WHEN T1."TransType" = '46' THEN (
            SELECT MAX(A0."LocCode") 
            FROM CombinedLocCodes A0
            INNER JOIN VPM2 A1 ON A0."DocEntry" = A1."DocEntry"
            INNER JOIN OVPM A2 ON A2."DocEntry" = T1."CreatedBy"
        )
        ELSE '0'
    END AS "Location"
FROM 
    OJDT T0  
INNER JOIN 
    JDT1 T1 ON T0."TransId" = T1."TransId"
GROUP BY 
    T1."TransType", T1."Account";

Previously I have worked on a system a Library Management System scenario where we needed to dynamically select a table based on the type of resource (e.g., Book or Journal) and retrieve relevant information. This is similar to your query, where the table name (PCH1 or RPC1) is dynamically selected based on A1.InvType. Also your query was designed to retrieve financial transaction data from a database, specifically focusing on debit and credit amounts, and optionally fetching a location code based on certain conditions. 

So talking about the scenario where I was in similar situation that you are now, in that library management system, we have two tables:

1. Books: Stores information about books (BookID, Title, Author, LocationCode, etc.).

2. Journals: Stores information about journals (JournalID, Title, Publisher, LocationCode, etc.).

We want to write a query that dynamically selects either the Books or Journals table based on the ResourceType (e.g., Book or Journal) and retrieves the LocationCode for a given resource.

Tables and Columns:

Resources Table (Resources):

ResourceID (Primary Key)

ResourceType (e.g., Book or Journal)

Title

Author

Publisher


Books Table (Books):

BookID (Primary Key)

ResourceID (Foreign Key to Resources)

LocationCode

Journals Table (Journals):

JournalID (Primary Key)

ResourceID (Foreign Key to Resources)

LocationCode

Dynamic SQL Query for Library Management System

DECLARE @SQL NVARCHAR(MAX);
DECLARE @TableName NVARCHAR(50);

-- Determine the table name based on ResourceType
SET @TableName = CASE
                    WHEN R."ResourceType" = 'Book' THEN 'Books'
                    WHEN R."ResourceType" = 'Journal' THEN 'Journals'
                    ELSE NULL
                 END;

-- Construct the dynamic SQL
IF @TableName IS NOT NULL
BEGIN
    SET @SQL = '
        SELECT
            R."ResourceID",
            R."Title",
            R."Author",
            R."Publisher",
            CASE
                WHEN R."ResourceType" = ''Book'' THEN
                    (SELECT B."LocationCode"
                     FROM Books B
                     WHERE B."ResourceID" = R."ResourceID")
                WHEN R."ResourceType" = ''Journal'' THEN
                    (SELECT J."LocationCode"
                     FROM Journals J
                     WHERE J."ResourceID" = R."ResourceID")
                ELSE ''Unknown''
            END AS "LocationCode"
        FROM
            Resources R
        WHERE
            R."ResourceID" = @ResourceID -- Replace with the desired ResourceID
    ';

    -- Execute the dynamic SQL
    EXEC sp_executesql @SQL, N'@ResourceID INT', @ResourceID = 123; -- Replace 123 with the desired ResourceID
END
ELSE
BEGIN
    -- Handle cases where ResourceType is neither 'Book' nor 'Journal'
    PRINT 'Invalid ResourceType';
END

1. Dynamic Table Selection:

   • The CASE statement determines the table name (Books or Journals) based on the ResourceType column in the Resources table.

2. Dynamic SQL Construction:

   • The @SQL variable constructs the query dynamically, selecting the LocationCode from the appropriate table (Books or Journals).

3. Execution:

   • The EXEC sp_executesql statement executes the dynamically constructed SQL query.

   • The @ResourceID parameter is passed to filter the results for a specific resource.

4. Handling Invalid ResourceType:

   • If the ResourceType is neither Book nor Journal, the query prints 'Invalid ResourceType'.

---

 

Resources Table

ResourceID	ResourceType	Title	Author	Publisher
1	Book	The Great Gatsby	F. Scott Fitzgerald	Scribner
2	Journal	Nature	Various	Springer Nature

Books Table

BookID	ResourceID	LocationCode
101	1	A1

Journals Table

JournalID	ResourceID	LocationCode
201	2	J1

---

 

Output for ResourceID = 1

ResourceID	Title	Author	Publisher	LocationCode
1	The Great Gatsby	F. Scott Fitzgerald	Scribner	A1

---

 

Output for ResourceID = 2

ResourceID	Title	Author	Publisher	LocationCode
2	Nature	Various	Springer Nature	J1

---

 

This approach dynamically selects the table (Books or Journals) based on the ResourceType.

To achieve the desired logic where the alias A0 is dynamically named PCH1 or RPC1 based on the value of A1.InvType, you can use dynamic SQL or conditional table selection in your query. SQL does not directly support dynamic aliases in a single query. Instead, you can use a CASE statement to conditionally select from the appropriate table (PCH1 or RPC1) based on A1.InvType

If your database supports dynamic SQL (e.g., in PL/SQL or T-SQL), you can construct the query dynamically based on the value of A1.InvType. 

DECLARE @SQL NVARCHAR(MAX);
DECLARE @TableName NVARCHAR(50);

-- Determine the table name based on A1.InvType
SET @TableName = CASE 
                    WHEN A1."InvType" = 18 THEN 'PCH1'
                    WHEN A1."InvType" = 19 THEN 'RPC1'
                    ELSE NULL
                 END;

-- Construct the dynamic SQL
IF @TableName IS NOT NULL
BEGIN
    SET @SQL = '
        SELECT 
            T1."TransType", 
            T1."Account", 
            SUM(T1."Debit") AS "Debit", 
            SUM(T1."Credit") AS "Credit",
            CASE
                WHEN T1."TransType" = ''46'' THEN
                    (SELECT MAX(A0."LocCode") 
                     FROM ' + @TableName + ' A0 
                     INNER JOIN VPM2 A1 ON A0."DocEntry" = A1."DocEntry"
                     INNER JOIN OVPM A2 ON A2."DocEntry" = T1."CreatedBy")
                ELSE ''0''
            END AS "Location"
        FROM 
            OJDT T0  
        INNER JOIN 
            JDT1 T1 ON T0."TransId" = T1."TransId"
        INNER JOIN 
            VPM2 A1 ON A1."DocEntry" = T1."CreatedBy"
    ';

    -- Execute the dynamic SQL
    EXEC sp_executesql @SQL;
END
ELSE
BEGIN
    -- Handle cases where A1.InvType is neither 18 nor 19
    PRINT 'Invalid InvType';
END

Hi Jaish

 It slows down the query when i write below code

WHEN T1."TransType" = '46' then (SELECT MAX(
                CASE 
                    WHEN A1."InvType" = 18 THEN (SELECT MAX(A0."LocCode") FROM PCH1 A0 WHERE A0."DocEntry" = A1."DocEntry")
                    WHEN A1."InvType" = 19 THEN (SELECT MAX(A0."LocCode") FROM RPC1 A0 WHERE A0."DocEntry" = A1."DocEntry")
                END
            )
            FROM VPM2 A1
            INNER JOIN OVPM A2 ON A2."DocEntry" = T1."CreatedBy")

Thanks

Try below.

You can modify your query to dynamically set the alias for the A0 table based on the value of A1."Invtype". To achieve this, use a conditional expression to determine which table to use in the subquery. Here's how you can rewrite your query:

SELECT 
    T1."TransType", 
    T1."Account",
    SUM(T1."Debit") AS "Debit", 
    SUM(T1."Credit") AS "Credit",
    CASE
        WHEN T1."TransType" = '46' THEN
            (SELECT MAX(
                CASE 
                    WHEN A1."Invtype" = 18 THEN (SELECT MAX(A0."LocCode") FROM PCH1 A0 WHERE A0."DocEntry" = A1."DocEntry")
                    WHEN A1."Invtype" = 19 THEN (SELECT MAX(A0."LocCode") FROM RPC1 A0 WHERE A0."DocEntry" = A1."DocEntry")
                END
            )
            FROM VPM2 A1
            INNER JOIN OVPM A2 ON A2."DocEntry" = T1."CreatedBy"
            )
        ELSE '0'
    END AS "Location"
FROM 
    OJDT T0  
INNER JOIN 
    JDT1 T1 
ON 
    T0."TransId" = T1."TransId"
GROUP BY 
    T1."TransType", T1."Account";

Explanation of the Changes:

1. Dynamic Table Reference:

   • A CASE statement within the subquery determines whether to use PCH1 or RPC1 based on A1."Invtype".

2. Subquery Logic:

   • If A1."Invtype" = 18, it selects the MAX(A0."LocCode") from PCH1.
   • If A1."Invtype" = 19, it selects the MAX(A0."LocCode") from RPC1.

3. Nested Queries:

   • The nested query ensures that the appropriate table is joined based on the condition.

4. Grouping and Aggregation:

   • Added GROUP BY for T1."TransType" and T1."Account" to work with aggregate functions like SUM().

This approach allows dynamic aliasing based on your condition (A1."Invtype") and ensures that the query produces the expected results.