Home » Developer & Programmer » Reports & Discoverer » How to make a query in data model (oracle 10g )
How to make a query in data model [message #589458] Sat, 06 July 2013 10:16 Go to next message
ora_bbm
Messages: 30
Registered: March 2013
Location: Chennai
Member
Hi,

I have one report consist of two user parameters like FROMDATAE and TODATE and two queries in data model..

The 1st query is..

SELECT WONO,MCV_DATE,QTY FROM MCSHOP1 WHERE MCV_DATE BETWEEN :FROMDATE AND :TODATE;


It created two user parameters.ie FROMDATE and TODATE.

And 2nd query like this

SELECT MCVN FROM MCSHOP1 WHERE WONO=:WONO OR WONO LIKE 'RW%'||:WONO;

I dont know how to make 2nd query in data model.
becoz the WONO will come from 1st query and LIKE command is there...so how to make this...

But I tried in formula Column....but it returns more than one row...

Any suggestions......
Re: How to make a query in data model [message #589459 is a reply to message #589458] Sat, 06 July 2013 10:42 Go to previous messageGo to next message
John Watson
Messages: 4515
Registered: January 2010
Location: Global Village
Senior Member
I think what you are looking for in your second query is,
SELECT MCVN ROM MCSHOP1 WHERE MCV_DATE BETWEEN :FROMDATE AND :TODATE
OR WONO LIKE 'RW%';

And by the way, just to do the moderator bit, please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read before you post agan.
Re: How to make a query in data model [message #589463 is a reply to message #589459] Sat, 06 July 2013 15:15 Go to previous messageGo to next message
Littlefoot
Messages: 19526
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why do you need 2 queries? One would suffice, wouldn't it? You'd then create 2 groups out of selected items (if necessary), such as
select 
  wono,          --> put it into group 1
  mcv_date,      --> group 1
  qty,           --> group 1
  mcvn           --> group 2
from mcshop1
where mcv_date between :fromdate and :todate
  and wono like 'RW%'
Re: How to make a query in data model [message #589504 is a reply to message #589463] Mon, 08 July 2013 01:18 Go to previous messageGo to next message
ora_bbm
Messages: 30
Registered: March 2013
Location: Chennai
Member
i have many queries like this

SELECT MCVN FROM MCSHOP1 WHERE WONO=:WONO OR WONO LIKE 'RW%'||:WONO;

in a single report.. So i wrote two queries for example ..

Actually the first query is

SELECT WONO,MCV_DATE,QTY FROM MCSHOP1 WHERE MCV_DATE BETWEEN :FROMDATE AND :TODATE AND WONO NOT LIKE 'RW%';


And 2nd query is

SELECT MCVN FROM MCSHOP1 WHERE WONO=:WONO OR WONO LIKE 'RW%'||:WONO;

And the 3rd query is

SELECT count(*) FROM MCSHOP1 WHERE mcv_stat='A' and (WONO=:WONO OR WONO LIKE 'RW%'||:WONO);

And 4th query is

SELECT count(*) FROM MCSHOP1 WHERE mcv_stat='R' and (WONO=:WONO OR WONO LIKE 'RW%'||:WONO);

i tried in many ways ...but i dont knw how to implement the Like condition in Data model..I discussed with my team members...They also dont know...

Re: How to make a query in data model [message #589513 is a reply to message #589504] Mon, 08 July 2013 02:52 Go to previous message
cookiemonster
Messages: 10929
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use 1 query as Littlefoot already suggested, then split the data into two groups if that's what you need:

SELECT m1.WONO,
       m1.MCV_DATE,
       m1.QTY, 
       m2.MCVN 
FROM MCSHOP1 m1, mcshop1 m2
WHERE m1.MCV_DATE BETWEEN :FROMDATE AND :TODATE
AND (m2.wono = m1.wono OR m2.wono LIKE 'RW%'||m1.wono);
Previous Topic: Get unique values with formula column
Next Topic: Down Cross Printing Direction
Goto Forum:
  


Current Time: Sat Aug 30 20:14:18 CDT 2014

Total time taken to generate the page: 0.10266 seconds