Automate

 View Only

  • 1.  Winshuttle query-Left outer join

    Posted 07-29-2021 03:30

    Hello All,

     

    In the studio in one query, only one outer join can be put. Is this the same even in Evolve?

    Also in one query table, Tj30T and TJ02T both are not working. Did anyone face this? and got the solution?

     

    Preeti Aich
    Maintenance & Asset Data Lead

    077476 46346

    preeti.aich@thameswater.co.uk

    Clearwater Court, Vastern Rd, Reading RG1 8DB

     

    Visit us online www.thameswater.co.uk , follow us on twitter www.twitter.com/thameswater or find us on www.facebook.com/thameswater. We're happy to help you 24/7.

    Thames Water Limited (company number 2366623) and Thames Water Utilities Limited (company number 2366661) are companies registered in England and Wales, both are registered at Clearwater Court, Vastern Road, Reading, Berkshire RG1 8DB. This email is confidential and is intended only for the use of the person it was sent to. Any views or opinions in this email are those of the author and don't necessarily represent those of Thames Water Limited or its subsidiaries. If you aren't the intended recipient of this email, please don't copy, use, forward or disclose its contents to any other person – please destroy and delete the message and any attachments from your system.



  • 2.  RE: Winshuttle query-Left outer join

    Posted 07-29-2021 08:22
    Hi Preeti -

    There is only one left outer join possible in one Query script. Evolve is not used to write Query scripts, Studio is, so this is not related to whether you are on an Evolve deployment or a desktop deployment or a Foundation deployment, there is still only one left outer join possible. 

    I don't understand what you mean by saying TJ30T and TJ02T are "not working"? Can you provide more detail on what it is that is not working? 

    Thank you.

    ------------------------------
    Jennifer Hwang | SE
    Winshuttle North America
    ------------------------------

    Original Message


  • 3.  RE: Winshuttle query-Left outer join

    Posted 07-29-2021 08:50

    Thanks Jennifer.

     

    I built a query to get a report for the functional location with user and system status and address data.

     

    Here I also need to put criteria to exclude certain databases on user status and system status.

     

    To put the exclude criteria, I wanted to use tables Tj30T and Tj02T.

     

    Till TJ30T I am getting the report. On the movement, I will add table TJ02T , I am getting no output.

     

     

     

     

    Thanks and Regards,

    Preeti

     

     




    Original Message


  • 4.  RE: Winshuttle query-Left outer join

    Posted 07-29-2021 09:10
    Can you attach the Query file? Both versions?

    ------------------------------
    Jennifer Hwang | SE
    Winshuttle North America
    ------------------------------

    Original Message


  • 5.  RE: Winshuttle query-Left outer join

    Posted 07-29-2021 10:49

    Hello Jannifer,

     

    Please find.

     

     

    Thanks and Regards,

    Preeti

     




    Original Message


  • 6.  RE: Winshuttle query-Left outer join

    Posted 07-29-2021 14:48
    Hi Preeti -

    So you get data if you just add the TJ30T table. Do you get data if you just add the TJ02T table by itself, not TJ02T and TJ30T?

    Thanks

    ------------------------------
    Jennifer Hwang | SE
    Winshuttle North America
    ------------------------------

    Original Message


  • 7.  RE: Winshuttle query-Left outer join

    Posted 07-30-2021 03:54

    Hello Jennifer,

     

    I am getting System status (TJ02T) only if I run Jest and Tj02T).

     

    Even without TJ30T, If I run a query with IFLOT+JEST+TJ02T, it is not giving me output.

     

    Thanks and Regards,

    Preeti

     




    Original Message


  • 8.  RE: Winshuttle query-Left outer join

    Employee
    Posted 07-29-2021 15:03
    Hi Preeti,

    Another approach is to skip the status tables and run the query without them.  Then run a transaction script against the output, using the FLOC # as input to download/read the status afterwards.

    In our system, we always have a system status but not a user status.  I played around with adding another JEST table, etc, and it's the criteria on the tables with a left outer join that cause no output.  I decided this was the simpler approach.  You can always output the full text of the status codes from the two tables and use a vlookup to get the text, i.e. CRTE is Created, AVAL is Available.

    HTH
    Sigrid​

    ------------------------------
    Sigrid Kok
    PSE | Winshuttle NA
    ------------------------------

    Attachment(s)

    Txr
    IL03_ReadSystemUserStatus.Txr   43 KB 1 version
    xlsx
    IL03_ReadSystemUserStatus.xlsx   5 KB 1 version
    Original Message


  • 9.  RE: Winshuttle query-Left outer join

    Posted 07-30-2021 03:31

    Hello Sigrid,

     

    I am also thinking of the same approach. But wanted to check if there is any work around.

     

    Thanks and Regards,

    Preeti

     




    Original Message