Conditional Value Increase With Sql? 关注

0
Avatar
Legacy Poster

Hi All!
 

I have created a label template that pulls information from two databases to create a shelf label that includes a price, description and VAT/No VAT. This has been easy enough to achieve.

However, now I am attempting to set the price displayed to gross, by adding 20% to a label price if it is VAT applicable (i.e. if it has a tax ID of 2583).

The information for tax is stored in one database table called StockItem.TaxCodeID, and the price is stored in another called StockItemPrice.Price.
Is there a way to make this happen? I'm very new to SQL and have gotten by so far using the query and join tools included with Bartender, and has generated the following code:

 

SELECT "dbo"."StockItem"."Code", "dbo"."StockItem"."Name", "dbo"."StockItem"."TaxCodeID", "dbo"."StockItem"."PartNumber", "StockItemPrice"."Price" FROM "dbo"."StockItem" , "StockItemPrice"
WHERE "dbo"."StockItem"."ItemID" = "StockItemPrice"."ItemID" 
AND 
   ("StockItemPrice"."PriceBandID" = 1001 
      AND "dbo"."StockItem"."Code" LIKE '6%' 
      AND "dbo"."StockItem"."PartNumber" LIKE '%?BarcodeSearch1%' 
      AND "dbo"."StockItem"."Name" LIKE '%?NameSearch%' 
      AND "dbo"."StockItem"."TaxCodeID" = 258

 

I have tried appending the following code to this:

SELECT *, CASE "dbo"."StockItem"."TaxCodeID"
     WHEN "dbo"."TaxCodeID"=2583 THEN "dbo"."StockItemPrice"."Price" *1.20
     ELSE "dbo"."StockItemPrice.Price"
END as newcolumn
FROM "dbo"."StockItem"

 

But no matter how I work it, I always get the generic error code from Bartender.

I have very little experience with SQL - is there something I'm missing or doing wrong?

 

Thanks anyone for any help!

 

*edited for unknown formatting cock-up*

4 评论

0
Avatar
Domingo Rodriguez
版主
评论操作 固定链接

You should be applying this instead by using VBScript over the text object's data source which will display the price on your BarTender document, something like:

 

If Field("StockItem.TaxCodeID") = "2583" Then

  value = value * 1.20

End If

0
Avatar
Legacy Poster
评论操作 固定链接

Hi! Thank you for stepping me in the right direction with the VB - I've applied the code to the Price field, however I get the message:

 

"This script did not read from the 'Value' property, which means that the currently specified data source was completely ignored", and no calculations are done.

 

However if I just use Value = Value * 1.20 (as a test) the priced changes accordingly.

 

Any ideas?

0
Avatar
Legacy Poster
评论操作 固定链接

got it! I just had to change the parenthesis:


If Field ("StockItem.TaxCodeID" = "2583") Then

value = value * 1.20
End If


This got it working, and now tax is applied =D Thanks Domingo!


---ADDITION:---
Is there any way for this VB to be applied to a currency field?
0
Avatar
Domingo Rodriguez
版主
评论操作 固定链接

When you choose "Currency" as the data type, you could still set the Source under the "Data Source" tab to be "VBScript", for example an "Event Control Script" one. The data types might be conflicting though (String vs numeric operations) so you might first need to do some data type conversions.

 

What you could do is to use the FormatCurrency() VBScript function instead and then convert your numeric value to a currency:

 

 

FormatCurrency Function

 

Returns an expression formatted as a currency value using the currency symbol
defined in the system control panel.

 

FormatCurrency(Expression[,NumDigitsAfterDecimal
[,IncludeLeadingDigit [,UseParensForNegativeNumbers
[,GroupDigits]]]])

 

Arguments

 

Expression

 

Required. Expression to be formatted.

 

NumDigitsAfterDecimal

 

Optional. Numeric value indicating how many places to the right
of the decimal are displayed. Default value is -1, which indicates that the
computer's regional settings are used.

 

IncludeLeadingDigit

 

Optional. Tristate constant that indicates whether or not a
leading zero is displayed for fractional values. See Settings section for
values.

 

UseParensForNegativeNumbers

 

Optional. Tristate constant that indicates whether or not to
place negative values within parentheses. See Settings section for values.

 

GroupDigits

 

Optional. Tristate constant that indicates whether or not
numbers are grouped using the group delimiter specified in the computer's
regional settings. See Settings section for values.

 

Settings

 

The IncludeLeadingDigit, UseParensForNegativeNumbers, and GroupDigits
arguments have the following settings:

 

Constant Value Description TristateTrue -1 True TristateFalse 0 False TristateUseDefault -2 Use the setting from the computer's regional
settings.

 

Remarks

 

When one or more optional arguments are omitted, values for omitted arguments
are provided by the computer's regional settings. The position of the currency
symbol relative to the currency value is determined by the system's regional
settings.

 

Note   All settings information comes from the
Regional Settings Currency tab, except leading zero, which comes from the Number
tab.

 

The following example uses the FormatCurrency function to format the
expression as a currency and assign it to MyCurrency:

Dim MyCurrency
MyCurrency = FormatCurrency(1000)   ' MyCurrency contains $1000.00.

请先登录再写评论。