SQL Problem on DB2

Sparkle1984 · 03-25-2004, 03:12 PM

I am having a problem with an SQL query. This is my original table definition in IBM DB2:

Code:

CREATE TABLE Book
(
	BookID  INTEGER NOT NULL,
	Title  VARCHAR(50) NOT NULL,
	Price  DECIMAL(10,2) NOT NULL,
	CategoryID	INTEGER,
	PublisherID	INTEGER,
        CONSTRAINT PrimaryKey PRIMARY KEY
        (
          BookID
        )
);

That works fine, but now I want to write a query which will make a discount on all the books which have a certain CategoryID.
For example, implement a discount of 10% on all books in categoryID 1:

Code:

CREATE VIEW DiscountView AS SELECT BookID, Title, Price, CategoryID, PubliserID, Price/10 AS Discount FROM Book WHERE Book.CategoryID = 1;
SELECT * FROM DiscountView WHERE Book.CategoryID = 1;
UPDATE Book SET Price = Price - Discount WHERE Book.CategoryID = 1;

The Update statement is where it all goes wrong - I get an error message saying:
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0206N "DISCOUNT" is not valid in the context where it is used.
SQLSTATE=42703

Does anyone know how I can get it to work? It has to take a percentage value, because later I will write a Java Database Connectivity program whereby a user will specify a percentage value, to discount all the books in a specific category.

Thanks for any help.

Tinkster · 03-25-2004, 04:42 PM

Quote:

Originally posted by Sparkle1984

Code:

CREATE VIEW DiscountView AS SELECT BookID, Title, Price, CategoryID, PubliserID, Price/10 AS Discount FROM Book WHERE Book.CategoryID = 1;
SELECT * FROM DiscountView WHERE Book.CategoryID = 1;
UPDATE Book SET Price = Price - Discount WHERE Book.CategoryID = 1;

Two things:
I've never seen a "select [mumble] as [mumble] from before,
is that something new in UDB?

What do you expect Discount to be up there?
Or, in other words, how will the update statement know which
of the values that you assigned to discount above to pick?

Cheers,
Tink

sharmapramod91 · 06-28-2010, 04:49 AM

this is an sql query which is used into php, but i want to convert into DB2 Sql Procedure using cursor.

$sites_served = 0;
$sqlQ = "
SELECT
COUNT(*) as SITESSERVED
FROM
SPCESM.ESMSTTPF
WHERE
STTUSR='".user::getMask()."'
AND
STTCST<>0
AND
STTJDT='".dateConversion::dateToJulian(Date('m'),Date('d'),Date('Y'))."'
".($_POST['level']>0?"AND TRIM(STT".str_pad($_POST['level'],3,'0',STR_PAD_LEFT).")=TRIM('".$_POST[site_num]."')":"")."
";
$result = $db2->query($sqlQ);
for($i=1;$i<=$db2->getNumRows($result);$i++){
$row = $db2->getRow($result,$i);
$sites_served=$row['SITESSERVED'];
}