REPORT SQL DETAILS to create a Simple Menu Price List

V5 Question
8

@pauln, it doesn’t work that way when it comes to SQL. And JTRTech, it does not matter if it is a SQL Statement or a @@SQLHandler - they both work the same way.

A @parameterList is a comma-separated set of values. This does not translate into SQL in the same way as it does with other Report Tags.

The actual full syntax for this Report Tag is:

{REPORT SQL DETAILS:<handler or sql>;<parameters,1,2>:<fields>:<expressions>:<format>}
                                    ^
this is NOT a typo - this IS a semi-colon (;), while the rest are colons (:)

Breakdown:

  • <handler or SQL> - this can be a SQL Statement or it can be a @@SQLHandler for a Script

  • ;<parameters> - values listed here will be passed into the SQL Statement and be held in variables like @1, @2, @3, etc. NOTE: this field is the only one preceded by a semi-colon (;) and it is optional so it can be omitted in the syntax.

  • :<fields> - the field names from the SQL Statement, in the format F.FieldName1, F.FieldName2, F.FieldName3

  • :<expressions> - normally used with other Report Tags, for example (TY=Delivery) which would match ‘Delivery’ Ticket Types. This is a good place to use $1 from a preceding @parameterList, for example (TY=$1). AFAIK, the <expressions> field is not valid for the {REPORT SQL DETAILS:X} Tag, and is always left empty.

  • :<format> - used to format the output of the Fields. Usually takes this form: {0}|{1}|{2}

Example:

{REPORT SQL DETAILS:SELECT '1' as [Field1]:F.Field1::{0}}

{REPORT SQL DETAILS:SELECT '@1' as [EntityName];{ENTITY NAME}:F.EntityName::<L00>{0}}

{REPORT SQL DETAILS:SELECT '@1' as [EntityName], '@2' as [Balance];{ENTITY NAME},{ENTITY BALANCE}:F.EntityName, F.Balance::<J00>{0}| {1}}

The last example could also be written something like this:

@John,15
{REPORT SQL DETAILS:SELECT '@1' as [EntityName], '@2' as [Balance];$1,$2:F.EntityName, F.Balance::<J00>{0}| {1}}

But the following will not work as you might expect when it comes to SQL:

@{TICKET TYPE LIST}
{REPORT SQL DETAILS:SELECT '@1' as [TicketType];$1:F.TicketType::{0}}

So here is the problem: lets say we have a @parameterList defined at the beginning of the Report, such as:

@{TICKET TYPE LIST}

This translates to: TicketType1,TicketType2,TicketType3. These values can then be used in $variables.

This works well, when used within an <expression>, because it will produce output for each Ticket Type in sequence, for example:

$1|{REPORT TICKET COUNT:(TY=$1)}|{REPORT TICKET TOTAL:(TY=$1)}

That will produce a row of data for each Ticket Type. But notice how the $1 is used in the <expression> portion of the Report Tag. It will evaluate and produce output like this:

TicketType1|{REPORT TICKET COUNT:(TY=TicketType1)}|{REPORT TICKET TOTAL:(TY=TicketType1)}
TicketType2|{REPORT TICKET COUNT:(TY=TicketType2)}|{REPORT TICKET TOTAL:(TY=TicketType2)}
TicketType3|{REPORT TICKET COUNT:(TY=TicketType3)}|{REPORT TICKET TOTAL:(TY=TicketType3)}

When it comes to feeding $variables into SQL Statements or a @@SQLHandler, it works differently. When we feed a @parameterList into SQL, it is evaluated as a single $1 variable, and that variable contains the entire list.

So in this case, @{TICKET TYPE LIST} is fed into the SQL as:

@@SQLHandler:$1

Then inside the SQL we read $varables as @variable, so it evaluates like this:

@1 (from $1 which contains= `TicketType1,TicketType2,TicketType3`)

So you can see, it does not “cycle” through the @parameterList replacing $1 with each Ticket Type in succession as it does with other Report Tags. Instead, it assigns a single $variable of $1 containing the entire @parameterList.

On the other hand, if you use a typed list instead of a @parameterList, for example:

@@SQLHandler:TicketType1,TicketType2,TicketType3

It comes out like this:

$1 (evaluates to TicketType1)
$2 (evaluates to TicketType2)
$3 (evaluates to TicketType3)

In SQL, we read these as @variables which are assigned in the order they are given. So in this case, within the SQL, we can read 3 values:

@1 (from $1 which contains TicketType1)
@2 (from $2 which contains TicketType2)
@3 (from $3 which contains TicketType3)

So you can see, it does not “cycle” through the @parameterList replacing $1 with each Ticket Type in succession as it does with other Report Tags. Instead, it assigns 3 unique $variables of $1,$2,$3 each containing a single Ticket Type.

6 Likes