Solved

not to return empty rows

Posted on 2013-02-01
11
259 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
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

809 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