Fining missing values in a query 2

[bigfoot]

I tried looking this up but it's hard to explain.

I have a table full of items and I run a query on those items. They want the query to spit out not only the rows but also the missing items.

Make sense?

Example:
User does a select of these numbers: '3010','3013','4912'

My query looks like this:
select item_no, barcode, qty
from mytable
where item_no IN ('3010','3013','4912')
ORDER BY CHARINDEX(item_no,'3010,3013,4912')

This spews out to a .net datatable and into a .net DataView that the user downloads as a spreadsheet.

The reason for the CHARINDEX is to keep the numbers in the same sequence that they entered it.
This works.

But wait, it gets better. What if record 4912 is missing?

They want a print out like this:

3010 123456789012 16
3013 123456789012 5
4912 Missing (or blank data)

The 4912 is not in my table and I can't figure out how to get sql server to return it in the query. I've tried everything I can think of.
The query is part of a .NET program and I was going to open the datatable and insert them. Yuck.

The reason for this is so they have a spreadsheet that matches the one they have to sit side by side.


Any help would be great. I know this is a hack.

 

[SkipVought]

Create a table containg each and every value you want to display in the query and do an outer join.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bborissov]

There is no need to use CHARINDEX() in the ORDER BY.
The SQL Server will ORDER your query gracefully if you order it by item_no.

How that query is sent to DB?
You use Stored Procedure you you build your query in your front end?

I ask because both solutions are very different.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bigfoot]

Skip: I can't build a table unless it was a temp table on the fly. Himmmmm. (thinking)


Borislav: It's a query built on the front end then passed to SQL Server as a query.
The item numbers are not in any kind of order except the order that the user types them into the text box.

The user starts out with a spreadsheet they get from sales and need to fill in size and cube information.
They copy the list of numbers from the Excel column.
I gave them a long TextBox to copy and paste the Excel column of numbers into. Then I parse it in asp.net and add the syntax and commas.

Then I parse it again and add the CHARINDEX() at the end so the query comes back in the same order, which is not the order of the table unfortunately.

As I'm writing this, I almost like Skip's approach but I'd do it on the program level, adding them into the returned data table.

I'm open to suggestions here as this not something that is not normal (that I have done) in SQL Server.

I can turn this whole thing into a monster stored procedure, and just pass in the parts.

It comes from a custom reporting package that we wrote in house so depending on choices the user makes, the SQL string gets added to or subtracted from before being passed to SQL Server.

Yes it's a mess but they love it and that's what counts at the end of the day.

 

[gmmastros]

For this, I would suggest that you create a stored procedure.

1. Pass in a Fixed Width list of item numbers.
2. In the stored procedure, use a split function to turn your list in to a table variable.
3. Use the table variable as the LEFT table in a left join query.
4. Coalesce the values from the real table to avoid nulls.

I would suggest you use option #6 from here: faq183-6684

You'll need a numbers table, but the FAQ's shows how to build one, too.

Assuming you have the numbers table and the splitfixed function....

select mytable.item_no, 
       Coalesce(mytable.barcode, '') As barcode, 
       Coalesce(mytable.qty, 0) As qty
from   dbo.SplitFixed('3010      3013      4912      ', 10) As InputValues
       Left Join mytable
         On InputValues.Value = mytable.item_no
Order By InputValues.TokenId

By ordering on the TokenId, this ensures the same ordering as the list of number.

You may want to change the SplitFixed function a little so that it returns an integer for the second column instead of a string. This may improve performance a little.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bigfoot]

Wonderful George.

After playing for a bit, I managed to remove the numbers table from the equation.

I had this from another project:

CREATE FUNCTION dbo.fnSplit(
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(1) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
 
 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END

It creates a table on the fly with 1 field called item.
(I had to go back and see what I did)


Now the query is simple:

Select 
InputValues.item  As Input,
Coalesce(mytable.item_no, 'N/A') As item_no,
Coalesce(mytable.upc, '') As upc, 
Coalesce(mytable.case_height, '') As case_height
From fnSplit('3010,3013,4912,2203016', ',') as InputValues
       Left Join mytable
         On InputValues.item = mytable.item_no

No need to Order By as SQL returns it in the order it was imput.

Next is passing in a variable to the fnSplit function.

Thank you.

 

[bigfoot]

Oh I forgot to add the result of the query:

2203016 2203016 012345592557 6
3013 3013 012345001651 8
3010 3010 012345001226 1
4912 N/A

The user sees the N/A and knows it's Not Available but the spreadsheet rows will still line up.

How cool is that? I figured there was a way to do it.
I should give Skip a star too because of his table join suggestion.

I keep getting smarter. Thank you.


RATS! I just noticed that it's not sorting. It was...

 

[gmmastros]

You should NEVER rely on sorting unless you specifically have an order by in your query. Without the order by, things may appear to sort properly for a while, and then suddenly stop sorting they way you want.

I suggest you modify your split function to include a an identity column. You could then use that to sort.

First, modify your split function like this:

ALTER FUNCTION dbo.fnSplit(
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(1) = ',' -- delimiter that separates items
) RETURNS @List TABLE ([!]RowId Int Identity(1,1), [/!]item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
 
 IF LEN(@sItem) > 0
  INSERT INTO @List[!](Item)[/!] SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List[!](Item)[/!] SELECT @sInputList -- Put the last item in
RETURN
END

Then...

Select 
InputValues.item  As Input,
Coalesce(mytable.item_no, 'N/A') As item_no,
Coalesce(mytable.upc, '') As upc, 
Coalesce(mytable.case_height, '') As case_height
From fnSplit('3010,3013,4912,2203016', ',') as InputValues
       Left Join mytable
         On InputValues.item = mytable.item_no
[!]Order By InputValues.RowId[/!]

This should cause the sorting to occur the way you want it to.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bigfoot]

Didn't work because I'm working with SQL Server 2000.

But I did create a workaround:

CREATE FUNCTION dbo.fnSplit2
(
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(1) = ',' -- delimiter that separates items
)
 RETURNS @List TABLE (item VARCHAR(100),rowid int)

BEGIN
DECLARE @sItem VARCHAR(100)
DECLARE @rowid int

WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
 Set @rowid = @rowid + 1

 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem, @rowid
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList, @rowid -- Put the last item in
RETURN
END

Notice the addition of the rowid.
And I cleaned up the variables too. I won't need any columns 8000 wide except the variable I'm passing in.

I always learn something new from problems.

 

[gmmastros]

Didn't work because I'm working with SQL Server 2000.

Doesn't matter. It should have worked with SQL2000. Anyway, I'm glad you got this worked out.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bigfoot]

George, it gave me an error on

Order By InputValues.RowId


Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'rowid'.


The reason I'm bring this back up is because the function is not counting and adding it to the fake rowid I created.

It seems I can't do Set @rowid = @rowid + 1 in a function.
It puts nothing into the rowid column.

 

[bigfoot]

AH OK!!!

Sorry! I didn't see the additions you made to my function.
And they were in red too.

It's Friday. Sorry. Thank you so much.

 

[gmmastros]

Just spitballin' here...

It looks like you created a function called fnSplit[!]2[/!].

Are you calling that function from your other code?
What happens when you use QueryAnalyzer to call fnSplit2 ?

[tt][blue]Select * From dbo.fnSplit2('a,b,c',',')[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bigfoot]

LOL, It's working!!!

I used fnSplit2 because I wanted the original. Also I wasn't sure until I checked if any other sprocs were using this.

Now I changed the original with the code you supplied. I forgot about the Identity and was trying to do a
Set @rowid = @rowid + 1. (too much VB as a kid)

It's working great and not affecting anything else.
My fault. I thought RowId was a built in function and didn't see the changes.
rushing to get this done by lunch.

Thank you so much for your time and trouble. it's working great!!