Solved

Problem to script

Posted on 2015-02-17
14
207 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
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:Dave Ford
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:Dave Ford
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:Dave Ford
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:
Dave Ford 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Leveraging AD Groups in DB2 1 93
Access to SYSIBM tables 7 125
aix tls version 6 648
DB2 - How to load results from a Stored Procedure into a table 8 35
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…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…
Suggested Courses

751 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