Solved

not to return empty rows

Posted on 2013-02-01
11
261 Views
Last Modified: 2013-02-01
Hi

Can someone please tell me how i can stop an empty row from appearing?

e.g. i get this returned but dont want row 3(dont want empty row)...

Barcode
-------------------
1 P64CS1.NCF
2 A20
3
0
Comment
Question by:razza_b
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 13

Expert Comment

by:Philip Pinnell
ID: 38844012
select barcode from yourtable where isnull(barcode,'') = ''
0
 
LVL 3

Expert Comment

by:Nalinkumarbalaji
ID: 38844016
Please try the below sql query.

Select * from TableName where BarCode is not null or barcode<>' ' or barcode<>''
0
 
LVL 1

Author Comment

by:razza_b
ID: 38844041
i should have attached the sp first time, please check out thanks.
barcode-sp.txt
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 15

Expert Comment

by:santhimurthyd
ID: 38844042
Select * from table where isnull(Barcode,'') <> ''
0
 
LVL 15

Accepted Solution

by:
santhimurthyd earned 500 total points
ID: 38844070
Change your Select with added where case

SELECT
			--RTRIM(LTRIM(ISNULL([WipTrack05].dbo.TblJobDetail.JobdInstruction,''))) AS 'WorkcentreInstructions'
			[dbo].[ParseLotInst](RTRIM(LTRIM(ISNULL([WipTrack05].dbo.TblJobDetail.JobdInstruction,'')))) AS Barcode
	FROM     #TtmpJobFlow 
		LEFT JOIN [WipTrack05].dbo.TblJobDetail WITH(NOLOCK) ON (#TtmpJobFlow.FldStationNmKey = [WipTrack05].dbo.TblJobDetail.JobdStationIdKey) 
		AND (#TtmpJobFlow.FldJobKey = [WipTrack05].dbo.TblJobDetail.JobdJobKey)
where RTRIM(LTRIM(ISNULL([WipTrack05].dbo.TblJobDetail.JobdInstruction,''))) <> ''
	ORDER BY #TtmpJobFlow.Seq

Open in new window

0
 
LVL 15

Expert Comment

by:santhimurthyd
ID: 38844085
hi

Could you confirm on the Join used in Select statement, becuase you have used Left Join which will return empt records on the records on right hand table which don't have match.

SELECT
 --RTRIM(LTRIM(ISNULL([WipTrack05].dbo.TblJobDetail.JobdInstruction,''))) AS 'WorkcentreInstructions'
[dbo].[ParseLotInst](RTRIM(LTRIM(ISNULL([WipTrack05].dbo.TblJobDetail.JobdInstruction,'')))) AS Barcode
	FROM     #TtmpJobFlow 
	[b]LEFT JOIN [WipTrack05].dbo.TblJobDetail [/b] WITH(NOLOCK) ON (#TtmpJobFlow.FldStationNmKey = [WipTrack05].dbo.TblJobDetail.JobdStationIdKey) 
		AND (#TtmpJobFlow.FldJobKey = [WipTrack05].dbo.TblJobDetail.JobdJobKey)
	ORDER BY #TtmpJobFlow.Seq

Open in new window

0
 
LVL 13

Expert Comment

by:Philip Pinnell
ID: 38844087
>select barcode from yourtable where isnull(barcode,'') = ''

my bad

as santhimurthyd says it should be <>

Select * from table where isnull(Barcode,'') <> ''
0
 
LVL 15

Expert Comment

by:santhimurthyd
ID: 38844095
Check this blog which has example on Joins

http://www.tizag.com/sqlTutorial/sqljoin.php
0
 
LVL 1

Author Comment

by:razza_b
ID: 38844120
im still getting empty rows :(

for this one above...

Barcode
-------------------
1 P64CS1.NCF
2 A20
3

i now get 2 rows which is good, but there is another param i supply which had 17 rows now has 13.

so from the 17 rows there was 10 empty, now from the 13 rows there is now 6 empty.
0
 
LVL 1

Author Closing Comment

by:razza_b
ID: 38844210
i knew what it was thi swas missing...

where [dbo].[ParseLotInst] (RTRIM(LTRIM(ISNULL([WipTrack05].dbo.TblJobDetail.JobdInstruction,'')))) <> ''

and we used...

where RTRIM(LTRIM(ISNULL([WipTrack05].dbo.TblJobDetail.JobdInstruction,''))) <> ''
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38844243
try this  ...

---Delete the contents
	IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('dbo.#TtmpJobFlow') AND SYSSTAT & 0XF = 3)
	DROP TABLE dbo.#TtmpJobFlow
	---Create a temporary flow table
	CREATE TABLE #TtmpJobFlow
	(
		 Seq 				INT
		,FldJobKey 			VARCHAR(15)
		,FldStationNmKey 	VARCHAR(30)
		,FldStationDesc		VARCHAR(200) NULL
	)
	---Insert values into temp table
	INSERT #TtmpJobFlow
	(
		 Seq		
		,FldJobKey
		,FldStationNmKey
		,FldStationDesc
	)
	SELECT 
	 	 CONVERT(INT,CONVERT(VARCHAR,FLDSEQ1KEY) + CONVERT(VARCHAR,FLDSEQ2KEY) + CONVERT(VARCHAR,FLDSEQ3KEY) + CONVERT(VARCHAR,FLDSEQ4KEY) + CONVERT(VARCHAR,FLDSEQ5KEY) + CONVERT(VARCHAR,FLDSEQ6KEY)) SEQ
	     ,[WipTrack05].dbo.TblFlowDetail.FldJobKey
	     ,[WipTrack05].dbo.TblFlowDetail.FldStationNmKey
	     ,ISNULL([WipTrack05].dbo.TblStation.StaDesc,'')
	FROM  [WipTrack05].dbo.TblFlowDetail WITH(NOLOCK)
		, [WipTrack05].dbo.TblJob WITH(NOLOCK)
		, [WipTrack05].dbo.TblStation WITH(NOLOCK)
	WHERE    [WipTrack05].dbo.TblFlowDetail.FldJobKey = [WipTrack05].dbo.TblJob.JobKey
	  AND [WipTrack05].dbo.TblFlowDetail.FldStationNmKey = [WipTrack05].dbo.TblStation.StaIdKey
	  AND [WipTrack05].dbo.TblFlowDetail.FldStationNmKey = @workcenter
	  AND [WipTrack05].dbo.TblStation.StaStage = 0
	  AND [WipTrack05].dbo.TblJob.JobKey = @strJob
	  AND CONVERT(INT,CONVERT(VARCHAR,FLDSEQ1KEY) + CONVERT(VARCHAR,FLDSEQ2KEY) + CONVERT(VARCHAR,FLDSEQ3KEY) 
                              + CONVERT(VARCHAR,FLDSEQ4KEY) + CONVERT(VARCHAR,FLDSEQ5KEY) + CONVERT(VARCHAR,FLDSEQ6KEY)) >= @intSeq

	SELECT	[dbo].[ParseLotInst](RTRIM(LTRIM(ISNULL([WipTrack05].dbo.TblJobDetail.JobdInstruction,'')))) AS Barcode
	FROM     #TtmpJobFlow 
	Inner JOIN [WipTrack05].dbo.TblJobDetail WITH(NOLOCK) 
                         ON #TtmpJobFlow.FldStationNmKey = [WipTrack05].dbo.TblJobDetail.JobdStationIdKey
                      AND #TtmpJobFlow.FldJobKey = [WipTrack05].dbo.TblJobDetail.JobdJobKey
                    where coalesce([dbo].[ParseLotInst](RTRIM(LTRIM(ISNULL([WipTrack05].dbo.TblJobDetail.JobdInstruction,'')))) ,'') <> ''
	ORDER BY #TtmpJobFlow.Seq

Open in new window

0

Featured Post

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question