Problems with LEFT function 1

[ecobb]

Hi, I'm new to the world of AS/400, most of my backgroud has been SQL Server with some Oracle thrown in there. I'm having problems trying to use the LEFT function in a query.

I have a column that is storing a date value as a number, for example today's date 05/14/2004 is stored in the format of 20040514 (yyyymmdd). Now, if I want to run a query on a specific date, (Where Date = 20040514) everything works great. But I need to pull all records for a certain month. Since I have a numeric datatype, I can't run any type of Month or Date functions, so I decided to just take the left six numbers (yyyymm) and use those.

Here's my query:

SELECT DISTINCT I.TLOCCD AS Location
FROM  ARLIB.INVTRAN I
INNER JOIN  ARLIB.PRODBASE P ON P.BROD = I.TITEM 
[red]WHERE 	LEFT(I.TDATE, 6) = 200405[/red]
AND 	P.BWGT > 0

This is the error I get:
Argument 1 of function LEFT not valid.

I tried putting single quotes around 'I.TDATE' but that just passes the value as a literal. I don't get errors but the query doesn't return results.

Since I know very little about the AS/400, I was wondering if someone could tell me if have some syntax problems in my query, or if theres a better way to accomplish what I'm trying to do.

Thanks!




Hope This Helps!

Ecobb

"My work is a game, a very serious game." - M.C. Escher

 

[MarcLodge]

Try

SELECT DISTINCT I.TLOCCD AS Location
FROM  ARLIB.INVTRAN I
INNER JOIN  ARLIB.PRODBASE P ON P.BROD = I.TITEM 
WHERE     SUBSTR(I.TDATE, 1,6) = 200405
AND     P.BWGT > 0

instead

 

[ecobb]

Thanks MarcLodge, but now I'm getting this:

Number of arguments for function LEFT not valid.

Just so I know for furture reference, what was reasoning behind adding the "1," to the LEFT function? What does that do?

Thanks!



Hope This Helps!

Ecobb

"My work is a game, a very serious game." - M.C. Escher

 

[PruSQLer]

Another way to do what you want is:


Where month(I.DATE) = 5
and year(I.DATE) = 2004

 

[ecobb]

Thanks PruSQLer, but since I'm working with a numeric datatype, I can't run any type of Month or Date functions, can I? How would I specify which part of '20040514' should be considered the month and year?

Thanks!



Hope This Helps!

Ecobb

"My work is a game, a very serious game." - M.C. Escher

 

[PruSQLer]

Ah yes, bad reading job on my part, you said that right at the top. Sorry.

This might work:

substr(char(decimal(I.DATE,8) ),1,6)

 

[ecobb]

Thanks again, PruSQLer! It's getting close. When I use this WHERE clause, it works great.

WHERE substr(char(decimal(I.TDATE,8) ),1,6) = '200405'

But, if i change the "Date" to last month ('200404'), it gives me this error:

Descriptor index not valid.(218)

The previous months (03, 02) seem to work fine. I don't know if this helps or not, but I was getting the same error message when I tried:
WHERE I.TDATE >= 20040401 AND I.TDATE <= 20040501

It seems that there's some reason that it doesn't like the month 04. Any ideas?



Hope This Helps!

Ecobb

&quot;My work is a game, a very serious game.&quot; - M.C. Escher

 

[PruSQLer]

That makes no sense. As far as I know, DB2 doesn't have anything against using a 4, as opposed to other numbers, in a SUBSTR function. What's the complete message you get with the error?

 

[ecobb]

Thanks so much for your help, PruSQLer. You were right, it doesn't seem to have anything to do with the date. Even when I run this simplified query:

SELECT DISTINCT I.TLOCCD AS Location
FROM	ARLIB.INVTRAN I

I still get this error:

Error Executing Database Query.  
Descriptor index not valid.(218)  

line
133 : SELECT DISTINCT I.TLOCCD AS Location
134 : FROM	ARLIB.INVTRAN I

I have another query that I run just before this one and there are no problems with it using the snippet of code you gave me. It's working great! Now I'm starting to wonder if there may be a problem with some of the data in the TLOCCD column and it just happens to be in the '04' month. I have no problems pulling info from there unless I try that month. Would something like that be causing this error?

Thanks!



Hope This Helps!

Ecobb

&quot;My work is a game, a very serious game.&quot; - M.C. Escher

 

[ecobb]

That was it, there was a problem with one of the records in that table. Thanks a million for you help, PruSQLer!



Hope This Helps!

Ecobb

&quot;My work is a game, a very serious game.&quot; - M.C. Escher

 

[PruSQLer]

No problem, Ecobb.

And I never realized M.C. was a serious guy.