Solved

MSSQL SELECT SPECIFIC COLUMS WITHOUT NULLS

Posted on 2013-06-25
28
318 Views
Last Modified: 2013-07-08
HELLO ALL...

So, this should be a simple process for MSSQL to handle.  This is for a Picking Ticket report.  Please consider the following table of data:

ITEM_NO      BIN_01      BIN_02      BIN_03      BIN_04      BIN_05      BIN_06      BIN_07      BIN_08
123      4073      1806      733      NULL      5504      3792      768      NULL
124      92      NULL      NULL      6812      NULL      NULL      5003      1022
125      NULL      3      NULL      NULL      20466      4092      NULL      NULL
126      NULL      NULL      NULL      842      4644      NULL      NULL      NULL
127      NULL      NULL      NULL      NULL      NULL      641      NULL      NULL

...and...what we *need* to be returned by an MSSQL Query, is the following:

ITEM_NO      PICK_01      PICK_02      PICK_03
123      4073      1806      733
124      92      6812      5003
125      3      20466      4092
126      842      4644      ------
127      641      ------      ------

In other words...we need the FIRST THREE Bin Locations which contain the Inventory Items.  For the life of me, I can't formulate the correct query & syntax.  PLEASE HELP!  We are in the midst of filling some very LARGE container orders, and this is a big burden to our warehouse picking staff.

Thank you very much in advance!...Mark
0
Comment
Question by:datatechcorp
  • 12
  • 12
  • 3
  • +1
28 Comments
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39275924
Does the total amount of bins stay constant at 8 or is it dynamic?
0
 

Author Comment

by:datatechcorp
ID: 39275969
At present, it is constant at 8...but in the future, I know...that "additional" BIN fields may be added, to accommodate warehouse growth.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39276052
IF OBJECT_ID('tempdb..#data') IS NOT NULL
    DROP TABLE #data
GO

CREATE TABLE #data (
    item_no int NOT NULL,
    BIN_01 int NULL,
    BIN_02 int NULL,
    BIN_03 int NULL,
    BIN_04 int NULL,
    BIN_05 int NULL,
    BIN_06 int NULL,
    BIN_07 int NULL,
    BIN_08 int NULL
)

insert into #data (item_no, BIN_01, BIN_02, BIN_03, BIN_04, BIN_05, BIN_06, BIN_07, BIN_08)
    values (123,      4073,      1806,      733,      NULL,      5504,      3792,      768,      NULL)
insert into #data (item_no, BIN_01, BIN_02, BIN_03, BIN_04, BIN_05, BIN_06, BIN_07, BIN_08)
    values (124,      92,      NULL,      NULL,      6812,      NULL,      NULL ,     5003 ,     1022)
insert into #data (item_no, BIN_01, BIN_02, BIN_03, BIN_04, BIN_05, BIN_06, BIN_07, BIN_08)    
    values (125,      NULL,      3,      NULL,      NULL,      20466 ,     4092  ,    NULL  ,    NULL)
insert into #data (item_no, BIN_01, BIN_02, BIN_03, BIN_04, BIN_05, BIN_06, BIN_07, BIN_08)
    values (126,      NULL,      NULL,      NULL,      842,      4644 ,     NULL  ,    NULL  ,    NULL)
insert into #data (item_no, BIN_01, BIN_02, BIN_03, BIN_04, BIN_05, BIN_06, BIN_07, BIN_08)
    values (127,      NULL,      NULL,      NULL,      NULL,      NULL ,     641   ,   NULL   ,   NULL)

/*
select 'Initial Data', d.*, cols_found
from #data d
CROSS APPLY (
    SELECT
        CASE WHEN BIN_01 IS NULL THEN '' ELSE '01' END +
        CASE WHEN BIN_02 IS NULL THEN '' ELSE '02' END +
        CASE WHEN BIN_03 IS NULL THEN '' ELSE '03' END +
        CASE WHEN BIN_04 IS NULL THEN '' ELSE '04' END +
        CASE WHEN BIN_05 IS NULL THEN '' ELSE '05' END +
        CASE WHEN BIN_06 IS NULL THEN '' ELSE '06' END +
        CASE WHEN BIN_07 IS NULL THEN '' ELSE '07' END +
        CASE WHEN BIN_08 IS NULL THEN '' ELSE '08' END AS cols_found
) AS cols_found
*/


SELECT
    item_no,
    CASE SUBSTRING(cols_found, 1, 2)
        WHEN '01' THEN BIN_01 WHEN '02' THEN BIN_02 WHEN '03' THEN BIN_03 WHEN '04' THEN BIN_04
        WHEN '05' THEN BIN_05 WHEN '06' THEN BIN_06 WHEN '07' THEN BIN_07 WHEN '08' THEN BIN_08 ELSE NULL END
        AS PICK_01,
    CASE SUBSTRING(cols_found, 3, 2)
                              WHEN '02' THEN BIN_02 WHEN '03' THEN BIN_03 WHEN '04' THEN BIN_04
        WHEN '05' THEN BIN_05 WHEN '06' THEN BIN_06 WHEN '07' THEN BIN_07 WHEN '08' THEN BIN_08 ELSE NULL END
        AS PICK_02,
    CASE SUBSTRING(cols_found, 5, 2)
                                                    WHEN '03' THEN BIN_03 WHEN '04' THEN BIN_04
        WHEN '05' THEN BIN_05 WHEN '06' THEN BIN_06 WHEN '07' THEN BIN_07 WHEN '08' THEN BIN_08 ELSE NULL END
        AS PICK_03
FROM #data d
CROSS APPLY (
    SELECT
        CASE WHEN BIN_01 IS NULL THEN '' ELSE '01' END +
        CASE WHEN BIN_02 IS NULL THEN '' ELSE '02' END +
        CASE WHEN BIN_03 IS NULL THEN '' ELSE '03' END +
        CASE WHEN BIN_04 IS NULL THEN '' ELSE '04' END +
        CASE WHEN BIN_05 IS NULL THEN '' ELSE '05' END +
        CASE WHEN BIN_06 IS NULL THEN '' ELSE '06' END +
        CASE WHEN BIN_07 IS NULL THEN '' ELSE '07' END +
        CASE WHEN BIN_08 IS NULL THEN '' ELSE '08' END AS cols_found
) AS cols_found
0
 

Author Comment

by:datatechcorp
ID: 39276119
WOW!  No wonder I couldn't figure it out...that seems fairly complex.  THANK YOU very much Scott for responding...but this seems above my head :-)

So...perhaps...what I should have stated...was that this is solely for reporting...in that, we have to marry this...to a Crystal Picking Ticket object.  How would we do this, with a query as complex as this?  Again, I apologize, I should have stated this sooner, but figured some "simplistic" SQL select statement, would clue me in.  Please let me know...and, again, THANK YOU!!!...Much appreciated!!!...Mark
0
 

Author Comment

by:datatechcorp
ID: 39276168
...oops...nevermind...I guess I needed to run this in MSSQL first...I jut realized, that you'd setup a "temp" table...to hold some data!  Geesh, senior moment on my part!

OK, this makes perfect sense now.  The 'CROSS APPLY' is the key, and *THAT* was the piece that I was missing!  Thank you SOOOOO much!...Mark
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 39276194
Sorry: I should have named it #your_data instead of just #data.

Yes, the CROSS APPLY is the key.

Notice, too, that it's easily extensible for additional column(s) and/or for selecting more columns in the result (top 4, top 6, whatever).
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39276304
Points were given but still:

Step 1:

Create a useful function that can help you in many situations:

CREATE FUNCTION [dbo].[fnArray]
( 
	@str varchar(8000),
	@sep char(1)=' ',
	@i int=1
)	
RETURNS varchar(8000)
as
/*******************************************************************************\
Function	: fnArray

Purpose		: Parse a string in elements sepparated by a given 
			  character and return the element in position i
			  
Parameters	: @str - the string to parse
			  @sep - the separator character
			  @i   - the position of the element to be returned; 
					 if i<0 the function will return the number of elemets(size)
					 if i out of range the function will return NULL
Invoke		:
	
		select dbo.fnArray('elem1 elem2 elem3',' ',2) 
		select dbo.fnArray('elem1/elem2/elem3','/',3)
		select dbo.fnArray('elem1/elem2/elem3','/',-1)

\*******************************************************************************/
begin
	-- declare variables	
	declare 
		@size int,
		@count int=1,
		@pos_start int=0,
		@pos_end int=0

	-- get the size of the array
	select 
		-- first, replace multiple spaces with one, just in case
		@str=replace(replace(replace(rtrim(ltrim(@str)),' ','`^'),'^`',''),'`^',' '),
		@size=LEN(@str)-LEN(replace(@str,@sep,''))+1

	-- if i negative return the size of the array
	if @i<0 
		-- return the size
		select
			@str=@size
	else	
	begin	

		-- if i out of range return null		
		if	@i not between 1 and @size 
				set @str=NULL
			else
			-- return the element in position i
			begin
			
				-- add an extra char to help with the last element
				select 
					@str=LTRIM(@str)+'^'
					
				-- loop the get the positions
				while @count<=@i
					select 
						@pos_start=@pos_end+1,
						@pos_end=CHARINDEX(@sep,@str,@pos_end+1),
						@count=@count+1
				
				-- isolate the element						
				select @str=	
					substring(
						@str, 
						@pos_start, 
						case 
							when @pos_end=0 then LEN(@str) 
							else @pos_end 
						end-@pos_start)
			end
	end
	
	-- return the element
	return @str
	
end

Open in new window


Step 2:
Your query simplifies to:

select
	item_no
	,dbo.fnArray(picks,' ',1) as Pick_01
	,dbo.fnArray(picks,' ',2) as Pick_02
	,dbo.fnArray(picks,' ',3) as Pick_03
from
(
		select 
			item_no,
			ltrim(
				isnull(cast(BIN_01 as varchar),'')+' '+
				isnull(cast(BIN_02 as varchar),'')+' '+
				isnull(cast(BIN_03 as varchar),'')+' '+
				isnull(cast(BIN_04 as varchar),'')+' '+
				isnull(cast(BIN_05 as varchar),'')+' '+
				isnull(cast(BIN_06 as varchar),'')+' '+
				isnull(cast(BIN_07 as varchar),'')+' '+
				isnull(cast(BIN_08 as varchar),'')) as picks
		from 
			#data
	) q1

Open in new window


Scalability is even simpler this way by just adding BINS in the sub query and/or more columns in the top select list where the function is used if more picks are needed.
0
 

Author Comment

by:datatechcorp
ID: 39276318
EXCELLENT ADVICE!!!  I *love* this forum!  Thank you Zberteoc very much!
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39278360
The performance using that approach will be just awful vs the CROSS APPLY method.

I can't imagine how long you'd have to wait for that to run on even a one million row table, let alone 50M or more.

For that reason, obviously re-using that type of function would just spread the performance problems to a lot of other queries.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39281962
Btw, you could replace the CROSS APPLY with a computed column in the table itself.  Then the SELECT query would not need any CROSS APPLY itself:


ALTER TABLE dbo.your_actual_table_name
ADD cols_found AS CAST(
        CASE WHEN BIN_01 IS NULL THEN '' ELSE '01' END +
        CASE WHEN BIN_02 IS NULL THEN '' ELSE '02' END +
        CASE WHEN BIN_03 IS NULL THEN '' ELSE '03' END +
        CASE WHEN BIN_04 IS NULL THEN '' ELSE '04' END +
        CASE WHEN BIN_05 IS NULL THEN '' ELSE '05' END +
        CASE WHEN BIN_06 IS NULL THEN '' ELSE '06' END +
        CASE WHEN BIN_07 IS NULL THEN '' ELSE '07' END +
        CASE WHEN BIN_08 IS NULL THEN '' ELSE '08' END
            AS varchar(16))


The final SELECT is the same, except that the CROSS APPLY is no longer required.

Unfortunately, one computed column can't be based on another computed column, so you can't the definition for PICK_01, etc., in the main table as computed columns themselves.
0
 

Author Comment

by:datatechcorp
ID: 39282104
OK, great...thanks!
0
 

Author Comment

by:datatechcorp
ID: 39308412
Hi Scott...

So...this is definitely working...kinda'...sorta'.  I'll explain.  There are some 600,000+ records in the database (yes, this is a VERY large warehouse!)...and the query is suffering from performance.  It is taking upwards of 7+ minutes...to get the full result set.  Note, if we just do a "select * from IM_INV"...the query executes completely in about 10-12 seconds.  Thus (I'm guessing here) the overhead in processing caused by all the comparisons in those case statements...is taxing the performance.

I tried to get around that, by implementing this with a Computed Column as you suggested...yet, the performance...is precisely the same, so I don't think it was the "Cross Apply" that is causing our performance hit.  Any suggestions...any at all?  I appreciate all your feedback here.

Thanks!...Mark
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39308630
Hmm ... I'd expect some overhead doing this process live for 600K+ rows, but not quite that much for just three CASE statements.

If you could use triggers to "normalize" rows (i.e., left-justify the values) as they are INSERTed or UPDATEd, that would solve it.  But there may be other reasons you can't do that.

I can't think of a more efficient way right now, but I'll keep thinking about it.

[Btw, I have to believe the function-based method would perform much worse, but you might want to test it on a smaller sample of rows just to be sure.]
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39308647
Also, if I could see the actual query and the query plan, that might help also.  Perhaps there is something else in the query that is taking a lot of time?!
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39308668
Is this in SQL 2008 or SQL 2005?
0
 

Author Comment

by:datatechcorp
ID: 39308706
OK, the actual query...using the Computed Columns, is as follows (with the CROSS APPLY commented out):

SELECT
    LOC_ID,ITEM_NO,
    CASE SUBSTRING(cols_found, 1, 2)
            WHEN '01' THEN DTC_BIN_5
                  WHEN '02' THEN DTC_BIN_6
                  WHEN '03' THEN DTC_BIN_7
                  WHEN '04' THEN DTC_BIN_8
                  WHEN '05' THEN DTC_BIN_9
            WHEN '06' THEN DTC_BIN_10
            WHEN '07' THEN DTC_BIN_11
            WHEN '08' THEN DTC_BIN_12
            WHEN '09' THEN DTC_BIN_13
            WHEN '10' THEN DTC_BIN_14
            WHEN '11' THEN DTC_BIN_15
            WHEN '12' THEN DTC_BIN_16
            WHEN '13' THEN DTC_BIN_17
            WHEN '14' THEN DTC_BIN_18
            WHEN '15' THEN DTC_BIN_19
            WHEN '16' THEN DTC_BIN_20  
            WHEN '17' THEN DTC_BIN_21
                  WHEN '18' THEN DTC_BIN_22
                  WHEN '19' THEN DTC_BIN_23
                  WHEN '20' THEN DTC_BIN_24
            WHEN '21' THEN DTC_BIN_25
            WHEN '22' THEN DTC_BIN_26
            WHEN '23' THEN DTC_BIN_27
            WHEN '24' THEN DTC_BIN_28
            WHEN '25' THEN DTC_BIN_29
            WHEN '26' THEN DTC_BIN_30
            WHEN '27' THEN DTC_BIN_31
            WHEN '28' THEN DTC_BIN_32
            WHEN '29' THEN DTC_BIN_33
            WHEN '30' THEN DTC_BIN_34
            WHEN '31' THEN DTC_BIN_35
            WHEN '32' THEN DTC_BIN_36
            WHEN '33' THEN DTC_BIN_37
            WHEN '34' THEN DTC_BIN_38
            WHEN '35' THEN DTC_BIN_39
            WHEN '36' THEN DTC_BIN_40
      ELSE NULL END AS PICK_01,
    CASE SUBSTRING(cols_found, 3, 2)
              WHEN '02' THEN DTC_BIN_6
                WHEN '03' THEN DTC_BIN_7
                  WHEN '04' THEN DTC_BIN_8
                  WHEN '05' THEN DTC_BIN_9
            WHEN '06' THEN DTC_BIN_10
            WHEN '07' THEN DTC_BIN_11
            WHEN '08' THEN DTC_BIN_12
            WHEN '09' THEN DTC_BIN_13
            WHEN '10' THEN DTC_BIN_14
            WHEN '11' THEN DTC_BIN_15
            WHEN '12' THEN DTC_BIN_16
            WHEN '13' THEN DTC_BIN_17
            WHEN '14' THEN DTC_BIN_18
            WHEN '15' THEN DTC_BIN_19
            WHEN '16' THEN DTC_BIN_20  
            WHEN '17' THEN DTC_BIN_21
              WHEN '18' THEN DTC_BIN_22
                WHEN '19' THEN DTC_BIN_23
                  WHEN '20' THEN DTC_BIN_24
            WHEN '21' THEN DTC_BIN_25
            WHEN '22' THEN DTC_BIN_26
            WHEN '23' THEN DTC_BIN_27
            WHEN '24' THEN DTC_BIN_28
            WHEN '25' THEN DTC_BIN_29
            WHEN '26' THEN DTC_BIN_30
            WHEN '27' THEN DTC_BIN_31
            WHEN '28' THEN DTC_BIN_32
            WHEN '29' THEN DTC_BIN_33
            WHEN '30' THEN DTC_BIN_34
            WHEN '31' THEN DTC_BIN_35
            WHEN '32' THEN DTC_BIN_36
            WHEN '33' THEN DTC_BIN_37
            WHEN '34' THEN DTC_BIN_38
            WHEN '35' THEN DTC_BIN_39
            WHEN '36' THEN DTC_BIN_40
      ELSE NULL END as PICK_02,
      CASE SUBSTRING(cols_found, 5, 2)
                WHEN '03' THEN DTC_BIN_7
                  WHEN '04' THEN DTC_BIN_8
              WHEN '05' THEN DTC_BIN_9
            WHEN '06' THEN DTC_BIN_10
            WHEN '07' THEN DTC_BIN_11
            WHEN '08' THEN DTC_BIN_12
            WHEN '09' THEN DTC_BIN_13
            WHEN '10' THEN DTC_BIN_14
            WHEN '11' THEN DTC_BIN_15
            WHEN '12' THEN DTC_BIN_16
            WHEN '13' THEN DTC_BIN_17
            WHEN '14' THEN DTC_BIN_18
            WHEN '15' THEN DTC_BIN_19
            WHEN '16' THEN DTC_BIN_20  
            WHEN '17' THEN DTC_BIN_21
                  WHEN '18' THEN DTC_BIN_22
                  WHEN '19' THEN DTC_BIN_23
                  WHEN '20' THEN DTC_BIN_24
            WHEN '21' THEN DTC_BIN_25
            WHEN '22' THEN DTC_BIN_26
            WHEN '23' THEN DTC_BIN_27
            WHEN '24' THEN DTC_BIN_28
            WHEN '25' THEN DTC_BIN_29
            WHEN '26' THEN DTC_BIN_30
            WHEN '27' THEN DTC_BIN_31
            WHEN '28' THEN DTC_BIN_32
            WHEN '29' THEN DTC_BIN_33
            WHEN '30' THEN DTC_BIN_34
            WHEN '31' THEN DTC_BIN_35
            WHEN '32' THEN DTC_BIN_36
            WHEN '33' THEN DTC_BIN_37
            WHEN '34' THEN DTC_BIN_38
            WHEN '35' THEN DTC_BIN_39
            WHEN '36' THEN DTC_BIN_40
      ELSE NULL END AS PICK_03,
    CASE SUBSTRING(cols_found, 7, 2)
              WHEN '04' THEN DTC_BIN_8
                WHEN '05' THEN DTC_BIN_9
            WHEN '06' THEN DTC_BIN_10
            WHEN '07' THEN DTC_BIN_11
            WHEN '08' THEN DTC_BIN_12
            WHEN '09' THEN DTC_BIN_13
            WHEN '10' THEN DTC_BIN_14
            WHEN '11' THEN DTC_BIN_15
            WHEN '12' THEN DTC_BIN_16
            WHEN '13' THEN DTC_BIN_17
            WHEN '14' THEN DTC_BIN_18
            WHEN '15' THEN DTC_BIN_19
            WHEN '16' THEN DTC_BIN_20  
            WHEN '17' THEN DTC_BIN_21
              WHEN '18' THEN DTC_BIN_22
                WHEN '19' THEN DTC_BIN_23
                  WHEN '20' THEN DTC_BIN_24
            WHEN '21' THEN DTC_BIN_25
            WHEN '22' THEN DTC_BIN_26
            WHEN '23' THEN DTC_BIN_27
            WHEN '24' THEN DTC_BIN_28
            WHEN '25' THEN DTC_BIN_29
            WHEN '26' THEN DTC_BIN_30
            WHEN '27' THEN DTC_BIN_31
            WHEN '28' THEN DTC_BIN_32
            WHEN '29' THEN DTC_BIN_33
            WHEN '30' THEN DTC_BIN_34
            WHEN '31' THEN DTC_BIN_35
            WHEN '32' THEN DTC_BIN_36
            WHEN '33' THEN DTC_BIN_37
            WHEN '34' THEN DTC_BIN_38
            WHEN '35' THEN DTC_BIN_39
            WHEN '36' THEN DTC_BIN_40
      ELSE NULL END AS PICK_04
FROM IM_INV
/*CROSS APPLY (
    SELECT
        CASE WHEN DTC_BIN_5 IS NULL THEN '' ELSE '01' END +
        CASE WHEN DTC_BIN_6 IS NULL THEN '' ELSE '02' END +
        CASE WHEN DTC_BIN_7 IS NULL THEN '' ELSE '03' END +
        CASE WHEN DTC_BIN_8 IS NULL THEN '' ELSE '04' END +
        CASE WHEN DTC_BIN_9 IS NULL THEN '' ELSE '05' END +
        CASE WHEN DTC_BIN_10 IS NULL THEN '' ELSE '06' END +
        CASE WHEN DTC_BIN_11 IS NULL THEN '' ELSE '07' END +
        CASE WHEN DTC_BIN_12 IS NULL THEN '' ELSE '08' END +
        CASE WHEN DTC_BIN_13 IS NULL THEN '' ELSE '09' END +
        CASE WHEN DTC_BIN_14 IS NULL THEN '' ELSE '10' END +
        CASE WHEN DTC_BIN_15 IS NULL THEN '' ELSE '11' END +
        CASE WHEN DTC_BIN_16 IS NULL THEN '' ELSE '12' END +
        CASE WHEN DTC_BIN_17 IS NULL THEN '' ELSE '13' END +
        CASE WHEN DTC_BIN_18 IS NULL THEN '' ELSE '14' END +
        CASE WHEN DTC_BIN_19 IS NULL THEN '' ELSE '15' END +
        CASE WHEN DTC_BIN_20 IS NULL THEN '' ELSE '16' END +
        CASE WHEN DTC_BIN_21 IS NULL THEN '' ELSE '17' END +
        CASE WHEN DTC_BIN_22 IS NULL THEN '' ELSE '18' END +
        CASE WHEN DTC_BIN_23 IS NULL THEN '' ELSE '19' END +
        CASE WHEN DTC_BIN_24 IS NULL THEN '' ELSE '20' END +
        CASE WHEN DTC_BIN_25 IS NULL THEN '' ELSE '21' END +
        CASE WHEN DTC_BIN_26 IS NULL THEN '' ELSE '22' END +
        CASE WHEN DTC_BIN_27 IS NULL THEN '' ELSE '23' END +
        CASE WHEN DTC_BIN_28 IS NULL THEN '' ELSE '24' END +
        CASE WHEN DTC_BIN_29 IS NULL THEN '' ELSE '25' END +
        CASE WHEN DTC_BIN_30 IS NULL THEN '' ELSE '26' END +
        CASE WHEN DTC_BIN_31 IS NULL THEN '' ELSE '27' END +
        CASE WHEN DTC_BIN_32 IS NULL THEN '' ELSE '28' END +
        CASE WHEN DTC_BIN_33 IS NULL THEN '' ELSE '29' END +
        CASE WHEN DTC_BIN_34 IS NULL THEN '' ELSE '30' END +
        CASE WHEN DTC_BIN_35 IS NULL THEN '' ELSE '31' END +
        CASE WHEN DTC_BIN_36 IS NULL THEN '' ELSE '32' END +
        CASE WHEN DTC_BIN_37 IS NULL THEN '' ELSE '33' END +
        CASE WHEN DTC_BIN_38 IS NULL THEN '' ELSE '34' END +
        CASE WHEN DTC_BIN_39 IS NULL THEN '' ELSE '35' END +
        CASE WHEN DTC_BIN_40 IS NULL THEN '' ELSE '36' END
       
        AS cols_found
       
) AS cols_found
*/
--ORDER BY LOC_ID,ITEM_NO ASC
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39308708
This sounds like a new question. :o)
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39308746
Not to me :-) ... it's the same essential issue.

36 values rather than 8 would add much overhead for sure.


This could/should(?) perform much better, but it'll work only in SQL 2008:


SELECT
    LOC_ID,ITEM_NO,
    MAX(CASE WHEN row_num = 1 THEN bin END) AS Pick_01,
    MAX(CASE WHEN row_num = 2 THEN bin END) AS Pick_02,
    MAX(CASE WHEN row_num = 3 THEN bin END) AS Pick_03,
    MAX(CASE WHEN row_num = 4 THEN bin END) AS Pick_04
FROM (
    SELECT
         LOC_ID, ITEM_NO, bin, ROW_NUMBER() OVER(PARTITION BY loc_id, item_no ORDER BY bin#) AS row_num
    FROM dbo.IM_INV
    CROSS APPLY (
        VALUES (1,DTC_BIN_5), (2,DTC_BIN_6), (3,DTC_BIN_7), --...
            (35,DTC_BIN_39), (36,DTC_BIN_40)
        bins(bin#,bin)
    WHERE
        bin IS NOT NULL
) AS derived
WHERE
    row_num <= 4
GROUP BY
    LOC_ID, ITEM_NO
ORDER BY
    LOC_ID, ITEM_NO
0
 

Author Comment

by:datatechcorp
ID: 39308748
This is in SQL 2008 R2.  And Zberteoc...perhaps it's a new question...likely not, as it's continual.  It's the performance side...of the solution that Scott has presented.  No worries...if you want/need me to open another question, I can do that...but we'll lose this dialogue thread if we do.  Let me know...Thanks!...Mark
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39308753
2008, sweet, pls try the query approach posted directly above your last comment.
0
 

Author Comment

by:datatechcorp
ID: 39308768
Hi Scott...

I'm getting the following error when I execute your query:

Msg 102, Level 15, State 1, Line 15
Incorrect syntax near 'bins'.

Any thoughts?  Thanks!...Mark
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39308774
Sorry, I left off a closing paren there:


    CROSS APPLY (
        VALUES (1,DTC_BIN_5), (2,DTC_BIN_6), (3,DTC_BIN_7), --...
            (35,DTC_BIN_39), (36,DTC_BIN_40)
    ) --<<
        bins(bin#,bin)
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39308786
Have you tried my solution? I am just curios if it can compare in terms of performance or not.

The question is really, do you need this result to be produced on the fly on a request base or you could generate some intermediate data on a schedule which will be then be used in normal request. If that is the case you can workaround the performance issue.
0
 

Author Comment

by:datatechcorp
ID: 39308840
Hi Zberteoc...no, I was concentrating on (1) solution at a time...didn't want to overwhelm myself...as I'm sure you can imagine. :-)

So far, Scott's solution has been working...and, Scott, the suggestion that you made on your *updated* query, is working beautifully...performance is now "up to snuff".

Thank you *both* for chiming in here!  I'd be truly lost without Experts Exchange...it's the *BEST* portal on the Web!!!

Mark
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39308877
Thanks!

May I ask, what is the new query time?  Old was ~7 mins., what is the new?  Just curious so I'll know for potential uses in the future.
0
 

Author Comment

by:datatechcorp
ID: 39308897
Approx. 12-seconds!  Genius! :-)
0
 

Author Comment

by:datatechcorp
ID: 39308898
Differences are Night and Day! :-)
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39308909
Nice!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL query 4 31
Contained Database Collations 6 20
replication - alerts? 4 23
Caste datetime 2 25
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

744 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

13 Experts available now in Live!

Get 1:1 Help Now