-2

I am trying to do a HTML table but i am having troubles where it does not return a value. This example shows my result.Sample Image

Is it possible to script that if there isnt any result, it will return 0?

I tried ISNULL method and it does not return me any values.

I am expecting that by using the ISNULL method, it will return a 0 in the result, but it doesn't do so.

SELECT
    ISNULL(body, 0) 
FROM
    [EMAIL_MATRIX] 
WHERE
    body NOT IN (SELECT c.Body
                 FROM [dbo].[EMAIL_DETAILS] a
                 INNER JOIN [Email_matrix] c ON SUBSTRING(a.Subject, 5, LEN(a.Subject) - 4) = c.Subject
                                             AND a.Body LIKE CONCAT('%', c.body, '%') 
                 WHERE CAST(a.Send AS DAte) = '2022-10-04')
Dale K
  • 25,246
  • 15
  • 42
  • 71
Rickyc
  • 11
  • 2
  • we can't possibly answer this question without seeing the sql you are using – Hogan Mar 30 '23 at 03:46
  • hi @Hogan I have added the code. Sorry as I am a first time user – Rickyc Mar 30 '23 at 04:45
  • Use standard SQL: `SELECT COALESCE(body, '0')`. body is clearly some kind of text field, so you should return a text value, to replace a `NULL`. However, your output is showing no rows at all. That strongly suggests, that there are no values that match your `WHERE`. I advise you to run the inner SQL - `SELECT c.body` etc separately to check the values returned. – Jonathan Willcock Mar 30 '23 at 05:17
  • FYI -- the ISNULL in your query does nothing, there is no case that field will be null, but your problem is there are no rows returned, @gotqn shows you how to solve that in his answer. – Hogan Mar 30 '23 at 14:51
  • Hi @Hogan, I tried gotqn solution but it still returns me a NULL value. – Rickyc Apr 12 '23 at 07:38
  • I didn't post a solution. Do you mean gotqn's solution? That solution is supposed to return a null value. What results are you expecting? Please update your question to clearer about what you expect. – Hogan Apr 12 '23 at 11:46

2 Answers2

2

Try this:

WITH DataSource AS
(
    Select ISNULL(body,0) AS body
    from [EMAIL_MATRIX] 
    where body NOT IN
    (
        SELECT c.Body
        FROM [dbo].[EMAIL_DETAILS] a
        INNER JOIN [Email_matrix] c 
            ON SUBSTRING(a.Subject,5,LEN(a.Subject)-4) = c.Subject
            and a.Body LIKE CONCAT('%', c.body, '%') 
        where Cast(a.Send as DAte) = '2022-10-04'
    )
)
SELECT body
FROM DataSource
WHERE EXISTS(SELECT 1 FROM DataSource)
UNION ALL
SELECT NULL
WHERE NOT EXISTS(SELECT 1 FROM DataSource)
gotqn
  • 42,737
  • 46
  • 157
  • 243
0

Try this,

Select REPLACE(ISNULL(body,0),'',0) from [EMAIL_MATRIX] 
where body NOT IN(
SELECT c.Body
FROM [dbo].[EMAIL_DETAILS] a
INNER JOIN [Email_matrix] c 
ON SUBSTRING(a.Subject,5,LEN(a.Subject)-4) = c.Subject
 and a.Body LIKE CONCAT('%', c.body, '%') 
where Cast(a.Send as DAte) = '2022-10-04')
milindgv94
  • 61
  • 1