• RELEVANCY SCORE 3.56

    DB:3.56:Database View Where Clause Condition j1






    Hi All.In database view How can I use the SQL syntax like IN LIKE NOT LIKE in where clause?

    DB:3.56:Database View Where Clause Condition j1


    Hi Saranya,According the wiki,The Where clause supports these JavaScript conditional operators: =, !=, , =, , =, , || So if the requirement is very urgent, I guess you should try to export the data to an internal database (using ODBC driver for example) and you should ask for an enhancement (if it still doesn't exist).https://wiki.servicenow.com/index.php?title=Database_Views#Task_2:_Add_Tables_to_the_Database_View Best regards,

  • RELEVANCY SCORE 3.45

    DB:3.45:Why Cant We Use Group Functions In Where Clause In Sql? z9





    Hi,

    The following query gives an error:

    select * from my_table where salary=MIN(salary);

    "group function is not allowed here"

    Why can't we use group functions in where clause in SQL?

    Thanks..

    DB:3.45:Why Cant We Use Group Functions In Where Clause In Sql? z9

    Hi,

    A sql query in Oracle is executed in this order,
    1)From / where clause goes first
    2) Rownum is generated
    3)SELECT is next
    4)Group by is applied
    5)Having and then order by is applied.

    Thanks

  • RELEVANCY SCORE 3.44

    DB:3.44:Can With Clause Be Used With A Insert Statement? k3





    Hi There,

    Can we use a SQL with clause in an insert statement,

    Something like

    create table tem (l number)

    insert into tem
    (l)
    (with
    t0 as
    (Select exp_amt as t0 from exp_main)
    select t0 from t0)The with statement in itself works fine, but it throws the following error with insert

    Error at Command Line:8 Column:17
    Error report:
    SQL Error: ORA-32034: unsupported use of WITH clause
    32034. 00000 - "unsupported use of WITH clause"
    *Cause: Inproper use of WITH clause because one of the following two reasons
    1. nesting of WITH clause within WITH clause not supported yet
    2. For a set query, WITH clause can't be specified for a branch.
    3. WITH clause can't sepecified within parentheses.
    *Action: correct query and retryThe reason I am using this format is because, we are trying to generate sqls from Analytics and the queries are generally formed like this. So instead of modifying the big queries, I was hoping we were able to insert it.

    Please advise

    thanks
    Sun

    Edited by: ryansun on Aug 14, 2012 1:59 AM

    DB:3.44:Can With Clause Be Used With A Insert Statement? k3

    BluShadow wrote:

    Cause: 3. WITH clause can't sepecified within parentheses.Well, just to clarify. WITH clause on OUTER level can't be enclosed in parenthesis:

    SQL create table emp1
    2 as select * from emp
    3 where 1 = 2
    4 /

    Table created.

    SQL insert
    2 into emp1
    3 (
    4 with t as (select * from emp)
    5 select * from t
    6 )
    7 /
    )
    *
    ERROR at line 6:
    ORA-32034: unsupported use of WITH clause

    SQL insert
    2 into emp1
    3 (
    4 select *
    5 from
    6 (
    7 ( -- with clause within parenthesis start
    8 with t as (select * from emp)
    9 select * from t
    10 ) -- with clause within parenthesis end
    11 )
    12 )
    13 /

    14 rows created.

    SQL SY.

  • RELEVANCY SCORE 3.44

    DB:3.44:Sql*Loader Clause When Compare Date z9


    Hi all,

    I i would like add a conditional clause in sqlldr.

    I try use WHEN to_date(DAT_INT_RATING_EFF,'YYYYMMDD') to_date('20301231','YYYYMMDD') but I have an error

    is it possible to compare a date in the "when" clause ?
    I am obliged to use sql*loader

    my script :
    UNRECOVERABLE LOAD DATA
    characterset 'CHARACTERSET'
    INFILE 'DONNEES'
    APPEND
    INTO TABLE SCHEMA.WSF_TABLE
    WHEN to_date(DAT_INT_RATING_EFF,'YYYYMMDD') to_date('20301231','YYYYMMDD')
    FIELDS TERMINATED BY 'DEBUTSEPARATEUR_FINSEPARATEUR' TRAILING NULLCOLS
    (
    ...
    DAT_INT_RATING_EFF char(8),
    DAT_INT_RATING_EXP char(8),
    ....
    )

    Thanks
    Sorry for my english :(

    DB:3.44:Sql*Loader Clause When Compare Date z9

    Tough luck...
    Oracle Database Utilities 10g Release 2 (10.2)Table 9-4 Parameters for the Field Condition Clause
    ------------------------------------------------
    Parameter Description
    ------------- ----------------------
    pos_spec Specifies the starting and ending position of the comparison field in the logical record. It must be surrounded by parentheses. Either
    . . . E t c . . .
    operator A comparison operator for either equal or not equal.
    . . . E t c . . .I suspected this, but thought maybe it was fixed in 11g. I had to look it up in a 10g manual.
    Perhaps you need to load first into a "staging" table or create an external table and write sql dml to load into the real table.
    ;)

  • RELEVANCY SCORE 3.44

    DB:3.44:When To Use "Is" Or "As" When Creating Procedures And Functions 8a


    This may be an easy one but can someone clarify when you should use one or the other? I've looked at the samples in the PL/SQL reference for both procedures and functions and they both say the same thing "IS | AS Clause
    Use the appropriate part of this clause to declare the body of the function.".

    Matt

    DB:3.44:When To Use "Is" Or "As" When Creating Procedures And Functions 8a

    In Oracle 5 you had CREATE TABLE AS... and CREATE VIEW AS... and that was it. PL/SQL was introduced in version 6, and stored PL/SQL (CREATE PACKAGE|PROCEDURE|FUNCTION) in version 7.

    CREATE TYPE appeared in 8.0.x, although as it was separately licenced nobody used it until 8i.

    Message was edited by:
    William Robertson

  • RELEVANCY SCORE 3.33

    DB:3.33:Sql Server "Left Outer Join" Syntax sj


    Haven't seen a solution to this on the forum or in the docs.

    I've got 2 objects, Task and Role, that are linked in a M-M relationship.

    My tables are:
    T_TASKS
    T_TASKSROLES
    T_ROLES

    I am querying T_TASKS and joining on T_ROLES, but I need to use an outer join on T_ROLES.

    In SQL Server, my FROM clause SHOULD look like this:

    FROM (T_TASKSROLES t2 LEFT OUTER JOIN T_TASKS t1 ON t1.ID = t2.TASKID) LEFT OUTER JOIN T_ROLES t0 ON t0.ID = t2.ROLEID

    however, if I use eb.anyOfAllowingNone(_roles) in my ExpressionBuilder. TopLink creates a LEFT OUTER JOIN clause that looks like this:

    FROM T_ROLES t0 LEFT OUTER JOIN T_TASKS t1 ON ((t0.ID = T_TASKSROLES.ROLEID) AND (t1.ID = T_TASKSROLES.TASKID))

    I can see the logic in how it builds this clause. But, it doesn't parse in SQL Server.

    Is there a way to effect how TopLink generates the FROM clause for outer joins? I mean, I understand how to use the XXXPlatform.java source files and can change whether to use OuterJoin in the WHERE clause or not. But, I can't see anything in the platform class that would allow me to figure this out.

    I realize I could write SQL manually, but is there a way to do this so that the same code would work on SQL Server, Oracle, and Sybase (assuming the DatabaseLogin is configured appropriately)?

    It just seems like LEFT OUTER JOIN when joining M-M relationships isn't generating proper SQL. Is the TopLink SQL SQL92-compliant?

    Nate

    DB:3.33:Sql Server "Left Outer Join" Syntax sj

    duplicate post: Question answered in LEFT OUTER JOIN SQL syntax

  • RELEVANCY SCORE 3.21

    DB:3.21:Where Clause In Sap zp



    Hi all,

    i cant use the where clause in the script to pull the SAP tables.

    the script which used is

    SQL Select MANDT BUKRS HKONT GJAHR BUDAT MONAT from BSIS Where MANDT = '800'

    and GJAHR = '2009' ;

    i m using 32 bit SAP Connector 5.5 sr3.

    could anyone help me to solve this problem.

    without where condition its working fine...

    DB:3.21:Where Clause In Sap zp


    Hi all,

    i cant use the where clause in the script to pull the SAP tables.

    the script which used is

    SQL Select MANDT BUKRS HKONT GJAHR BUDAT MONAT from BSIS Where MANDT = '800'

    and GJAHR = '2009' ;

    i m using 32 bit SAP Connector 5.5 sr3.

    could anyone help me to solve this problem.

    without where condition its working fine...

  • RELEVANCY SCORE 3.21

    DB:3.21:Where Like Clause ck


    I have sql that uses a like clause instead of == or in. The like values do NOT have wildcards associated with them. Do they perform as quickly as == or in claues?

    DB:3.21:Where Like Clause ck

    Depends on the database. Contact your vendor.

  • RELEVANCY SCORE 3.17

    DB:3.17:Re: How To Get Multiple Row Data In A Single Row In Sql 3z


    What values do you mean?
    The values in the with clause?
    Just omit the with clause and use your own table.

    DB:3.17:Re: How To Get Multiple Row Data In A Single Row In Sql 3z

    One more query please....
    If output is required in this format....

    CUST_ID CARD_NO - CARD_STATUS
    ---------------------------------------------------
    1 111,1,222,1,333,1,777,2
    2 444,1,555,1
    3 666,1
    4 888,1,999,1

    1,2 are card status.............

    Every card number has status attached with it..........

  • RELEVANCY SCORE 3.17

    DB:3.17:(Or Clause) In Mdx a9


    Hi All,
            How can we use OR clause in MDX as we do in Sql like:

    DB:3.17:(Or Clause) In Mdx a9

    Here is a good post and an example.http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/8692b30e-2685-4972-8ec3-5ff01d5045e7Thanks,Nagy

  • RELEVANCY SCORE 3.14

    DB:3.14:Ejb Ql That Uses An In Clause Does Not Work. xa



    I am trying to use a method in an EJB that uses a select statement that uses the in clause.An sql example would be select * from quote q where q.id in ('sw','se')My code looks like the following.priceMatrixEntityHome.java

    Collection findRowsById(String sIds) throws RemoteException, FinderException;

    DB:3.14:Ejb Ql That Uses An In Clause Does Not Work. xa


    Your solution is correct and works. EJB-QL is very limited.But for performance reasons you might want to issue a single query to database and let the database do the work in one round-trip.A JBoss-specific approach can be seen in http://docs.jboss.org/jbossas/jboss4guide/r3/html/ch11.chapter.html#ch11.dynamicql.sect

  • RELEVANCY SCORE 3.12

    DB:3.12:Sql Stmt With Like And Variable! c8



    I would like to use a SQL stmts like this:

    SELECT * FROM tab WHERE key1 LIKE '%ivariable/i%'.

    Using LIKE in the WHERE clause requieres a character field. But I want to use a vairable...

    how should i write that?

    thx,

    holger

    DB:3.12:Sql Stmt With Like And Variable! c8


    Hi Holger,

    You can do like this also.

    Declare a range table like this.

    RANGES: R_VBELN FOR VBAK-VBELN. (You can declare your own variable type here.)

    Then populate the variable into the Ranges like this.

    R_VBELN-LOW = VARIABLE.

    R_VBELN-SIGN = 'I'.

    R_VBELN-OPTION = 'CP'.

    APPEND R_VBELN.

    CLEAR R_VBELN.

    Then write a select like this.

    SELECT * FROM TABLE WHERE VBELN IN R_VBELN.

    The above select will achieve the same functionality.

    Thanks,

    Sreekanth

  • RELEVANCY SCORE 3.12

    DB:3.12:Oracle - Where Clause - Date Question pj



    Hello,

    I want to query a table in QlikView with the following SQL Statement over an OLEDB Oracle connection:

    SQL SELECT *FROM "GLB_RPT"."SALES_TAG_SOR"WHERE "SALES_TAG_SOR"."DATUM" = '01.03.2009';

    But I got all records.

    How must the statement look like? Must I use to_date?

    Thanks for help

    Bastian

    DB:3.12:Oracle - Where Clause - Date Question pj


    Bastian,

    The safe way to get rid of this sort of problems is to use the preceeding load:
    LOAD...WHERE "SALES_TAG_SOR"."DATUM" = '01.03.2009' // if this is your format in QVSQL SELECT *FROM "GLB_RPT"."SALES_TAG_SOR";
    Regards,Michael

  • RELEVANCY SCORE 3.10

    DB:3.10:Using Like Clause In Prepared Statement pf


    Hi,
    I want to use LIKE clause in prepared statement. This is not returning any record.
    This is the query. Please help me in this.

    SELECT EMPLYR_GRP,EMPLYR_GRP_NAME FROM EMPLOYER_GROUP WHERE EMPLYR_GRP_NAME LIKE ? AND EMPLYR_GRP = ?

    This is giving Oracle SQL Error.

    SELECT EMPLYR_GRP,EMPLYR_GRP_NAME FROM EMPLOYER_GROUP WHERE EMPLYR_GRP_NAME LIKE %?% AND EMPLYR_GRP = ?

    DB:3.10:Using Like Clause In Prepared Statement pf

    The first variant should work just fine as long as you use stmt.setString(1, '%' + pattern + '%') to set the parameter. The second query is just plain wrong.

    Alin.

  • RELEVANCY SCORE 3.10

    DB:3.10:Like Clause 7s



    Hi,

    I want to use Like clause in where condition.

    select f1,f2 from tab1 where f3 like '%' input_param '%'.

    input_param is input parameter's value.

    Please help with the syntax.

    Thanks

    DB:3.10:Like Clause 7s


    Hi,

    data test type string.

    CONCATENATE '%' input_param '%' INTO test.

    iselect f1 f2 from tab1 into (n1, n2) where f3 like test.

    endselect./i

  • RELEVANCY SCORE 3.10

    DB:3.10:Create Group From Query 7m


    --
    When I use the Create_Group_From_Query built in I get an error
    due to the where clause of my select statement. I want a where
    clause that selects character date. How do I do this when the
    whole SQL statement is already in single quotes? Do I use double
    quotes for the statement?
    null

    DB:3.10:Create Group From Query 7m

    One small error in my post--the trigger is in the ANALYTE_METHOD_JN_PROD.METHOD_ID field.

  • RELEVANCY SCORE 3.09

    DB:3.09:Sql Tuning xa


    Hi,
    I have read in one of the sites that 'Avoid the use of NOT IN or HAVING in the WHERE clause. Instead, use the NOT EXISTS clause'.

    Is it the NOT IN or HAVING wont use indexes?How about NOT LIKE?Can anyone pls clarify?

    Thanks.

    DB:3.09:Sql Tuning xa

    I have read in one of the sites that 'Avoid the use of NOT IN or HAVING in the WHERE clause. Instead, use the NOT EXISTS clause'.
    Is it the NOT IN or HAVING wont use indexes?How about NOT LIKE?Can anyone pls clarify?Tom Kyte in his book "Effective Oracle by Design" says that both are valid for different datasets depending
    on the circumstances. Get his runstats package and test on your system - runtime and contention.

    Indeed, the statement makes no sense - if one was systematically and always better than the
    other, the logical thing to do would be to throw an error if the "wrong" one is used.

    Paul...

  • RELEVANCY SCORE 3.06

    DB:3.06:Using Indexes When Using Group By Clause j8


    How can I make use of indexes when there is a group by clause in the sql/ pl/sql. Heard that when ever there is a group by clause , sql bypasses use of indexes. Is it true?

    Thanks in advance

    DB:3.06:Using Indexes When Using Group By Clause j8

    Indexes are typically most useful in the WHERE clause. Does your query have one?

  • RELEVANCY SCORE 3.06

    DB:3.06:Format Date With Apostrophe zk



    Hello,

    I like to get the max(dateofday) from an existing qvd file and use this date in my SQL against the database to get the missing data up to the current date.

    To query the database (Teradata) I need the SQL where clause like " where dateofday '2012-03-15' ".

    To get the max date from the qvd is no problem, but how can I get the apostrophe around the date value ?

    Thanks !

    Amigoser

  • RELEVANCY SCORE 3.05

    DB:3.05:Use Of Exists Clause In Interface p1


    I want to build an interface from one table but using an exist clause to check the data change in the same table.In SQL statement it can be expressed as follows SELECT PB.PROJECT_KEY, COUNT (DISTINCT DW_PROJECT_BUILDING_KEY) FROM DW_PROJECT_BUILDING PB WHERE EXISTS (SELECT 1 FROM DW_PROJECT_BUILDING PB1 WHERE PB1.PROJECT_KEY = PB.PROJECT_KEY AND PB1.RECORD_STATUS_ID = 1) AND PB.RECORD_STATUS_ID = 1 AND TRUNC (PB.LOAD_DT) = '03-OCT-2013'GROUP BY PB.PROJECT_KEY;How can I build this SQL with exists clause in the interface ?Thanks

    DB:3.05:Use Of Exists Clause In Interface p1

    Found a way to do it in the INTERFACE. I put this part of the predicate EXISTS (SELECT 1 FROM DW_PROJECT_BUILDING PB1 WHERE PB1.PROJECT_KEY = PB.PROJECT_KEY AND PB1.RECORD_STATUS_ID = 1) AND PB.RECORD_STATUS_ID = 1in the Filter expression of Filter Properties for the column RECORD_STATUS_ID.

  • RELEVANCY SCORE 3.05

    DB:3.05:Sql Question: How To Use "In" In The "Where" Clause? da



    Hi,

    I notice that queries like the one below always fail when used in transformations:

    SELECT * FROM TABLE_X

    WHERE COLUMN_Y IN (A,B,C)

    or...

    SELECT * FROM TABLE_X

    WHERE COLUMN_Y NOT IN (A,B,C)

    It appears that Power Center doesn't like the use of "IN" in the WHERE clause. Is there an accepted method of writing queries that do the same thing (i.e., look for matches in - or not in - a list of values)?

    The only work-around I'm aware of is simply to create separate SELECT statements for each item in the IN clause.

    Thanks for your input,

    bkelso

    DB:3.05:Sql Question: How To Use "In" In The "Where" Clause? da


    I did forget the single ticks around my values, which are of course necessary if you are matching string values.

    Thanks again,

    bk

  • RELEVANCY SCORE 3.04

    DB:3.04:On Clause sp


    hi experts.. what is the use of ON clause when i can also write any condition through where clause as well... when column names are different i can use where clause also.. and any condition can also be given through where clasue like E.DEPTNODEPTNO .. Yes through WHERE clause i cant go for a full outer join... what else is the difference

    DB:3.04:On Clause sp

    Hi,

    You can refer to "bennadel", he provided the better information
    http://www.bennadel.com/blog/284-SQL-Optimization-And-ON-Clause-vs-WHERE-Clause.htm

    - Pavan Kumar N

  • RELEVANCY SCORE 3.04

    DB:3.04:Limitation Of A View: Order By Clause Not Supported 81


    Why can't we use Order By clause while creating the view?
    What is the reason behind SQL supporting Order by clause with TOP clause mentioned in the query and not supporting the same without TOP clause?

    DB:3.04:Limitation Of A View: Order By Clause Not Supported 81

    Here is what experts are sayingagainst ORDER BY in view:
    1.
    The real reason why you can’t ORDER any of these things is because they are all sets.
    Tables are ordered with clustered primary keys? (also with multiple NCI)
    Indexed views are ordered, aren't they?
    When constructing a SELECT query you start out thinking about unordered sets, but as soon as you hit the JOIN ON clause you start paying attention to ordering (indexing). When you hit the WHERE clause you know that indexing (ordering)will make or break
    your career.
    2. A key is only unique and not null and nothing else
    Yes on an imaginary ANSI SQL Server. However, on Microsoft SQL ServerPK/UQ is implemented as UNIQUE CI / NCI and indexes are used by the database engine when processing the table at will.
    3.How would theengine know not to generate a plan that provides the ORDER BY declared in the view definition?
    The assumption is that the database engine would be enhanced to support this feature. Just like now it can ignore an index, it can ignore the view ORDER BY clauseor take it as a hint.
    4. A table, by definition, is an unordered set of rows.
    That is called a heap. However, a table with PRIMARY KEY is ordered via the indexing mechanism.

    Without indexing (ordering)RDBMS industrywould be dead. Indexing existed prior to RDBMS (see IBM ISAM).
    While indexing is not part of the relational theory, a BIG mistake, all RDBMS vendors (MS, ORACLE, IBM, SYBASE) are using indexing for performance and otherreasons.

    Kalman Toth Database OLAP Architect
    IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

  • RELEVANCY SCORE 3.04

    DB:3.04:Sql Ce 3.5 Sp1 - Is Top Percent Clause Supported In Nested Query? s1


    I have a query that includes a nested query. However, the nested query makes use of an ORDER BY clause and thus requires the use of a TOP clause.I know that as of SQL CE 3.5 SP1, the TOP Clause is supported. I have tested it and it works (I.E. SELECT TOP (10) * FROM table)However, what I am wondering is if the PERCENT Clause is also supported? (I.E. SELECT TOP 100 PERCENT * FROM table )If not, is there another alternative to accessing every record within the nested query using a TOP clause?Thanks in advance for any help you guys can provide! :)Pseudo code:SELECT *FROM(    SELECT TOP 100 PERCENT * FROM table ORDER BY column)WHERE filter

    DB:3.04:Sql Ce 3.5 Sp1 - Is Top Percent Clause Supported In Nested Query? s1

    Hello,PERCENT clause is not supported in SQL CE v3.5 SP1. For more information, please see this article: http://technet.microsoft.com/en-us/library/bb686896.aspxThanks,Mohammad Imran.“This posting is provided AS IS with no warranties, and confers no rights”.

  • RELEVANCY SCORE 3.04

    DB:3.04:Distinct Sql Clause With Recordset Object pj



    Hi all, I was using a recordset with an sql distinct clause, on SAP B1 SP:0

    Now we have updated to SP1, and it seems like recordset query doesnt works well with the distinct clause.

    It returns all values.

    Does anybody tried to use this clause with SP1?

    Thanks.

    DB:3.04:Distinct Sql Clause With Recordset Object pj


    Hi,

    The DISTINCT clause by itself is NOT working in recordsets. You have to programatically make sure that you have the distinct records. So, if you're adding distinct records to a combo box you can either check if it exists before you add it or you can add a try... catch block around it that just catches the error (without displaying anything) when you add to a combo box.

    Hope it helps,

    Adele

  • RELEVANCY SCORE 3.04

    DB:3.04:Usage Of "Connect By" Clause In Timesten 1k


    Can we use certain special pl/sql like connect by clause in TimesTen. If not what is the alternative to accessing hierarchial data witha single query.

    DB:3.04:Usage Of "Connect By" Clause In Timesten 1k

    Can we use certain special pl/sql like connect by clause in TimesTen. If not what is the alternative to accessing hierarchial data witha single query.

  • RELEVANCY SCORE 3.02

    DB:3.02:How To Use An Unix Shell Variable In Sql Where Clause 7a


    Hi ,
    In my shell script first I get the Date and time from the system into the shell variable.At the end of the shell script I need to run a sql script in which I want to use the variable value in the where clause . Is there any way we can use a shell variable value in SQl script ?. Any help is greatly appriciated .
    Thanks in advance,
    Sampath

    DB:3.02:How To Use An Unix Shell Variable In Sql Where Clause 7a

    Try the following

    In Unix
    SQLPLUS username/password @MyScript.sql UNIX_Variable

    In SQL*Plus
    Reference the variable as 1

    select *
    from MyTable
    where MyDate = to_date('1','date_format');

  • RELEVANCY SCORE 3.01

    DB:3.01:Binding Variables Directly In Sql px


    We have a situation where a WHERE clause could have up to 5 constraints in the WHERE clause, or as few as 1. Is there a way to write the SQL so that it uses bind variables inline, so that we can have some dynamic function which generates the WHERE clause as needed? Something like

    SELECT *
    FROM table
    WHERE status = :A1 and
    initials :A2 : using 'A' as :A1 and 'CWF' as :A2;We'd be using an external application to generate and submit the SQL (ColdFusion).

    -Chuck

    DB:3.01:Binding Variables Directly In Sql px

    select ...
    from ...
    where (:p1 is null OR status = :p1)
    and (:p2 is null OR initials :p2)
    and (:p3 is null OR condition3 = :p3)
    and (:p4 is null OR condition4 = :p4)
    and (:p5 is null OR condition5 = :p5)or to write that another way...

    select ...
    from ...
    where status = NVL(:p1, status)
    and initials NVL(:p2, -1)
    and condition3 = NVL(:p3, condition3)
    and condition4 = NVL(:p4, condition4)
    and condition5 = NVL(:p5, condition5)Eliminates all those nasty OR's. :o))

  • RELEVANCY SCORE 3.00

    DB:3.00:When Like Clause Is Used In Sql zx


    Does sql select do full table scan when LIKE clause is used or not.

    Thx

    DB:3.00:When Like Clause Is Used In Sql zx

    Use either of the following :

    Set autotrace traceonly exp;

    Explain Plan for Select Statement;

    The explain plan generated by the oracle optimizer will better answer your query than any other Oracle techie.

    Thanks,
    Abhijit

  • RELEVANCY SCORE 3.00

    DB:3.00:How To Use The Query_Partition Clause ax


    COULD SOMEBODY GIVE ME AN EXAMPLE OF USING THE QUERY_PARTITION_CLAUSE? I KNOW THAT THE QUERY_PARTITION_CLAUSE IS TO BE USED WITH THE COUNT FUNCTION IN SQL WHEN YOU SPECIFY DISTINCT. THE QUERY_PARTITION_CLAUSE IS AN ANALYTIC_FUNCTION. I KNOW THAT THE PARTITION BY CLAUSE IS TO PARTITION THE QUERY RESULT SET INTO GROUPS BASED ON ONE OR MORE VALUE_EXPRESIONS. I WOULD LIKE TO SEE AN EXAMPLE IN AN SQL STATEMENT THAT WOULD RETURN COUNTS FOR ROWS USING DISTINCT AND QUERY_PARTITON_CLAUSE.

    DB:3.00:How To Use The Query_Partition Clause ax

    This is Oracle Reports forum. Looks like your question is pertaining to SQL. Please try posting your question in PL/SQL forum

    (Click "Forum Home" above and you should be able to find out )

    Thanks
    Ratheesh

  • RELEVANCY SCORE 3.00

    DB:3.00:Function In Where Clause 8a


    Hi All,

    Can anyone know how to use the user defined function in the where clause of the SQL statement.

    Thanks in Advance,
    Madhu N.

    DB:3.00:Function In Where Clause 8a

    Hi,
    Boolean is a pls variable.. and cannot be used in a where clause returned from a function.

    Simple can we define a variable of type boolean in sql*plus?

    Rgds
    Srinivas..

  • RELEVANCY SCORE 3.00

    DB:3.00:Cant Use The Like Clause In An Sql Server Reporting Service Embedded Rdlc Report? 88


    Hello Community

    This may seem like an obvious question but I used
    every approach that I could think of for Reporting Services
    to return the rows using the Like Clause.
    In SQL Server Reporting Services if I query by Date rows are returned
    back.
    But if I add a Like Clause no rows are returned.
    In its simplest form I have an embedded rdlc report
    that I am passing a variable to that contains a value in it
    for SQL Server Reporting services to query the table with.
    In the code behind the variable is set something like this:
    string GetName = '%' Smith '%';
    Then the variable gets assigned to a parameter to be
    processed in rdlc properties:
    Expression Operator
    Value
    ---------- --------
    -----
    =Fields!Date.Value =
    =Parameters!FromDate.Value
    =Fields!Date.Value =
    =Parameters!ToDate.Value
    =Fields!Last_Name.Value Like =Parameters!paramGetName.Value
    If I remove the line with Last_Name from the rdlc properties then I get all of the
    rowsreturned by Date,but if I try to use the filter on last names for example beginning
    with the variable having the value of Sm or Smith or anything else then no rows are returned,

    only the column heading appear.
    Even if I try to use a select statement (which I prefer not to use)with a Where Clauseto

    pass to the rdlc:

    Where [From Date] = '1/1/2011'
    AND[To Date] = '1/1/2012'
    AND ([Last Name] LIKE '%' @paramGetName '%') ;
    no rows are returned when I try use the Like Clause.
    How do you use the Like Clause passing thevalue in a variableto an SQL

    Server Reporting Service embedded rdlc report?
    Thank you
    Shabeaut

    DB:3.00:Cant Use The Like Clause In An Sql Server Reporting Service Embedded Rdlc Report? 88

    wildcard for SQL is %
    wildcard for FILTER values in the report is *
    so it depends what that LIKE is used for...if t is used as a filter expression in the report then as per KevinNicholas, use * rather than %Rgds Geoff

  • RELEVANCY SCORE 3.00

    DB:3.00:Sql Syntax Using 'Like' 3d


    Hello,

    I am using with MS SQL Server.
    I use for SELECT as

    pstmtSelect = conn.prepareStatement(
    "select Id, Description from Stock where Id = ?");

    What is the syntax for using LIKE in the WHERE clause? ( I would like to use - WHERE Id LIKE ......?).

    Thanks,

    Aung.

    DB:3.00:Sql Syntax Using 'Like' 3d

    No, it's working. It's just that nobody seems to have a good answer for this, and "You can't do that" doesn't seem to be an acceptable answer, so people just keep asking.

  • RELEVANCY SCORE 3.00

    DB:3.00:How To Export The Result Of A Sql For Xml Query To An Xml Document 13


    I have a sql query with a 'for xml explicit' clause. I would like to export the results of the query as an .xml file.  I specify the query as 'SQL Command' in an OLEDB source adapter, but I don't know see an adapter that I can use for the destination.  Any ideas?  Thanks.

    DB:3.00:How To Export The Result Of A Sql For Xml Query To An Xml Document 13


    I got the book and attempted to write a script component to serve as an xml destination. I tried following the example in chapter 9 of the book, but in my case the input to my (destination) componentappears to be a single row with asingle column named 'XML_F52E2B61-18A1-11d1-B105-00805F49916B' data type DT_Image. Thisrepresents the result of the SQL 'FOR XML AUTO, ROOT('Root')' query. I convert it to a string using System.Text.Encoding.Utf8:
    Dim len As Int32
    len = CInt(Row.XMLF52E2B6118A111d1B10500805F49916B.Length)
    Dim str As String = System.Text.Encoding.UTF8.GetString(Row.XMLF52E2B6118A111d1B10500805F49916B.GetBlobData(0, len))

    On PostExecute I assign the user variable MyXml = str and when I look at it in Localsall I see is: {D\nC. Any ideas?

  • RELEVANCY SCORE 2.99

    DB:2.99:Re: Sql Problem ka


    You may want to check the datatype of the column you are using
    for
    your WHERE clause. If it is not VARCHAR (or VARCHAR2) this may be
    your
    problem.
    I have seen similar problems trying to use a TEXTDATA or
    TEXTNULLABLE variable to match a CHAR column in an Oracle database.

    Hardcoding the clause (as you did with 'one') works fine but
    substituting a variable results in no matches.

    Mark Salisbury
    salisbury@metatec.com

    ______________________________ Reply Separator
    _________________________________
    Subject: SQL problem
    Author: Mike McFadden [SMTP:mrm@atgs.com] at Dublin
    Date: 07/29/97 9:12 AM

    I am having a problem with parts of an SQL statement and I'm out of
    ideas.
    Specifically, it is with there 'where =' clause using a variable. If
    it's
    relevant, I'm using Forte' 2.0.H.2 on a Windows NT box. The DB manager
    is
    Oracle 7.1.

    The following statement works:

    sql select description into :xxxx from plot where name = 'one'
    on session DBSess;

    This one fails to return a row in the database. It doesn't produce an
    exception:

    yy: string;
    yy = 'one';

    sql select description into :xxxx from plot where name = :yy
    on session DBSess;

    Additionally, I can use a variable to do a 'like' clause, e.g. :

    yy = '%one%'; .. where name like :yy ....

    Thanks,
    --
    Mike McFadden
    mrm@atgs.com

    DB:2.99:Re: Sql Problem ka

    You may want to check the datatype of the column you are using
    for
    your WHERE clause. If it is not VARCHAR (or VARCHAR2) this may be
    your
    problem.
    I have seen similar problems trying to use a TEXTDATA or
    TEXTNULLABLE variable to match a CHAR column in an Oracle database.

    Hardcoding the clause (as you did with 'one') works fine but
    substituting a variable results in no matches.

    Mark Salisbury
    salisbury@metatec.com

    ______________________________ Reply Separator
    _________________________________
    Subject: SQL problem
    Author: Mike McFadden [SMTP:mrm@atgs.com] at Dublin
    Date: 07/29/97 9:12 AM

    I am having a problem with parts of an SQL statement and I'm out of
    ideas.
    Specifically, it is with there 'where =' clause using a variable. If
    it's
    relevant, I'm using Forte' 2.0.H.2 on a Windows NT box. The DB manager
    is
    Oracle 7.1.

    The following statement works:

    sql select description into :xxxx from plot where name = 'one'
    on session DBSess;

    This one fails to return a row in the database. It doesn't produce an
    exception:

    yy: string;
    yy = 'one';

    sql select description into :xxxx from plot where name = :yy
    on session DBSess;

    Additionally, I can use a variable to do a 'like' clause, e.g. :

    yy = '%one%'; .. where name like :yy ....

    Thanks,
    --
    Mike McFadden
    mrm@atgs.com

  • RELEVANCY SCORE 2.99

    DB:2.99:Optimisation Sql ms


    Hi,

    When i use a clause OR in my select as the optimizer can use an index.

    ThanKS

    DB:2.99:Optimisation Sql ms

    user8946397 wrote:
    Hi,

    When i use a clause OR in my select as the optimizer can use an index.Although, you question lacks lot of information, may be this Article can help you decide better which index to use for DSS or OLTP type environment.

    *Bitmap Index vs. B-tree Index: Which and When? --by Vivek Sharma+ *
    http://www.oracle.com/technology/pub/articles/sharma_indexes.html

    HTH

    Regards
    Z.K.

  • RELEVANCY SCORE 2.98

    DB:2.98:With Clause za


    can we use th WITH clause in the report SQL main query?

    as its not accepting in the report query bulider...whereas in sqlplus the same query with the WITH clause works fine

    DB:2.98:With Clause za

    can we use th WITH clause in the report SQL main query?

    as its not accepting in the report query bulider...whereas in sqlplus the same query with the WITH clause works fine

  • RELEVANCY SCORE 2.98

    DB:2.98:Oracle Sql Tuning xk


    Hi,

    Can somebody explain me about the basics of tuning or any good resource online for absolute beginners.
    Concepts of Explain Plan, Hints, indexes etc...
    I ve come across some terminolgy like,

    Driving table,
    The order of the table in the from clause
    The order of the joins in the where clause.
    When and where to use hints.
    which one is a candidate for an hint
    Explain plan explained, like nested loops, hash join, merge, etc,,, with some examples on how it works...
    How to judge on the costs section of the explain plan...
    Any help is appreciated...

    Thanks!

    DB:2.98:Oracle Sql Tuning xk

    That's a very broad question. I've always found the online Oracle documentation to be the best available answer to most questions and doubts that I come across.

    Regarding your particular doubts, I'd say you should take a look at the concepts book, the SQL reference and maybe the Performance Tuning guide for info on explain plans and the sort.

    Concepts - http://download.oracle.com/docs/cd/E11882_01/server.112/e10713.pdf
    SQL - http://download.oracle.com/docs/cd/E11882_01/server.112/e10592.pdf
    Performance Tuning - http://download.oracle.com/docs/cd/E11882_01/server.112/e10821.pdf

    The developer book may also be very useful as well as the PL/SQL reference. Just take a look at below for the complete list of books:
    http://www.oracle.com/pls/db112/portal.portal_db?selected=4frame=#essentials

    It will take you a lot of effort and patience to go through those but it certainly pays off.

  • RELEVANCY SCORE 2.98

    DB:2.98:Anyone Any Good With Access? m7


    You cannot use an aggregate function in the WHERE clause of an SQL statement.

    What the hell does this mean? :/



    DB:2.98:Anyone Any Good With Access? m7

    Okay i figure I didn't explain myself too well...

    I need to create a calculated query which allows the size, depth and width of a package to be measured, from which i calculate the price from. However when i try a basic Sum formula in the criteria box, which is: Sum(++), I'm presented with something along the lines of this:

    I even rebuild the whole database and I still can't get it to work, and I get the impression its something really simple. I had a calculated query in my last project, which was very similar to this and it worked perfectly.



  • RELEVANCY SCORE 2.97

    DB:2.97:Cannot Use Where Clause In Sql String To Retrieve Data In Vb.Net 2005 Upgrade Of A Vb 6 App. xz


    I am connected to an Access database and I am using sql strings to retrieve a recordset. Thestring

    SQLstr =

    SELECT * FROM Catalog WHERE [Cat No] Like ' MinSize
    *' ORDER BY [Cat No]
    returns an Comerror 80004005. Removing the where clause works fine.The variable MinSize is a string.

    Thanks,
    Ron

  • RELEVANCY SCORE 2.97

    DB:2.97:Dataset Designer Problem Using Sql In Clause kx


    I have a query that works just fine agains the database using an in clause.
    where id in (5,6,7)
    However, when I try to use the dataset design and build a query using a in clause the designer is apparently looking at the data type of id and only allow me to pass in a valid integer for the parameter for the in clause.
    where id in (?)
     
    I have tried change the parameter definition and actually specify that the parameter is a string but it still complains.   Anyone know how to fix this?
     
    Thanks

    DB:2.97:Dataset Designer Problem Using Sql In Clause kx

    I think basically this is a good solution. But for security reasons I suggest to use SqlParameters.

    public partial class MyDataTableTableAdapter : Component
    {
    public int FillByIds(MyDataSet.MyDataTable dataTable, params int[] ids)
    {
    SqlCommand cmd = new SqlCommand();
    string paramTxt = string.Empty;
    SqlParameter param = null;

    for (int i = 0; i ids.Length; i)
    {
    param = new SqlParameter(id i, ids[i]);
    paramTxt = (paramTxt.Length == 0 ? : , ) @ param.ParameterName;
    cmd.Parameters.Add(param);
    }

    cmd.Connection = this.Connection;
    cmd.CommandText = Properties.Settings.Default.SqlSelectWithInClause.Replace(@ids, paramTxt);

    this.Adapter.SelectCommand = cmd;
    if ((this.ClearBeforeFill == true))
    {
    dataTable.Clear();
    }

    int returnValue = this.Adapter.Fill(dataTable);
    return returnValue;
    }
    }

  • RELEVANCY SCORE 2.97

    DB:2.97:How To Pass An Array To A Procedure &Amp; How To Use Array In In Clause In Sql ms


    Hi all,

    how do i pass an array of varchar2[10] in an procedure and i want to use this array in the IN caluse of the SQL statement inside the procedure. Can anyone please help me on this.

    Thanks regards
    shyam~

    DB:2.97:How To Pass An Array To A Procedure &Amp; How To Use Array In In Clause In Sql ms

    There are multiple ways. For example:

    SQL create or replace
    2 type str10_tbl_type is table of varchar2(10)
    3 /

    Type created.

    SQL -- Using TABLE operator
    SQL create or replace
    2 procedure p1(
    3 p_str10_tbl str10_tbl_type
    4 )
    5 is
    6 begin
    7 for rec in (
    8 select ename,
    9 sal
    10 from emp,
    11 table(p_str10_tbl)
    12 where ename = column_value
    13 ) loop
    14 dbms_output.put_line(rpad(rec.ename,10) || rec.sal);
    15 end loop;
    16 end;
    17 /

    Procedure created.

    SQL set serveroutput on format wrapped
    SQL exec p1(str10_tbl_type('KING','ALLEN','SMITH'));
    KING 5000
    ALLEN 1600
    SMITH 800

    PL/SQL procedure successfully completed.

    SQL -- Using MEMBER OF method
    SQL create or replace
    2 procedure p1(
    3 p_str10_tbl str10_tbl_type
    4 )
    5 is
    6 begin
    7 for rec in (
    8 select ename,
    9 sal
    10 from emp
    11 where ename member of p_str10_tbl
    12 ) loop
    13 dbms_output.put_line(rpad(rec.ename,10) || rec.sal);
    14 end loop;
    15 end;
    16 /

    Procedure created.

    SQL set serveroutput on format wrapped
    SQL exec p1(str10_tbl_type('KING','ALLEN','SMITH'));
    SMITH 800
    ALLEN 1600
    KING 5000

    PL/SQL procedure successfully completed.

    SQL SY.

  • RELEVANCY SCORE 2.97

    DB:2.97:Escape Clause Causes Error In Lookup Modified Sql Statement 17


    I need to use a modified SQL statement for a lookup component. It has an escape clause in it and this causes error:select * from dbo.typecustomer where ? like '%'+type_subtype +'%'ESCAPE '_'Is this is a bug? Any help will be greatly appreciated.ThanksAkin

    DB:2.97:Escape Clause Causes Error In Lookup Modified Sql Statement 17

    The escape clause shouldn't be affecting the parameter usage if the SQL works without it.  The lookup doesn't parse the SQL, so I suspect the problem is with the provider.  Essentially, the lookup asks the provider to prepare the command and then to derive parameter information.  The provider is failing in one of the above steps (probably the prepare step).  I would first try this with the latest provider (i.e. use snac instead of sqloledb in the connection manager for the lookup) and if the problem persists, ask on the snac forum if command preparation followed by parameter derivation is problematic with your specific command.

  • RELEVANCY SCORE 2.97

    DB:2.97:With Clause In Pl/Sql xz


    Hi,

    Can we use With sql clause in PL/SQL?

    Adi

    DB:2.97:With Clause In Pl/Sql xz

    More explanation:
    http://www.oracle-base.com/articles/misc/WithClause.php
    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4423923392083

  • RELEVANCY SCORE 2.97

    DB:2.97:Rsa Envision Custom Report Using Aggregates In Sql Clause Gives Error zp



    I would like to create a Windows failed logons report that shows users exceeding 50 failed logons. How can I specify criteria to show me failed logons that exceed a number say 50. When I put in criteria where count(username) 50, I get an error

    Invalid SQL WHERE clause: .ASA Error -150: Invalid use of an aggregate function.

    Thanks for your help.

    DB:2.97:Rsa Envision Custom Report Using Aggregates In Sql Clause Gives Error zp


    Hi there

    sorry for the late answer, Christmas and so on,...:-)

    Well, I'll try to explain with an own report at first:

    We create a report on the Windows table and select SourceAddress, Username, count(UserName) as fields.

    Next we filter for MessageID Security_675, Username shall be longer than 4 characters and shall not end on '$'. We sort by count(Username) descending, the order of the fields is: SourceAddress, UserName, count(UserName).

    The resulting XML looks like this:

    ?xml version="1.0" encoding="UTF-8"?

    report name="SomeReportName" dateTimeStamp="false" runtimeChecked="false" resultLimit="-1" distinct="false" regex="false" resolve="false"description/table name="Windows"/chartfieldListfield name="SourceAddress" sort="none" title="SourceAddress"/field name="UserName" sort="none" title="UserName"/field name="count(UserName)" sort="descending" title="count(UserName)"//fieldList/chartvariables/sql![CDATA[MessageID = 'Security_675_Security' AND char_length(UserName) 4 AND UserName NOT LIKE '%$' ]]/sql/report

    The interesting part is BOLD. As we know there's a Sybase DB in the back-end, so we might use all commands a Sybase database accepts. Unfortunately the enVision Web-UI does not accept them (partly because we have no control over ordering the statements, so they end up in a wrong order for Sybase and it would not work anyway...).

    Let's change the bold statement above to the following:

    MessageID = 'Security_675_Security' AND char_length(UserName) 4 AND UserName NOT LIKE '%$') GROUP BY "saddr","username" HAVING COUNT(UserName) 30 ORDER BY 3 DESC /*

    Ok. What does it do? First part till NOT LIKE '%$' is same as before. Now we got a closing bracket there. Why? Because RSA enVision creates an opening bracket before the whole filter construct, but we just don't ever see that one. So we need to close it to be able to continue with our own statement.

    Next comes the GROUP BY statement, which groups the results by sourceaddress and username. Then we got a HAVING COUNT(UserName) 30 which will only show results that have a count higher than 30 and finally the ORDER BY 3 DESC /*.

    ORDER BY 3 DESC orders the result by the count (it's in third position of the fields we selected, therefore 3).

    And really important: /* comments out the whole rest of the SQL statement that RSA enVision would append to the report query. As we just created our own query here we don't need the rest that RSA enVision would append, so we comment it out. Actually we have to comment it out as the query will not be accepted by Sybase anymore,...it would be a really ugly mix between our own query and RSA enVision statements ;-)

    As mentioned before: You need to do those changes in the report XML directly, and afterwards you need to restart the NIC Webserver Service. You can't modify that report in the WEB-UI anymore.

    Hope that's clear more or less,...otherwise just ask.

    Regards

    Rafael

  • RELEVANCY SCORE 2.97

    DB:2.97:Is There A Max Length For A Proc Sql Statement? 8s



    Looks like my SQL where clause is being truncated which causes an error when I run it in SAS. The syntax is correct when checked. Was wondering if there is a max length for the where clause of the proc SQL statement? If so, do you know what it is?

    Message was edited by: Suitland at May 5, 2006 2:42 PM

    DB:2.97:Is There A Max Length For A Proc Sql Statement? 8s


    I recommend that you report your problem to Technical Support. See http://support.sas.com/techsup/intro.html .

    -- David Kelley, SAS

  • RELEVANCY SCORE 2.97

    DB:2.97:Slow Performance With Sql Server 2005 Executing Parameterized Queries With Like And An Aggregate Comparison In The Where Clause z1


    We have a slow-performing query and, after some analysis, have narrowed the cause down to using parametrized queries in conjunction with LIKE and aggregate comparisons used in the WHERE clause. If we use a non-parametrized query (but keep the WHERE
    clause the same) the query performs much faster and, in fact, executes a different plan. Using OPTIMIZE FOR UNKNOWN is not an option for us as we are using SQL Server 2005. Obviously, we'd like to use parametrized queries to prevent SQL injection
    but the slow perf is unacceptable. We need the LIKE to support wildcard scenarios. If we need to we'll use dynamic SQL (scrubbed as much as we can) in lieu of parametrized queries. We're wondering, however, is there another option that would
    give us the perf without sacrificing safety? I can provide a sample database, query, plans, etc. if necessary...I'm assuming SQL Server generates a less efficient plan with the parametrized query as it can't make any assumptions about the parameter values...
    Mike Bouck

    DB:2.97:Slow Performance With Sql Server 2005 Executing Parameterized Queries With Like And An Aggregate Comparison In The Where Clause z1


    We have a slow-performing query and, after some analysis, have narrowed the cause down to using parametrized queries in conjunction with LIKE and aggregate comparisons used in the WHERE clause.

    Do you mean query or stored procedure?
    It is better toturn queries intostored procedures because they are official compiled
    database objects. Queries are not database objects.
    Performance related links:
    http://www.sqlusa.com/bestpractices/training/scripts/parametersniffing/
    http://www.sqlusa.com/bestpractices/coveringindex/
    Kalman Toth, SQL Server Business Intelligence Training; SQL 2008 GRAND SLAM

  • RELEVANCY SCORE 2.96

    DB:2.96:Index Usage From Sql Query In Oracle Forms mk


    Hello:

    Using LIKE/OR in where clause (of an indexed column) will force the the query to NOT use INDEX? We have these where clause in Oracle Forms Records Group.

    Below are two examples...
    1. If we have a where clause with LIKE would that NOT use the index?
    Example: ColumnName like :block.Column||%

    2. How about having an OR clause?
    Example: and (ColumnName = :block.column or :block.column is null)

    Thanks

  • RELEVANCY SCORE 2.96

    DB:2.96:For Clause - What Is Wrong? 7m


    Hi,
    I've got a problem using FOR clause. I use 8.1.6 client and server. The SQL compiler constatnly rejects FOR clause in the statements, like

    for 3 insert into admpentities(entityid) values (:values);

    PLS-00103: Encountered the symbol "FOR" when expecting one of the following:
    %

    And it seems that FOR is rejecting in any SQL expression. I guess I do something not right. What I have to do to make it work, gurus??

    Thanks,
    Val.

    null

    DB:2.96:For Clause - What Is Wrong? 7m

    Try this :

    for i in 1..3
    loop
    -- insert statement
    end loop;

    -------------------------------
    HTH

    null

  • RELEVANCY SCORE 2.96

    DB:2.96:Unable To Use In Clause With Sqlj k7


    Hi,
    i need to use the IN clause in my SQLJ sql. for example: #sql
    { select p.customer_name from customers p where p.state IN ('IL','MI','CA') };

    The above works, but when i try to pass in an array param
    #sql
    { select p.customer_name from customers p where p.state IN (:arr) }; - where arr is an array, this does not work. Later i understood that since IN is a keyword, this will not work. Is there any fix or workaround for this ? i dont want to embed SQL as a string in my java code ? Any suggestions will be greatly appreciated.

    Thanks in advance,
    Geetha

    DB:2.96:Unable To Use In Clause With Sqlj k7

    Geetha,
    This question is asked with incredible frequency on JDBC/SQLJ related newsgroups, forums and similar Web sites (even on Ask Tom).

    As far as I know, what you wish to do is not possible. The best you can do is something like this:
    select p.customer_name from customers p where p.state IN (?,?,?)But please, don't take my word for it, search the Internet for other possibilities.

    Good Luck,
    Avi.

  • RELEVANCY SCORE 2.96

    DB:2.96:Create Lookup List fk



    Hi

    I'd like to create a lookup list of dates from an existing dataset, which can then be used in the where clause of proc sql.

    Any ideas?

    DB:2.96:Create Lookup List fk


    Well, you could have just changed the line:

    where DATE in ("LIST_OF_DATES.");

    To

    where put(datepart(DATE),date9.) in ("LIST_OF_DATES.");

    But glad it works for you.

  • RELEVANCY SCORE 2.96

    DB:2.96:Index Usage From Sql Query In Oracle Forms k7


    Would using LIKE/OR in where clause (of an indexed column) will force the the query to NOT use INDEX. We have these where clause in Oracle Forms Records Group.

    Below are two examples...
    1. If we have a where clause with LIKE would that NOT use the index?
    Example: ColumnName like :block.Column||%

    2. How about having an OR clause?
    Example: and (ColumnName = :block.column or :block.column is null)

    Thanks

    DB:2.96:Index Usage From Sql Query In Oracle Forms k7

    Hi
    Answer 1: Where with like clause WOULD use the index.
    In this example index on ColumnName

    Answer 2: Write better where:
    Example: and (:block.column is null or ColumnName = :block.column)
    When :block column is null then statement after 'or' is not used. Index will not be used with RBO, i think.

    The best way to be sure is to look at explain plan on the original query.

    Regards
    Kuba

  • RELEVANCY SCORE 2.96

    DB:2.96:Dynamic Sql Question. zf


    Hi all,

    I have an array coming into the PL/SQL from the Java Layer. But I need to use this nested table to fetch data and use it in my where clause.

    My ddl string looks like this

    ddl_String := 'SELECT USERID, REPORTID, DISTMGRID, SALESREPID, DISTMGRFNAME,SALESREPFNAME,OPPSTATUS,OPPCOUNT,
    OPPAMOUNT FROM EMCSV_R25_DIST_PROF_TBL WHERE OPPSTATUS IN
    (SELECT * FROM TABLE(CAST (' || statusArray || 'AS RPTR25_STRING_ARRAY)))

    OPEN tot FOR ddl_String using something;

    Can I do the above like giving the nested table into dynamic sql. How to handle this situation.

    Thanks for your help.

    DB:2.96:Dynamic Sql Question. zf

    I posted some sample code at Challanging processing and How do i seperate the comma seperated string? that does this type of thing.

  • RELEVANCY SCORE 2.96

    DB:2.96:Passing Parameter To From Clause Of Sql aa


    HI:

    We have a scenario where a custom table gets created every month and we have to use this in the from clause of a cursor.

    We would like to send this custom table as an Input to the Procedure and inturn to the cursor.

    Can this be achieved, any pointers in this direction will be appreciated.

    Rahul

    DB:2.96:Passing Parameter To From Clause Of Sql aa

    Hi:

    This is what I have as a from caluse of my cursor:

    From table_name igibuk
    , apps.ar_payment_schedules_all@erplnk pay
    , apps.ra_customer_trx_all@erplnk rct

    The first table "table_name" has to be dynamically passed at run time any ideas of how to so it.

    Rahul

  • RELEVANCY SCORE 2.96

    DB:2.96:Sql's In Clause Can Have Resident Load d8



    Hi all,

    I have a resident table, in which I have a list of values.

    I need to use those values in the IN clause of SQL. like

    SQL Select * from Table1 where Value1 IN ( Load Value2 Resident Master1);

    please suggest.

    I have some values coming from some different source which I need to use while loading another table.

    Thanks in advance.

    DB:2.96:Sql's In Clause Can Have Resident Load d8


    Hi amars,

    Did you find any solution, I have the same issue.

    Thx

  • RELEVANCY SCORE 2.96

    DB:2.96:Use Variable In Place Of Tablename In Sql From Clause d7


    I am trying to find a way to use a variable in a SQL statement in place of a tablename in the FROM clause. Dynamic SQL is not an option.This will be used inside a SSIS package if that will help get me anything.

    DB:2.96:Use Variable In Place Of Tablename In Sql From Clause d7

    It's really quite simple.Simple example - Instead of Select field1, field2 from tablenameI want Select field1, field2 from @variabletablenameWhy do I want this? Because I want to use the same generic function or SPROC but change where it is applied to, such as table1. table2.
    Good finemake 2 variable uVar_TableName (or as you mentioned @variabletablename) =  table1   then you can change this to table 2 as you wantuVar_SelectStmnt = SELECT f1 , f2 , FROM [ + @[User::uVar_TableName]  + ] and in your OLE set the variable OLE DB Connection manager = SQLServerDestinationConnection Or what ever you have in your packageData access mode = Table name view name variableVariable name : uVar_SelectStmnt

    Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post(s)

  • RELEVANCY SCORE 2.95

    DB:2.95:Viewlink Accessors Generating Faulty Sql fp


    It seems that ViewLink Accessors use a back-to-front WHERE clause when trying to accesses the linked view.

    ViewSource has attribute SourcePK used to link to ViewDest.DestFK representing a foreign-key 1-many relationship.

    We expose the accessor for the Source - getViewSource() - which is added to ViewDestRowImpl.java. We don't expose any accessor for the Dest.

    Examining the ViewLink SQL shows:
    Source attributes: EntitySource.SourcePK
    Where: ? = EntityDest.DestFK
    Query clause: EntitySource.SourcePK = EntityDest.DestFK

    When we call getViewSource() faulty SQL causes an exception:

    oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement:
    SELECT EntitySource.SourcePK, EntitySource.SourceDataAtt WHERE (? = EntityDest.DestFK)

    This seems like the WHERE clause for the reverse accessor (ie getViewDest() to get the DEST rows for a particular SOURCE row).

    EntityDest is not in ViewSource at all.

    Is this a bug?
    Should I be defining a reverse-link (Source and Dest reversed)? - I thought ViewLinks were bi-directional else why allow the accessor to be exposed?

    Tony.

    DB:2.95:Viewlink Accessors Generating Faulty Sql fp

    This seems to be the same problem as
    View links

    I've managed to get things working by
    (a) setting both views' bind types to oracle rather than '?'. [Less than ideal, because I want to run against non-Oracle database. I'll cross that bridge later, I guess...]
    (b) removing the "Where" clause from the ViewLink's XML file (as specified in other thread). It will come back if you edit anything...
    (c) I may also have had to delete my classes directories to ensure everything was recompiled from scratch. [I've got one project dependent upon another and there's a cross-compilation bug that might have caused problems.]

    BTW: I'm on 9.0.3.1035.

    There's definitely a bug or two wrapped up in here - hopefully they have been or are being fixed.

    Something else that concerns me that I haven't had time to validate yet: is the view link accessor giving me a row from the correct view? I'm wondering whether there's a problem because I wouldn't have expected any SQL to be run when I use the (e.g.) getMasterView1 accessor; the instance of the master view linked to the detail row from which I'm calling the accessor has already been executed, so the row ought to be in the cache.
    My master-instance isn't called MasterView1 though; is it possible that an instance called MasterView1 is being executed?
    I guess I'll look into it....

    Hope that's useful to someone,
    Mike.

  • RELEVANCY SCORE 2.95

    DB:2.95:Sysdate In Conditions af


    How do I specify a where clause in a condition for an easy SQL report using sysdate?
    e.g.
    start_date sysdate
    the notes ,mention :SYSDATE_YYYYMMDD. HOw do I use this??
    Paul Platt

    DB:2.95:Sysdate In Conditions af

    Paul,

    1) Edit your report region
    2) Click the Query Definition tab
    3) In the Query Conditions region, click the button "Add/Modify Conditions"
    4) Choose your column from the select list, and for the condition for that column, enter:

    SYSDATE

    That's it.

    Joel

  • RELEVANCY SCORE 2.94

    DB:2.94:Adf Vo Bind Variable Does Not Work In 'Orderby' Clause dc


    Hello, All

    When trying to define a VO with a SQL query, we use VO bind variables on the 'OrderBy' clause and set Groovy expression to populate values at runtime. Based on simple experiments, ADF throws out an exception saying that "Attempt to set a parameter name that does not occur in the SQL'. I am wondering if it is allowed to use bind variables in a 'OrderBy' clause? I know it does in 'Where' clause.

    Thanks a lot for your answers.

    Regards,
    David

    DB:2.94:Adf Vo Bind Variable Does Not Work In 'Orderby' Clause dc

    So is there any change of the BC framework being corrected to allow this, since it is legal syntax, as in the following sql*plus example?

    select last_name, decode(1, 'SA_REP', nvl(commission_pct,0), 'SA_MAN', nvl(commission_pct,0), nvl(salary,0))
    from hr.employees
    where job_id = 1
    order by decode (1, 'SA_REP', nvl(commission_pct,0), 'SA_MAN', nvl(commission_pct,0), nvl(salary,0))
    /

  • RELEVANCY SCORE 2.94

    DB:2.94:Create View Using ;With Clause dm


    Hi
    I would like to create a view using ';with' clause. I wanted to find a running total of values. I got the sql from one of my other posts and I wanted to implement it in a view. I am newbie in SQL server, I tried to create a view and it is throws me an error
    saying Incorrect syntax near ';'
    Thanks in advance.

    DB:2.94:Create View Using ;With Clause dm

    thanks for the post. It was the semicolon which was causing issue

  • RELEVANCY SCORE 2.94

    DB:2.94:Extending Connect By Clause In Tree Component 88


    Hi guys,

    I am afraid I know the answer to this one but I'll ask anyway.

    The build-in tree component only lets you enter the basic sql-statement. It "handles" the connect by clause. Now that is all fine and dandy, but some of our connect by clauses are a little bit more complex. So I would like to add to the connect by clause and the start with clause.
    Alternatively, allow us to write the complete sql-statement
    Also there is no way now to utilise the 10g features.

    I guess an enhancement request for Version 2 please?

    DB:2.94:Extending Connect By Clause In Tree Component 88

    Hi guys,

    I am afraid I know the answer to this one but I'll ask anyway.

    The build-in tree component only lets you enter the basic sql-statement. It "handles" the connect by clause. Now that is all fine and dandy, but some of our connect by clauses are a little bit more complex. So I would like to add to the connect by clause and the start with clause.
    Alternatively, allow us to write the complete sql-statement
    Also there is no way now to utilise the 10g features.

    I guess an enhancement request for Version 2 please?

  • RELEVANCY SCORE 2.94

    DB:2.94:Sybase Subscribe By Equivalent In Sql Server k1


    I have a sybase publication like this.Create Publication pub1 (TABLE Customer csubscribe by select e.employeeid from Employee e where e.employeeid=c.employeeid)From Help.SUBSCRIBE BY clause    In SQL Remote, one way of defining a subset of rows of a table to be included in an article is to use a SUBSCRIBE BY clause. This clause allows many different subscribers to receive different rows from a table in a single publication definition.Is this equivalent to a filter row in sql server?Thanks in advance,

    DB:2.94:Sybase Subscribe By Equivalent In Sql Server k1

    With transactional replication filtering is done by the log reader agent. It uses filtering stored procedures with a prefix of FLTR. You will find these in the publication database. Now the log reader agent runs on the publisher and it will use the results of a call to HOST_NAME() executed in the publication database on the publisher. This will always return the name of the publisher. With merge replication it will evaluate correctly. I was incorrect earlier. looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X

    looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

  • RELEVANCY SCORE 2.94

    DB:2.94:How To Dynamically Add Where Clause To An Sql Object From Peoplecode fs


    Hi,
    Can anyone please assist me how the below situation can be handled?

    I have an SQL object like one below
    Z_SEL_JRNL
    ---------------------------------
    Select
    A.JOURNAL_ID
    FROM PS_JRNL_LN A
    WHERE A.BUSINESS_UNIT = :1
    ---------------------------------
    I have a where clause string in PeopleCode Str = " AND A.JRNL_DATE = '24-MAR-2010'" [This where clause is just an example; it might change as required]

    while opening the SQL object from PeopleCode how do I pass this where clause string to the SQL object.

    for the bind variable :1 following can be the code; but
    SQL_Obj = GETSQL(sql.Z_SEL_JRNL,BU);

    Regards,
    Pratheepan

    DB:2.94:How To Dynamically Add Where Clause To An Sql Object From Peoplecode fs

    Have considered fetchsql
    SQLSTR = FetchSQL(SQL.ABC_XY);

    add clause to SQLSTR and
    do exec on SQLSTR.

    Subhash

  • RELEVANCY SCORE 2.94

    DB:2.94:Query Where Clause Dynamic Binding?????? 8s


    We are facing some difficulties in setting where claues.

    We have built a viewObject based on an entity.
    we want to set the where clause of the sql query used in the view constructor.

    we have set the where clause in the constructor of the viewObjectImpl.

    public ITEMVIEWImpl() {
    this.setWhereClause(" ITEM_CODE BETWEEN 'FAB' AND 'FAB80000' ");
    }

    it is working fine.

    But How to set the parametered queries.

    can we say like this ?

    this.setWhereClause(" ITEM_CODE BETWEEN ? AND ? ");

    it gives en error when the corresponding JSP is run in the Browser.

    if possible , how to assign value to the ? bind variables...
    we have also found some methods in the viewObjectImpl class ,like setwhereClauseParam();

    How to make use of the setWhereClauseParam() method?

    If I am getting a range (above example query) from a separate JSP/HTML,
    How to pass this values to the view's where clause bind parameter?

    please suggest us.

    Advance thanks
    null

    DB:2.94:Query Where Clause Dynamic Binding?????? 8s

    Originally posted by Blaise - JDeveloper product group:In your case you would do:

    this.setWhereClauseParam(0, "FAB");
    this.setWhereClauseParam(1, "FAB80000");

    Archana :
    I tried this its not working. I used setWhereClauseParam() in the View O's IMPL java file. Am missing out something ? Where should I place this code.

    Thanks !

  • RELEVANCY SCORE 2.93

    DB:2.93:Date_Popup 77


    When I use htmldb_item.date_popup in SQL report with an order by clause,

    the data that I select to replace the current row goes to another row instead.

    This does not happen if I remove the order by clause.

    Has anyone encountered this ? How to work around it ?

    DB:2.93:Date_Popup 77

    I can see this working for presenting data wheb the page loads but without giving the p_row parameter, isn't the date_popup api pretty useless as the array can not be processed when the page is submitted?

    Phil

    p_row
    This parameter is deprecated. Anything specified for this value will be ignored

  • RELEVANCY SCORE 2.93

    DB:2.93:Using Ssis Variables In A Sql Task a1


    Hello all.
     
    I am not well versed in creating SSIS packages and I am running into an issue where I use a sql task as a result set to assign a variable a value. I then, in a later sql task would like to use that variable in the Where clause but using @[User:QL] doesn't seem to work. Is there a way to do this?

    DB:2.93:Using Ssis Variables In A Sql Task a1

    Ah, I believe I know what your issue is.
     
    You can't use the SSIS variable inside the SQL statement.  The SQL statement specified in your Execute SQL Task is passed to the server - and the server has no idea what @User:QL is.
     
    Don't fret though, you just have to set up your statement and Task properly.  Instead of putting your SSIS variable name in the statement, use ? instead.  Then you have to go to the Parameters tab of the Execute SQL Task, and set parameter 0 to be your SSIS variable.

  • RELEVANCY SCORE 2.93

    DB:2.93:How Can I Set A Variable Number Of Values In A Sql In Clause? 91


    Hi,

    How can I set a variable number of values in a SQL IN clause without having to change the text of the SQL statement each time?
    I read the link http://radio.weblogs.com/0118231/2003/06/18.html. as steve wrote.

    SELECT *
    FROM EMP
    WHERE ENAME IN (?)

    But we need the steps not to create type in the system and would there be any other solution if we would like to use variable number of values in a SQL IN clause ?
    We are using JDeveloper 10.1.3.2 with Oracle Database 10.1.3.2

    Thanks
    Raj

    DB:2.93:How Can I Set A Variable Number Of Values In A Sql In Clause? 91

    Hi,

    can you please explain why the solution from steve is not the right solution for you.

    regards
    Peter

  • RELEVANCY SCORE 2.93

    DB:2.93:Bulk Collect Using Pl/Sql Table In Where Clause cx


    Hi All,

    Oracle 11.2 on Linux.

    I want to do SELECT... BULK COLLECT INTO, but also use pl/sql table in WHERE clause. Something like,
    DECLARE
    TYPE emp_table IS TABLE OF employee%ROWTYPE ;
    e_tab emp_table ;
    TYPE emp_id_list IS TABLE OF NUMBER(10) INDEX BY BINARY_INTEGER ;
    t_id emp_id_list ;
    BEGIN

    SELECT *
    BULK COLLECT INTO e_tab
    FROM employee
    WHERE emp_id = t_id (i) ; -- This is the problem

    END ;Can I use pl/sql table in WHERE clause? like we do it in FORALL...INSERT ( or upadet or delete ).
    How ?

    Thanks in advance

    DB:2.93:Bulk Collect Using Pl/Sql Table In Where Clause cx

    WHERE emp_id = t_id (i) ; -- This is the problemThe reason that there is a problem is because 'i' isn't defined anywhere and even if it was you defined
    t_id emp_id_list ;But you haven't populated the 't_id' variable with any data.
    Dipali showed you one way to put some values in 't_id' t_id(1) := 1;
    t_id(2):= 2;and how to loop through them so your code works.

  • RELEVANCY SCORE 2.93

    DB:2.93:Using The % Character With A Like Clause In Odp.Net p7


    When i use the special character %, i get an invalid character error in my C# program. But if the copy the exact same call into sql+, it works fine. here is a same call that i would use:
    DELETE FROM table ownder.table WHERE USERID LIKE 'QAL5%';

    Any reason why this errors out?

    DB:2.93:Using The % Character With A Like Clause In Odp.Net p7

    ? is not how Oracle marks bind variables. Try something like;

    OracleCommand cmd = new OracleCommand("select * from emp_info where ename like '%:1%'", conn);

  • RELEVANCY SCORE 2.93

    DB:2.93:Using The "In" Expression In Sql Statement ak



    Hi,

    I have an array with multiple values stored in it. I would like to use these values in the filter expression of another query using the "IN" clause (data is coming from a sql dataabse). Is there a way to achieve this, I did not see an option in the std query template for using the "IN" expression.

    Thanks.

    DB:2.93:Using The "In" Expression In Sql Statement ak


    Thanks for blindly correcting your earlier post...

  • RELEVANCY SCORE 2.93

    DB:2.93:Mysql Limit Clause Equivalent In Oracle Statement 73


    Is there an Oracle SQL statement that would be equivalent to the MySQL LIMIT clause used to return only part of a result set?

    DB:2.93:Mysql Limit Clause Equivalent In Oracle Statement 73

    CJ I am so grateful to you.. your suggestion helped me alot...

    Thanx

  • RELEVANCY SCORE 2.93

    DB:2.93:Index Usage From Sql Query In Oracle Forms p8


    Hello:

    Using LIKE/OR in where clause (of an indexed column) will force the the query to NOT use INDEX? We have these where clause in Oracle Forms Records Group.

    Below are two examples...
    1. If we have a where clause with LIKE would that NOT use the index?
    Example: ColumnName like :block.Column||%

    2. How about having an OR clause?
    Example: and (ColumnName = :block.column or :block.column is null)

    Thanks

    DB:2.93:Index Usage From Sql Query In Oracle Forms p8

    Even Like should use index. But depends on how many characters you're giving before % sign. if it has only one or two chars e.g. ab%, then optimizer may scan entire table instead of checking this value...

  • RELEVANCY SCORE 2.93

    DB:2.93:Multiple Database Connections With At Clause fp


    Hello,

    I'm working on an application using the Pro*C/C++ precompiler for Oracle 9i. This app has to establish multiple connections to the same database, which I can do using the follwing statement repeatedly with different values in user, pwd and conn :

    EXEC SQL CONNECT :user IDENTIFIED BY :pwd AT :conn USING :dbname;

    I know that I have to use the AT clause in subsequent SQL statements to identify the connection to use, like in:

    EXEC SQL AT :conn SELECT ....

    This works fine and I'm happy with that. But unfortunately there are a few statements which do not allow an AT clause! For example allocating a descriptor for ANSI dynamic SQL method 4:

    EXEC SQL allocate descriptor :descin with max 256;

    When I add an AT clause, the precompiler gives the error message
    "PCC-02386 Use of the AT clause is not permitted here". When I leave it away, I get a runtime error "ORA-01012 not logged on".

    Does anybody know what I'm doing wrong? Do I have to establish an
    additional default (unnamed) connection just for the descriptor
    allocation? Will this work together with the subsequent SQL statements on a named connection, using the descriptor from the unnamed one? I fear problems concerning transaction handling.

    Any help will be greatly appreciated!

    Best wishes,
    Joerg

    DB:2.93:Multiple Database Connections With At Clause fp

    Hello,

    I'm working on an application using the Pro*C/C++ precompiler for Oracle 9i. This app has to establish multiple connections to the same database, which I can do using the follwing statement repeatedly with different values in user, pwd and conn :

    EXEC SQL CONNECT :user IDENTIFIED BY :pwd AT :conn USING :dbname;

    I know that I have to use the AT clause in subsequent SQL statements to identify the connection to use, like in:

    EXEC SQL AT :conn SELECT ....

    This works fine and I'm happy with that. But unfortunately there are a few statements which do not allow an AT clause! For example allocating a descriptor for ANSI dynamic SQL method 4:

    EXEC SQL allocate descriptor :descin with max 256;

    When I add an AT clause, the precompiler gives the error message
    "PCC-02386 Use of the AT clause is not permitted here". When I leave it away, I get a runtime error "ORA-01012 not logged on".

    Does anybody know what I'm doing wrong? Do I have to establish an
    additional default (unnamed) connection just for the descriptor
    allocation? Will this work together with the subsequent SQL statements on a named connection, using the descriptor from the unnamed one? I fear problems concerning transaction handling.

    Any help will be greatly appreciated!

    Best wishes,
    Joerg

  • RELEVANCY SCORE 2.92

    DB:2.92:Generate A Sql "In" Clause Using Aldsp 8p


    Problem Summary---------------------------------------------------ALDSP: Generate a SQL "IN" clauseProblem Description---------------------------------------------------I would like to know if there is a possibility of generating an SQL "IN" clause using ALDSP.I would need the XQuery construct to create an SQLsomething like-select * from emp where dept_no in ('101', '201', '301');The values for dept_no would be passed at runtime.(Or)Will be I able to create a physical data service using the SQL - select * from emp where dept_no in ?If yes, how do I map the parameter to "?"Thanks.

    DB:2.92:Generate A Sql "In" Clause Using Aldsp 8p

    ODI calls what you are doing a parameter-passing-clustered-indexed join. ODI uses multiple OR's instead of an "IN" to do that. It does not rely on the number of ids being fixed. It's all explained in that section.

  • RELEVANCY SCORE 2.92

    DB:2.92:Adding A Where Clause To An Update Strategy p9



    Hi,I am trying to add a where clause to an Update Strategy in one of my mappings, but cannot figure out how to do it correctly. Below is the type of update I am trying to do:Update table_name

    Set column1 = Adding a where clause to an Update Strategy

    Where column2 between Adding a where clause to an Update Strategy and Adding a where clause to an Update StrategyThe three values in the update statement are passed through the mapping to the Update Strategy Transformation. Specifying the rows in the Target to update by the logic in the where clause it where I am stuck. It seems like this should be easy to do, however I cannot figure out how to specify it in Informatica. BTW, I am using PowerCenter 7.1.3 with a SQL Server database.Any help would be greatly appreciated.Thanks,

    Ryan

    DB:2.92:Adding A Where Clause To An Update Strategy p9


    By default PowerCenter uses the primary key defined on the target to construct its update strategy. It's of the formUPDATE TARGET_TABLE SET FIELD1 = :TU.FIELD1 WHERE PK_FIELD = :TU.PK_FIELDPowerCenter includes connected primary key fields in the where clause and connected non-primary key fields in the update clause.If you want to customize this open your mapping, edit the target. On the properties tab edit update override. Click generate SQL to see the default update statement. Then override it with something like:Update table_nameSet column1 = :TU.column1Where column2 between :TU.min_value and :TU.max_value

    The places in the statement where you see :TU. followed by a field name represent placeholders for the actual values that come through when the session runs.

  • RELEVANCY SCORE 2.92

    DB:2.92:How To Define An Object 9a


    Hi all,I am trying to all 2 new tables into my universe. The 2 tables are joined by the column1 and column2. I want a condition, say if table1.column1=table2.column2, for the newely added objects from these 2 tables. So, I have written the satement in the where clause of the object. But if I use the objects as report objects the SQL has where clause twice It looks like select column3 from table1 where table1.column1=table2.column2(table1.column1=table2.column2). Nevertheless, if I remove the where clause from the object definition the the SQL in the query panel has no where clause. The SQL just looks like select column3 from table1Regards

    DB:2.92:How To Define An Object 9a

    Hi Sushil,Thankyou it really helpedEdited by: Ash Pitt on Jul 24, 2008 1:15 AM

  • RELEVANCY SCORE 2.92

    DB:2.92:Can A Sql With Clause Be Used In Materialized View a1


    Hello,

    Can we use SQL WITH clause in Materialized View.

    Thanks

    DB:2.92:Can A Sql With Clause Be Used In Materialized View a1

    You could have found it out yourself. All you would have to do is to just try as OrionNet did.

    Thanks,
    Karthick.

  • RELEVANCY SCORE 2.91

    DB:2.91:Form Variables In An If Clause jf


    hi again,

    how can i use form variables in an if clause?
    if i just want to display the variable i write:
    %= request.getParameter("variable") %

    but how can i use this in my if clause. lets say in this if clause:

    % if ( variable 50 ) { %
    blabla
    % } %

    plz give me the syntax for that

    thx

    pumpindave

    DB:2.91:Form Variables In An If Clause jf

    sorry, i have forgotten ''

    % String strVariable = request.getParameter("variable");
    if (Integer.valueOf(strVariable).intValue() 50) { %
    blabla
    % } %

    Badr.

  • RELEVANCY SCORE 2.91

    DB:2.91:Sql Server - Concat Rows Into 1 String And Use It As The Where Clause For An Insert Query 97


    I got the following code (it iterates over rows in a table and creates a Where Clause by concatenating all rows into 1 string). Works OK, but now I want to use it as part of an Insert query :
    USE ESM
    DECLARE @where_clause as varchar(MAX)
    DECLARE @SQL varchar(8000)
    SET @where_clause = ''
    SELECT @where_clause = @where_clause '[Reference Specs] LIKE ''%' [delimiter] ''' OR '
    FROM [ESM].[dbo].[tblDelimiters]
    SELECT @where_clause=rtrim(SUBSTRING(@where_clause,1,LEN(@where_clause)-3))
    DELETE FROM dbo.tblDelimiters_where_clause
    SELECT @where_clause
    SET @SQL =
    'select *
    from [ESM].[dbo].[tblDoc_Ref_Specs_to_Doc_Controlling_Parts] Where ' @where_clause
    -- CROSS APPLY [ESM].[dbo].DelimitedSplit8K([Reference Specs],''';''') Where ' @where_clause
    EXEC (@SQL)
    The previous code works great. When the last line is ran (Exec (@SQL), the results of the table are returned with the Where clause selecting the correct records. What I need to do is a similar, but, @SQL should contain an Insert query that takes the value
    of @where_clause and inserts it into 1 table. Tried different alternatives, but none work (always get different syntax errors). This is what I would like to do :
    INSERT INTO dbo.tblDelimiters_where_clause @where_clause

    Adrian Hernandez

    DB:2.91:Sql Server - Concat Rows Into 1 String And Use It As The Where Clause For An Insert Query 97

    Thanks. I was missing the Select before the @where_clause. Thank you very much.Adrian Hernandez

  • RELEVANCY SCORE 2.91

    DB:2.91:Multi-Select Lov In A Report ca


    Hello everybody,

    I have a multi-select lov which returns a string like this: 0001:000:003 .

    I want to use the return string in a sql-report in an IN-Clause. I tried it like this: ... where filter in ( chr(39) || replace(:P1_TEST,':',''',''') || chr(39) )

    But it down't work. Has anybody an idea how to use a multi-select lov?

    Thanks.

    Best regards!

    DB:2.91:Multi-Select Lov In A Report ca

    It works, Denes.

    Thanks for your quick reply.

    Best regards!

  • RELEVANCY SCORE 2.91

    DB:2.91:Function/Subrutine And Where Clause k9



    Hi,

    Is it possible to define a subroutine.function and use it in the SQL where clause in ABAP??

    If so, any reference code??

    Regards,

    Kit

    DB:2.91:Function/Subrutine And Where Clause k9


    Hi all,

    How about define a function in database lavel and execute it in ABAP??

    Regards,

    Kit

  • RELEVANCY SCORE 2.90

    DB:2.90:Use Output If A Refcursor In The Where Clause? k7


    Hi
    I have a package with a procedure that has ref cursor as an out parameter. It returns multiple rows (example id). I would like to use this output in the where clause of another query. Is that possible?
    Thx!

  • RELEVANCY SCORE 2.90

    DB:2.90:Add An Attribute To A View Object When A Certain Where Clause Is Added 97


    I am using JDeveloper/ADF 10.1.3.5. I have a read only view object based on a query from a table of names and addresses, which also has a coordinates column, which is an SDO_GEOMETRY - an Oracle Type specific to Oracle Locator (a subset of Oracle Spatial). I have methods in the ViewImpl class that call setWhereClause to add where clauses to the query. One possible where clause uses the Oracle Locator function, SDO_NN, to locate rows where the coordinates are near a set of coordinates provided by the user. So I'd like to have an attribute for the distance from the user's coordinates. This can be determined with the SDO_NN_DISTANCE function.

    Here's the problem: a SQL Query cannot use the SDO_NN_DISTANCE function in the SELECT list, unless a call to SDO_NN is in the WHERE clause. But not all of my queries use that where clause. So I only need the attribute for distance, if the method to add the proximity search to the where clause has been called.

    I'm looking at two possibilities:
    Can I dynamically add the attribute in the method that adds the where clause, perhaps by calling "addDynamicAttribute", or define an attribute associated with the SQL expression, "NULL", but write code to change it's SQL expression to a call to SDO_NN_DISTANCE when appropriate? And can I delete the attribute or change the expression back to "NULL" when the where clause is replaced with one of the other possible clauses?

    Or can I create a base view object and then have two view objects that extend it, with two different versions of the query, one with the select list with SDO_NN_DISTANCE and the where clause with SDO_NN, and one without?

    DB:2.90:Add An Attribute To A View Object When A Certain Where Clause Is Added 97

    I'm surprised that nobody answered this. But I kept plugging and answered it myself.

    The answer is that there IS in fact a setQuery method on VOs that lets you completely replace the original query with a new one. The only caveat is that the query must include SELECT expressions for every Attribute that you have specified as mapping to a SQL expression. So I wrote my base query as a simple select that does not include the call to SDO_NN_DISTANCE - it simply selects NULL as a placekeeper for that expression.

    Then in the method that adds the SDO_NN to the WHERE clause, it calls setQuery, and replaces the entire query with a query that includes SDO_NN_DISTANCE. This could include the additional WHERE clause, but I still add it with setWhereClause as all my methods do. Finally, I wrote a method to reset the query to the default. If you are going to add additional where clauses with setWhereClause or addWhereClause, you need to add:
    this.setFullSqlMode(this.FULLSQL_MODE_AUGMENTATION); before you call setQuery.

  • RELEVANCY SCORE 2.90

    DB:2.90:Adding An Order By Clause In Add_Parameter Syntax 88


    Hi,
    I've a form with the add_parameter clause in it.I use a where_clause currently to specify my condition in the add_parameter clause Can i include an order by clause t order my data in ascending order?

  • RELEVANCY SCORE 2.90

    DB:2.90:How To Write A Java Function For Use In Where Clause In Sql Statement 8x


    Hi,
    Does anyone know a good tutorial on how to write and include a Java class/function into Oracle.
    I'd like to write mathematical function to use in my queries, but the resources available in PL/SQL are very limited.

    Many thanx

    DB:2.90:How To Write A Java Function For Use In Where Clause In Sql Statement 8x

    Pim,
    I see you got an answer in the PL/SQL forum.
    But in case you haven't seen it, perhaps this Web page will help:

    http://www.oracle.com/technology/tech/java/jsp/index.html

    Good Luck,
    Avi.

  • RELEVANCY SCORE 2.89

    DB:2.89:Using Xor Like Condition In Where Clause fm


    Hi,
    I need to use an XOR like condition in my where clause. please suggest some way.
    Regards,
    Ankit

  • RELEVANCY SCORE 2.89

    DB:2.89:Sql Loader When Clause jx


    Hi
    I want to specify conditions in my SQL Loader control file using the "WHEN" clause.
    But i haven't come across any articles specifying the use of WHEN for operators other than "=" or "".
    Does SQL Loader support , and like operators to be specified in the WHEN clause.If so, can somebody help me in achieving the same.
    Thank you in advance.

    DB:2.89:Sql Loader When Clause jx

    No, it's very basic, either = or !=

    What version of oracle are you running? In 9i you could make use of External Tables.

  • RELEVANCY SCORE 2.88

    DB:2.88:Escape Characters Using Oracle Jdbc 89


    We use Oracle JDBC driver to do some operations on an Oracle 9i database, and ran into some problems with escape characters. Basically we'd like to escape the _ and % characters.

    The following two example statements both work:
    ResultSet rs = stmt.executeQuery("select * from identifier_protein where upper(IDENTIFIER_ACCNO) like 'NM\\_%' escape '\\' ");

    ResultSet rs = stmt.executeQuery("select * from identifier_protein where upper(IDENTIFIER_ACCNO) like 'NM\\_%' {escape '\\' }");

    However, when we have multiple query terms and the "escape" clause doesn't immediately follows EACH "like" clause, we got errors saying the sql statement does not end properly. One such example is the following:
    ResultSet rs = stmt.executeQuery("select * from identifier_protein where upper(IDENTIFIER_ACCNO) like 'NM\\_%' and creator = 'ABC' {escape '\\'} ");

    If we put an "escape" clause following each "like" clause, then it works.

    My question is, is there a smart way of letting JDBC knows that you want to use an escape character everywhere in the query? We often do very complicated dynamic queries, frequently with table joins and boolean logic and subqueries with tons of query terms. Trying to add the escape clause to each "like" clause is very painful. Any help is highly appreciated. Thanks!

    BL

    DB:2.88:Escape Characters Using Oracle Jdbc 89

    I'd use PreparedStatements, if you're not already. Let the JDBC driver escape things properly for you. That's what the setString() and setDate() methods are all about.

    %

  • RELEVANCY SCORE 2.88

    DB:2.88:Sqr And Begin-Select Where Clause Functions fc


    Hi all - I have a simple question but can't find an answer.

    I have a Begin-Select in an SQR and in the where clause I need to select on a certain year WITHIN a date field. In normal Oracle SQL I'd just use a To_Char() or other function to do that. SQR does not seem to take funtions in the Where clause. Is there a way around this?

    Thanks much --- Jeff

    DB:2.88:Sqr And Begin-Select Where Clause Functions fc

    Yep. Just about anything you can code into SQL Worksheet you can code into SQR (though I'm not sure how true that is of DDL statements).

    Now when you get into really advanced stuff, you're going to have some hit and miss situations. All of the standard Oracle functions should work, though.

    Paul

  • RELEVANCY SCORE 2.87

    DB:2.87:Comma Seperated List In Where Clause Of A Rule ps



    Hi everybody,I saw a rule which had a comman seperated List in the where clause. Now I was wondering what the author wants to tell us? Can this be treated like an AND? Can I use commas instead of AND in the where clause?ThanksMartin

    DB:2.87:Comma Seperated List In Where Clause Of A Rule ps


    Yes, this is part of the old syntax which still works. quot;ANDquot; can be substitued by comma.Jens

  • RELEVANCY SCORE 2.87

    DB:2.87:Is It Possible To Have Variant Condition Clause In Procedure? 8f


    i want to use OLEDB to build a COM for my app
    in the case, i want to execute a select statement which the where-clause is variant.
    ex,
    select * from db1 where code='abc'
    select * from db1 where name='mike'
    As it's very difficult to change sql-command in oledb, i want to build a procedure like this,
    create procedure viewDB  @filter CHAR(20)
    as
      select * from db1 where @filter
    go
    but failed!
    i tried EXEC(select), but i cant get the variants when building a oledb consumer

    DB:2.87:Is It Possible To Have Variant Condition Clause In Procedure? 8f

    Yes, it is safe if you do not do anything else inside if this SP. Just small suggestion. If you can, select just the fields you need and avoid using *. It will impove performance

  • RELEVANCY SCORE 2.87

    DB:2.87:Sql : Urgent Pls dd


    Hi

    Can we use ORDER BY Clause in a Query using set operators like UNION, UNION ALL, INTERSECT and MINUS?

    When I tried to join tables using UNION with order by clause in both the queries, I get this error ....

    SQL command not properly ended.

    But the same, when I execute without order by clause in both the queries it's working fine!

    Can any one tell me wether its possible to use order by in a query using set operators(UNION)?

    Thnaks in Advance.

  • RELEVANCY SCORE 2.87

    DB:2.87:Implementing In Operator With Array Binding j9


    Is there a way to have an SQL statement with a simple WHERE clause like "WHERE my_col = :p1" or "WHERE my_col IN (:p1)" and bind an array to p1?

    My goal is to avoid blowing up of the command text like "WHERE my_col IN (:p1, :p2, :p3 ...)". In my case the array containing the params could contain several hundreds of items, so the where clause may become quite long...

    Thanks,
    Daniel

    DB:2.87:Implementing In Operator With Array Binding j9

    Its nasty and involves a stored procedure

    http://asktom.oracle.com/pls/ask/f?p=4950:8:16751067169065885101::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:110612348061

  • RELEVANCY SCORE 2.87

    DB:2.87:Using Insert With Returning Clause 1f


    Hi, can anyone give me an example of how to use the insert statement with a returning clause in C#, using ODP, please. I've got the SQL syntax, its how to code it up in C# that's puzzling me.
    Thanks
    Steve

  • RELEVANCY SCORE 2.87

    DB:2.87:Lookup Sql Override Problem sz



    Hi, I want to use an input port say prev_dt(which is coming from expression transformation) in my sql override where(like SMRY_DTE = PREV_DT) clause. I have gone through ur steps in the forum ,but cannot figure it out properly. Please help me.

    DB:2.87:Lookup Sql Override Problem sz


    You can't refer to an input port in a sql override. You would add a condition on the condition tab saying smry_dte = prev_dt. Then regenerate your sql override and modify as necessary.On 9/7/2004 1:05:00 AM, Anonymous wrote:Hi,

    br

    I want to use an input portsay prev_dt(which is comingfrom expressiontransformation) in my sqloverride where(like SMRY_DTE =PREV_DT) clause.

    br

    I have gone through ur stepsin the forum ,but cannotfigure it out properly.

    br

    Please help me.

  • RELEVANCY SCORE 2.87

    DB:2.87:Replace Method To Create Sql Alias In Where Clause fp


    I am trying to modify the SQL below by adding an alias to the join clause:

    DB:2.87:Replace Method To Create Sql Alias In Where Clause fp

    Regex.Replace strings allow very little other than substitution characters in the string. Since the same expression is to be substituted differently on the first match than on subsequent matches, a single Replace string must replace the entire string. Since you have a potentially indeterminate number of matches after the first, a single Replace string cannot work. There are numerous ways to solve the problem. I chose going through the string twice, replacing the first match on the first pass, and replacing all the others on the second. The code sample worked on your example. Obviously, for a generalized procedure to work on other database names, the database name must be an argument.Personally, I think this problem could be more quickly and easily solved using replacement functionality offered by the string class and that's the way I recommend handling it.Ed McElroy

    string TestStr = [CustomerDim] ON [p21_view_sales_fact].[CustomerID]=
    + [CustomerDim].[CustomerID] AND [p21_view_sales_fact].[companyid]
    + =[CustomerDim].[Company_ID];

    string FirstPatternStr = @(\[CustomerDim\]);
    string FirstReplaceStr = @$1 blah;
    string SecondPatternStr = @\[CustomerDim\](?!.*blah.*);string SecondReplaceStr = blah;

    Regex FirstRegex = new Regex(FirstPatternStr, RegexOptions.IgnoreCase);
    Regex SecondRegex = new Regex(SecondPatternStr, RegexOptions.IgnoreCase);
    string ReplaceFirstResultStr = FirstRegex.Replace(TestStr, FirstReplaceStr, 1);
    string ResultStr = SecondRegex.Replace(ReplaceFirstResultStr, SecondReplaceStr);

    Console.WriteLine(ResultStr);

  • RELEVANCY SCORE 2.87

    DB:2.87:Does Group By Clause Guarantee That Returned Rows Are Sorted By The Grouped Columns? jf


    I know that when I use GROUP BY clause in a SELECT statement, the returned rows are always sorted by the columns stated in the GROUP BY clause. But, I wonder that this is a guaranteed and documented feature of the GROUP BY clause. Or, do I have to use ORDER BY clause, that consumes additional system resorces, to guarantee the returned rows sorted by the columns stated in the GROUP BY clause.
    I have searched some documents about the topic but, I couldn't find anything. On the contrary, in the SQL Reference document , in the order_by_clause explanation of the SELECT statement, it stated that "Without an order_by_clause, no guarantee exists that the same query executed more than once will retreive rows in the same order."
    Thanks for your help, in advance.

    DB:2.87:Does Group By Clause Guarantee That Returned Rows Are Sorted By The Grouped Columns? jf

    Andrew:

    It is a perfectly lovely word, and after finding the definition, I will definitely add it to my list of favourite pedantic euphemisms :-).

    Eschew Obfuscation
    John

  • RELEVANCY SCORE 2.87

    DB:2.87:Case Statement In Where Clause 8k


    Hi I have a question, how can I use case statement in where clause? I have multiple conditions in my where clause...how can i remove one of my multiple conditions using a case statement?

    Select * from myTable, yourTable where
    myTable.id = yourTable.id and myTable.date = 'YYYYMMDD'
    and yourTable.name like '%David%' and
    (case when myTable.date = yourTable.iDate then myTable.stdate = yourTable.dtDate else '' end)

    Is the sql statement above possible? Thanks. Appreciate your help.

    DB:2.87:Case Statement In Where Clause 8k

    Hi,

    CASE expressions don't help in WHERE clauses nearly as much as you might think.

    Try something like this:
    Select *
    from myTable
    , yourTable
    where myTable.id= yourTable.id
    and myTable.xdate = 'YYYYMMDD'
    and yourTable.name like '%David%'
    and (LNNVL (myTable.xdate = yourTable.iDate)
    ORmyTable.stdate = yourTable.dtDate
    )
    ;I hope that solves your problem.
    If not, explain what you want to do. For example: "If x then condition y should apply, but if x is not TRUE, then condition z should apply instead of y:.
    Post a little sample data (CREATE TABLE table and INSERT statements for both tables) and the results you want from that data.
    It never hurts to say what version of Oracle you're using.

  • RELEVANCY SCORE 2.87

    DB:2.87:Question Ask A New Question Ms Access Vba: I Cant Get Where Like Clause (Sql) To Work, And Cant Thus Get One Fields Choice To Change Rowsource Of Downstream Field p3


    Background:
    * I'm using Access 2010, VBA, and SQL linked to a SQL Server database.
    * I want the choice made in an upstream field Clustered_Or_Standalone drop-down to change the choices available in the downstream field's
    Clusters_Nr_Needed drop-down Rowsource.
    * I have changed the Clustered_Or_Standalone After Update event to issue a SQL query that is to update the Rowsource for Clusters_Nr_Needed using a WHERE LIKE clause that has the
    Clustered_Or_Standalone field's choice.

    Problem:
    * I can't get the WHERE LIKE clause to work using the '%' or '*' wildcard.

    DB:2.87:Question Ask A New Question Ms Access Vba: I Cant Get Where Like Clause (Sql) To Work, And Cant Thus Get One Fields Choice To Change Rowsource Of Downstream Field p3

    Hi Peter,
    You can include the Primary key field in the recordsource but do not need to have it visible on your form...Brian, ProcessIT- Hawke's Bay, New Zealand

  • RELEVANCY SCORE 2.87

    DB:2.87:Dql: Not Able To Use 'Union' Clause In 'In' Clause 3d



    I am trying to use the 'UNION' clause in the 'IN' clause for a Select Query. But it seems that UNION clause does not work with 'IN'.

    If any one has any idea, please share.

    Following is just an example what I am trying to do.

    select r_object_id,object_name from cutom_type where r_object_id IN

    (select r_object_id from cutom_type where object_name like '%TestObject%'

    UNION

    select r_object_id from cutom_type where object_name like '%MyTest%')

    DB:2.87:Dql: Not Able To Use 'Union' Clause In 'In' Clause 3d


    Hi,

    repeating attributes are tricky, but not impossible to handle in a proper way. Have you considered to use ENABLE (ROW_BASED)?

    Maybe you can post the query as you would like to have it, kind of difficult to understand the logical completely

    At the end of day, you can always consider to create a view at db level to query exactly the way you want, register this view in Documentum, and use DQL to query that view

    Regards

    Jrg

  • RELEVANCY SCORE 2.86

    DB:2.86:Sql Pl\Sql f1


    Hy everybody,

    I call a function that returns me information by an open_cursor. But, Is it possible to use the open_cursor and integrate it in a where clause. For example, if I have an open cursor which contains all department that a worker is related to and in my condition I write a select with a where clause like " where dept_name = open_cursor. Open_cursor is equal to dept1, dept2.

    Thank you Bye.

    DB:2.86:Sql Pl\Sql f1

    you should look at Pipeline Function.

    go to http://asktom.oracle.com
    and search for pipeline function you will get many examples.

  • RELEVANCY SCORE 2.86

    DB:2.86:Using Proc Sql To Delete Rows With A Where And Having Clause 9p



    What I'd like to work is this:

    proc sql;

    delete from odbclib.cp_tariff_dim

    where tariff_initial = 'NS'

    and tariff_item = '0010100000;

    having min(tariff_dim_key) ne tariff_dim_key

    ; quit;

    This code works perfectly to create a data set as in select * from etc.

    It creates a data set of all the rows I want to delete. But if I run the above I get an "ERROR 22-322: Syntax error...".

    Is there a way to use a 'having' clause with the where in order to delete rows from a SQL Server table?

    DB:2.86:Using Proc Sql To Delete Rows With A Where And Having Clause 9p


    You have a logical problem ACID - Wikipedia, the free encyclopedia as deleting records that could add to the value of the aggregated function.

    That is why you are getting:

    WARNING: This DELETE/INSERT statement recursively references the target table. A consequence of this is a possible data integrity problem.