| Author |
Message |
togu
Newbie
Joined: 28 Sep 2009
Location: Sweden
Online Status: Offline
Posts: 2
|
  
Topic: How should I retype this SQL-question to Qrystal..
Posted: 28 Sep 2009 at 3:15am |
Hi, Im new at this Forum. But lets se if there´s anyone how can help me.
I have a SQL-question that´s including a subquestion. How can I make it work in Qrystal Report 9.2.
select oborno, obponr, obposx, obitno, obitds, oborqt, ucsaam, ucofra
from osbstd join mitmas on ucitno=mmitno
where ucorno in
(select oborno from ooline where obitno like 'PAKET1%')
and ucivdt >=20090901
I want to first select the customordernumer where there is an item that starts with PAKET1. And then use the selected CO-numbers in the next question to select only the rows that´s included in the subqry.
Best regards Tommy
|
 IP Logged |
|
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 28 Sep 2009 at 6:23am |
put your select into a stored proc with a parameter for the date. Have Crystal get the data from the stored proc.
HTH
|
| IP Logged |
|
togu
Newbie
Joined: 28 Sep 2009
Location: Sweden
Online Status: Offline
Posts: 2
|
Posted: 29 Sep 2009 at 10:42pm |
Thanx for your reply. The problem is that the qry is towards a DB2 database with Movex. Not a SQL database.
Maybe I should have given som more information;-)
Is there someother way to solve my problem?
Best regards
/Tommy
|
| IP Logged |
|
Jyothi Yepuri
Senior Member
Joined: 11 May 2009
Location: Australia
Online Status: Offline
Posts: 127
|
Posted: 29 Sep 2009 at 11:18pm |
|
Instead of stored procedure , you can use sql command.
create date parameter in crystal use it in sql command
Database expert --> under current connection --> Add Command
HTH,
Jyothi
|
| IP Logged |
|
iami21
Newbie
Joined: 20 Jul 2009
Online Status: Offline
Posts: 11
|
Posted: 30 Sep 2009 at 8:18pm |
 Hi, i think my problem is related to the original question. what if i have an address column in info table, and the fields are like the ff.: field 1 : 123 St., Washington D.C. field 2 : 456 St., WASH DC field 3 : 324 d.c and i want to assign "DC" when the field contains "DC" or "D.C." or "dc" or "d.c." else "Others" is that possible to do that in crystal without creating a stored proc? thanks very much!
Edited by iami21 - 30 Sep 2009 at 8:22pm
|
| IP Logged |
|
Jyothi Yepuri
Senior Member
Joined: 11 May 2009
Location: Australia
Online Status: Offline
Posts: 127
|
Posted: 30 Sep 2009 at 8:33pm |
|
Create formula using INSTR function
IF INSTR(field1 , string pattern) > 0 then
"DC"
else
"Others"
replace the string pattern with one of the strings "DC" or "D.C." or "dc" or "d.c."
same applies to field2, field3
HTH,
Jyothi
|
| IP Logged |
|
iami21
Newbie
Joined: 20 Jul 2009
Online Status: Offline
Posts: 11
|
Posted: 30 Sep 2009 at 10:39pm |
|
thanks jyothi!
is there any way that i can include all the possible combinations for 'DC' in one IF statement? i mean using the suggestion you gave it would come out like this:
IF INSTR(address, "DC") > 0 THEN
"DC"
ELSE IF INSTR(address, "D.C") > 0 THEN
"DC"
ELSE IF INSTR(address, "d.c.") > 0 THEN
"DC"
ELSE
"OTHERS"
did i understand correctly?
if i have a lot of cities, not only DC, my formula will be very long....
|
| IP Logged |
|
Jyothi Yepuri
Senior Member
Joined: 11 May 2009
Location: Australia
Online Status: Offline
Posts: 127
|
Posted: 30 Sep 2009 at 11:01pm |
|
May be write IF statement like this
IF INSTR(address, "DC") > 0 OR
INSTR(address, "D.C") > 0 OR
INSTR(address, "d.c.") > 0 THEN
"DC"
ELSE
"OTHERS"
how many string patterns you need to search?
you need to check all string patterns for a generic formula
HTH,
Jyothi
Edited by Jyothi Yepuri - 30 Sep 2009 at 11:08pm
|
| IP Logged |
|
iami21
Newbie
Joined: 20 Jul 2009
Online Status: Offline
Posts: 11
|
Posted: 30 Sep 2009 at 11:47pm |
more than 50  but this worked!thanks very much!! 
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 01 Oct 2009 at 6:02am |
|
you can also include the command uppercase around the address, then you only have to match the D.C, while d.c will not be caught by code above.
|
| IP Logged |
|
|
|
