Case When STatement

Question

Hi

I have below code . I want if A1.Invtype = 18 then alias A0 should be PCH1 , if A1.Invtype = 19 then alias A0 should be RPC1

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 PCH1 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"

Thanks

Tuhin Paul · Answer

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: Books : Stores information about books ( BookID , Title , Author , LocationCode , etc.). 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 Dynamic Table Selection: The CASE statement determines the table name ( Books or Journals ) based on the ResourceType column in the Resources table. Dynamic SQL Construction: The @SQL variable constructs the query dynamically, selecting the LocationCode from the appropriate table ( Books or Journals ). 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. 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 .

Tuhin Paul · Answer

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

Prasad Raveendran · Answer

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";

Ramco Ramco · Answer

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

Jaish Mathews · Answer

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: Dynamic Table Reference: A CASE statement within the subquery determines whether to use PCH1 or RPC1 based on A1."Invtype" . 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 . Nested Queries: The nested query ensures that the appropriate table is joined based on the condition. 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.