Change contents of field in a command - Crystal 9 1

[shaleen7]

I have to combine 2 SQL queries in a command and I need to change the content of the field to reflect a certain phrase.

For example I want to change the contents of the "STATUSTABLE"."CODEVE" field to reflect one word, that word is, FILING
Our database contains the following info.
AppNo. Status Date of App
1 App 1/10/2003
2 Pub 1/12/2003
3 Val 2/11/2003
4 App 11/1/2003

I want to replace the status field query results to read filing for all results.

AppNo. Status Date of App
1 Filing 1/1/2003
2 Filing 1/1/2003
3 Filing 1/1/2003
4 Filing 1/1/2003

Our database contains different words (PUB, APP, VAL, DES etc.) in short it all means it's a filing.

#Query 1:
SELECT "DATETABLE"."DATDLV", "APPTABLE"."APPNO", "COUNTRYTABLE"."CODPAY", "STATUSTABLE"."CODEVE"

FROM APPTABLE" "APPTABLE", "COUNTRYTABLE" "COUNTRYTABLE", "STATUSTABLE" "STATUSTABLE", "LOCATIONTABLE" "LOCATIONTABLE", "DATETABLE" "DATETABLE", "TYPEAPPTABLE" "TYPEAPPTABLE"

WHERE ("STATUSTABLE"."IDEEVE" (+)="DATETABLE"."IDEEVE&quot AND ("COUNTRYTABLE"."IDEPAY"="DATETABLE"."IDEPAY&quot AND ("APPTABLE"."IDEDOS"="DATETABLE"."IDEDOS&quot AND
&quot;COUNTRYTABLE&quot;.&quot;CODPAY&quot;<>'EP' AND DATETABLE&quot;.&quot;DATDLV&quot; IS NOT NULL

I can't change the field from within crystal because the 2nd query uses the same status names but the 2nd query results are not filings, they are grants.

Any suggestions?

 

[synapsevampire]

My first suggestion is that you mention what type of database you're using...

But, this should handle it:

SELECT &quot;DATETABLE&quot;.&quot;DATDLV&quot;, &quot;APPTABLE&quot;.&quot;APPNO&quot;, &quot;COUNTRYTABLE&quot;.&quot;CODPAY&quot;, 'FILING'

FROM APPTABLE&quot; &quot;APPTABLE&quot;, &quot;COUNTRYTABLE&quot; &quot;COUNTRYTABLE&quot;, &quot;STATUSTABLE&quot; &quot;STATUSTABLE&quot;, &quot;LOCATIONTABLE&quot; &quot;LOCATIONTABLE&quot;, &quot;DATETABLE&quot; &quot;DATETABLE&quot;, &quot;TYPEAPPTABLE&quot; &quot;TYPEAPPTABLE&quot;

WHERE (&quot;STATUSTABLE&quot;.&quot;IDEEVE&quot; (+)=&quot;DATETABLE&quot;.&quot;IDEEVE&quot AND (&quot;COUNTRYTABLE&quot;.&quot;IDEPAY&quot;=&quot;DATETABLE&quot;.&quot;IDEPAY&quot AND (&quot;APPTABLE&quot;.&quot;IDEDOS&quot;=&quot;DATETABLE&quot;.&quot;IDEDOS&quot AND
&quot;COUNTRYTABLE&quot;.&quot;CODPAY&quot;<>'EP' AND DATETABLE&quot;.&quot;DATDLV&quot; IS NOT NULL

Details like the database you're using, version of Crystal, etc., really do matter.

You can do this directly from Crystal using a SQL Expression, depending upon your version...

-k

 

[shaleen7]

It worked. Thanks.