Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 335
  • Last Modified:

SyBase SQL Syntax

Hey guys,

SyBase SQL Anywhere v10

I have 4 tables. POSDetail, product, promo and refundreasons.

When POSdetail.prodtype = 0 or 1 I want to return product.descript where posdetail.prodnum = product.prodnum

When POSdetail.prodtype = 100 I want to return promo.descript where posdetail.prodnum = promo.promonum

When POSdetail.prodtype = 101 I want to return 2 columns product.descript where posdetail.prodnum = product.prodnum AND refundreasons.descript where posdetail.howpaid = refundreaons.refnum

It's ok if the first two cases have a blank column next to them when there is no prodtype 101.

This is what I have so far.....

select t1.quan as Qty, 
case when t1.prodtype in (0,1, 101) then t3.descript 
when t1.prodtype = 100 then t2.descript 
else 'no desc avail' end as Description, 
t1.costeach as 'Cost Each', 
(t1.quan * t1.costeach) as 'Extended Cost'
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
where t1.transact = 899330

Open in new window



Also, I cant figure our how to round Cost Each and Extended Cost to two decimal places :/


Thank you in advance!
0
triphen
Asked:
triphen
  • 3
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
select t1.quan as Qty, 
case when t1.prodtype in (0,1, 101) then t3.descript 
when t1.prodtype = 100 then t2.descript 
else 'no desc avail' end as Description, 
case when t1.prodtype = 101 then refundreasons.descript end refundreason
round(t1.costeach, 2) as 'Cost Each', 
round(t1.quan * t1.costeach , 2) as 'Extended Cost'
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 
left join dba.refundreasons t4 on t4.refnum = t1.howpaid  
where t1.transact = 899330 

Open in new window

0
 
triphenAuthor Commented:
---------------------------
PixelPoint Service Utility
---------------------------
[Sybase][ODBC Driver][SQL Anywhere]Syntax error near 'ROUND' on line 6.
---------------------------
OK  
---------------------------

Also in line 2 it now needs to be modified to read:

case when t1.prodtype in (0,1) then t3.descript           Without the 101 because it is being accounted for in the case you made.
0
 
triphenAuthor Commented:
I modified to this and it works as expected....


select t1.quan as Qty, 
case when t1.prodtype in (0,1, 101) then t3.descript 
when t1.prodtype = 100 then t2.descript 
else 'no desc avail' end as Description, 
case when t1.prodtype = 101 then t4.descript end as 'Void/Comp',
t1.costeach as 'Cost Each', 
(t1.quan * t1.costeach) as 'Extended Cost'
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 
left join dba.refundreasons t4 on t1.howordered = t4.refnum
where t1.transact = 899330

Open in new window



Still need the rounding though.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SharathData EngineerCommented:
Apply ROUND function
select t1.quan as Qty, 
case when t1.prodtype in (0,1, 101) then t3.descript 
when t1.prodtype = 100 then t2.descript 
else 'no desc avail' end as Description, 
case when t1.prodtype = 101 then t4.descript end as 'Void/Comp',
t1.costeach as 'Cost Each', 
ROUND(t1.quan * t1.costeach,2) as 'Extended Cost'
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 
left join dba.refundreasons t4 on t1.howordered = t4.refnum
where t1.transact = 899330

Open in new window

0
 
triphenAuthor Commented:
You have a Round with a parenthesis open but no close. See first post.
0
 
SharathData EngineerCommented:
I overlooked at Guy Hengel's post. What is the error you are getting with that code?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that error sounds like your sybase version and/or odbc driver you use does not support the usage of the function ROUND...
you may want to try a CAST( .... as number(10,2)) instead, or try to check to get a newer odbc driver, and double-check that the ROUND function is really supported.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now