You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Need Data Returned From Two Tables Using One of Two Prompts

3 replies [Last post]
Ed-D's picture
User offline. Last seen 8 years 1 week ago. Offline
Joined: 05/14/2008
Posts: 25

Hello everyone,

I am using PSoft Query Manager (Ver 8.8xxx - Web Based.) Here is what I need to do.

I need to use a Prompt to enter the RESUME_TEXT_FILE from the PERS_APPL_INFO Table
Or...
I need to use a Prompt to enter NAME from the HRO_DASHBOARD Table.

The RESUME_TEXT_FILE contains 7 characters for each entry, it is a unique entry, and corresponds to an EMPLID in that same Table

Not all employees have a RESUME_TEXT_FILE entry in that Table. Employees who do not have the RESUME_TEXT_ENTRY simply do not appear in this Table.

So I want to set up a query where I can enter the RESUME_TEXT_FILE at a Run Prompt, and have it return the EMPLID from that Table, along with the Employee's Name, Status, etc. from the HRO DASHBOARD Table.

BUT - for instances where we do not have the RESUME_TEXT_FILE entry, or perhaps this employee does not have one (if you are unsure about that or cannot get that information from Brass Ring) I need to enter the employee's NAME at a Run Prompt and have the query return all names that match - along with the RESUME_TEXT_FILE entry - if one exists... along with the Employee's Name, Status, etc. from the HRO DASHBOARD Table.

I know this will involve a Left-Outer Join, and have been playing around with it without success. I also know to use criteria for NAME or the RESUME_TEXT_FILE Between two Expressions NULL(TRIM(:1),'A') AND NULL(TRIM(:1),'Z') || 'ZZZZZZZ' so I can use both Prompts, and leave one null.

Can anyone help?

Thanks,
Ed

Ed-D's picture
User offline. Last seen 8 years 1 week ago. Offline
Joined: 05/14/2008
Posts: 25
Re: Need Data Returned From Two Tables Using One of Two Prompts

Hi,

Just to make a point of clarification -

Quote:
I need to enter the employee's NAME at a Run Prompt and have the query return all names that match - along with the RESUME_TEXT_FILE entry - if one exists... along with the Employee's Name, Status, etc. from the HRO DASHBOARD Table.

** I am using LAST_NAME. I If I enter a LAST_NAME, data is returned only for those whose last name matches, and I only get those who actually have a RESUME_TEXT_ENTRY.

** I need to get back a list of ALL employees with the same LAST_NAME, even those who do not have a RESUME_TEXT_ENTRY. That's where I am having the problem. We do not always have the RESUME_TEXT_ENTRY, but always have LAST_NAME.

Ed-D's picture
User offline. Last seen 8 years 1 week ago. Offline
Joined: 05/14/2008
Posts: 25
Re: Need Data Returned From Two Tables Using One of Two Prompts

Hmmm... No ideas or comments? Have I stumped everyone, or is this an impossible task?

Lepa's picture
User offline. Last seen 36 weeks 1 day ago. Offline
Joined: 06/23/2008
Posts: 591
Re: Need Data Returned From Two Tables Using One of Two Prompts

Quote:
I am using LAST_NAME. I If I enter a LAST_NAME, data is returned only for those whose last name matches, and I only get those who actually have a RESUME_TEXT_ENTRY.

If you are prompting for last name and only last name, you should then get all data that matches. Can you share the actual SQL that query generates so we can take a look?

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog