Use of Alias in Query Generator

[Former Member]

Have 2 questions for Query Generator Syntax:

In query generator, created a query similar to the simplified one below:

SELECT T0.[FirstName]+' '+T0.[LastName] "ABCXYZ"

     FROM OCPR

WHERE T0.[FirstName]+T0.[LastName] LIKE '%[%1]%'

Q1) How can I use the alias "ABCXYZ" in the WHERE statement instead of the concatenation?

Q2) On executing this statement, a pop-up of Query - Selection Criteria window appears with text "First Name contains". How can I make it appear as either "ABCXYZ contains" or something similar instead of just First Name since it is really searching the full concatenation for the text and not simply the first name?

Thanks,

Ajay

[pvsbprasad · Accepted solution]

HI,

Try this

Select X.ABCX from (SELECT T0.[FirstName]+''+ T0.[LastName]  FROM OCPR T0 WHERE T0.[CardCode] =[%0] )  X

where X.ABCX='[%0]'

Regards,

Prasad

[KennedyT21]

Hi  Ajay Audich...

This Is not possible in the Query Gen in standard...you can use the CR to achieve this

Regards

Kennedy

[Former Member]

Hi Ajay,

This is the current limitation for B1 Query Generator/Manager. You need to try Crystal Report to get the desired result.

Thanks,

Gordon

[former_member204969]

The SBO query manager always uses a field name known inside the system when ask for a parameter. And the list the user can choose from contains all the existing values of this field. (You can modify the list and specify the parameter name only in crystal report.)