Issue with multiply in DB2



IBM's flagship relational database management system

Issue with multiply in DB2

Postby Papya013 » Fri Jun 17, 2016 12:36 pm

Hi Team,

I am using below mentioned query to get the multiplied value of three columns and then I will doing the order by on the multiplied value.

Columns from table used for multiplication are as mentioned below.

10 LAGERVARDERING-PRS   PIC S9(6)V9(2) USAGE COMP-3.  
10 LEVERERAD-BPK        PIC S9(5)V USAGE COMP-3.            
10 GM-KBPAK             PIC S9(5)V USAGE COMP-3.


Working store variable to receive the multiplied value.

05 SECB-BELLOP                        PIC 9(18)V9(9).


Query used:

SELECT TERM,DEPA,GM_VGR,CAST(BELLOP AS DECIMAL(18,9)) FROM
 (SELECT                                                  
   '505' AS TERM,                                        
   '01' AS DEPA,                                          
   GM_VGR,                                                
   SUM(CASE WHEN LEVERERAD_VKT>0                          
   THEN(LAGERVARDERING_PRS *LEVERERAD_BPK*GM_KBPAK)      
   ELSE(DPRIS_TOT*LAGERVARDERING_PRS/DEBITERINGS_PRS)    
   END) AS BELLOP                                        
FROM T_TABEL1 U,T_TABLE2 R )  


I am getting below mentioned issue with the variable defined.

0DSNH312I E     DSNHSMUD LINE 2006 COL 31  UNDEFINED OR UNUSABLE HOST VARIABLE "SECB-BELLOP"  


Thankful if someone could help me in fixing the issue.
Papya013
 
Posts: 35
Joined: Wed Nov 06, 2013 6:53 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Issue with multiply in DB2

Postby NicC » Fri Jun 17, 2016 3:22 pm

I do not know why, as you state DB2 in the topic tile, you posted in the COBOL forum. The language used is immaterial - it is your SQL or program structure, that is wrong. Topic moved.

BTW, SECB_BELLOP is not mentioned in the SQL that you have shown so it is probably the wrong bit of SQL that you have posted.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: Issue with multiply in DB2

Postby Raja190 » Tue Jun 21, 2016 5:39 pm

@Papaya013,

Can you post some details of the error message. this doesn't seem like DB2 error.

and I don't think sum is necessary on your query, try below.


SELECT TERM,DEPA,GM_VGR,CAST(BELLOP AS DECIMAL(18,9)) FROM
 (SELECT                                                  
   '505' AS TERM,                                        
   '01' AS DEPA,                                          
   GM_VGR,                                                
   ,CASE WHEN LEVERERAD_VKT>0                          
   THEN(LAGERVARDERING_PRS *LEVERERAD_BPK*GM_KBPAK)      
   ELSE(DPRIS_TOT*LAGERVARDERING_PRS/DEBITERINGS_PRS)    
   END AS BELLOP                                        
FROM T_TABEL1 U,T_TABLE2 R )
Raja190
 
Posts: 36
Joined: Mon Dec 14, 2015 8:13 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Issue with multiply in DB2

Postby enrico-sorichetti » Tue Jun 21, 2016 6:06 pm

0DSNH312I E     DSNHSMUD LINE 2006 COL 31  UNDEFINED OR UNUSABLE HOST VARIABLE "SECB-BELLOP"  

Can you post some details of the error message. this doesn't seem like DB2 error.

what more details do You want?
it is not a DB2 error in se,
DB2 discovered and error in the sql statement and told the user about it.
the user should fix the sql statement!
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2994
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 164 times

Re: Issue with multiply in DB2

Postby GuyC » Tue Nov 22, 2016 6:21 pm

a bit late :
- SECB-BELLOP is not defined as COMP-3 , it should
- decimal(18,9) is not S9(18)v9(9) comp-3 it is actually s9(9)v9(9) comp-3. 18 = precision = #significant digits, 9 = scale = digits behind the comma.
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post