Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

MSSQL SELECT SPECIFIC COLUMS WITHOUT NULLS

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
datatechcorp
Asked:
datatechcorp
  • 12
  • 12
  • 3
  • +1
1 Solution
 
didnthaveanameCommented:
Does the total amount of bins stay constant at 8 or is it dynamic?
0
 
datatechcorpAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
datatechcorpAuthor Commented:
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
 
datatechcorpAuthor Commented:
...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
 
Scott PletcherSenior DBACommented:
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
 
ZberteocCommented:
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
 
datatechcorpAuthor Commented:
EXCELLENT ADVICE!!!  I *love* this forum!  Thank you Zberteoc very much!
0
 
Scott PletcherSenior DBACommented:
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
 
Scott PletcherSenior DBACommented:
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
 
datatechcorpAuthor Commented:
OK, great...thanks!
0
 
datatechcorpAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
Scott PletcherSenior DBACommented:
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
 
Scott PletcherSenior DBACommented:
Is this in SQL 2008 or SQL 2005?
0
 
datatechcorpAuthor Commented:
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
 
ZberteocCommented:
This sounds like a new question. :o)
0
 
Scott PletcherSenior DBACommented:
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
 
datatechcorpAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
2008, sweet, pls try the query approach posted directly above your last comment.
0
 
datatechcorpAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
ZberteocCommented:
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
 
datatechcorpAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
datatechcorpAuthor Commented:
Approx. 12-seconds!  Genius! :-)
0
 
datatechcorpAuthor Commented:
Differences are Night and Day! :-)
0
 
Scott PletcherSenior DBACommented:
Nice!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 12
  • 12
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now