[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Dynamic SQL using "LIKE" ---ERROR: "SQL COMMAND NOT PROPERLY ENDED"

Posted on 2009-02-21
16
Medium Priority
?
491 Views
Last Modified: 2013-12-07
I'm gettting an error with the code at " AND SUBSTR (c.map, 1, 3) LIKE ''%'' '''||p12||''' ''%'' " 
saying as "SQL COMMAND NOT PROPERLY ENDED"
0
Comment
Question by:PMP2009
  • 4
  • 4
  • 4
  • +3
15 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23703693
Try this one..

AND SUBSTR (c.map, 1, 3) LIKE '%' || p12 || '%'
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 23703950
SQL Server T-SQL does not have a SUBSTR function, it's called SUBSTRING instead.

If you are trying to select records that contain "p12" in the c.map field, no matter where, try the first code snippet.  The % stands for any bunch of characters.

If your search string "p12" should really be the first 3 characters of the c.map field then try snippet 2.  No need to use LIKE because you always have 3 characters returned from the SUBSTRING().

--snippet 1
AND c.map LIKE '%p12%'
 
--snippet 2
AND SUBSTRING(c.map, 1, 3) = 'p12'

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 23703970
dynamic sql, and within oracle:
 AND SUBSTR (c.map, 1, 3) LIKE ''%'' ||p12|| ''%'' 

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 3

Expert Comment

by:lilian-arnaud
ID: 23704081
same result sa rrjegan17 :-)
AND SUBSTR (c.map, 1, 3) LIKE '%' ||p12|| '%'

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 23704165
as the sql is part of dynamic sql, for example:

EXECUTE IMMEDIATE ' select ... where ... '

the suggestions with single quotes won't work...
0
 
LVL 18

Expert Comment

by:sventhan
ID: 23704610
If its dynmaic sql then try the following...
v_sql := q'[SELECT * FROM yourtable WHERE .... SUBSTR (c.map, 1, 3) LIKE '%' || p12 || '%']';
0
 

Author Comment

by:PMP2009
ID: 23709985
No....I have tried all the above ways and still can't get the correct solution...Plz help its very urgent
0
 

Author Comment

by:PMP2009
ID: 23710014
Also please not that p12  is my input parameter passed to the Stored procedure
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 375 total points
ID: 23710068
then we need this:
AND SUBSTR (c.map, 1, 3) LIKE ''%' ||p12|| '%'' 

Open in new window

0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23710386
Angel,
   Since you are caring about the first three characters only, why it cant be like this:

AND c.map LIKE p12 || '%'
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23710392
<< Also please not that p12  is my input parameter passed to the Stored procedure >>

What is the parameter data type declared for this p12?
If it is varchar2() then you wont be facing any issue, I guess..
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 23710557
>Angel,
>   Since you are caring about the first three characters only, why it cant be like this:

that depends on the needs.

say c.map contains "ABC000000"

and p12 passed contains "C"

it might still require a match...
0
 

Author Comment

by:PMP2009
ID: 23710678
Hello...i think i got the answer
0
 

Author Closing Comment

by:PMP2009
ID: 31549727
Thank you very much...in solving this....
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23710693
Angel, Got it..
I assumed that the asker will be passing three characters as input and hence the confusion.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses
Course of the Month20 days, 7 hours left to enroll

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question