<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Verdana;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:"Time new Roman";}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
p.msonormal0, li.msonormal0, div.msonormal0
{mso-style-name:msonormal;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
span.E-MailFormatvorlage18
{mso-style-type:personal;
font-family:"Verdana",sans-serif;
color:windowtext;}
span.E-MailFormatvorlage19
{mso-style-type:personal;
font-family:"Tahoma",sans-serif;
color:windowtext;}
span.E-MailFormatvorlage21
{mso-style-type:personal-reply;
font-family:"Verdana",sans-serif;
font-variant:normal !important;
color:#1F4E79;
text-transform:none;
text-decoration:none none;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 2.0cm 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=DE link="#0563C1" vlink="#954F72"><div class=WordSection1><p class=MsoNormal><span style='font-family:"Verdana",sans-serif;color:#1F4E79'>Hello Michael,<o:p></o:p></span></p><p class=MsoNormal><span style='font-family:"Verdana",sans-serif;color:#1F4E79'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-family:"Verdana",sans-serif;color:#1F4E79'>sorry for the delay, I had some days out of office.<o:p></o:p></span></p><p class=MsoNormal><span style='font-family:"Verdana",sans-serif;color:#1F4E79'>The query you sent is working (that is: no error message), but the resulting virtual layer is empty. Are there other ideas?<o:p></o:p></span></p><p class=MsoNormal><span style='font-family:"Verdana",sans-serif;color:#1F4E79'><o:p> </o:p></span></p><div><p class=MsoNormal><span style='font-family:"Verdana",sans-serif;color:#1F4E79;mso-fareast-language:DE'>Mit freundlichen Grüßen,<o:p></o:p></span></p><p class=MsoNormal><span style='font-family:"Verdana",sans-serif;font-variant:small-caps;color:#1F4E79;mso-fareast-language:DE'>Uwe Fischer<o:p></o:p></span></p><p class=MsoNormal><span style='font-family:"Verdana",sans-serif;color:#1F4E79;mso-fareast-language:DE'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-family:"Verdana",sans-serif;color:#1F4E79;mso-fareast-language:DE'>--<o:p></o:p></span></p><p class=MsoNormal><span style='font-family:"Verdana",sans-serif;color:#1F4E79;mso-fareast-language:DE'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-family:"Verdana",sans-serif;color:#1F4E79;mso-fareast-language:DE'>Ingenieurbüro Fischer<o:p></o:p></span></p><p class=MsoNormal><span style='font-family:"Verdana",sans-serif;color:#1F4E79;mso-fareast-language:DE'>Esbecker Str. 8<o:p></o:p></span></p><p class=MsoNormal><b><span style='font-family:"Verdana",sans-serif;color:#1F4E79;mso-fareast-language:DE'>31036 Eime<o:p></o:p></span></b></p><p class=MsoNormal><span style='font-family:"Verdana",sans-serif;color:#1F4E79;mso-fareast-language:DE'>Tel.: 05182/8325<o:p></o:p></span></p><p class=MsoNormal><span style='font-family:"Verdana",sans-serif;color:#1F4E79;mso-fareast-language:DE'>Mobil: 0172/8876934<o:p></o:p></span></p></div><p class=MsoNormal><span style='font-family:"Verdana",sans-serif;color:#1F4E79'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=MsoNormal><b><span style='mso-fareast-language:DE'>Von:</span></b><span style='mso-fareast-language:DE'> Qgis-user [mailto:qgis-user-bounces@lists.osgeo.org] <b>Im Auftrag von </b>Reetz, Michael (NLPV) via Qgis-user<br><b>Gesendet:</b> Montag, 24. Oktober 2022 15:32<br><b>An:</b> qgis-user@lists.osgeo.org<br><b>Betreff:</b> Re: [Qgis-user] query for Virtual Layer in relation to another layer<o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><span style='font-family:"Tahoma",sans-serif'>Hello Uwe,<o:p></o:p></span></p><p class=MsoNormal><span style='font-family:"Tahoma",sans-serif'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-family:"Tahoma",sans-serif'>you could try this:<o:p></o:p></span></p><p class=MsoNormal><span style='font-family:"Tahoma",sans-serif'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-family:"Tahoma",sans-serif'>select * from my_lines l join my_polys p on l.my_line_attribute like '%' + p.my_poly_attribute + '%'</span><span lang=EN-US><o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span style='font-family:"Tahoma",sans-serif'>Best regard<o:p></o:p></span></p><p class=MsoNormal><span style='font-family:"Tahoma",sans-serif'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-family:"Tahoma",sans-serif'>Michael<o:p></o:p></span></p><p class=MsoNormal><span style='font-family:"Tahoma",sans-serif'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=MsoNormal><b><span style='mso-fareast-language:DE'>Von:</span></b><span style='mso-fareast-language:DE'> Qgis-user <<a href="mailto:qgis-user-bounces@lists.osgeo.org">qgis-user-bounces@lists.osgeo.org</a>> <b>Im Auftrag von </b>Uwe Fischer via Qgis-user<br><b>Gesendet:</b> Montag, 24. Oktober 2022 13:52<br><b>An:</b> <a href="mailto:qgis-user@lists.osgeo.org">qgis-user@lists.osgeo.org</a><br><b>Betreff:</b> [Qgis-user] query for Virtual Layer in relation to another layer<o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p><div style='border:solid #003333 1.0pt;padding:2.0pt 2.0pt 2.0pt 2.0pt'><p style='line-height:10.0pt;background:#D5EAFF'><span style='font-size:10.0pt;font-family:"Time new Roman";color:red'>ACHTUNG!!</span><span style='font-size:10.0pt;font-family:"Time new Roman"'> Diese E-Mail erreicht Sie von einem Absender außerhalb der niedersächsischen Landesverwaltungs-Infrastruktur mit TLS-Verschlüsselung. Bitte klicken Sie auf keine Links oder öffnen Sie keine E-Mail-Anhänge, falls Sie den Absender nicht kennen und nicht wissen, ob der Inhalt sicher ist.<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Verdana",sans-serif'>Hello,<o:p></o:p></span></p><p class=MsoNormal><span style='font-family:"Verdana",sans-serif'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-family:"Verdana",sans-serif'>I am looking for a way to create a virtual layer in 3.24 in a 'special' way. I have a line layer with attributes like:<o:p></o:p></span></p><p class=MsoNormal><span style='font-family:"Verdana",sans-serif'><o:p> </o:p></span></p><p class=MsoNormal style='text-indent:35.4pt'><span style='font-family:"Verdana",sans-serif'>xxxx_123_xxxxxxxxx<o:p></o:p></span></p><p class=MsoNormal style='text-indent:35.4pt'><span style='font-family:"Verdana",sans-serif'>oooo_abc_000_123_0<o:p></o:p></span></p><p class=MsoNormal><span style='font-family:"Verdana",sans-serif'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-family:"Verdana",sans-serif'>and a polygon layer with attributes like:<o:p></o:p></span></p><p class=MsoNormal><span style='font-family:"Verdana",sans-serif'><o:p> </o:p></span></p><p class=MsoNormal style='text-indent:35.4pt'><span style='font-family:"Verdana",sans-serif'>abc<o:p></o:p></span></p><p class=MsoNormal style='text-indent:35.4pt'><span style='font-family:"Verdana",sans-serif'>123<o:p></o:p></span></p><p class=MsoNormal><span style='font-family:"Verdana",sans-serif'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-family:"Verdana",sans-serif'>Now, I would like to use only lines that contain one of the strings that are present in the polygon layer.<o:p></o:p></span></p><p class=MsoNormal><span style='font-family:"Verdana",sans-serif'>A prototype for the SQL query might be:<o:p></o:p></span></p><p class=MsoNormal><span style='font-family:"Verdana",sans-serif'><o:p> </o:p></span></p><p class=MsoNormal style='text-indent:35.4pt'><span style='font-family:"Verdana",sans-serif'>select * from my_lines where my_line_attribute in (select my_poly_attribute from my_polys)<o:p></o:p></span></p><p class=MsoNormal><span style='font-family:"Verdana",sans-serif'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-family:"Verdana",sans-serif'>The problem is that I cannot use the IN clause because the line attributes contain additional surrounding characters around the polygon attributes. The surrounding characters can be any token, so it is not possible to remove them in a systematic way prior to the query. I thought about working with '%123%'. But how do I put every possible value in my_poly_attribute in that clause? That is, '%123%' would have to be in the query as well as '%abc%'<o:p></o:p></span></p><p class=MsoNormal><span style='font-family:"Verdana",sans-serif'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-family:"Verdana",sans-serif'>Thanks a lot for ideas!<o:p></o:p></span></p><p class=MsoNormal><span style='font-family:"Verdana",sans-serif'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-family:"Verdana",sans-serif;mso-fareast-language:DE'>Regards, Uwe<o:p></o:p></span></p><p class=MsoNormal><o:p> </o:p></p></div></div></body></html>