Please help with this query

Posted on 2009-12-21
Last Modified: 2013-12-18
map table1.column1 if not null  / spaces else table2.column2 if not null/spaces
else ''

column 1 datatype is in char ----it should be in varchar2
column 2 datatypes is in numeric value ------it should be in varchar2


I have some conditons to satisfy for table 1 as well as table 2 and i need to subset the data and
order it ascending

I have written as

Select(case when table1.column1 is not null and table1.column1 <> ''
then cast( table1.column1 as varchar2(25)) when table2.column2 is not null and
table2.column2  <> '' then cast(table2.column2 as varchar2(25)) else '' end) from table1 where table1.column4 = 018 and table1. column6 between '     ' and  '    ' order by table1.column4, table1.column6 , table2 where table2.column4 = 018 and table2.column6 between ' ' and  ' ' order by table2. column4, table2.colum8

but i am getting error as query end is not correct

Question by:aruku
    LVL 29

    Expert Comment

    You query is malformed.

    -- You have this:
      SELECT   (CASE
                    WHEN Table1.Column1 IS NOT NULL AND Table1.Column1 <> ''
                        CAST (Table1.Column1 AS VARCHAR2 (25))
                    WHEN Table2.Column2 IS NOT NULL AND Table2.Column2 <> ''
                        CAST (Table2.Column2 AS VARCHAR2 (25))
        FROM   Table1
       WHERE   Table1.Column4 = 018 AND Table1.Column6 BETWEEN '     ' AND '    '
    ORDER BY   Table1.Column4, Table1.Column6
    -- Followed by this???:
           ,   Table2
       WHERE   Table2.Column4 = 018 AND Table2.Column6 BETWEEN ' ' AND ' '
    ORDER BY   Table2.Column4, Table2.Colum8

    Open in new window

    LVL 15

    Accepted Solution

    In query please correct space between table and column6 "table1. column6" and "order by" and "Where" clause comes two time.

    Author Comment

    Please help me write this query .......Do i need to use outer join here or how can i specify the conditions for tables
    LVL 29

    Expert Comment


    We would gladly try and help you but unfortunately:

    1) The requirements are not clear
    2) The table definitions are not posted
    3) Sample data is unavailable
    4) Expected result are not given
    LVL 31

    Expert Comment

    Can you provide the table structure and sample data for table1, column1, column2,
    and column6 and the structure and sample data for table2, column2, column4, and
    column8, and what you want as the output?

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    SQL PIVOT Table 21 32
    Oracle SQL Select Statement 19 46
    PL/SQL Two statements 6 34
    MS Access Add A Yes/No Box to Each Record 5 34
    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
    This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

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

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

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now