Problem to script

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

LVL 12
HuaMin ChenProblem resolverAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bbaoIT ConsultantCommented:
it just means the field in WHERE clause does not exist. double check any typo please?
0
HuaMin ChenProblem resolverAuthor 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?
0
HuaMin ChenProblem resolverAuthor Commented:
Any other help?
0
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

HuaMin ChenProblem resolverAuthor 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.
0
HuaMin ChenProblem resolverAuthor 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?
0
HuaMin ChenProblem resolverAuthor Commented:
BTW, can I have one example to subtract one day from given date? Thanks
0
HuaMin ChenProblem resolverAuthor Commented:
Exactly, what is the equivalent date to this

1,150,130

Open in new window


?
0
Dave FordSoftware Developer / Database AdministratorCommented:
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
Dave FordSoftware Developer / Database AdministratorCommented:
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
Dave FordSoftware Developer / Database AdministratorCommented:
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
HuaMin ChenProblem resolverAuthor 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?
0
HuaMin ChenProblem resolverAuthor Commented:
I mean the current script does keep on running there for long while. Why?
0
Dave FordSoftware Developer / Database AdministratorCommented:
I can't say for sure why your script runs too long, but take out the DISTINCT and try it again.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tliottaCommented:
...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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.