[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 19801
  • Last Modified:

SQLSTATE=42703

any way i can around this query
select  ICRTDT ,
     substr (cast (int( ICRTDT)+20000000 as char (8)), 1, 4) || '-' ||
    substr (cast (int( ICRTDT)+20000000 as char (8)), 5, 2) || '-' ||
    substr (cast (int( ICRTDT)+20000000 as char (8)), 7, 2) as NEWICRTDT
   from v_aaa_item
where NEWICRTDT = '2001-06-07'
fetch first 10 rows only;
 

select  ICRTDT , substr (cast (int( ICRTDT)+20000000 as char (8)), 1, 4) || '-' || substr (cast (int( ICRTDT)+20000000 as char (8)), 5, 2) || '-' || substr (cast (int( ICRTDT)+20000000 as char (8)), 7, 2) as NEWICRTDT from v_aaa_item where NEWICRTDT = '2001-06-07' fetch first 10 rows only
SQL0206N  "NEWICRTDT" is not valid in the context where it is used.  
SQLSTATE=42703

SQL0206N  "NEWICRTDT                                                             " is not valid in the context where it is used.
thanks
Bobby
0
bobby2929
Asked:
bobby2929
  • 4
  • 4
  • 2
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi bobby2929,

A couple of ways.  Perhaps the easiest is to treat the list as a subquery.

SELECT * FROM
(
  select  ICRTDT ,
      substr (cast (int( ICRTDT)+20000000 as char (8)), 1, 4) || '-' ||
      substr (cast (int( ICRTDT)+20000000 as char (8)), 5, 2) || '-' ||
      substr (cast (int( ICRTDT)+20000000 as char (8)), 7, 2) as NEWICRTDT
   from v_aaa_item
) AS a
where NEWICRTDT = '2001-06-07'
fetch first 10 rows only;


If performance is an issue, you can also repeat the computing in the WHERE clause.

select  ICRTDT ,
    substr (cast (int( ICRTDT)+20000000 as char (8)), 1, 4) || '-' ||
    substr (cast (int( ICRTDT)+20000000 as char (8)), 5, 2) || '-' ||
    substr (cast (int( ICRTDT)+20000000 as char (8)), 7, 2) as NEWICRTDT
   from v_aaa_item
where '2001-06-07' =
    substr (cast (int( ICRTDT)+20000000 as char (8)), 1, 4) || '-' ||
    substr (cast (int( ICRTDT)+20000000 as char (8)), 5, 2) || '-' ||
    substr (cast (int( ICRTDT)+20000000 as char (8)), 7, 2)
fetch first 10 rows only;


Note however, that your math appears off.  You probably don't want to add 20000000 to the month and day values.



Good Luck!
Kent
0
 
LowfatspreadCommented:
what is the underlying data type of ICRTDT?

and what are you attempting to do?

wouldn't using the DATE functions... be more appropriate?
DATE/TIMESTAMP/DAY/MONTH/YEAR ...

which version and O/S of DB2/UDB are you using?
0
 
bobby2929Author Commented:
ICRTDT IS IN DECIMAL
your rite date format should be appropriate for NEWICRTDT
how can i do cast in date format ?
thanks
bobby
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
Kent OlsenData Warehouse Architect / DBACommented:
Hi bobby2929,

SELECT * FROM
(
  select ICRTDT,
    cast (
      (cast ((ICRTDT/10000+2000) as char(4)) || '-' ||
      cast (MOD(ICRTDT/100, 100) as char(2)) || '-' ||
      cast (MOD(ICRTDT, 100) as char(2))) as date) as NEWICRTDT
  from v_aaa_item
) AS a
where NETICRTDT = '2001-06-07'
fetch first 10 rows only;


   
Kent
0
 
LowfatspreadCommented:
Select  ...
           ,Date(substr(newicrtdt,7,2) concat '.' concat substr(newicrtdt,5,2) concat '.' concat substr(newicrtdt,1,4)) as newicrtdt
   from (
select a.* ,
         cast(icrtdt as char(8)) as newicrtdt
  from v_aaa_item
 ) as x  
where neticrtdt = '2001-06-07'
fetch first 10 rows only
0
 
bobby2929Author Commented:
HI KDO
its version 8.2
SELECT * FROM
(
  select ICRTDT,
    cast (
      (cast ((ICRTDT/10000+2000) as char(4)) || '-' ||
      cast (MOD(ICRTDT/100, 100) as char(2)) || '-' ||
      cast (MOD(ICRTDT, 100) as char(2))) as date) as NEWICRTDT
  from v_aaa_item
) AS a
where NEWICRTDT = '2001-06-07'
fetch first 10 rows only;
------------------------------------------------------------------------------
SELECT * FROM ( select ICRTDT, cast ( (cast ((ICRTDT/10000+2000) as char(4)) || '-' || cast (MOD(ICRTDT/100, 100) as char(2)) || '-' || cast (MOD(ICRTDT, 100) as char(2))) as date) as NEWICRTDT from v_aaa_item ) AS a where NEWICRTDT = '2001-06-07' fetch first 10 rows only
SQL0440N  No authorized routine named "MOD" of type "FUNCTION" having
compatible arguments was found.  SQLSTATE=42884
Thanks,
Bobby
0
 
Kent OlsenData Warehouse Architect / DBACommented:

MOD() expects two integer parameters.  The fact that it's failing suggests that ICRTDT is not an integer type.

How is ICRTDT defined?


Kent
0
 
bobby2929Author Commented:
ICRTDT IS DECIMAL
0
 
Kent OlsenData Warehouse Architect / DBACommented:

MOD() should convert the decimal to integer.  weird.

Just for grins, try this one:


SELECT * FROM
(
  select ICRTDT,
    cast (
      (cast ((ICRTDT/10000+2000) as char(4)) || '-' ||
      cast (MOD(INT(ICRTDT/100), 100) as char(2)) || '-' ||
      cast (MOD(INT(ICRTDT), 100) as char(2))) as date) as NEWICRTDT
  from v_aaa_item
) AS a
where NEWICRTDT = '2001-06-07'
fetch first 10 rows only;
0
 
bobby2929Author Commented:
-----------------------------------------------------------------------------
SELECT * FROM ( select ICRTDT, cast ( (cast ((ICRTDT/10000+2000) as char(4)) || '-' || cast (MOD(INT(ICRTDT/100), 100) as char(2)) || '-' || cast (MOD(INT(ICRTDT), 100) as char(2))) as date) as NEWICRTDT from v_aaa_item ) AS a where NEWICRTDT = '2001-06-07' fetch first 10 rows only

ICRTDT    NEWICRTDT
--------- ----------
SQL0180N  The syntax of the string representation of a datetime value is
incorrect.  SQLSTATE=22007

SQL0180N  The syntax of the string representation of a datetime value is incorrect.

Explanation:

The string representation of a date, time, or timestamp value
does not conform to the syntax for the specified or implied data
type.  

 The statement cannot be processed.  

User Response:

Ensure that the syntax of the date, time, or timestamp value
conforms to the syntax for its data type.  If the string is not
intended to be a date, time, or timestamp value, ensure that when
used, it does not imply that data type.  

 Federated system users: the problem might be due to a date/time
representation problem at the data source.  If the reason is
unknown, isolate the problem to the data source failing the
request problem determination guide and examine the date/time
representation restrictions for that data source.  

 sqlcode :  -180

 sqlstate :  22007

0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now