Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2004
    Posts
    54

    Unanswered: A simple select statement - I'm stumped!

    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,
    .
    .
    .
    ============

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2004
    Posts
    54
    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>

  5. #5
    Join Date
    Nov 2004
    Posts
    54
    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 *****************************

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by goldfishhh View Post
    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.

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb Just change it

    Quote Originally Posted by goldfishhh View Post
    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

  8. #8
    Join Date
    Nov 2004
    Posts
    54
    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                    
    ---------+---------+---------+---------+---------+---------+---------+---------+

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    String literals are enclosed in single quotes, not double quotes. Also, CONCAT takes only two parameters, as far as I know.

  10. #10
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question Need some classes

    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

  11. #11
    Join Date
    Nov 2004
    Posts
    54
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •