Problem to script

HuaMin Chen
HuaMin Chen used Ask the Experts™
on
Hi,
I get the problem to the script below. why?

SELECT * FROM TAB1 where varchar($postingdate)='20150217'
Column $POSTINGDATE not in specified tables.             

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
bbaoIT Consultant

Commented:
it just means the field in WHERE clause does not exist. double check any typo please?
HuaMin ChenProblem resolver

Author

Commented:
Sorry, when I only put the script without "where" clause, I can see this column name

  Posting  
  Date     

Open in new window

         
how to refer to it?
HuaMin ChenProblem resolver

Author

Commented:
Any other help?
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

HuaMin ChenProblem resolver

Author

Commented:
Hi,
I run this

Select distinct(name), ColType, Length from syscolumns where tbname = 'TAB1'

Open in new window


but I still do not see the name of the column to which I'm referencing.
HuaMin ChenProblem resolver

Author

Commented:
Hi,
I see the relevant date values are

1,021,204 
1,021,204 
1,021,211 
1,021,211 
1,021,211 
1,021,218 
1,031,007 
1,031,209 
...

Open in new window


how can I know the exact equivalent date to the above values?
HuaMin ChenProblem resolver

Author

Commented:
BTW, can I have one example to subtract one day from given date? Thanks
HuaMin ChenProblem resolver

Author

Commented:
Exactly, what is the equivalent date to this

1,150,130

Open in new window


?
Dave FordSoftware Developer / Database Administrator

Commented:
OK, it looks like we have a couple questions here. Did you figure out the actual column-name? Is it postingDate?

Also, since we don't know your database definition, there's no way to be absolutely sure how that integer-looking field translates to a DATE. (BTW, never use an integer for a date .... just use the native DATE data-type. It's way easier to understand and deal with.)

I speculate that 1,150,130 corresponds to 2015-01-30. The leading '1' probably corresponds to the century ('1' for this century, '0' for last century). The rest of the number is probably two-digit-year ('15'), month ('01') and day ('30').

HTH,
DaveSlash
Dave FordSoftware Developer / Database Administrator

Commented:
Notice the complexity needed to translate your integer into a real date. That is exactly why you should NEVER use an integer to store a date. Any "space-savings" you gain is far outweighed by the unnecessary and error-prone complexity in dealing with the data.

select anIntegerDate,
       date(
       case integer(anIntegerDate/1000000)
         when 1 then '20'
         else '19'
       end ||
       lpad(varchar(mod(integer(anIntegerDate/10000), 100)),2,'0')
          || '-' ||
       lpad(varchar(mod(integer(anIntegerDate/100),100)),2,'0')   
          || '-' ||
       lpad(varchar(mod(anIntegerDate, 100)),2,'0')) aRealDate
  from MySchema.deleteme

ANINTEGERDATE   AREALDATE 
    1,150,130   2015-01-30
    1,021,204   2002-12-04
    1,021,211   2002-12-11
    1,021,218   2002-12-18

Open in new window


HTH,
DaveSlash
Dave FordSoftware Developer / Database Administrator

Commented:
Hmmm ... I checked the DB2 for i manual (v7.1), and it looks like they still haven't implemented LPAD natively. So, you might have a hard time with the query above. But, this should help. I wrote it (maybe stole it) over 15 years ago.

CREATE FUNCTION LPad (
    inString VARCHAR(100),
    padLength integer,
    padChar varchar(1)
)
RETURNS varchar(100)
LANGUAGE SQL
modifies sql data

BEGIN
-- Scratch variables used for processing
DECLARE outString VARCHAR(100);

-- If the incoming string is NULL, return NULL
IF (inString IS NULL) THEN
   RETURN null;
End If;

-- Initialize the scratch variables
set OutString = right(REPEAT(padChar, padLength) || inString, padLength);

-- Return the final output
RETURN OutString;
END

Open in new window


HTH,
DaveSlash
HuaMin ChenProblem resolver

Author

Commented:
How to get the table structure of one table, within AS/400? I run this

Select distinct(name), ColType, Length from syscolumns where tbname = 'SCPF'

Open in new window


and want to see the structure of one table. But the script keeps on running. why?
HuaMin ChenProblem resolver

Author

Commented:
I mean the current script does keep on running there for long while. Why?
Software Developer / Database Administrator
Commented:
I can't say for sure why your script runs too long, but take out the DISTINCT and try it again.
...when I only put the script without "where" clause, I can see this column name

  Posting  
  Date    
That doesn't look like a column name. It looks like a column heading.
But the script keeps on running. why?
How are you running a "script"? What are you running the script on? What is your OS version?

We need to know something about the system before we can make guesses. Maybe the system is full, or maybe it needs some tuning, or maybe it's way behind in PTFs, or it's an out of date system, or maybe it hasn't been IPLed recently and it has far overcommitted unprotected space, or...? There's no way for us to know without a general description of the environment.

Can you provide some background?

Tom

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial