Home » SQL & PL/SQL » SQL & PL/SQL » Query Error (TOAD 8.5.3.4)
Query Error [message #580257] Fri, 22 March 2013 01:36 Go to next message
bhawnakaamra
Messages: 66
Registered: March 2013
Location: delhi
Member
I have a query ,and i am getting error of
"expression must have same datatype as corresponding expression".
in that line

and when i do comment to two lines named
--'update date' AS last_update_date 
SELECT --BBM.Last_update_date ,

like this.then it is giving me output.
But when i am running it with Last_update_date column ,it is not giving me the output.

Please help me in resolving the query.

SELECT '00' AS COMPONENT_ITEM_ID,
       'Item Code' AS CICODE,
       'Item Name' AS CINAME,
       'Unit' AS CUNIT,
       'Comp Qty' AS COMPONENT_QUANTITY,
       'Category' AS CF_CATEGORY,
       'update date' AS last_update_date
  FROM DUAL
 WHERE (SELECT COUNT (*)
          FROM (SELECT BIC.COMPONENT_ITEM_ID
                  FROM BOM_BILL_OF_MATERIALS BBM,
                       BOM_INVENTORY_COMPONENTS BIC,
                       MTL_SYSTEM_ITEMS_vl MSI1
                 WHERE     BBM.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
                       AND BBM.ORGANIZATION_ID = MSI1.ORGANIZATION_ID
                       AND bbm.organization_id = :P_ORG_ID
                       AND BBM.ASSEMBLY_ITEM_ID = :COMPONENT_ITEM_ID)) > 0
UNION ALL
SELECT BBM.Last_update_date,
       TO_CHAR (BIC.COMPONENT_ITEM_ID),
       MSI1.SEGMENT1 CICODE,
       MSI1.DESCRIPTION AS CINAME,
       MSI1.PRIMARY_UNIT_OF_MEASURE AS CUNIT,
       TO_CHAR (BIC.COMPONENT_QUANTITY),
       (SELECT B.DESCRIPTION
          FROM MTL_ITEM_CATEGORIES_V A, FND_FLEX_VALUES_VL B
         WHERE B.FLEX_VALUE =
                  (SUBSTR (
                      CATEGORY_CONCAT_SEGS,
                      1,
                      (LENGTH (CATEGORY_CONCAT_SEGS)
                       - LENGTH (
                            SUBSTR (CATEGORY_CONCAT_SEGS,
                                    INSTR (CATEGORY_CONCAT_SEGS, '.'))))))
               AND B.FLEX_VALUE_SET_ID = '1013624'
               AND A.category_set_name = 'Inventory Item Catagory'
               AND A.category_set_id = 1
               AND A.ORGANIZATION_ID = :P_ORG_ID
               AND A.INVENTORY_ITEM_ID LIKE BIC.COMPONENT_ITEM_ID)
          AS CF_CATEGORY
  FROM BOM_BILL_OF_MATERIALS BBM,
       BOM_INVENTORY_COMPONENTS BIC,
       MTL_SYSTEM_ITEMS_vl MSI1
 WHERE     BBM.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
       AND MSI1.INVENTORY_ITEM_ID = BIC.COMPONENT_ITEM_ID
       AND BBM.ORGANIZATION_ID = MSI1.ORGANIZATION_ID
       AND bbm.organization_id = :P_ORG_ID
       AND BBM.ASSEMBLY_ITEM_ID = :COMPONENT_ITEM_ID
       AND BIC.DISABLE_DATE IS NULL

[EDITED by LF: formatted code, applied [code] tags]

[Updated on: Fri, 22 March 2013 01:41] by Moderator

Report message to a moderator

Re: Query Error [message #580259 is a reply to message #580257] Fri, 22 March 2013 01:41 Go to previous messageGo to next message
bhawnakaamra
Messages: 66
Registered: March 2013
Location: delhi
Member
and if i am doing SELECT to_char(BBM.Last_update_date,'dd-mm-yyy')

it is not giving me the date values.

Please help me asap
Re: Query Error [message #580260 is a reply to message #580257] Fri, 22 March 2013 01:42 Go to previous messageGo to next message
Michel Cadot
Messages: 58494
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Use SQL*Plus and copy and paste your session, the WHOLE session.

Regards
Michel
Re: Query Error [message #580261 is a reply to message #580259] Fri, 22 March 2013 01:43 Go to previous messageGo to next message
Michel Cadot
Messages: 58494
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Please help me asap


please post as requested asap.

Regards
Michel
Re: Query Error [message #580262 is a reply to message #580257] Fri, 22 March 2013 01:44 Go to previous messageGo to next message
Michel Cadot
Messages: 58494
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SELECT '00' AS COMPONENT_ITEM_ID,
...
UNION ALL
SELECT BBM.Last_update_date,
...

Both expressions must have the same type.

Regards
Michel
Re: Query Error [message #580263 is a reply to message #580259] Fri, 22 March 2013 01:46 Go to previous messageGo to next message
Littlefoot
Messages: 19299
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
When you use UNION, you have to pay attention to columns you select - they must match. Your query says:
SELECT '00' AS COMPONENT_ITEM_ID,
...
union
SELECT BBM.Last_update_date,
...

The first value is, certainly, a string. The second one looks like a DATE. So, either select some data value in the first SELECT, or select a character in the second one.

What does your next message mean? TO_CHAR, with valid format mask, *does* return a character. You used 'YYY' - did you, perhaps, mean 'YYYY'?

Quote:
it is not giving me the date values
What do you mean by that?

P.S. Forgot to mention: I formatted code in your initial message. Next time, PLEASE, format it yourself. It is very difficult to read unformatted code. I understand that some people don't care about it, but - if you require any kind of assistance - you should make it easier for the rest of the community to read what you wrote. Therefore, once again (as Michel already told you that) - spend 10 seconds of your time and check How to use [code] tags and make your code easier to read.

[Updated on: Fri, 22 March 2013 01:50]

Report message to a moderator

Re: Query Error [message #580284 is a reply to message #580263] Fri, 22 March 2013 04:33 Go to previous messageGo to next message
zssultana
Messages: 3
Registered: March 2013
Junior Member
WHEN YOU ARE USING SET OPERATOR, YOU HAVE TO REMEMBER
1. TOTAL NO. OF COLUMNS MUST BE SAME
2. THEIR DATA TYPES MUST BE SAME

ZAKIA
Re: Query Error [message #580287 is a reply to message #580284] Fri, 22 March 2013 04:37 Go to previous messageGo to next message
Michel Cadot
Messages: 58494
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and do NOT post in UPPER case.
Posting all in UPPER case means shouting.

In addition, this is what we already said, no need you repeat it.

Regards
Michel
Re: Query Error [message #580290 is a reply to message #580287] Fri, 22 March 2013 04:58 Go to previous messageGo to next message
bhawnakaamra
Messages: 66
Registered: March 2013
Location: delhi
Member
Thanks guys..

I am done with my problem...

Smile
Re: Query Error [message #580296 is a reply to message #580290] Fri, 22 March 2013 08:47 Go to previous messageGo to next message
Michel Cadot
Messages: 58494
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So, be nice and explain what was it and how did you succeed for future readers.

Regards
Michel
Re: Query Error [message #580345 is a reply to message #580296] Sat, 23 March 2013 01:54 Go to previous message
bhawnakaamra
Messages: 66
Registered: March 2013
Location: delhi
Member
zssultana is right.

the 1. TOTAL NO. OF COLUMNS MUST BE SAME
2. THEIR DATA TYPES MUST BE SAME

expression must have same datatype as corresponding expression,thats the error i was getting.

SELECT '00'         AS COMPONENT_ITEM_ID, 
       'Item Code'  AS CICODE, 
       'Item Name'  AS CINAME, 
       'Unit'       AS CUNIT, 
       'Comp Qty'   AS COMPONENT_QUANTITY, 
       'Category'   AS CF_CATEGORY, 
       'updatedate' AS disable_date 
FROM   dual 
WHERE  (SELECT Count (*) 
        FROM   (SELECT BIC.component_item_id 
                FROM   bom_bill_of_materials BBM, 
                       bom_inventory_components BIC, 
                       mtl_system_items_vl MSI1 
                WHERE  BBM.bill_sequence_id = BIC.bill_sequence_id 
                       AND BBM.organization_id = MSI1.organization_id 
                       AND bbm.organization_id = :P_ORG_ID 
                       AND BBM.assembly_item_id = :COMPONENT_ITEM_ID)) > 0 
UNION ALL 
SELECT To_char (BIC.component_item_id), 
       MSI1.segment1                                               CICODE, 
       MSI1.description                                            AS CINAME, 
       MSI1.primary_unit_of_measure                                AS CUNIT, 
       To_char (BIC.component_quantity), 
       (SELECT B.description 
        FROM   mtl_item_categories_v A, 
               fnd_flex_values_vl B 
        WHERE  B.flex_value = ( Substr (category_concat_segs, 1, ( 
                                                         Length ( 
                                category_concat_segs) 
                                                         - Length 
                                                         ( 
                                Substr ( 
                                category_concat_segs, Instr ( 
                                category_concat_segs 
                                        , '.'))) )) ) 
               AND B.flex_value_set_id = '1013624' 
               AND A.category_set_name = 'Inventory Item Catagory' 
               AND A.category_set_id = 1 
               AND A.organization_id = :P_ORG_ID 
               AND A.inventory_item_id LIKE BIC.component_item_id) AS 
       CF_CATEGORY, 
       To_char(BIC.disable_date) 
FROM   bom_bill_of_materials BBM, 
       bom_inventory_components BIC, 
       mtl_system_items_vl MSI1 
WHERE  BBM.bill_sequence_id = BIC.bill_sequence_id 
       AND MSI1.inventory_item_id = BIC.component_item_id 
       AND BBM.organization_id = MSI1.organization_id 
       AND bbm.organization_id = :P_ORG_ID 
       AND BBM.assembly_item_id = :COMPONENT_ITEM_ID 
--AND BIC.DISABLE_DATE IS NULL 


that is the right query i solved.the sequence and the data type should be and i was putting disable date in first line where i was giving name of the column and the sequence was wrong when i was trying to fetch the data out of it.

and i was not able to get the disable date coz of my silly mistakei did

where disable date is null.
now i made it in comment..


hope future readers will get benefits ffrom my mistakes..

Thanks
Bhawna Kaamra

[Updated on: Sat, 23 March 2013 01:59]

Report message to a moderator

Previous Topic: SQL to transform a single row into multiple rows.
Next Topic: Complex select query with criteria
Goto Forum:
  


Current Time: Wed Jul 23 03:35:58 CDT 2014

Total time taken to generate the page: 0.14069 seconds