New query issue... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

New query issue...

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-08 : 15:39:00

I'm having a hard time trying to get this to work. I'm trying to count the FO's.

select a.fo,
n.Doc,
n.Area,
n.Reg,
a.cossn

into test
From
(select fo, count(distinct cossn) as cossn
from t16pendall
group by fo) a

join natdocfile n on n.doc=a.fo
order by n.doc

Below is the create table and some values to enter into the table if someone could help me please.

CREATE TABLE [dbo].[T16pendall](
[COSSN] [char](11) NOT NULL,
[FO] [char](4) NOT NULL
) ON [PRIMARY]

GO

insert into T16Pendall
select '1259798j733','200' union all
select '5259478j745','200' union all
select '7239798j787','420' union all
select '8255798j747','520' union all
select '9259768j765','a48' union all
select '1259748j766','a48' union all
select '1959798j771','a48' union all
select '1059798j780','600' union all
select '1359798j713','600' union all
select '1159798j741','600' union all
select '1756798j765','800' union all
select '1469798j765','200' union all
select '1357798j765','200' union all
select '1858798j765','200' union all
select '1750598j765','200' union all

What am I doing wrong I get 0 rows

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-08 : 15:41:00

Does the derived table part work as expected?:
select fo, count(distinct cossn) as cossn
from t16pendall
group by fo

If that part works, then the problem is most likely with the join condition.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-08 : 15:43:26

Yes this works so it must be my join. I want to doc, area and reg fields into the test table... what am I doing wrong with the join?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-08 : 15:46:42

I don't know. You'll need to post sample data for that table too in order for us to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-08 : 15:58:15

Here's some data ...

Doc char(3)
Area varchar(2)
Reg char(1)


DOC     Area     Reg
200      02       A
420      01       C
520      06       B
A28      01       C
600      05       D
800      02       F

Does it make a difference that in the T16pendall table the FO is char(4) and in the natdocfile the DOC is char(3)?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-08 : 16:30:22

It works fine for me when I used the information you provided.

Here's my test:


CREATE TABLE [dbo].[#T16pendall](
[COSSN] [char](11) NOT NULL,
[FO] [char](4) NOT NULL
) ON [PRIMARY]

insert into #T16Pendall
select '1259798j733','200' union all 
select '5259478j745','200' union all 
select '7239798j787','420' union all 
select '8255798j747','520' union all 
select '9259768j765','a48' union all 
select '1259748j766','a48' union all 
select '1959798j771','a48' union all 
select '1059798j780','600' union all 
select '1359798j713','600' union all 
select '1159798j741','600' union all 
select '1756798j765','800' union all 
select '1469798j765','200' union all 
select '1357798j765','200' union all 
select '1858798j765','200' union all 
select '1750598j765','200'

create table #natdocfile (doc char(3), area varchar(2), reg char(1))
insert into #natdocfile values('200', '02', 'A')
insert into #natdocfile values('420', '01', 'C')
insert into #natdocfile values('520', '06', 'B')
insert into #natdocfile values('A28', '01', 'C')
insert into #natdocfile values('600', '05', 'D')
insert into #natdocfile values('800', '02', 'F')

select *
from (select fo, count(distinct cossn) as cossn
from #T16Pendall group by fo) a
join #natdocfile n on n.doc = a.fo

drop table #T16Pendall, #natdocfile

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-08 : 16:44:38

Okay the natdocfile is a view do I need to call it a certain way?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-08 : 16:51:03

Yours does work and mine doesn't. Okay I need to research. Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-08 : 17:05:27

You could try posting the view code for us to help you with this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-12 : 10:11:48

Sorry I didn't check in over the weekend but I found out the person who built the table put a space in front of the FO field.

So this works:

select * from t16pendall
where FO=' 293'

How would I get this to work?

select cossn, ltrim(t.fo)as fo
from t16pendall t join natdocfile n
on n.doc=t.fo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources