Solved

not to return empty rows

Posted on 2013-02-01
11
258 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

911 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

25 Experts available now in Live!

Get 1:1 Help Now