Sql - Return a Default Value When My Search Returns No Results Along With Search Criteria

How to set a default row for a query that returns no rows?

One approach for Oracle:

SELECT val
FROM myTable
UNION ALL
SELECT 'DEFAULT'
FROM dual
WHERE NOT EXISTS (SELECT * FROM myTable)

Or alternatively in Oracle:

SELECT NVL(MIN(val), 'DEFAULT')
FROM myTable

Or alternatively in SqlServer:

SELECT ISNULL(MIN(val), 'DEFAULT')
FROM myTable

These use the fact that MIN() returns NULL when there are no rows.

Return default value if no results found in SQL query

While this would probably be easier to handle with your scripting language, you could do a union all to get the default value since you're ordering by the id field:

SELECT id, picture 
FROM media 
WHERE userID = $friend->user_id 
    AND relation = 'profile_picture' 
UNION ALL 
SELECT -1 id, 'default.png' picture
ORDER BY id DESC LIMIT 1

• SQL Fiddle Demo

How to return default value from SQL query

Assuming the name is not nullable and that Id is unique so can match at most one row.

 SELECT 
    ISNULL(MAX(Name),'John Doe')
 FROM 
    Users 
 WHERE 
    Id = @UserId  

MYSQL search returns all results in the table no matter what is searched

May be it cannot identifies the format specifier properly as it is within multiple % sign. Use concatenation operator.

$query = $wpdb->prepare("SELECT * FROM mytable name WHERE columnname LIKE '%%' . $search . '%%'");

SQL query select return result not in ONE table

You can use the SQL IN operator, selecting the values already in the table;

SELECT * FROM people WHERE name IN (...your values...)

and then finding the difference between the two lists:

not_in_db = list(set(names).difference(set(results)))

T-SQL Retrieve rows with default values on records that don't meet search criteria

To simplify the answer, I assume your result (in that big SELECT) can be in a table (for example, you can use CTE to enclose your SELECT query). With that in mind, here is the result (treat #s as your big SELECT result)

create table #s ([Count] int, [Value] int, [BuyerID] varchar(10))

insert into #s values (1, 250, 'Person1'), (1, 285, 'Person2'), (1, 1560, 'Person3')


select [UnconfirmedCount]=(isnull(s.[count],0)+ t.[count]) 
, [UnconfirmedValue]=isnull(s.[value],0)+t.[value],
, buyerID = case when s.BuyerID is not null then s.BuyerID else t.BuyerID end 
from #s s
right join (values (0, 0, 'Person1'), (0, 0, 'Person2'), (0, 0, 'Person3'), (0, 0, 'Person4')) T([Count], [Value], [BuyerID] ) 
on s.BuyerID = t.BuyerID

If you want to re-write your query, it should be like the following (sorry, I did not build table to test it, but you can try yourself)

; with s ([Count], [Value], buyerID) as
(
SELECT COUNT(DISTINCT h.PONum) AS unconfirmedCount,
   ISNULL(SUM(d.DocExtCost), 0) AS unconfirmedValue,
    CASE v.Buyer_c
        WHEN 'Person5' THEN 'Person4'
        ELSE v.Buyer_c
     END AS buyerID    

FROM [Dbo].POHeader AS h

INNER JOIN [Dbo].PODetail AS d
        ON (h.Company = d.Company AND 
            h.PONum = d.PONum)

INNER JOIN [Dbo].Vendor AS v
        ON (h.Company = v.Company AND 
            h.VendorNum = v.VendorNum) 

WHERE h.FirstPublishedToPortal_c < DATEADD(HOUR, 24, GETDATE())
     AND v.Buyer_c IN ('Person1', 'Person2', 'Person3', 'Person4', 'Person5')
     AND h.ReadyForSupplierPortal_c = 1
     AND ( h.Confirmed = 0 OR h.Confirmed IS NULL )

 GROUP BY CASE v.Buyer_c
             WHEN 'Person5' THEN 'Person4'
             ELSE v.Buyer_c
          END             
)
select [UnconfirmedCount]=(isnull(s.[count],0)+ t.[count]), [UnconfirmedValue]=isnull(s.[value],0)+t.[value]
, buyerID = case when s.BuyerID is not null then s.BuyerID else t.BuyerID end 
from  s
right join (values (0, 0, 'Person1'), (0, 0, 'Person2'), (0, 0, 'Person3'), (0, 0, 'Person4')) T([Count], [Value], [BuyerID] ) 
on s.BuyerID = t.BuyerID

Oracle: How do I grab a default value when a more specific value is null from within the same query?

Use this,

select
  nvl(
    (SELECT val FROM k_v WHERE user_id = 123 AND k = 'FAVORITE_COLOR'),
    (SELECT val FROM k_v WHERE user_id = 0 AND k = 'FAVORITE_COLOR')
  ) val 
from dual
;

From the Oracle docs,

NVL(expr1, expr2):
NVL lets you replace null (returned as a blank) with a string in the results of a query. If
expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.

---

Related Topics