Adding parameters to a filter for a report

[Cryzo]

Hi Guys

I need help very agent, I just started learning PL/SQL for oracle and I have a task that I need to fix. I'm required to create a report system using report filter to pass parameters e.g Dates, listbox, textboxex etc. I have the following SQL:

SELECT DISTINCT CC.iid,
                CC.Shortcode || '.' || CC.NAME Occupation,
                G.Shortcode || '.' || G.NAME || '<br>' || F.Shortcode || '.' || F.NAME || '<br>' || E.Shortcode || '.' || E.NAME || '<br>' || D.Shortcode || '.' || D.NAME OFOAll,
                CC.iID ofoOccupationID,
                ( SELECT COUNT(V.iID)
                  FROM inveloper.conOrganisationVacancies V
                    WHERE V.ofoOccupationID = CC.iid ) Opportunities
  FROM inveloper.ofoOccupation CC
         JOIN inveloper.conOrganisationVacancies V             ON CC.iid = V.ofoOccupationID
         LEFT JOIN inveloper.conOrganisation O             ON V.conOrganisationID = O.iid
         LEFT JOIN inveloper.ofoUnit D             ON D.iID = CC.ofoUNitID
         LEFT JOIN inveloper.ofoMinor E             ON E.iID = D.ofoMinorID
         LEFT JOIN inveloper.ofoSubMajor F             ON F.iID = E.ofoSubMajorID
         LEFT JOIN inveloper.ofoMajor G             ON G.iID = F.ofoMajorID
         LEFT JOIN inveloper.globState H             ON V.globStateID = F.iID
         LEFT JOIN inveloper.conSetaIndustry SI             ON V.ConSetaIndustryID = SI.iid
         LEFT JOIN inveloper.conSETA S             ON SI.conSETAID = S.iid
  WHERE (1=1)
 

I need filter the report using date, Listboxes containing data from oracle database. I don't know if i'm making sence to u guys. Pls help!

[Rabbit]

You have posted in the wrong forum. Your thread will be moved.

[amitpatel66]

Hi Cryzo,

Welcome to TSDN!!

Please make sure you follow POSTING GUIDELINES every time you post in this forum.

Thanks
MODERATOR

[amitpatel66]

Hi Guys

I need help very agent, I just started learning PL/SQL for oracle and I have a task that I need to fix. I'm required to create a report system using report filter to pass parameters e.g Dates, listbox, textboxex etc. I have the following SQL:

SELECT DISTINCT CC.iid,
                CC.Shortcode || '.' || CC.NAME Occupation,
                G.Shortcode || '.' || G.NAME || '<br>' || F.Shortcode || '.' || F.NAME || '<br>' || E.Shortcode || '.' || E.NAME || '<br>' || D.Shortcode || '.' || D.NAME OFOAll,
                CC.iID ofoOccupationID,
                ( SELECT COUNT(V.iID)
                  FROM inveloper.conOrganisationVacancies V
                    WHERE V.ofoOccupationID = CC.iid ) Opportunities
  FROM inveloper.ofoOccupation CC
         JOIN inveloper.conOrganisationVacancies V             ON CC.iid = V.ofoOccupationID
         LEFT JOIN inveloper.conOrganisation O             ON V.conOrganisationID = O.iid
         LEFT JOIN inveloper.ofoUnit D             ON D.iID = CC.ofoUNitID
         LEFT JOIN inveloper.ofoMinor E             ON E.iID = D.ofoMinorID
         LEFT JOIN inveloper.ofoSubMajor F             ON F.iID = E.ofoSubMajorID
         LEFT JOIN inveloper.ofoMajor G             ON G.iID = F.ofoMajorID
         LEFT JOIN inveloper.globState H             ON V.globStateID = F.iID
         LEFT JOIN inveloper.conSetaIndustry SI             ON V.ConSetaIndustryID = SI.iid
         LEFT JOIN inveloper.conSETA S             ON SI.conSETAID = S.iid
  WHERE (1=1)
 

I need filter the report using date, Listboxes containing data from oracle database. I don't know if i'm making sence to u guys. Pls help!

In your query, add the filter conditions:

Something like this:

 
hdate DATE;
emp_no NUMBER;
 
hdate:= (Your date value)
emp_no:= (your list box value)
 
SELECT * from table_name WHERE
hiredate = hdate AND empno =  emp_no;
 
 

Use above logic for your query and I hope you an achieve what you are looking for!!