Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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,
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,
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
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,
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
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_idthis 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.
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,
Hi Vlad,
correct
kind rgrds
Hans
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,
Hi Vlad,
Great! with this answer, I think you have helped me out with more issues I have to deal with.
Kind rgrds
Hans