Solved

not to return empty rows

Posted on 2013-02-01
11
257 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
 
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This video discusses moving either the default database or any database to a new volume.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now