MINUS instructie - Support - RS Data Extension

Search Forum Home

Forum

Enesys RS Data ...

Support - RS Da...

MINUS instructie

11/15/2007 12:00 PM

sylvie

Joined: 11/15/2007

Posts: 6

MINUS instructie

Hi,

I don't know, but is it possible to get a report with all the records of a list "companies" that aren't present in another list "reduction". The purpose is to get the same result as the MINUS instruction.

Kind regards,

Sylvie

11/15/2007 6:33 PM

Frédéric LATOUR

Joined: 5/31/2006

Posts: 629

Re: MINUS instructie

Hi,

You could use an outerjoin operation and then apply a select operation for keeping only items which joining column from the second list (or result set) is empty.

Though I didn't test, it should work.

Give me more time and I will come up with a sample for you.

Regards,

Frederic LATOUR
ENESYS

11/19/2007 4:49 PM

Frédéric LATOUR

Joined: 5/31/2006

Posts: 629

Re: MINUS instructie

Hi,

We have written a detailed knowledge base article explaining how to achieve what you want.

Let me know if it was not clear enough.

Regards,

Frederic LATOUR
ENESYS

11/20/2007 12:57 PM

sylvie

Joined: 11/15/2007

Posts: 6

Re: MINUS instructie

Thanks,

Sylvie

1/21/2008 3:22 PM

sylvie

Joined: 11/15/2007

Posts: 6

Re: MINUS instructie

Hi,

I have this select and I wil translate in Reports:

Select *

from Sharepoint_Leden.dbo.VW_Ondernemingen a

where Stopdatum is null

and Lidstatus in (4,5,6)

and Provincie between 1 and 37

and not exists (select 'X'

from Sharepoint_Leden.dbo.VW_Voordelen b

where Voordeelcode = 2

and a.Identnr = b.Identnr

and datediff(day,b.Startdatum, GETDATE()) >= 0

and datediff(day,b.Einddatum, GETDATE()) <= 0)

The words in red are the values for pQueryON.

At the moment, I have the members that don’t have Sabam and I want to add a test that gives me the persons who don’t have sabam today(shown in blue), I don’t know if it’s possible:

<?xml version="1.0" encoding="utf-8" ?>

<root>

<fields>Identnr, Lidnr, FED, Provincie, BV, Taal, Lid_x0020_Aanspreektitel, Title, Lid_x0020_Voornaam, Adres_x0020_Straat, Adres_x0020_Nummer, Adres_x0020_Bus, Plaats_x0020_Postcode, Plaats_x0020_Gemeente, Uitbater_x0020_Aanspreektitel, Uitbater_x0020_Naam, Uitbater_x0020_Voornaam, Uitbater_x0020_Adres_x0020_Straa, Uitbater_x0020_Adres_x0020_Numme, Uitbater_x0020_Adres_x0020_Bus, Uitbater_x0020_Plaats_x0020_Post, Uitbater_x0020_Plaats_x0020_Geme, Rechtspersoon_x0020_Juridische_x, Bedrijfsnaam, Uithangbord_x0020_Naam, Brief, Lidstatus, Stopdatum, Lidmaatschap_x0020_Van</fields>

<query>@pQueryON!</query>

</list>

<fields>Identnr</fields>

</list>

<fields>Identnr, Voordeelcode</fields>

</list>

<dstTableName>VSabam1</dstTableName>

<parentTableName>Ondernemingen2</parentTableName>

<childTableName>Voordelen</childTableName>

<parentFieldName>Identnr</parentFieldName>

<childFieldName>Identnr</childFieldName>

</sqlOp>

<dstTableName>VSabam2</dstTableName>

<sourceTableName>VSabam1</sourceTableName>

</sqlOp>

<dstTableName>VSabam3</dstTableName>

<parentTableName>Ondernemingen</parentTableName>

<childTableName>VSabam2</childTableName>

<parentFieldName>Identnr</parentFieldName>

<childFieldName>Identnr</childFieldName>

</sqlOp>

<dstTableName>Sabam</dstTableName>

<sourceTableName>VSabam3</sourceTableName>

</sqlOp>

<resultSet>Sabam</resultSet>

</root>

Thanks

Sylvie

1/21/2008 10:48 PM

Frédéric LATOUR

Joined: 5/31/2006

Posts: 629

Re: MINUS instructie

Hi,

Sure, you should add a Caml query to the Voordelen list query to further filter the list according to your need:

The following KB explains how to compare date columns with today's date:

How-To: Comparing a date column with today's date within a CAML Query

Let me know if you need more help.

Regards,

Frederic LATOUR
ENESYS

3/26/2008 8:01 AM

sylvie

Joined: 11/15/2007

Posts: 6

Re: MINUS instructie

Hi,

I want to add a test," (Today) - 30 days ", in the field Einddatum.

I have tried the different syntaxes but they all give the same result as <Today/>.

If I hard code 2008-2-25 at the place of <Today/> it works fine.

What is the best solution?

<fields>Identnr, Voordeelcode</fields>

<query>

<Where>

</Where>

</query>

</list>

<fields>Identnr, Voordeelcode</fields>

<query>

<Where>

</Where>

</query>

</list>

<fields>Identnr, Voordeelcode</fields>

<query>

<Where>

</Where>

</query>

</list>

<fields>Identnr, Voordeelcode</fields>

<query>

<Where>

</Where>

</query>

</list>

Thanks,

Sylvie

3/26/2008 2:22 PM

Frédéric LATOUR

Joined: 5/31/2006

Posts: 629

Re: MINUS instructie

Hi,

The following syntax works just fine - I have just made a test to be sure:

<?xml version="1.0" encoding="utf-8" ?>
<root xmlns="http://enesyssoftware.com/schemas">
  <list title="Products" relativeSiteUrl="/SITES/ersdetest/"  tableName="Products">
    <fields>Title, Category, Modified</fields>
    <query>
      <Where>
        <Geq>
          <FieldRef Name="Modified"></FieldRef>
          <Value Type="DateTime">
            <Today OffsetDays="-90" />
          </Value>
        </Geq>
      </Where>
    </query>
  </list>
  <resultSet>Products</resultSet>
</root>

What exactly do you obtain?

Regards,

Frederic LATOUR
ENESYS

3/26/2008 3:02 PM

sylvie

Joined: 11/15/2007

Posts: 6

Re: MINUS instructie

Idem when I say :

<list title="Voordelen" relativeSiteUrl="/LedenDevelopment" tableName="Voordelen" useDisplayName="false">
      <fields>Identnr, Voordeelcode</fields>
      <query>
           <Where>
                 <And><Leq>><FieldRef Name="Startdatum"/><Value Type="DateTime"><Today /></Value></Leq>
                            <Geq><FieldRef Name="Einddatum"/><Value Type="DateTime"><Today /></Value></Geq></And>
           </Where>
      </query>
</list>

<list title="Voordelen" relativeSiteUrl="/LedenDevelopment" tableName="Voordelen" useDisplayName="false">
      <fields>Identnr, Voordeelcode</fields>
      <query>
           <Where>
                 <And><Leq>><FieldRef Name="Startdatum"/><Value Type="DateTime"><Today /></Value></Leq>
                            <Geq><FieldRef Name="Einddatum"/><Value Type="DateTime"><Today OffsetDays="-30"/></Value></Geq></And>
           </Where>
      </query>
</list>

3/26/2008 4:04 PM

Frédéric LATOUR

Joined: 5/31/2006

Posts: 629

Re: MINUS instructie

Difficult to say what could go wrong without accessing the site and the list for making further tests. The query part within the query keyword is pure CAML query and is interpreted directly by SharePoint Web Services. The only thing I can say at that point is that it is working as expected on my side.

You may want to try an alternative approach involving the use of a hidden parameter. Instead of using <Today>, use a parameter like in the following query:

<?xml version="1.0" encoding="utf-8" ?>
<root xmlns="http://enesyssoftware.com/schemas">
  <list title="Products" relativeSiteUrl="/SITES/ersdetest/"  tableName="Products">
    <fields>Title, Category, Modified</fields>
    <query>
      <Where>
        <Geq>
          <FieldRef Name="Modified"></FieldRef>
          <Value Type="DateTime">@RefDate!</Value>
        </Geq>
      </Where>
    </query>
  </list>
  <resultSet>Products</resultSet>
</root>

Create a hidden report parameter (named RefDate in the previous query) of type DateTime and set its default value to the following expression:

=DateAdd(DateInterval.Day, -30, Now())

Let me know how it goes.

Regards,

Frederic LATOUR
ENESYS

3/27/2008 8:09 AM

sylvie

Joined: 11/15/2007

Posts: 6

Re: MINUS instructie

Yes .

Thanks

Page 1 of 1