SyBase SQL Syntax

Hey guys,

I am using SyBase SQL Anywhere 10. I have 3 tables. Tables 1, 2, and 3.

Table1
ProdNum
Quan
Cost
Type

Table2
ProdNum
Descript

Table3
PromoNum
Descript


I want to select all from Table1 but reference Descript from

Table 2 where the ProdNum matches ProdNum WHEN Type = 0
OR
Table 3 where the ProdNum matches PromoNum WHEN Type = 101


I believe I need to use the CASE function, but I am unsure on the syntax. Help?
triphenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
Try this, it has both a case expression and use of coalesce as an alternative
(coalesce should be ok in Sybase) :
select
      t1.*
    , coalesce(t2.Descript, t3.Descript) as Descript_1
    , case when t1.type = 0 then t2.Descript
           when t1.type = 101 then  t3.Descript
           else 'No description available'
      end as Descript_2
from table1 as t1
left join table2 as t2 on t1.ProdNum = t2.ProdNum
left join table2 as t2 on t1.ProdNum = t2.PromoNum 

Open in new window

{+ an edit - sorry - typos}
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
triphenAuthor Commented:
I modified the query slightly to match my database better:

select
      t1.transact, t1.prodnum, t1.quan, t1.costeach, t1.prodtype
    , coalesce(t2.Descript, t3.Descript) as Descript_1
    , case when t1.prodtype = 0 then t2.Descript
               when t1.prodtype = 100 then  t3.Descript
               else 'No description available'
      end as Descript_2
from dba.posdetail as t1
left join dba.promo as t2 on t1.ProdNum = t2.ProdNum
left join dba.product  as t3 on t1.ProdNum = t2.PromoNum


Didn't get the desired results. See attached. I was expecting the description to be there.
sql.JPG
0
PortletPaulfreelancerCommented:
from dba.posdetail as t1
left join dba.promo as t2 on t1.ProdNum = t2.ProdNum
left join dba.product  as t3 on t1.ProdNum = t2.PromoNum

these do not look right, especially reference to t2 in the last line
try this instead

from dba.posdetail as t1
left join dba.promo as t2 on t1.ProdNum = t2.PromoNum
left join dba.product  as t3 on t1.ProdNum = t3.ProdNum


still no joy?
what do you get from these?

select distinct
t1.prodtype, t2.Descript
from  dba.promo as t2
inner join dba.posdetail as t1 on t1.ProdNum = t2.PromoNum
where t1.prodtype = 100
;

select distinct
t1.prodtype, t3.Descript
from  dba.product as t3
inner join dba.posdetail as t1 on t1.ProdNum = t3.ProdNum
where t1.prodtype = 0
;
0
triphenAuthor Commented:
This worked in the end



select t1.transact, t1.prodnum, t1.quan, t1.costeach, t1.prodtype,

case
when t1.prodtype = 0 then t3.descript
when t1.prodtype = 100 then t2.descript
else 'no desc avail'

end as descript_1

from dba.posdetail as t1
left join dba.promo as t2 on t1.prodnum = t2.promonum
left join dba.product as t3 on t1.prodnum = t3.prodnum
0
PortletPaulfreelancerCommented:
Excellent, glad you got the solution. Cheers, Paul
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.