LinuxQuestions.org
Review your favorite Linux distribution.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices

Reply
 
Search this Thread
Old 03-25-2004, 03:12 PM   #1
Sparkle1984
LQ Newbie
 
Registered: Mar 2004
Posts: 1

Rep: Reputation: 0
SQL Problem on DB2


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.
 
Old 03-25-2004, 04:42 PM   #2
Tinkster
Moderator
 
Registered: Apr 2002
Location: in a fallen world
Distribution: slackware by choice, others too :} ... android.
Posts: 22,983
Blog Entries: 11

Rep: Reputation: 879Reputation: 879Reputation: 879Reputation: 879Reputation: 879Reputation: 879Reputation: 879
Re: SQL Problem on DB2

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
 
Old 06-28-2010, 04:49 AM   #3
sharmapramod91
LQ Newbie
 
Registered: Jun 2010
Posts: 1

Rep: Reputation: 0
convert into db2 sql procedure

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'];
}

Last edited by sharmapramod91; 06-28-2010 at 04:51 AM.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
DB2 Log problem.... apenguinlinux Linux - Software 0 07-25-2005 04:32 AM
DB2 vendor API problem shitij Linux - Software 0 08-22-2004 05:23 PM
Problem using DB2 Vendor APIs shitij Linux - Software 0 08-17-2004 01:36 AM
Problem in using and starting DB2 shitij Linux - Software 1 07-27-2004 12:14 AM
DB2 & Java problem/ Please HELP! Bogdan Linux - Software 0 05-06-2002 02:23 PM


All times are GMT -5. The time now is 12:24 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration