OraFAQ Forum: Forms » selected wrong lov

Home » Developer & Programmer » Forms » selected wrong lov

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

selected wrong lov [message #602429]

Wed, 04 December 2013 05:35

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Hi,

I select lov org_code(CU1) lov and click on find it displays CU1 org_code records only and i create one post-query on Database Block , next 1hr after i select same or_code(CU1) click on find button it selected another org_code(DET) records , why it display like that

Thank you

Report message to a moderator

Re: selected wrong lov [message #602451 is a reply to message #602429]

Wed, 04 December 2013 06:52

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Without knowing the code you used, no idea.
And after 256 posts you really ought to know that you need to supply the code without us telling you.

Report message to a moderator

Re: selected wrong lov [message #602492 is a reply to message #602451]

Wed, 04 December 2013 22:33

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Hi cookiemonster,

Please find the below code

FIND Button:
DECLARE 
    finalstr  VARCHAR2(1000); 
    dfinalstr VARCHAR2(1000); 
BEGIN 
    IF :HOPE_FORECAST_CONTROL.ORG_CODE IS NOT NULL THEN 
      Go_block('HOPE_FORECAST_DATA'); 

      dfinalstr := Get_block_property('HOPE_FORECAST_DATA', default_where); 

      finalstr := 'where ORGANIZATION_CODE = ' ||( ':HOPE_FORECAST_CONTROL.ORG_CODE' ); 

      Set_block_property('HOPE_FORECAST_DATA', default_where, finalstr); 

      Execute_query(); 
    END IF;

POST-QUERY

DECLARE 
    CURSOR c1 IS 
      SELECT organization_code 
      FROM   hope.hope_forecast_data 
      WHERE  fiscal_year = :HOPE_FORECAST_CONTROL.FISCAL_YEAR; 
BEGIN 
    IF :HOPE_FORECAST_CONTROL.FISCAL_YEAR IS NOT NULL THEN 
      OPEN c1; 

      FETCH c1 INTO :HOPE_FORECAST_CONTROL.ORG_CODE; 

      CLOSE c1; 
    END IF; 
END;

Report message to a moderator

Re: selected wrong lov [message #602499 is a reply to message #602492]

Thu, 05 December 2013 00:17

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

How many records are returned when you execute

SELECT organization_code 
      FROM   hope.hope_forecast_data 
      WHERE  fiscal_year = :HOPE_FORECAST_CONTROL.FISCAL_YEAR

in SQL*Plus? (Substitute block item reference with real value you choose from a LoV).
If it returns more than one record, then it is rather clear - as you use a cursor, it fetches ONE VALUE (but you don't know which one). Once you run the form - it returns one value. Another time, it returns another value. If you want it always to return the same value, include ORDER BY clause or MAX or MIN functions or some other mechanism you find appropriate.

On the other hand, is the above situation valid? I mean, more than a single record for a fiscal year?

Anyway: first answer my question, then we'll see what to do next.

Report message to a moderator

Re: selected wrong lov [message #602500 is a reply to message #602499]

Thu, 05 December 2013 00:30

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Hi Little foot,

The below query you are written is returns 2664 records,so, i think it is not valid , can you suggest how to do that one plz?

Thank you

Report message to a moderator

Re: selected wrong lov [message #602501 is a reply to message #602500]

Thu, 05 December 2013 00:35

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Just now i used group by clause it returns 4 records only, so, can i use group by clause?

Report message to a moderator

Re: selected wrong lov [message #602503 is a reply to message #602501]

Thu, 05 December 2013 00:53

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

GROUP BY what? DISTINCT probably does the same, doesn't it? Anyway 4 records are still too many. How can you tell which one fits chosen :HOPE_FORECAST_CONTROL.FISCAL_YEAR? You might need to add additional condition to SELECT's WHERE clause.

Report message to a moderator

Re: selected wrong lov [message #602505 is a reply to message #602503]

Thu, 05 December 2013 00:58

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

yes correct it is same as distinct , so, how can i get the 1 record? suggest me

Report message to a moderator

Re: selected wrong lov [message #602507 is a reply to message #602505]

Thu, 05 December 2013 01:10

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

How would I know? It is not a problem to select 1 record, there are different ways to do that. Question is: which one of them is correct?

[Updated on: Thu, 05 December 2013 01:10]

Report message to a moderator

Re: selected wrong lov [message #602508 is a reply to message #602507]

Thu, 05 December 2013 01:19

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Hi Littlefoot,

when i select org_code lov and click on find button to display records , that's why i wrote post-query and i checked in the DB (:HOPE_FORECAST_CONTROL.FISCAL_YEAR)it returns so many values, for ex: when i select year as 2006 ,that related records get only, i posted post-query, is this wrong?

Thank you

Report message to a moderator

Re: selected wrong lov [message #602509 is a reply to message #602508]

Thu, 05 December 2013 01:27

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I use POST-QUERY to populate items that don't belong to a table that is a source for that data block. If you did that as well, I guess that it is NOT wrong.

Report message to a moderator

Re: selected wrong lov [message #602510 is a reply to message #602509]

Thu, 05 December 2013 01:59

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Hi littlefoot,
i used in post-query like :HOPE_FORECAST_CONTROL.FISCAL_YEAR(non-database) and in FIND button i wrote like below

finalstr := 'where FISCAL_YEAR = ' ||( ':HOPE_FORECAST_CONTROL.FISCAL_YEAR' );

Here HOPE_FORECAST_CONTROL is non-database na , so, it retrieve all the data from the DB Block ,I want to retrieve the data from the DB Block any suggestion please?

Thank you

Report message to a moderator

Re: selected wrong lov [message #602514 is a reply to message #602510]

Thu, 05 December 2013 03:07

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I don't have any; you already know how to create DEFAULT_WHERE, you've been discussing that for weeks now. Besides, what does it have to do with many rows returned by a SELECT statement? As I told you: if possible, include another condition into the WHERE clause.

Or

SELECT max(organization_code)
      FROM   hope.hope_forecast_data 
      WHERE  fiscal_year = :HOPE_FORECAST_CONTROL.FISCAL_YEAR

SELECT min(organization_code)
      FROM   hope.hope_forecast_data 
      WHERE  fiscal_year = :HOPE_FORECAST_CONTROL.FISCAL_YEAR

SELECT organization_code
      FROM   hope.hope_forecast_data 
      WHERE  fiscal_year = :HOPE_FORECAST_CONTROL.FISCAL_YEAR
        AND  rownum = 1

will "fix" the problem, but - once again - it is a truly questionable "solution" until you answer which one of 4 (or 2664) values is the right value.

Report message to a moderator

Re: selected wrong lov [message #602516 is a reply to message #602514]

Thu, 05 December 2013 03:17

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Thank you Littlefoot you have more patience to answering great job , Once again thank you very much, actually i have requirement on this.. Smile

Report message to a moderator

Previous Topic:	Format Mask Problem
Next Topic:	Form get close automatically

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 20:21:06 CDT 2024