Date Expression as Query Prompt

[tcimis]

We would like to prompt users for a year based on a date. We have created an expression substr(date,1,4) which provides us with just the year portion of the date. We would like to now prompt for the year using that expression. We are using PeopleSoft query on the web. When we attempt to do this we always receive 0 records back when we know there are records that meet the criteria. Does anyone know of a way to do this using PeopleSoft query?

 

[notadba]

Hi TCMIS,

What database are you on? You will need to use that expression either with a 'year' or 'datepart' or Oracle equivalent for that to work. The other issue you will have is depending on the database, query will convert the date to a char and string it to a particular format - so you may need to have a look at the format of the column that you are passing the expression against (check the sql produced).

For example if DB2 you expression would be year(effdt) = :1

Do you have the full sql?

 

[tcimis]

We are on Oracle. Unfortunately I do not have access to the full sql just query through the web. I thought I had tried using the 'year' in the expression and received an error message but I will try again and let you know.

Thank you!

 

[notadba]

For Oracle you can do a couple of things (the sql is available on one of the query tabs)

The easiest one is to change your expression to something like:

substr(to_char(a.effdt,'YYYY-MM-DD'),1,4) and prompt against this.

 

[tcimis]

I have the expression entered as substr(a.effdt,1,4) which PeopleSoft converts to substr(TO_CHAR(a.effdt,'YYYY-MM-DD'),1,4).

I am now to where when I prompt I receive no errors but I also have no rows returned and I know rows exist. I also return no rows when I use a constant for the criteria. I have made progress but no success yet.

Thank you for your assistance.

 

[tcimis]

I am now trying to use the trunc function since I'm wondering if I'm receiving no rows because it is actually a date/time field.

The error I receive is: SQL error. Stmt #: 5653 Error Position: 249 Return: 1722 - ORA-01722: invalid number
A SQL error occurred. Please consult your system log for details.
Error in running query because of SQL Error, Code=1722, Message=ORA-01722: invalid number (50,380)

Here is the sql code that Peoplesoft creates:
SELECT A.EMPLID, A.ACCOMPLISHMENT, TO_CHAR(A.DT_ISSUED,'YYYY-MM-DD'), trunc(substr( TO_CHAR(A.DT_ISSUED,'YYYY-MM-DD'),1,4))
FROM PS_ACCOMPLISHMENTS A, PS_PERS_SRCH_QRY A1
WHERE A.EMPLID = A1.EMPLID
AND A1.OPRID = '49534'
AND ( trunc(substr( A.DT_ISSUED,1,4)) = '1974' )

I have the expression set as char 4 - trunc(substr(a.dt_issued,1,4)). If I set the expression to number 4, I receive the same error message.

 

[tcimis]

I have this working now.

I am using the expression: substr(to_char(B.DT_ISSUED,'YYYY-MM-DD'),1,4)

I don't use this as a field just use for criteria.

Thank you for your assistance.

 

[notadba]

Glad you got it working - it can be really frustrating!!!

When writing queries, it is really important to look at the SQL - especially on SQL server and Oracle in Query since it automatically forces its own date conversions into your query.