1 Reply Latest reply: Dec 19, 2012 8:08 AM by MLBrown RSS

    List of Values

    im99_chs
      Dear all good morning

      I would like to help me with a little problem in List of Values.
      Here is the Record Group code:

      SELECT UNIQUE D_YEAR,SEIRA, NUMFR,NUMTO, COM
      FROM SEQDOC, app_users
      WHERE D_YEAR >= SUBSTR(SYSDATE,7,10) and
      com LIKE concat(decode(:osuser,'mitilini', 'ΜΥΤΙΛΗΝΗ',
      'volos', 'ΒΟΛΟΣ',
      'zakinthos', 'ΖΑΚΥΝΘΟΣ',
      'iraklio', 'ΗΡΑΚΛΕΙΟ',
      'ioannina', 'ΙΩΑΝΝΙΝΑ',
      'kavala', 'ΚΑΒΑΛΑ',
      'kalamata', 'ΚΑΛΑΜΑΤΑ',
      'kerkira', 'ΚΕΡΚΥΡΑ',
      'kozani', 'ΚΟΖΑΝΗ',
      'komotini', 'ΚΟΜΟΤΗΝΗ',
      'larisa', 'ΛΑΡΙΣΑ',
      'orestiada', 'ΟΡΕΣΤΙΑΔΑ',
      'patra', 'ΠΑΤΡΑ',
      'pirgos', 'ΠΥΡΓΟΣ',
      'rodos', 'ΡΟΔΟΣ',
      'serres', 'ΣΕΡΡΕΣ',
      'tripoli', 'ΤΡΙΠΟΛΗ',
      'florina', 'ΦΛΩΡΙΝΑ',
      'xania', 'ΧΑΝΙΑ') ,'%')
      order by com

      but the where condition seems that it doesn't work!!! The LOV brings all the information instead of a piece.
      Shall I create a block in my form with APP_USER??


      TABLE SEQDOC
      (
      D_YEAR VARCHAR2(4),
      SEIRA VARCHAR2(2) ,
      TYPE NUMBER(1) ,
      GRAMENT NUMBER(1),
      DOCNO1 NUMBER(15),
      DOCNO2 NUMBER(15),
      DEPOT VARCHAR2(2),
      NUMFR NUMBER(7),
      NUMTO NUMBER(7),
      COM VARCHAR2(30)
      )

      TABLE APP_USERS
      (
      OSUSER VARCHAR2(20) NOT NULL,
      OSEPON VARCHAR2(30),
      OSPASSWORD VARCHAR2(20) NOT NULL,
      AUS_OKADMIN VARCHAR2(1) NOT NULL,
      AUS_DEPT_KEY VARCHAR2(4) NOT NULL,
      AUS_SID NUMBER(10) NOT NULL
      )

      Thanks in advance!
        • 1. Re: List of Values
          MLBrown
          It looks like your WHERE clause is wrong to me. When you do a SUBSTR(SYSDATE,7,10) you are telling the database to start at character 7 and check the next 10 characters. I believe what you are trying to do is extract character 7,8,9, and 10. To do that you would have to do a SUBSTR(SYSDATE,7,4), but if you are just trying to extract the year I wouldn't recommend a SUBSTR. Instead use a to char: TO_CHAR(SYSDATE, 'YYYY').
          SELECT UNIQUE D_YEAR,SEIRA, NUMFR,NUMTO, COM 
            FROM SEQDOC, app_users 
           WHERE D_YEAR >= TO_CHAR(SYSDATE, 'YYYY')  -- Use TO_CHAR to extract the Year from the SYSDATE
             AND com LIKE concat(decode(:osuser,'mitilini', 'ΜΥΤΙΛΗΝΗ',
                    'volos', 'ΒΟΛΟΣ',
                    'zakinthos', 'ΖΑΚΥΝΘΟΣ',
                    'iraklio', 'ΗΡΑΚΛΕΙΟ',
                    'ioannina', 'ΙΩΑΝΝΙΝΑ',
                    'kavala', 'ΚΑΒΑΛΑ',
                    'kalamata', 'ΚΑΛΑΜΑΤΑ',
                    'kerkira', 'ΚΕΡΚΥΡΑ',
                    'kozani', 'ΚΟΖΑΝΗ',
                    'komotini', 'ΚΟΜΟΤΗΝΗ',
                    'larisa', 'ΛΑΡΙΣΑ',
                    'orestiada', 'ΟΡΕΣΤΙΑΔΑ',
                    'patra', 'ΠΑΤΡΑ',
                    'pirgos', 'ΠΥΡΓΟΣ',
                    'rodos', 'ΡΟΔΟΣ',
                    'serres', 'ΣΕΡΡΕΣ',
                    'tripoli', 'ΤΡΙΠΟΛΗ',
                    'florina', 'ΦΛΩΡΙΝΑ',
                    'xania', 'ΧΑΝΙΑ') ,'%') 
           order by com