?
Solved

not to return empty rows

Posted on 2013-02-01
11
Medium Priority
?
263 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 15

Expert Comment

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

Accepted Solution

by:
santhimurthyd earned 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

771 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