Solved

Problem to script

Posted on 2015-02-17
14
192 Views
Last Modified: 2015-02-25
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

0
Comment
Question by:HuaMinChen
14 Comments
 
LVL 37

Expert Comment

by:bbao
ID: 40615953
it just means the field in WHERE clause does not exist. double check any typo please?
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40615958
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?
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40615976
Any other help?
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 10

Author Comment

by:HuaMinChen
ID: 40616001
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.
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40616034
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?
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40616058
BTW, can I have one example to subtract one day from given date? Thanks
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40616067
Exactly, what is the equivalent date to this

1,150,130

Open in new window


?
0
 
LVL 18

Expert Comment

by:daveslash
ID: 40616988
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
0
 
LVL 18

Expert Comment

by:daveslash
ID: 40617047
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
0
 
LVL 18

Expert Comment

by:daveslash
ID: 40617091
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
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40625004
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?
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40625012
I mean the current script does keep on running there for long while. Why?
0
 
LVL 18

Accepted Solution

by:
daveslash earned 500 total points
ID: 40625957
I can't say for sure why your script runs too long, but take out the DISTINCT and try it again.
0
 
LVL 27

Expert Comment

by:tliotta
ID: 40632457
...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
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question