Click here to Skip to main content
15,908,254 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
My code able to retrieve book code data that match with the barcode but not when there is no data match. I want it to print "Bookcode not found" in the cell also if there's no data found.

What I have tried:

I tried with one-by-one barcode in sql server. If I put only one barcode example '123' that do not have data then it managed to display 'Bookcode not found' but if I put 3 other barcodes that have data then the 'Bookcode not found' will not be included. I really appreciate your help.

WITH cte AS
(SELECT *,ROW_NUMBER() OVER(PARTITION BY Barcode ORDER BY InvtID Asc) rid 
FROM [Danny].[dbo].[InventoryCustomer] WHERE Barcode In ('ean','9789830093819'))
SELECT InvtID, BOOKCODE = coalesce(InvtID, 'Bookcode not found') 
FROM cte WHERE rid=1 UNION SELECT InvtID = '', BOOKCODE = 'Bookcode not found' 
WHERE NOT EXISTS(SELECT 1 FROM CTE) 
Posted
Updated 7-Feb-19 22:52pm
Comments
Santosh kumar Pithani 8-Feb-19 3:39am    
Any sample data and expected output?
Member 14127871 8-Feb-19 3:50am    
For example my script above will display like below IF only I put 1 barcode that do not have bookcode
InvtID | Bookcode
null | Bookcode not found

.. Below IF 1 code with no bookcode and 2 code have bookcode
InvtID | Bookcode
CB0212 | CB0212
DD2921 | DD2921

It skip the 'Bookcode not found' where the expected output I want as below

InvtID | Bookcode
null | Bookcode not found
CB0212 | CB0212
DD2921 | DD2921
Santosh kumar Pithani 8-Feb-19 4:05am    
Are you using SQL Server 2016?
Member 14127871 8-Feb-19 4:31am    
Sql server 2008
Member 14127871 10-Feb-19 22:55pm    
This are all the query I have tried but none of it will display 'no data' if no rows are found in sql. It basically just display existed data only

1. 'SELECT isnull((SELECT InvtID
FROM InventoryCustomer WHERE Barcode IN('123','9789830093819')),'No bookcode found')
AS InvtID '

2. 'Select case when s.InvtID IS NOT NULL Then s.InvtID else 'no data' end as Bookcode
from (Select InvtID as InvtID FROM InventoryCustomer WHERE Barcode IN('123','9789830093819')) R
Left Join InventoryCustomer s ON s.InvtID = R.InvtID'

3. 'IF NOT EXISTS (Select InvtID From InventoryCustomer WHERE Barcode in ('123','9789830093819'))
Begin SELECT 'Bookcode not found' as Bookcode end
ELSE
SELECT InvtID From InventoryCustomer WHERE Barcode in ('123','9789830093819')'

4. 'SELECT InvtID, CASE WHEN InvtID is null OR InvtID='' THEN 'no data'
ELSE InvtID
END AS Bookcode
FROM InventoryCustomer WHERE Barcode in ('123','9789830093819')'

5. 'WITH cte AS(SELECT case WHEN InvtID IS NULL OR InvtID='' THEN 'No Bookcode Found'
ELSE InvtID END AS InvtID,Barcode,ROW_NUMBER() OVER(PARTITION BY Barcode ORDER BY InvtID Asc)
rid FROM InventoryCustomer)
SELECT InvtID AS BOOKCODE FROM cte WHERE rid=1 and Barcode In ('123','9789830093819')'

Maciej solution will work, but ... this isn't really a data retrieval function, it's a presentation function and it would be both much better and more efficient to do this in your presentation language.

It's simple there: retrieve the rows, count them, and take the appropriate action. Doing it in SQL returns a variable number of columns, which is messy for presentation.

Maciej's solution will work, but it means effectively running the query twice, which is both inefficient and risks problems if the query needs to be modified.
 
Share this answer
 
Comments
Maciej Los 8-Feb-19 5:23am    
I was waiting for that... :) Thanks. It's most valuable comment posted in a solution.

What you think about below form?
 SELECT ISNULL(BookCode, 'Not found'), Field2, Field3, ...
FROM TableName
WHERE BookCode = '12345'
OriginalGriff 8-Feb-19 5:36am    
Returns no rows because the WHERE returns no rows, rather than a row of null values. You could do it with a JOIN, I think - but it's still a messy solution to a presentation problem.
MadMyche 8-Feb-19 7:31am    
The KEY term in this answer is it's a presentation function. The OP should run the query and if no rows are returned then you display the Not Found message.
Maciej Los 8-Feb-19 7:44am    
Totally agree.
Member 14127871 11-Feb-19 1:45am    
Can you explain more about presentation function?
In a short: you can use IF[^] + EXISTS[^]:

SQL
IF NOT EXISTS
(
    SELECT TOP 1 Barcode FROM [Danny].[dbo].[InventoryCustomer] WHERE Barcode In ('ean','9789830093819'))
) 
   SELECT 'Bookcode not found'
ELSE
    SELECT Your_Complex_Query_Here
END
 
Share this answer
 
v2
Comments
Member 14127871 10-Feb-19 23:07pm    
I tried this but still unable to display 'no data' if no rows are found in sql. I list all the queries I tried at the comment above
Maciej Los 11-Feb-19 2:55am    
Check updated answer.
Member 14127871 11-Feb-19 4:12am    
Doesnt works also. I already tried coalesce, isnull, if exist, case when, union, left join same results
Maciej Los 11-Feb-19 4:26am    
So, there's someting wrong with initial query...
Note that i have no access to your data and don't see your screen, so i can't help you without knowing very specific information.
SQL
--Actually Table records is filtered by values but you including filtered values as records.This solution is given based on output,i hope this helpful 

CREATE TABLE #InventoryCustomer(InvtID Varchar(20),Bookcode Varchar(20),BarCode Varchar(50)) 

INSERT INTO #InventoryCustomer
Values
      ('CB0212','CB0212','ean'),
      ('DD2921','DD2921','9789830093819');

--In 2008 server need to create split_function for splitting  values.

select 
  ISNULL(InvtID,'') AS InvtID
 ,ISNULL(Bookcode,'Bookcode not found') AS Bookcode 

   FROM split_fun('1,ean,9789830093819',',') AS Stv LEFT JOIN #InventoryCustomer cte   
       ON( Stv.value=Cte.Barcode)

OUTPUT:
-------------------
InvtID | Bookcode
--------------------
	Bookcode not found
CB0212	CB0212
DD2921	DD2921
 
Share this answer
 
Comments
CHill60 8-Feb-19 8:12am    
Better would be
select 
 ISNULL(InvtID,Stv.[value]) AS InvtID,
 ISNULL(Bookcode,'Bookcode not found') AS Bookcode 
FROM string_split('1,ean,9789830093819',',') AS Stv 
LEFT JOIN #InventoryCustomer cte   ON Stv.value=Cte.Barcode
which would give the results
InvtID  Bookcode
-----------------
1	Bookcode not found
CB0212	CB0212
DD2921	DD2921
Santosh kumar Pithani 8-Feb-19 8:57am    
What is the use to post same solution in comment?
CHill60 8-Feb-19 9:14am    
It is not the same solution. I changed your
ISNULL(InvtID,0)
to
ISNULL(InvtID,Stv.[value])
as evidenced by the change in the output.
Otherwise you can't easily determine which bookcodes were not found
Member 14127871 10-Feb-19 21:52pm    
Is there other alternative other than use string_split as I afraid other database would be affected if I change the compatibility of my sql server to other level to use that function. It is fine actually to get the Bookcode column only without the InvtID column
Santosh kumar Pithani 10-Feb-19 23:31pm    
If you afraid to use string split then take table type parameter for that its a perfect solution.

Create Type Temp as Table(Barcode Varchar(100));
Insert parameters(1,ean,9789830093819) into Table Type from application side .
Pass this Table type as Stored Procedure parameter.

Declare @Temp Temp ;

select
ISNULL(InvtID,'') AS InvtID
,ISNULL(Bookcode,'Bookcode not found') AS Bookcode

FROM @Temp AS Stv LEFT JOIN #InventoryCustomer cte
ON( Stv.value=Cte.Barcode)



This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900