using CAST in INNER

Question

Good morning I’m trying to make a mistake but Join.

 string Query = "Select rec.chvbfj, rec.doc, vndB.chvvnd, vndB.chvps, ps.chvps, ps.Dsc " +
               "From (( " +
               "rec " +
               "inner join vndB " +
               "on vndB.chvvnda = cast(rec.doc as int)) " +
               "inner join ps " +
               "on ps.chvps = vndB.chvps)";

of the error:

Ierrorinfo.Getdescription failure with E_FAIL(0x80004005).'

rec.chvbfj number rec.doc text vndB.chvvnda number vndB.chvps number ps.chvps number ps. Dsc text

I will list rec.chvbfj (3), result would be 3(3,4,5), text format, look in table vndB.chvnda(3,4,5), numeric format, field chvps the result has to leave table ps field Dsc the name.

Answer 1

Based on what you provided I managed to do something that maybe you need! Obs: ignore the temporary tables!

declare @rec table(chvbfj int, doc varchar(20))
insert into @rec values(1,'1')
insert into @rec values(2,'2')
insert into @rec values(3,'3')
insert into @rec values(3,'4')
insert into @rec values(3,'5')
insert into @rec values(4,'6')

declare @vndB table(chvvnda int, chvps int)
insert into @vndB values(3,1)
insert into @vndB values(4,1)
insert into @vndB values(5,2)

declare @ps table(chvps int, Descc text)
insert into @ps values(1,'escova')
insert into @ps values(1,'escova')
insert into @ps values(2,'lixa')

declare @rec_temp table(chvbfj int, doc int)
insert into @rec_temp(chvbfj,doc)
select * from @rec where doc in (3,4,5)


Select a.chvbfj, a.doc, b.chvvnda, b.chvps, c.chvps, c.Descc
               From 
               @rec_temp A
               inner join @vndB B
               on b.chvvnda = a.doc
               inner join @ps C
               on c.chvps = b.chvps