9 Replies Latest reply: Mar 12, 2010 2:51 AM by Hans kleijnen RSS

    connect queries

    Hans kleijnen

      Hi,

      I want to build a QV but the query becomes too complex. Is it possible to give a queries names, and then connect the queries to eachother, for example as you would do in MSaccess?

      thanks

      Hans

        • connect queries
          Vlad Gutkovsky

          Hans,

          You can store connection strings as variables like anything else. Then you would use variable expansion to call the string. I believe you can do this at any point, including inside other connection strings.

          Regards,

            • connect queries
              Hans kleijnen

              Hi Vlad, since I am a newbie, I do not exactly understand what you are advising me here. Would it be possible to post an example?

              kndrgrds,

              Hans

                • connect queries
                  Vlad Gutkovsky

                  Sure. But, I think I misunderstood--you want queries, not connection strings. Can you give me an example of a query you would like to store?

                  Regards,

                    • connect queries
                      Hans kleijnen

                      Is going to be after the weekend. Lost the remote connection to my desktop and it is night time here already, so no one to reset the machine .

                      Hope you will be on line somewhere next week to help out

                      kind rgrds

                      Hans

                      • connect queries
                        Hans kleijnen

                        Hi Vlad,

                        Query1:

                         

                         

                        Select

                         

                         

                         

                         

                         

                         

                         

                         

                         

                         

                        CRM5.contact.contact_id,

                        CRM5.contact.name

                         

                        As bedrijfsnaam,

                        CRM5.contact.number2

                         





                        As klantnummer,

                        CRM5.udlist.name

                         



                        As markt,

                        CRM5.associate.name

                         



                        As relatiebeheerder,

                        CRM5.country.name

                         



                        As land,

                        CRM5.business.name

                         



                        As rol,

                        CRM5.category.name

                         



                        As categorie

                         

                         

                        From

                         

                         

                        CRM5.contact Left Join CRM5.udcontactsmall On CRM5.contact.userdef_id =CRM5.udcontactsmall.udcontactSmall_idLeft JoinCRM5.udlist On CRM5.udcontactsmall.long01 = CRM5.udlist.UDList_id Left Join CRM5.associate On CRM5.contact.associate_id = CRM5.associate.associate_id Left Join CRM5.country On CRM5.contact.country_id = CRM5.country.country_id Left Join CRM5.business On CRM5.contact.business_idx = CRM5.business.business_id Left Join CRM5.category On CRM5.contact.category_idx = CRM5.category.Category_id

                        this one must be joined to a table which has 'klantnummer' in common. Query 1 has more records than query 2. All records of Query 1 must be included.



                          • connect queries
                            Vlad Gutkovsky

                            Hans,

                            So just to clarify, you want to store the "From" part as a variable? If you want to store the entire query, I'm not sure what the point of that would be...

                            Regards,

                              • connect queries
                                Hans kleijnen

                                Hi Vlad,

                                correct

                                kind rgrds

                                Hans

                                  • connect queries
                                    Vlad Gutkovsky

                                    Hans,

                                    All you need to do is to set this as a variable:

                                     

                                    Let vConnection = 'CRM5.contact Left Join CRM5.udcontactsmall On CRM5.contact.userdef_id =CRM5.udcontactsmall.udcontactSmall_idLeft JoinCRM5.udlist On CRM5.udcontactsmall.long01 = CRM5.udlist.UDList_id Left Join CRM5.associate On CRM5.contact.associate_id = CRM5.associate.associate_id Left Join CRM5.country On CRM5.contact.country_id = CRM5.country.country_id Left Join CRM5.business On CRM5.contact.business_idx = CRM5.business.business_id Left Join CRM5.category On CRM5.contact.category_idx = CRM5.category.Category_id';


                                    Then you should be able to call this variable at any point in the script, including in a query:

                                     


                                    Select
                                    CRM5.contact.contact_id,
                                    CRM5.contact.name As bedrijfsnaam,
                                    CRM5.contact.number2 As klantnummer,
                                    CRM5.udlist.name As markt,
                                    CRM5.associate.name As relatiebeheerder,
                                    CRM5.country.name As land,
                                    CRM5.business.name As rol,
                                    CRM5.category.name As categorie
                                    From
                                    $(vConnection);



                                    Also keep in mind that if you want to use "AS", you will need a preceding load, which you are currently missing.

                                    Regards,