OraFAQ Forum: Reports & Discoverer » How to make a query in data model

Home » Developer & Programmer » Reports & Discoverer » How to make a query in data model (oracle 10g )

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

How to make a query in data model [message #589458]

Sat, 06 July 2013 10:16

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......

Report message to a moderator

Re: How to make a query in data model [message #589459 is a reply to message #589458]

Sat, 06 July 2013 10:42

John Watson
Messages: 8964
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.

Report message to a moderator

Re: How to make a query in data model [message #589463 is a reply to message #589459]

Sat, 06 July 2013 15:15

Littlefoot
Messages: 21823
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%'

Report message to a moderator

Re: How to make a query in data model [message #589504 is a reply to message #589463]

Mon, 08 July 2013 01:18

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...

Report message to a moderator

Re: How to make a query in data model [message #589513 is a reply to message #589504]

Mon, 08 July 2013 02:52

cookiemonster
Messages: 13963
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);

Report message to a moderator

Previous Topic:	Get unique values with formula column
Next Topic:	Down Cross Printing Direction

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 22:04:56 CST 2025