Solved

Problem to script

Posted on 2015-02-17
14
220 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 11

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 11

Author Comment

by:HuaMinChen
ID: 40615976
Any other help?
0
Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

 
LVL 11

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 11

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 11

Author Comment

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

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 11

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 11

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

Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

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 (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

623 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