Query Value to be stored in Variable

Try below query. You can declare variable to store the result yes or no. You can use IF EXISTS condition to check if the query returns records and then set the variable accordingly.

DECLARE @Result VARCHAR(3);  -- Variable to hold 'Yes' or 'No'

IF EXISTS (
    SELECT 1 
    FROM OINV T0
    INNER JOIN CRD1 T3 ON T0."CardCode" = T3."CardCode"
    INNER JOIN INV12 T5 ON T5."DocEntry" = T0."DocEntry"
    LEFT JOIN "LIVE"."@EINV" T4 ON T0."DocEntry" = T4."U_UTL_BaseEntry"
    WHERE T0."CardCode" = T3."CardCode" 
      AND T3."GSTRegnNo" <> ''
      AND T0."DocEntry" NOT IN (
          SELECT A4."U_UTL_BaseEntry" 
          FROM "LIVE"."@EINV" A4 
          WHERE A4."U_IST" <> 'F'
      )
)
BEGIN
    SET @Result = 'Yes';
END
ELSE
BEGIN
    SET @Result = 'No';
END

SELECT @Result AS RecordExists;

You can achieve your requirement by using a SQL query with a conditional check. You can use a CASE statement to set a variable to "No" if no record exists or "Yes" otherwise.

Here's how you can modify your SQL query:

DECLARE @Result NVARCHAR(10);

SELECT @Result = CASE 
    WHEN EXISTS (
        SELECT 1 
        FROM OINV T0
        INNER JOIN CRD1 T3 ON T0."CardCode" = T3."CardCode"
        INNER JOIN INV12 T5 ON T5."DocEntry" = T0."DocEntry"
        LEFT JOIN "LIVE"."@EINV" T4 ON T0."DocEntry" = T4."U_UTL_BaseEntry"
        WHERE T3."GSTRegnNo" <> ''
          AND T0."DocEntry" NOT IN (
              SELECT A4."U_UTL_BaseEntry" 
              FROM "LIVE"."@EINV" A4 
              WHERE A4."U_IST" <> 'F'
          )
    ) THEN 'Yes'
    ELSE 'No'
END;

-- Output the result
SELECT @Result AS Result;

Explanation:

1. EXISTS Clause:

   • Checks if the subquery returns any rows.
   • The subquery is your original query, checking for the records matching your criteria.

2. CASE Statement:

   • If the EXISTS condition evaluates to TRUE, the variable @Result is set to "Yes".
   • Otherwise, it is set to "No".

3. @Result Variable:

   • A variable declared to store the result of the condition.

4. Output:

   • At the end, the value of @Result is selected to display whether records exist or not.

Adjustments:

• Replace DECLARE with the appropriate syntax for your SQL environment if required (e.g., in PL/SQL or T-SQL).
• Ensure table names and schema are correctly referenced as per your database setup.

Hello Ramco,

You can declare one variable and assign values to that variable based on your query result,

DECLARE @IsDocEntryExists NVARCHAR(3);

IF EXISTS (
    SELECT 1
    FROM OINV T0
    INNER JOIN CRD1 T3 ON T0."CardCode" = T3."CardCode"
    INNER JOIN INV12 T5 ON T5."DocEntry" = T0."DocEntry"
    LEFT JOIN "LIVE"."@EINV" T4 ON T0."DocEntry" = T4."U_UTL_BaseEntry"
    WHERE T0."CardCode" = T3."CardCode"
      AND T3."GSTRegnNo" <> ''
      AND T0."DocEntry" NOT IN (
          SELECT A4."U_UTL_BaseEntry"
          FROM "LIVE"."@EINV" A4
          WHERE A4."U_IST" <> 'F'
      )
)
BEGIN
    SET @IsDocEntryExists = 'Yes';
END
ELSE
BEGIN
    SET @IsDocEntryExists = 'No';
END;

SELECT @IsDocEntryExists AS Result;