A simple select statement - I'm stumped!

goldfishhh · #1 (**permalink**) 05-27-10, 08:58

Ok gang, I've been doing the MS Access thing for many years and now am playing with DB2 - yes, I finally grew up.

Anyway, I simply can't get this to work and need a very small push in the right direction. I'm trying to look at a date and if it is within a range, make the field value 1 otherwise 0.

When I run this code through SPUFI, I get this error (See below) and know that it is related to my IF/THEN statement. I have tried to use CASE with the same results.

What simple thing am I overlooking?

Oh, this is DB2 for Zos v9.1.

---------+---------+---------+---------+---------+---------+---------+---------
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "(". SOME SYMBOLS THAT MIGHT
BE LEGAL ARE: FROM INTO , <IDENTIFIER> AS || CONCAT * / + -
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 3 0 0 -1 516 502 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000003' X'00000000' X'00000000' X'FFFFFFFF'
X'00000204' X'000001F6' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+---------

Here are a few versions I have tried with no success:

============
SELECT TST_ABEND_ID,
AJ.JOB_NBR,
DT_DT,
JOB_TXT,
APPLCTN_TXT,

CASE WHEN DATE(DT_DT)>=DATE('2008-1-1')
AND DATE(DT_DT)<=DATE('2008-1-31')
THEN COUNT(AJ.JOB_NBR) AS CNT_JAN_2008_NBR
.
.
.

===========

SELECT TST_ABEND_ID,
AJ.JOBNUM_NBR,
DT_DT,
JOB_TXT,
APPLCTN_TXT,

12 AS CNT_JAN_2008_NBR,
WHEN MONTH(DT_DT) = 1 THEN 1 ELSE 0 END CASE
AS CNT_FEB_2008_NBR,
.
.
.
============

dav1mo · #2 (**permalink**) 05-27-10, 09:18

Bot sure why you are getting the -104 as you did not supply that part of the SQL. Your first example's case statement looks fine, the second one though CASE should be at front not at the end. Something like:
CASE WHEN MONTH(DT_DT) = 1 THEN 1 ELSE 0 END AS CNT_FEB_2008_NBR

Also, it is a better practice to use ELSE NULL, as if you start counting or summing your case statement, you will, normally, get a little faster response as the NULL is not added/counted.
Dave

r937 · #3 (**permalink**) 05-27-10, 09:20

if you're trying to count, put the CASE inside the COUNT function...

Code:

COUNT( CASE WHEN DATE(DT_DT) >= DATE('2008-1-1')
             AND DATE(DT_DT) <= DATE('2008-1-31')
            THEN 1 ELSE NULL END ) AS CNT_JAN_2008_NBR

goldfishhh · #4 (**permalink**) 05-27-10, 10:35

Bingo! It worked. Putting the "(" outside of the CASE worked like a champ.

<code>
(CASE WHEN DT_DT >= DATE('2008-1-1')
AND DT_DT <= DATE('2008-1-31')
THEN 1 ELSE NULL END) AS CNT_JAN_2008_NBR,
</code>

goldfishhh · #5 (**permalink**) 05-27-10, 10:57

Ok, lets push things up a notch a tad. In the MS world, for me to run a MAKE TABLE query, I would start with my SELECT then use INTO <table>

The field names in the select statement would then match up with the destination table and the new table would be made (or appended). I am struggling to find where DB2 will let me do this and I think my syntax is backwards.

Code:

SELECT AJ.JOBNUM_NBR,TST_ABEND_ID,             
       DT_DT,                                  
       JOB_TXT,                                
       APPLCTN_TXT,                            
                                               
(CASE WHEN DT_DT >= DATE('2008-1-1')           
 AND DT_DT <= DATE('2008-1-31')                
 THEN 1 ELSE NULL END) AS CNT_JAN_2008_NBR
INTO TST_CYC_ABEND_TOT                                        
FROM TOTDB01.ALL_JOBS   AJ                                    
LEFT JOIN TOTDB01.TST_ABEND                                   
  ON TOTDB01.ALL_JOBS.NM_TXT = TOTDB01.TST_ABEND.JOB_TXT;   
  
---------+---------+---------+---------+---------+---------+---------+-----
DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL "TST_CYC_ABEND_TOT". SOME  
         SYMBOLS THAT MIGHT BE LEGAL ARE: :                                  
DSNT418I SQLSTATE   = 42601 SQLSTATE RETURN CODE                             
DSNT415I SQLERRP    = DSNHSMS1 SQL PROCEDURE DETECTING ERROR                 
DSNT416I SQLERRD    = 2 0  0  -1  6630  502 SQL DIAGNOSTIC INFORMATION       
DSNT416I SQLERRD    = X'00000002'  X'00000000'  X'00000000'  X'FFFFFFFF'     
         X'000019E6'  X'000001F6' SQL DIAGNOSTIC INFORMATION                 
---------+---------+---------+---------+---------+---------+---------+-------
DSNE618I ROLLBACK PERFORMED, SQLCODE IS 0                                    
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0                    
---------+---------+---------+---------+---------+---------+---------+-------
DSNE601I SQL STATEMENTS ASSUMED TO BE BETWEEN COLUMNS 1 AND 72               
DSNE620I NUMBER OF SQL STATEMENTS PROCESSED IS 1                             
DSNE621I NUMBER OF INPUT RECORDS READ IS 96                                  
DSNE622I NUMBER OF OUTPUT RECORDS WRITTEN IS 113                             
******************************** Bottom of Data *****************************

n_i · #6 (**permalink**) 05-27-10, 11:09

Quote:

Originally Posted by goldfishhh

In the MS world, for me to run a MAKE TABLE query, I would start with my SELECT then use INTO <table>

That would look like INSERT INTO table ... SELECT ...

The target table must exist.

Lenny77 · #7 (**permalink**) 05-27-10, 11:12

Quote:

Originally Posted by goldfishhh

Ok, lets push things up a notch a tad. In the MS world, for me to run a MAKE TABLE query, I would start with my SELECT then use INTO <table>

The field names in the select statement would then match up with the destination table and the new table would be made (or appended). I am struggling to find where DB2 will let me do this and I think my syntax is backwards.

Code:

SELECT AJ.JOBNUM_NBR,TST_ABEND_ID,             
       DT_DT,                                  
       JOB_TXT,                                
       APPLCTN_TXT,                            
                                               
(CASE WHEN DT_DT >= DATE('2008-1-1')           
 AND DT_DT <= DATE('2008-1-31')                
 THEN 1 ELSE NULL END) AS CNT_JAN_2008_NBR
INTO TST_CYC_ABEND_TOT                                        
FROM TOTDB01.ALL_JOBS   AJ                                    
LEFT JOIN TOTDB01.TST_ABEND                                   
  ON TOTDB01.ALL_JOBS.NM_TXT = TOTDB01.TST_ABEND.JOB_TXT;   
  
---------+---------+---------+---------+---------+---------+---------+-----
DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL "TST_CYC_ABEND_TOT". SOME  
         SYMBOLS THAT MIGHT BE LEGAL ARE: :                                  
DSNT418I SQLSTATE   = 42601 SQLSTATE RETURN CODE                             
DSNT415I SQLERRP    = DSNHSMS1 SQL PROCEDURE DETECTING ERROR                 
DSNT416I SQLERRD    = 2 0  0  -1  6630  502 SQL DIAGNOSTIC INFORMATION       
DSNT416I SQLERRD    = X'00000002'  X'00000000'  X'00000000'  X'FFFFFFFF'     
         X'000019E6'  X'000001F6' SQL DIAGNOSTIC INFORMATION                 
---------+---------+---------+---------+---------+---------+---------+-------
DSNE618I ROLLBACK PERFORMED, SQLCODE IS 0                                    
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0                    
---------+---------+---------+---------+---------+---------+---------+-------
DSNE601I SQL STATEMENTS ASSUMED TO BE BETWEEN COLUMNS 1 AND 72               
DSNE620I NUMBER OF SQL STATEMENTS PROCESSED IS 1                             
DSNE621I NUMBER OF INPUT RECORDS READ IS 96                                  
DSNE622I NUMBER OF OUTPUT RECORDS WRITTEN IS 113                             
******************************** Bottom of Data *****************************

If I understood your query well, you have to change it in following way:

Code:

SELECT AJ.JOBNUM_NBR,TST_ABEND_ID,             
       DT_DT,                                  
       JOB_TXT,                                
       APPLCTN_TXT,                            
       CASE WHEN DT_DT between DATE('2008-01-01') AND DATE('2008-01-31')                
            THEN 1 
       END AS CNT_JAN_2008_NBR
INTO :TST-CYC-ABEND-TOT                                        
FROM TOTDB01.ALL_JOBS       AJ                                    
LEFT JOIN TOTDB01.TST_ABEND AB                                  
  ON aj.NM_TXT = ab.JOB_TXT

Lenny

goldfishhh · #8 (**permalink**) 05-27-10, 13:56

Well, now I'm on a roll. I got a few dozen tables further, created some cool things and now hit a brick wall. I want to create a new unique number without repeats (used in a temp table as my new primary key) using two existing numbers. The field type is INTEGER for both therefore I can't add the 2 numbers together, but if I can make up a new number, Ill be ok.

Code:

SELECT JOBNUM_NBR                          
           AS TST_CYC_ABEND_TOT_ID,        
       TST_ABEND_ID AS JOBNUM_NBR,         
       MONTH(DT_DT) AS DT_DT,

Here is what I want to do.

Code:

SELECT (CONCAT(EXP(JOBNUM_NBR),".",EXP(TST_ABEND_ID)))        
           AS TST_CYC_ABEND_TOT_ID,                           
       TST_ABEND_ID AS JOBNUM_NBR,                            
       MONTH(DT_DT) AS DT_DT,                                 
       JOB_TXT AS JOB_TXT,                                    
       APPLCTN_TXT AS APPLCTN_TXT,                            
  APPLCTN_TXT AS APPLCTN_PFX_TXT,

It looks like the system doesn't want me putting a period in there.

Code:

---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -206, ERROR:  . IS NOT VALID IN THE CONTEXT WHERE IT IS USED 
DSNT418I SQLSTATE   = 42703 SQLSTATE RETURN CODE                                
DSNT415I SQLERRP    = DSNXORSO SQL PROCEDURE DETECTING ERROR                    
DSNT416I SQLERRD    = -100 0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION            
DSNT416I SQLERRD    = X'FFFFFF9C'  X'00000000'  X'00000000'  X'FFFFFFFF'        
         X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION                    
---------+---------+---------+---------+---------+---------+---------+---------+

n_i · #9 (**permalink**) 05-27-10, 14:11

String literals are enclosed in single quotes, not double quotes. Also, CONCAT takes only two parameters, as far as I know.

Lenny77 · #10 (**permalink**) 05-27-10, 14:20

Somebody want to learn DB2 on this forum, I think:

Bad:

Code:

SELECT (CONCAT(EXP(JOBNUM_NBR),".",EXP(TST_ABEND_ID)))        
           AS TST_CYC_ABEND_TOT_ID

Better:

Code:

SELECT char(EXP(JOBNUM_NBR)) || '.' || char(EXP(TST_ABEND_ID))  AS TST_CYC_ABEND_TOT_ID

Lenny

goldfishhh · #11 (**permalink**) 05-27-10, 14:37

My poor head had been so tied up in MSSQL and Access for many years, I have to relearn the "real" way of doing things. This sure is a huge change from the Windows world.

Yes, I might need a refresher course or ninteen.

Thanks for the help.

Oh, it worked wonders.

Internet Services

Web Development

Digital Marketing

Consumer Tech