calling a view in a stored procedure

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

calling a view in a stored procedure

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-11-17 : 10:00:59

How do I do this it's in the same dabase.

I want these fields:
select substring(FO_JURIS, 2, 3) DOC,Clmssn, bicnum, dowr_rcpdt, cossn, Inet_ind
from iclaims

I want to join zipcodes and they can both join on the DOC fields.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-17 : 10:47:35

Its not clear. If you want to get zip codes against docs, then your query should be like:

select substring(FO_JURIS, 2, 3) DOC,Clmssn, bicnum, dowr_rcpdt, cossn, Inet_ind, <ZipCode column>
from iclaims a join <YourOtherTable> b on a.Doc=b.<Doc Column name in 2nd table>

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-11-17 : 10:57:58

Sorry... my table is called iclaims and I have a view called zipcodes. I want to join table iclaims on the view zipcodes. They both have DOC as fields that I can join on.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-17 : 11:10:25

ok. You can use a view like a table that you join normally. Decide on the type of join basis your requirements. I'm just putting an inner join to illustrate.

select substring(FO_JURIS, 2, 3) DOC,Clmssn, bicnum, dowr_rcpdt, cossn, Inet_ind, b.<ZipCode column>
from iclaims a join zipcodes b on a.Doc=b.Doc

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-11-17 : 11:24:40

almost there my problem is since I'm using substring(FO_JURIS, 2, 3) and calling that field DOC it's saying invalid column name Doc in line 2. The substring column is called FO_JURIS but after I get out what I want I want it to be called DOC to join it on the view. Can I do this?

select substring(FO_JURIS, 2, 3) DOC,Clmssn, bicnum, dowr_rcpdt, cossn, Inet_ind, b.Doc
from iclaims a join zipcodes b on a.Doc=b.Doc

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-17 : 11:26:57

Use the existing name while joining,

select substring(FO_JURIS, 2, 3) DOC,Clmssn, bicnum, dowr_rcpdt, cossn, Inet_ind, b.Doc
from iclaims a join zipcodes b on substring(a.FO_JURIS, 2, 3)=b.Doc

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-11-17 : 11:28:22

Yes that did it! Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources