(T-Sql)-Not able to pull all records 1

[markajem]

When an item is set up in the master inventory file "itm_mstr" a corresponding record is automatically created in the file "loc_mstr". This automatically created record is the master location record. In order for the system to hold inventory amounts and ship an additional location record must be entered thru the system by a user. This means that there will be 2 location records for each item in the inventory master file. Please see below. Notice each item has the item number and then a "" (not null) for the first record (master location record) and then the second record is the location entry for that item "nj". What I am attempting to do is show the items that do not have a location entry assigned. I have linked the loc_mstr and the itm_mstr together hoping I can acheve a NULL to represent the fact that example: item 05555 does not have a location record of "nj" like the other items do. I just cant generate that null. Well, actually it does not have to be a null I just figured I could work with that.

If only the items that dont have a location assigned are generated that is okay too. I could work with that.

Any better ideas would be much appreciated.
Thanks

SELECT     
	case 
	when [COLOR=purple]Lm.LOCNCODE[/color] is null
		then [COLOR=blue]Im.ITEMNMBR[/color]
		else [COLOR=purple]Lm.LOCNCODE[/color]
		end as location,
	[COLOR=blue]Im.ITEMNMBR[/color] as item, [COLOR=purple]Lm.LOCNCODE[/color] as location


FROM         [COLOR=blue]dbo.itm_mstr[/color] as [COLOR=blue]Im[/color] full outer JOIN
                      [COLOR=purple]dbo.loc_mstr[/color] as [COLOR=purple]Lm[/color] ON [COLOR=blue]Im.ITEMNMBR[/color] = [COLOR=purple]Lm.ITEMNMBR[/color]

Records from table "loc_mstr"
[tt]
item location
03202
03202 nj
04356
04356 nj
05555
06928
06928 nj
07233
07233 nj
[/tt]

 

[hneal98]

I hope I followed you correctly.

Try this:

SELECT     
    case 
    	when Lm.LOCNCODE is null
    	    then Im.ITEMNMBR
    	    else Lm.LOCNCODE
    end as location,
    Im.ITEMNMBR as item,
	Lm.LOCNCODE as location
FROM
    dbo.itm_mstr as Im 
	left outer JOIN dbo.loc_mstr as Lm ON Im.ITEMNMBR = Lm.ITEMNMBR
WHERE
	isnull(Lm.ITEMNMBR,'X') = 'X'

 

[markajem]

I think it better that you refer only to what I need the view to do in my explanation. I think my VIEW is just more confusing. The results of your view gave me zero records and I know that there are items in the loc_mstr that do not have a location assigned. As I said my poor attempt of creating the view is just confusing.

Thanks
[tt]
Records from table "loc_mstr"

item location
03202
03202 nj
04356
04356 nj
05555
06928
06928 nj
07233
07233 nj
[/tt]

If my results returned are:

05555

that would be fine. Or as I said any other suggestions.


Thanks as always.

 

[hneal98]

SELECT     
    case 
        when Lm.LOCNCODE is null
            then Im.ITEMNMBR
            else Lm.LOCNCODE
    end as location,
    Im.ITEMNMBR as item,
    Lm.LOCNCODE as location
FROM
    dbo.itm_mstr as Im 
    INNER JOIN dbo.loc_mstr as Lm ON Im.ITEMNMBR = Lm.ITEMNMBR
WHERE
    len(rtrim(Lm.LOCNCODE)) < 1

 

[markajem]

Thank you again. The results returned were all the master location records. This still won't tell me what items do not have a location record assigned. As I said I think my poor attempt at originally trying to create the view is just confusing you more.

Each item in the IM have a corresponding record in the LM which your new VIEW shows me. What I need is results of no location record assigned. In my example below the master location records (which your view returned) in teal and the assigned ones are in red. I need to see which ones do not have a location assignment which would be the ones in red.

[tt]
item location
03202
03202 nj
04356
04356 nj
05555
06928
06928 nj
07233
07233 nj
[/tt]

So the only item that does not have a location assigned to it is 05555.

I hope I explained myself better now.

Thanks for helping.

 

[hneal98]

So you are saying that the nj represents those with no location assigned?

If that is the case, just reverse my last query to

len(rtrim(Lm.LOCNCODE)) > 0

I thought you were trying to get those that did not have LOCNCODE.

 

[markajem]

Okay thanks hneal98

Appreciate it

 

[markajem]

You were in the right direction because of the VIEW I originally posted which did nothing but confuse the issue. Sorry again.

This is the code I ended up with:

select itemnmbr, sum(1) as total

from dbo.im

group by itemnmbr

I was making it much more complicated than it had to be.

Records in table as above:

[tt]
item location
03202
03202 nj
04356
04356 nj
05555
06928
06928 nj
07233
07233 nj
[/tt]

Results of my view:
[tt]
item total
03202 2
04356 2
05555 1
06928 2
07233 2
[/tt]

As you can see all I need to do it pull the records where there is a 1 and that did it.

Thanks again for your help.

 

[hneal98]

Glad I could help.