Prefixing a strings with @’s – multi lines and escape characters

This post just shows a couple of ways to use the feature of putting a @ in front of a text string and what the result is.

The first example shows how to use it when referring to eg. a file with backslashes.

Example 2 shows how to split a text line into two or more lines for easier reading.

 

static void coupleOfStringTricks(Args _args)
{
    str text1;
 
    // Example 1
    // ---------

    // Using a backslash in the text messes up the result
    text1 = "c:\temp\someFile.txt";
    info(text1);

    // An option is to use double backslash
    text1 = "c:\\temp\\someFile.txt";
    info(text1);

    // Or use the @ as prefix to the string
    text1 = @"c:\temp\someFile.txt";
    info(text1);


    // Example 2
    // ---------

    // Allowing to extend a text string over multiple lines
    text1 = @"This text is toooooooooooooooooooo loooooooooooooooong
    to keep in one line for easy reading";
    info(text1);
}
Advertisements

Using HAVING in a query

With AX 2012 we now have the option of using HAVING in a query. This allows us to limit a result set based on aggregated fields.

The advantage is that we let the SQL server do some filtering and receive less records compared to the old-school version were we had to receive all records and then use an IF or the likes to filter away the records that did not match the criterias.

In this simple example we want to get all sales ids on orders having lines with a line amount total of 100000 and beyond.

 

static void SimpleQueryWithHavingExample(Args _args)
{
    Query query;
    QueryRun queryRun;
    QueryBuildDataSource qBDS_SalesTable;
    QueryBuildDataSource qBDS_SalesLine;
    SalesTable salesTable;
    SalesLine salesLine;
 
    // Init query
    query = new Query();
 
    // Add datasources and use standard relations
    qBDS_SalesTable = query.addDataSource(tableNum(SalesTable));
    qBDS_SalesLine = qBDS_SalesTable.addDataSource(tableNum(SalesLine));
    qBDS_SalesLine.relations(true);
 
    // Add a group by on SalesTable
    qBDS_SalesTable.addGroupByField(fieldNum(SalesTable, SalesId));

    // Add aggregation on LineAmount
    qBDS_SalesLine.addSelectionField(fieldNum(SalesLine, LineAmount), SelectionField::Sum);
 
    // Add the having filter
    query.addHavingFilter(qBDS_SalesLine, fieldStr(SalesLine, LineAmount), AggregateFunction::Sum).value(SysQuery::range(100000, ''));
 
    // Create and run the queryRun object
    queryRun = new QueryRun(query);

    while (queryRun.next())
    {
        salesTable = queryRun.get(tablenum(SalesTable));
        salesLine = queryRun.get(tableNum(salesLine));
        info(strFmt("%1 %2", salesTable.SalesId, salesLine.LineAmount));
    }
}

 

I am sure that this will come in handy. 🙂

 

EXISTS JOIN/NOT EXISTS JOIN versus NOT EXISTS JOIN/EXISTS JOIN

Not the prettiest headline so far. I do admit that. Nevertheless it is actually quite relevant. Here is the question to be answered: If I have multiple exists joins in my select statement and one of them is a not exists how can I be certain that I will get the expected result set back from the SQL server.

Let us start off by lining up 3 tables:

Trans

GroupId SubGroupId
A 123
B 456
C 789
D 987

 

GroupTable

GroupId
A
D
G

 

SubGroupTable

SubGroupId
123
456

 

The structure should be fairly obvious. Trans has 2 fields that refers to the sub tables. What I would like is to write a select statement that gives me all records from Trans where the GroupId isn’t present in GroupTable and where the SubGroupId is found in SubGroupTable.

This could be done like this:

select trans
    exists join subGroupTable.SubGroupId
        where subGroupTable.SubGroupId == trans.SubGroupId
            not exists join grouptable
                where grouptable.GroupId == trans.GroupId

Another way could be by turning it a bit upside down like this:

select trans
    not exists join grouptable
        where grouptable.GroupId == trans.GroupId
            exists join subGroupTable.SubGroupId
                where subGroupTable.SubGroupId == trans.SubGroupId

What happens when we look at what the SQL server receives as the TSQL-statement is a bit different than expected:

select GroupId, SubGroupId
    from Trans as t
        where EXISTS (SELECT 'x'
            FROM GroupTable as g
                where g.GroupId = t.GroupId
                    and not (exists (select 'x'
                    from SubGroupTable as s
                        where s.SubGroupId = t.SubGroupId)))

and

select GroupId, SubGroupId
    from Trans as t
        where not (EXISTS (SELECT 'x'
            FROM SubGroupTable as s
                where s.SubGroupId = t.SubGroupId
                    and (exists (select 'x'
                    from GroupTable as g
                        where g.GroupId = t.GroupId))))

 

What to notice is that in the first statement the EXISTS and NOT EXISTS are considered as one and in the second statement we now have 2 EXISTS that is   encapsulated by a NOT giving it a bit of a twist compared to what we might have expected.

The result set for the first statement is

D 987

since

A is existing in SubGroupTable which was ruled out by our NOT EXISTS

B is in SubGroupTable and not in GroupTable which both goes against our EXISTS/NOT EXISTS

C is in SubGroupTable A was

D is present in GroupTable and not in SubGroupTable

 

and for the second the result set is

B 456

C 789

D 987

since

A is in both GroupTable and SubGroupTable and we specified by having two EXISTS’s and adding these to a NOT that this was a no go.

B is returned although being in SubGroupTable it was not in GroupTable making the double EXISTS false and thereby pleasing the NOT

C is not present in neither GroupTable nor SubGroupTable and therefore matching the NOT

D is – like B – returned since it only exists in GroupTable and not i SubGroupTable and the EXISTS’s then returning false

 

I realise that this can be a bit tricky to get the head around but nevertheless very important to keep in mind when doing those large selects with lots of tables and EXISTS/NOT EXISTS joins.