Help Translating SQL to Crystal Basic 1

[NattyCat]

Hello,

I have an SQL query that I'm using to ensure that names are populated as best as they can be where one or more of the fields are not poplated. ie, if there is no title, or no inititals, or no first name etc.

For reasons I can't go into, I cannot just create a view and use the view in the report linked to one of the existing report tables, so i need to recreate this case statement within a formula in crystal, but I'm stuck. Can someone help me out?

The SQL is as follows:

select iindividualid , "Name_Field" =

case

when isnull(vchsalutation, '') <> '' and isnull(vchinitials,'') <> '' and vchlastname <> '' then

vchsalutation + ' ' + vchinitials + ' ' + vchlastname

when isnull(vchsalutation, '') <> '' and isnull(vchinitials,'') = '' and vchfirstname = '' and vchlastname <> '' then

vchsalutation + ' ' + vchlastname

when isnull(vchsalutation, '') <> '' and isnull(vchinitials,'') = '' and vchlastname <> '' then

vchsalutation + ' ' + left(vchfirstname,1) + ' ' + vchlastname

when isnull(vchsalutation, '') = '' and vchfirstname <> '' and vchlastname <> '' then

vchfirstname + ' ' + vchlastname

when isnull(vchsalutation, '') = '' and vchfirstname = '' and vchlastname <> '' and isnull(vchinitials,'') <> '' then

vchinitials + ' ' + vchlastname

when isnull(vchsalutation, '') = '' and vchfirstname = '' and vchlastname = ' ' and isnull(vchinitials,'') = '' then
'No Name Details Found'
else 'Mr ' + vchlastname

end



Thanks,
Natasha

 

[rosemaryl]

Dear Natasha,

You could try creating a SQL Expression to do this.

You would inlcude the whole statement in Parens and remove this portion "select iindividualid , "Name_Field" = " to get it to work. I don't know your db, driver, or Crystal version so it makes it hard to guess if it would work.


For a Crystal formula to get you started, I think you will find variables quite handy:

StringVar FNAME :=
if isnull({T.VCHFirstName}) then ''
else if {T.VCHFirstName} = '' then ''
else {T.VCHFirstName} + ' ';

StringVar LNAME;
if isnull({T.VCHLastName}) then ''
else if {T.VCHLastName} = '' then ''
else {T.VCHLastName} + ' ';

// at the end of each line we are concatenating a
//single quote space single quote

Stringvar INIT;
if isnull({T.VCHInititals}) then ''
else if {T.VCHInititals} = '' then ''
else {T.VCHInititals} + ' ';

StringVar Sal;

Stringvar INIT;
if isnull({T.VCHSalutation}) then ''
else if {T.VCHSalutation} = '' then ''
else {T.VCHSalutation} + ' ';

if
(FNAME = '' //the are two single quotes we are looking at!
and
LName = ''
and
INIT = ''
and
SAL = ''
)
then 'No Name Details Found'
else
Sal + FName + INIT + LNAME

Something like that. You might need to play around with it to meet some of your scenarios .. I just hit the basics.

Regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[rosemaryl]

Dear Natasha,

There are errors in my formula from cutting and pasting, so sorry. Here is corrected one:

// at the end of each assignment formula we are
//concatenating a single quote space single quote
StringVar FNAME :=
if isnull({T.VCHFirstName}) then ''
else if {T.VCHFirstName} = '' then ''
else {T.VCHFirstName} + ' ';

StringVar LNAME :=
if isnull({T.VCHLastName}) then ''
else if {T.VCHLastName} = '' then ''
else {T.VCHLastName} + ' ';

Stringvar INIT :=
if isnull({T.VCHInititals}) then ''
else if {T.VCHInititals} = '' then ''
else {T.VCHInititals} + ' ';

StringVar Sal :=

if isnull({T.VCHSalutation}) then ''
else if {T.VCHSalutation} = '' then ''
else {T.VCHSalutation} + ' ';

if
(FNAME = '' //the are two single quotes we are looking at!
and
LName = ''
and
INIT = ''
and
SAL = ''
)
then 'No Name Details Found'
else
Sal + FName + INIT + LNAME


Basically, I forgot to change the semicolons after some of the variable names to := which is the assignment operator.

Regards,

ro


Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762