SQL statement to find records that BEGIN WITH

Help! I'm trying to build a live report that pulls data based on the first 2 characters in a particular field. I have LL 8.1.5 and SQL 7.0. I'm trying to do a comparison and either I get a SQL error or no results found. I am trying to use the LIKE comparison - I want to match, for example, where the first two chars are "ab". How do I format the wildcard portion of the statement? Thanks!jrussell@kjmassoc.com

Find more posts tagged with

Comments

eLink User

Message from Steve Klosky via eLink[use famisdatabasego]Select * from [dtreetableowner.]dtreewhere name like 'ab%'bracketed stuff is optional depending on which user you go in as.-----Original Message-----From: knowledge@opentext.com [mailto:knowledge@opentext.com]On Behalf OfeLink Discussion: Livelink LiveReports DiscussionSent: Wednesday, February 14, 2001 5:57 PMTo: eLink RecipientSubject: SQL statement to find records that BEGIN WITHSQL statement to find records that BEGIN WITHPosted by KJMAdmin on 02/14/2001 05:54 PMHelp! I'm trying to build a live report that pulls data based on the first2 characters in a particular field. I have LL 8.1.5 and SQL 7.0. I'mtrying to do a comparison and either I get a SQL error or no results found.I am trying to use the LIKE comparison - I want to match, for example, wherethe first two chars are "ab". How do I format the wildcard portion of thestatement? Thanks!jrussell@kjmassoc.com[To reply to this thread, use your normal e-mail reply function.]============================================================Discussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=viewLivelink Server:https://knowledge.opentext.com/knowledge/livelink.exe

KJM_Associates_(kjmadmin_-_(deleted))

I don't know what's going on because my statement is following that format!Here's the exact statement:Select * from DTree, ResumesTBL where DTree.DCategory = 'Resumes' AND DTree.DataID=ResumesTBL.DataID AND %1 AND ResumesTBL.BirthDate like 'se%'%1 is Filter DocumentsI get results when I use BirthDate = with the full value. This one gives a Livelink report error.

eLink User

Message from Steve Klosky via eLinkHmmm, I wonder what sort of field ResumesTBL.BirthDate is?-----Original Message-----From: knowledge@opentext.com [mailto:knowledge@opentext.com]On Behalf OfeLink Discussion: Livelink LiveReports DiscussionSent: Wednesday, February 14, 2001 6:45 PMTo: eLink RecipientSubject: I don't know what's going on because my statement is followingthat format!I don't know what's going on because my statement is following that format!Posted by KJMAdmin on 02/14/2001 06:41 PMI don't know what's going on because my statement is following that format!Here's the exact statement:Select * from DTree, ResumesTBL where DTree.DCategory = 'Resumes' ANDDTree.DataID=ResumesTBL.DataID AND %1 AND ResumesTBL.BirthDate like 'se%'%1 is Filter DocumentsI get results when I use BirthDate = with the full value. This one gives aLivelink report error.[To reply to this thread, use your normal e-mail reply function.]============================================================Topic: SQL statement to find records that BEGIN WITHhttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2455685&objAction=viewDiscussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=viewLivelink Server:https://knowledge.opentext.com/knowledge/livelink.exe

KJM_Associates_(kjmadmin_-_(deleted))

I set that field up as a text field.

eLink User

Message from Alex Kowalenko via eLinkIn Oracle use like 'stem%%' (2 percent signs).BirthDate is an attribute in the custom category called "Resumes" and it'sprobably a Date type. To do a stem search on a date it has to be convertedto string; ie. in Oracle use this: to_char(BirthDate,'mon/dd/yyyy') like'Se%%' to get September.-alex------Original Message-----From: knowledge@opentext.com [mailto:knowledge@opentext.com]On Behalf OfeLink Discussion: Livelink LiveReports DiscussionSent: Wednesday, February 14, 2001 6:45 PMTo: eLink RecipientSubject: I don't know what's going on because my statement is followingthat format!I don't know what's going on because my statement is following that format!Posted by KJMAdmin on 02/14/2001 06:41 PMI don't know what's going on because my statement is following that format!Here's the exact statement:Select * from DTree, ResumesTBL where DTree.DCategory = 'Resumes' ANDDTree.DataID=ResumesTBL.DataID AND %1 AND ResumesTBL.BirthDate like 'se%'%1 is Filter DocumentsI get results when I use BirthDate = with the full value. This one gives aLivelink report error.[To reply to this thread, use your normal e-mail reply function.]============================================================Topic: SQL statement to find records that BEGIN WITHhttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2455685&objAction=viewDiscussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=viewLivelink Server:https://knowledge.opentext.com/knowledge/livelink.exe

KJM_Associates_(kjmadmin_-_(deleted))

I created the attribute and the user wanted it to be a text field, not a date, so I did define it as a text field. I've tried every possible combination but the 'ab%%' you suggested and it did work! Thanks!

Chad Latka

Have you tried WHERE (Item) LIKE 'ab%%' ? I have tried something similar when looking for user ID's.Ex: WHERE (KUAF_1.Name LIKE '1%%')Seems to return the results I was looking for. Hope it helps!