MY FILTER DOES NOT RETURN DATA ROWS, YES THE ROWS EXIST, WHAT'S UP?

What is the "Q" that you're concatenating to the dates? I don't see that defined anywhere.

-- DaveSlash

Howdy....

BETWEEN ' || Q || TRIM ( @BEGINDATE ) || Q || ' AND' || Q || TRIM ( @ENDDATE ) || Q || '

needs to change to:

BETWEEN 'Q' || TRIM ( @BEGINDATE ) || 'Q' AND 'Q' || TRIM ( @ENDDATE ) || 'Q'

or

BETWEEN Q || TRIM ( @BEGINDATE ) || Q AND Q || TRIM ( @ENDDATE ) || Q

or even

BETWEEN TRIM ( @BEGINDATE ) AND TRIM ( @ENDDATE )



And I suspect that the '@' sign will give you trouble, too.  Take it off of the variable/parameter names.


Kent

Hi Dave,

At first glance, I missed that he was comparing two constant strings, too.  :)


Kent

DECLARE Q CHAR ( 1 ) DEFAULT '''' ;

THIS HAS BEEN APPLIED TO CURRENT SPROC WITH NO CHANGE.

termdate format mm/dd/yyyy
termdatec format mmddyyyy

neither termdate or termdatec formats return rows.

THE SPROC BELOW DOES RETURN ROWS ON THE DATEENTERED COLUMN IN THE WHERE CLAUSE , ITS' FORMAT IS yyyymmdd
---------------------------------------------------------------------------------------------------------------------------------


CREATE PROCEDURE HCMDEV.NEWHIREREPORT ( IN @BEGINDATE CHAR(8),
                                        IN @ENDDATE CHAR(8),
                                        IN @DIVISIONDISTRICT CHAR(7),
                                        IN @PAYTYPE NUMERIC(5, 0) )
      RESULT SETS 1
      LANGUAGE SQL
      SPECIFIC NEWHIREREPORT

BEGIN


DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000' ;
DECLARE EOF_COND CHAR ( 1 ) DEFAULT '0' ;
DECLARE STMT VARCHAR ( 5000 ) ;
DECLARE BLANKS CHAR ( 1 ) DEFAULT ' ' ;
DECLARE Q CHAR ( 1 ) DEFAULT '''' ;

DECLARE CSR1 CURSOR FOR S1 ;



SET STMT = 'SELECT
                  EMP.EMPID ,
                  EMP.DIVISION ,
                  EMP.DISTRICT ,
                  EMP.LOCATION ,
                  EMP.FIRSTNAME ,
                  EMP.LASTNAME ,
                  REPLACE (EMP.TELEPHONE1 , '' '' , '''' ) AS TELEPHONE1 ,
                  TRANS.OPERATIONALAREA ,
                  TRANS.DATEENTERED ,
                  TRANS.HIREDATE ,
                  TRANS.REHIREDATE ,
                  EMP.JOBCODE ,
                  EMP.BASEPAYRATE
                    FROM
                  HCMDEV.EMPLOYEES EMP
                  INNER JOIN HCMDEV.TRANSACTIONS TRANS
                          ON EMP.EMPID = TRANS.EMPLOYEEID
                    WHERE 1=1
                    AND
                    (TRANS.TRANSACTIONTYPE = ' || Q || 'HI' || Q || ' OR TRANS.TRANSACTIONTYPE = ' || Q || 'RH' || Q || ')
                  AND
                    (TRANS.OPERATIONALAREA = ' || Q || 'FIELD' || Q || ')
                  AND (TRANS.DATEENTERED BETWEEN ' || Q || TRIM ( @BEGINDATE ) || Q || ' AND' || Q || TRIM ( @ENDDATE ) || Q || ')' ;

IF @PAYTYPE = '1' THEN SET STMT = TRIM ( STMT ) || ' AND EMP.PAYTYPE = ' || Q || 'H' || Q ; END IF ;
IF @PAYTYPE = '2' THEN SET STMT = TRIM ( STMT ) || ' AND EMP.PAYTYPE = ' || Q || 'S' || Q ; END IF ;

IF ( @DIVISIONDISTRICT <> BLANKS AND @DIVISIONDISTRICT <> 'ALL' ) THEN SET STMT = TRIM ( STMT ) || ' AND '
|| '('
|| ' EMP.DIVISION= ' || Q || TRIM ( @DIVISIONDISTRICT ) || Q
|| ' OR '
|| ' EMP.DISTRICT= ' || Q || TRIM ( @DIVISIONDISTRICT ) || Q
|| ')' ;
END IF ;

IF @DIVISIONDISTRICT = 'ALL' THEN SET STMT = TRIM ( STMT ) || ' AND '
|| ' EMP.DIVISION IN '
|| '(SELECT DISTINCT DIVISION FROM HCMDEV.EMPLOYEES WHERE UCASE(EMP.DIVISION) = LCASE(EMP.DIVISION)GROUP BY DIVISION)' ;
END IF ;


PREPARE S1 FROM STMT ;

OPEN CSR1 ;
RETURN ;
CLOSE CSR1 ;
END

Hi rhservan,

Are you sure that you need a DB2 forum?  The '@' symbol is a token used to start variable and parameter names in SQL Server, not DB2.  I doubt that this will work on DB2.

That said, it won't work on SQL Server either.  Most of your string concatenations won't work.

This comparison is not what you want.  In fact, it shouldn't complile.

TRANS.TRANSACTIONTYPE = ' || Q || 'HI' || Q || ' OR TRANS.TRANSACTIONTYPE = ' || Q || 'RH' || Q || '


Breaking the line down into tokens, you get:

TRANS.TRANSACTIONTYPE =
' || Q || '
HI
' || Q || '
   OR
TRANS.TRANSACTIONTYPE =
' || Q || '
RH
' || Q || '

In this case, the concatenation operator is within the string.  It needs to be the operator (separator) between strings.

And since Q is a constant, you don't need it unless the data is stored with whatever the value of Q is, appended to the front and back of the TRANSACTION TYPE string..



Kent

It Works in DB2 - See second SPROC - When I run the sproc it prompts me for the @BEGINDATE and it prompts me for @ENDDATE. I enter the information in its format yyyymmdd.  It returns all data for all columns in the sproc.  Also, this sproc has all of the Q format and everything works here.

I am debunking both the @ symbol and the Q issues since they both work in the second sproc.  Would you agree with my debunking based on the second sproc working without hindrance?

Are you running DB2 on the AS400?  That syntax won't work on most versions of DB2.

As I said, the value of Q is necessary only if your database prepends and appends it to the data that is stored in the database.

Wow.  I just caught that Q contains a single quote.  This is a very ugly way to do this.  :(

It looks a lot cleaner if you'll just put consecutive single quotes in the string instead of concatenating a quote.

TRANS.DATEENTERED BETWEEN ' || Q || TRIM ( @BEGINDATE ) || Q || ' AND' || Q || TRIM ( @ENDDATE ) || Q || ')'

becomes

TRANS.DATEENTERED BETWEEN '''' || TRIM ( @BEGINDATE ) || '''' AND '''' || TRIM ( @ENDDATE ) '''')'

Kent

Kent,

Okay,  I have applied the changes to the original sproc to where my problem lies.

See attached for results - notice red squiggly.

NOTE: ORIGINAL SPROC DOES NOT USE THE ' AT THE END.
SPROC-CHANGE-RESULTS.PNG

That code needs a double-pipe (||) between the last ')' and the '''' characters.


Kent

More info for last sproc change.

[SQL0104] Token '''' was not valid. Valid tokens: FOR SKIP WITH FETCH ORDER UNION EXCEPT OPTIMIZE

Maybe I’m missing something fundamental, but if your dates are stored as strings in the format mm/dd/yyyy (or mmddyyyy), then you wouldn't be able to use the BETWEEN operator anyway. That’s why I always try to store my dates as a DATE data-type. They’re much easier to compare.

HTH,
DaveSlash

Disregard comment 2 comments back listed as:

by: rhservanPosted on 2012-06-05 at 20:35:13ID: 38050612

Dave,
I just want to mention the 2nd sproc uses a BETWEEN on CHAR (8) string yyymmdd and returns all rows and all data.

Kent,
I applied your change above from the second sproc to the first sproc where the problem lies.
Here is what it looks like:
(TERMDATE BETWEEN '''' || TRIM ( @BEGINDATE ) || '''' AND '''' || TRIM ( @ENDDATE ) ||'''');
This implemented clean with no errors. However, when I ran the sproc it still returned no rows. Also, tried with TERMDATEC


Is there such a thing as a native date format and would this have any issue regarding this problem?

Hi rhservan,

There sure is a native date format.  :)  All of the major DBMS have them.

In this case, the data type is DATE.  But you'll have to convert both the database value and the parameter value to a date type.  That's probably more trouble than it's worth, and if you're using indexes, it'll be a performance killer.

Let's revisit two things.  As Dave points out, your date strings MUST be in year/month/day order for this type of test to work.  If they are, we're in good shape.  (Also, the year must be a 4 digit year or all of the years must be since 2000.)

There's nothing magical about the query.  Why not just declare the cursor with the query definition?

DECLARE CSR1 CURSOR FOR 
SELECT
  EMP.EMPID ,
  EMP.DIVISION ,
  EMP.DISTRICT ,
  EMP.LOCATION ,
  EMP.FIRSTNAME ,
  EMP.LASTNAME ,
  REPLACE (EMP.TELEPHONE1 , ' ' , '' ) AS TELEPHONE1 ,
  TRANS.OPERATIONALAREA ,
  TRANS.DATEENTERED ,
  TRANS.HIREDATE ,
  TRANS.REHIREDATE ,
  EMP.JOBCODE ,
  EMP.BASEPAYRATE
FROM
  HCMDEV.EMPLOYEES EMP
INNER JOIN HCMDEV.TRANSACTIONS TRANS
  ON EMP.EMPID = TRANS.EMPLOYEEID
WHERE 1=1
  AND TRANS.TRANSACTIONTYPE = 'HI' OR TRANS.TRANSACTIONTYPE = 'RH')
  AND TRANS.OPERATIONALAREA = 'FIELD')
  AND TRANS.DATEENTERED BETWEEN TRIM ( @BEGINDATE ) AND TRIM ( @ENDDATE )) ;

Select allOpen in new window

 
Kent

Here are the sproc run results showing no row returns but says it complted successfully.
SprocRunResults.PNG

All,

The first sproc is the one with the problem. TermDate column default format is mm/dd/yyyy
TermDateC converts this to mmddyyyy and removes the slashes.

So, based on the communications the first sproc dates are incorrect and neither the TermDate nor the TermDateC will work as filters since neither of the formats meet the yyyymmdd.

The 2nd sproc is only there to show you what is working and the filter, dateentered, is in the format string of yyyymmdd that must be why it is working. Meaning it returns all dates and all data when using the parameters.

Am I perceiving this correctly?

If so, I want to temporarily convert TermDate to the yyyymmdd and test this right away.

Is there a convert for this?

A variant of the code that you have will convert the date to yyyymmdd

Change:

  SUBSTR ( EMP . TERMDATE , 1 , 2 ) || SUBSTR ( EMP . TERMDATE , 4 , 2 ) || SUBSTR ( EMP . TERMDATE , 7 , 4 ) AS TERMDATEC

to

SUBSTR ( EMP . TERMDATE , 7 , 4 ) || SUBSTR ( EMP . TERMDATE , 4 , 2 ) || SUBSTR ( EMP . TERMDATE , 1 , 2 ) AS TERMDATEC


Kent

Still won't work with the filter. There are still no rows returned with the sproc.

Although,  it does run with the selected sql and attached are the TermDate & TermDateC sample.
TERMDATE-TERMDATEC-RESULTS.PNG

HERE IS THE UPDATED SPROC 1 WITH ALL THE CHANGES DISCUSSED.  THE RESULTS ARE STILL NO ROWS RETURNED.
--------------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE HCMDEV.TERMINATIONREPORT
 ( IN @BEGINDATE CHAR(8),
             IN @ENDDATE CHAR (8) )

      RESULT SETS 1
      LANGUAGE SQL
      SPECIFIC TERMINATIONREPORT

P1 : BEGIN

DECLARE TERMDATEC CHAR(8);
-- Declare cursor
DECLARE CURSOR1 CURSOR FOR

SELECT
EMP . TERMDATE ,
SUBSTR ( EMP . TERMDATE , 7 , 4 ) || SUBSTR ( EMP . TERMDATE , 4 , 2 ) || SUBSTR ( EMP . TERMDATE , 1 , 2 ) AS TERMDATEC ,
EMP . HIREDATE ,
EMP . REHIREDATE ,
EMP . EMPID ,
EMP . COMPANY ,
EMP . FIRSTNAME ,
EMP . MIDDLENAME ,
EMP . LASTNAME ,
EMP . DIVISION ,
EMP . DISTRICT ,
EMP . LOCATION ,
EMP . STATUS ,
EMP . JOBCODE ,
EMP . LOCATIONDESCRIPTION ,
EMP . FACILITYID ,
TRANS . TRANSACTIONTYPE ,
TRANS . DATEENTERED ,
TRANS . TERMORTRANSFERREASON ,
TRANS . ELIGLBLEFORREHIRE ,
TRANS . TERMINATIONTYPE
FROM HCMDEV . TRANSACTIONS TRANS
INNER JOIN HCMDEV . EMPLOYEES EMP
ON TRANS . EMPLOYEEID = EMP . EMPID
WHERE TRANS . TRANSACTIONTYPE = 'TE'
AND (TERMDATEC BETWEEN '''' || TRIM ( @BEGINDATE ) || '''' AND '''' || TRIM ( @ENDDATE ) ||'''');
-- Cursor left open for client application
OPEN CURSOR1;
END P1

tliotta,

I am not able to access the system until AM CST. I will try this without the quotes and let you know the results.

Okay,

We haven't gained the desired success yet but the efforts have been extraordinary.  I am awarding points right now on efforts.

I would appreciate your continued efforts in the new ticket I created.

I have initiated a new ticket as ID27745824.

Thanks,
rhservan

I've requested that this question be closed as follows:

Accepted answer: 0 points for rhservan's comment #38052871
Assisted answer: 200 points for daveslash's comment #38050154
Assisted answer: 250 points for Kdo's comment #38050172
Assisted answer: 50 points for tliotta's comment #38051327
Assisted answer: 0 points for rhservan's comment #38051649

for the following reason:

This question was awarded on effort not based on correct answer. &nbsp;This will not be added to Knowledge Base.<br /><br />I have restarted question under a new ticket for simplicity:<br /><br />ID27745824

Adding this line:

AND (SUBSTR( EMP . TERMDATE , 7 , 4 ) || SUBSTR ( EMP . TERMDATE , 4 , 2 ) || SUBSTR ( EMP . TERMDATE , 1 , 2 ) BETWEEN @BEGINDATE AND @ENDDATE);

effected a change where I could return data and rows although they are mostly incorrect based on the parameter dates entered.

For an example:
20110901 for @BEGINDATE
20110930 for @ENDDATE  
returns a variety of different months and days but the year is correct.
How can this be corrected.

Below is the current stored procedure
--------------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE HCMDEV.TERMINATIONREPORT ( IN @BEGINDATE CHAR(8),
                                            IN @ENDDATE CHAR(8) )
      RESULT SETS 1
      LANGUAGE SQL
      SPECIFIC TERMINATIONREPORT

P1 : BEGIN

-- Declare cursor
DECLARE CURSOR1 CURSOR FOR

SELECT
EMP . TERMDATE ,
EMP . HIREDATE ,
EMP . REHIREDATE ,
EMP . EMPID ,
EMP . COMPANY ,
EMP . FIRSTNAME ,
EMP . MIDDLENAME ,
EMP . LASTNAME ,
EMP . DIVISION ,
EMP . DISTRICT ,
EMP . LOCATION ,
EMP . STATUS ,
EMP . JOBCODE ,
EMP . LOCATIONDESCRIPTION ,
EMP . FACILITYID ,
TRANS . TRANSACTIONTYPE ,
TRANS . DATEENTERED ,
TRANS . TERMORTRANSFERREASON ,
TRANS . ELIGLBLEFORREHIRE ,
TRANS . TERMINATIONTYPE
FROM HCMDEV . TRANSACTIONS TRANS
INNER JOIN HCMDEV . EMPLOYEES EMP
ON TRANS . EMPLOYEEID = EMP . EMPID
WHERE TRANS . TRANSACTIONTYPE = 'TE'
AND (SUBSTR( EMP . TERMDATE , 7 , 4 ) || SUBSTR ( EMP . TERMDATE , 4 , 2 ) || SUBSTR ( EMP . TERMDATE , 1 , 2 ) BETWEEN @BEGINDATE AND @ENDDATE);

-- Cursor left open for client application
OPEN CURSOR1 ;
END P1

Date Information

@BEGINDATE CHAR (8)
@ENDDATE CHAR (8)

Prompted Date info For parameters when the sproc runs.
20110901
20110930

Return Results (Emp.TermDate) when the sproc runs. As 10 Characters with the /.
1/05/2011   <--------incorrect
10/16/2011 <--------incorrect
09/01/2011 <--------correct

What's more important me getting the solution I need or following rules that put my question into oblivion for 24 hrs.

This question as been recreated at this ID

ID27745824.

Hi rhservan,

1/05/2011   <--------incorrect
10/16/2011 <--------incorrect
09/01/2011 <--------correct

I can understand the first line causing problems.  The swap to yyyymmdd (using the substr function) is expecting 2 digits for the month and day.  1/05/2011 converts to '0115/1/', which is jibberish.

But I don't understand why the second row is selected.  It would appear that something in the data isn't as we're expecting.


Kent

Kent said:  It would appear that something in the data isn't as we're expecting.

In the previous sproc where we used the TermDateC it would return 201111 1 as in the attached graphic

So I see 2 issues:

1. Only filtering on year ----> I would resolve this one first.
2. Maybe issues with single digit months.
TERMDATE-TERMDATEC-RESULTS.PNG

Based on the graphic above:

8/05/2008 = 200805 8

I had already started preparing this and couldn't back it out, see if the attachment helps at all.
TERMINATIONREPORT-REPORT-SAMPLES.docx

I think this is what you asked for.  Interesting there are no results for 20111016.
20111016-20111017-Results.docx

Hi rhservan,

That's very interesting, and highly enlightening!  It appears that the month may be 1 or 2 digits, but the day and year are always 2 or 4.

The graphic also seems to show that begindate and enddate are improperly formatted.  I don't understand that as your examples show that they are entered in the correct format.


But since we now know that the date can have one of two forms, it's pretty straight-forward to address it.  The cleanest solution will depend on the data definition and usage.

-- Is the TERMDATE declared as CHAR or VARCHAR?
-- Is the date stored left or right justified?


Kent

CHAR (8) & Right justified.

Remember we have used SUBSTR two different formats:

1. Column Based - SUBSTR ( EMP . TERMDATE , 1 , 2 ) || SUBSTR ( EMP . TERMDATE , 4 , 2 ) || SUBSTR ( EMP . TERMDATE , 7 , 4 ) AS TERMDATEC ,

2. Filter Based - AND (SUBSTR( EMP . TERMDATE , 7 , 4 ) || SUBSTR ( EMP . TERMDATE , 4 , 2 ) || SUBSTR ( EMP . TERMDATE , 1 , 2 ) BETWEEN @BEGINDATE AND @ENDDATE);

Current Stored Procedure attached.
CurrentStoredProcedure.docx

Results attached using:

AND (SUBSTR( EMP . TERMDATE , 7 , 4 ) || SUBSTR ( EMP . TERMDATE , 4 , 2 ) || translate (SUBSTR ( EMP . TERMDATE , 1 , 2 ), '0', ' ') BETWEEN BEGINDATE AND ENDDATE);

Also,  I have removed all of the @ signs.  This seems to be working fine.
yyyymmdd.docx
yyyyddmm.docx

It looks like we got it!

Disregard CurrentStoredProcedure above, in:
Posted on 2012-06-06 at 20:48:23ID: 38055351

This attached CurrentStoredProcedure was used in the results in
the above comment:
Posted on 2012-06-06 at 21:06:26ID: 38055415

which includes your new statement:
AND (SUBSTR( EMP . TERMDATE , 7 , 4 ) || SUBSTR ( EMP . TERMDATE , 4 , 2 ) || translate (SUBSTR ( EMP . TERMDATE , 1 , 2 ), '0', ' ') BETWEEN BEGINDATE AND ENDDATE);

Although the results are still not working.
CurrentStoredProcedure.docx

The results in both of the word documents above look right to me.  Or did I miss something?


Kent

When entering the date string in the parameter window,
We think it is yyyymmdd and it is actually yyyyddmm.
So when the table interprets a 09 after the year it is a day and the table results show only 09 for days.

Example: 8/09/2011

Sorry, but I'm not following this.  Using days less than 12 is ambiguous as 08/09/2011 could be Aug 9, or Sep 8.

Can you restate that using perhaps Aug 20?   08/20/2011?


Thanks,

We think it is yyyymmdd and it is actually yyyyddmm.

I don't quite grasp that either.

Are you saying that we've been interpreting it as YYYYMMDD, but it actually is YYYYDDMM?

That can easily be adjusted.

But if it means that it might be entered by a user either way and we need to figure out what the format is, then there will always be a chance of mistake. The proc could only return its "best guess".

Tom

Okay,  I think I am there.  I changed the last line for the date format to this:

AND ( SUBSTR ( EMP . TERMDATE , 7 , 4 ) || TRANSLATE (SUBSTR ( EMP . TERMDATE , 1 , 2 ), '0' , ' ' ) ||  ( SUBSTR ( EMP . TERMDATE , 4 , 2 ))  BETWEEN TRIM (BEGINDATE) AND TRIM (ENDDATE) ) ;

From this:
AND (SUBSTR( EMP . TERMDATE , 7 , 4 ) || SUBSTR ( EMP . TERMDATE , 4 , 2 ) || translate (SUBSTR ( EMP . TERMDATE , 1 , 2 ), '0', ' ') BETWEEN BEGINDATE AND ENDDATE);


I reversed the 2nd & 3rd EMP.Termdate and moved the translate to the middle.

That looks like it should work fine.

I didn't notice until rechecking the results files, but the date's don't seem to be stored in a consistent fashion.  TERMDATE is dd/mm/yyyy but the others are the more common mm/dd/yyyy.

I'm sure that that contributed to our confusion.


But glad that you've got a solution!

Kent

Here is the Final Stored Procedure Attached that is working correctly now with the date parameters.  This will now integrate into an SSRS package as a dataset.
FinalStoredProcedure.docx

KDO submitted #1 below, adding the translate brought it very close. This was significant.

1. AND ( SUBSTR ( EMP . TERMDATE , 7 , 4 ) || TRANSLATE (SUBSTR ( EMP . TERMDATE , 1 , 2 ), '0' , ' ' ) ||  ( SUBSTR ( EMP . TERMDATE , 4 , 2 ))  BETWEEN TRIM (BEGINDATE) AND TRIM (ENDDATE) ) ;

I just reversed the 2nd termdate with the 3rd termdate and everything began working:

2. AND (SUBSTR( EMP . TERMDATE , 7 , 4 ) || SUBSTR ( EMP . TERMDATE , 4 , 2 ) || translate (SUBSTR ( EMP . TERMDATE , 1 , 2 ), '0', ' ') BETWEEN BEGINDATE AND ENDDATE);