PS Query - Extensive use of Expression and Prompts

Posted on

3 way Prompts

In this way user can able to run the query either by giving value or giving prompt value or some part of the value.
To do so we need to,
  1. Create the Expression
  2. Create a Prompt
  3. Add criteria with Expression and Prompt
  4. Change logical mapping
  5. Group the criteria
Initial Query

Create the Expression

Create the Expression like below and in the Express Text give only <single quote> <space> <singlequote>




Create a Prompt

Create a Prompt for the Field where u need a Prompt while running the Query
Please note that here the Prompt Table been mentioned with “No Table Edit” as Edit Type



Add criteria with Expression and Prompt

While adding criteria first select the Expression and Condition Type should be equal to and then select the Prompt which you created earlier.


Add other Criteria like below

Make sure here the Condition Type should be “Like” and Expression 1 Type as Field (Field you are implementing Prompt)


Change logical mapping

Make the Logical operation between two Criteria’s as “OR”

Group the criteria

Group the “OR” clause both side Criteria’s, this will be mandatory when you are doing complex criteria’s





Final SQL will be like this

Running Query – Way 1 (Blank Run - Complete)



Running Query – Way 2 (Specific Run – Exact)






Running Query – Way 3 (Match Run – Contains)




Here you can use % character either left or right side to accomplice "begins with" and "ends with" kind of searches.



Labels =>
Location: Chennai, Tamil Nadu, India

Comments

Post a Comment

You might wanna check out my Popular Post

Running Connected Query BIP / XMLP through Peoplecode / AE Process

Posted on
Hi All, Most of us tend to find the Answer for this Question "How to I execute Connected Query XMLP report through Peoplecode or AE Peoplecode?" Here it is, what you are seeking for, import PSXP_RPTDEFNMANAGER:ReportDefn; import PT_CONQRS:QUERYITEMPROMPT:*;  import PT_CONQRS:CONQRSMGR:*;  Global string &BU, &Invoice;  Local array of PT_CONQRS:QUERYITEMPROMPT &prompts;  Local File &xmlFile; &reportDefinition = "XX_CONNQRY";  &connQueryName = "BI_INV_PRNT_CQRY_V1";  /* Start by creating the Connected Query Object */  &connQueryObject = create PT_CONQRS:CONQRSMGR("", &connQueryName);  If Not (&connQueryObject.Open( False)) Then  /* If it can't be opened, throw an error */   Error ("Failed to open Connected Query.");  End-If; If Not (&connQueryObject.Validate()) Then  /* Validate that all PSQueries within the Connected Query Exist */   Error ("Unable ...
Read more

Localize your PeopleSoft System Date

Posted on
In Peoplesoft system you can easily localize your Date format based on your country format. For that what you need to do is Log on to PIA (Pure Internet Architecture) through the User ID has PTPT1000 Permission List Go to Root >> PeopleTools >> Personalization >> Locale Defaults Search for your PIA's default language code (e.g.: "en-us") For user option "Date Format" change the "Override Value" to  D for DDMMYY format M for MMDDYY format Y for YYMMDD format Note YY or YYYY is based on Page Field properties Also for "Date Separator" you can use any Single Character to separate / (Forward Slash) is a default one Save the Page Now where ever the Date field is occurring which will appear in the updated format. and this can be overridden by Users personalization which is in, My Personalizations >> Regional Settings and change the Date Format and Date Separator options based on your convenience....
Read more