Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> SQL issues - sub query returns multiple results

SQL issues - sub query returns multiple results

From: BookerT <chipw_at_ascac.org>
Date: 18 Jan 2007 12:51:02 -0800
Message-ID: <1169153462.342608.99160@a75g2000cwd.googlegroups.com> 





This will be a long post, but maybe someone with a different set of
eyes can see where my Sql statement addition is causing problems.



I am trying to add the following to a long sql statement:
NVL(( TO_CHAR(( select audit_data.date_inserted from audit_data where
audit_data.related_tbl_name = 'organization'
and audit_data.related_id = ORGANIZATION.organization_id), 'MM/DD/YYYY'
) ),( 'UNKNOWN' )) Date_Org_Created






When I add that to the following statement below I get positive
results:


SELECT



RAWTOHEX( ORGANIZATION.organization_id )
ds_organization_sskey,


TO_CHAR(( SYSDATE ),'MM/DD/YYYY') date_modified,
NVL(( TO_CHAR(( select audit_data.date_inserted from audit_data where
audit_data.related_tbl_name = 'organization'
and audit_data.related_id = ORGANIZATION.organization_id), 'MM/DD/YYYY'
) ),( 'UNKNOWN' )) Date_Org_Created



FROM



ORGANIZATION



LEFT OUTER JOIN ADDRESS ON (ADDRESS.Organization_ID =
ORGANIZATION.ORGANIZATION_ID)


WHERE



ORGANIZATION.obsolete_flag = 0




However, when it is added to this long statement below is when I get
the error message that a sub query returns multiple results: Not sure
why:



SELECT



RAWTOHEX( ORGANIZATION.organization_id )
ds_organization_sskey,


TO_CHAR(( SYSDATE ),'MM/DD/YYYY') date_modified,
NVL(( RAWTOHEX( ORGANIZATION.organization_id )
),( 'UNKNOWN' )) Organization_Id,


NVL(( ORGANIZATION.at_owner_Id ),( 'UNKNOWN' )) AT_Dealer_Id,
NVL(( ( select text_string from lookup where lookup_id =
ORGANIZATION.at_dealer_segmentation) ),( 'UNKNOWN' ))
AT_Dealer_Segmentation,


NVL(( ( select text_string from lookup where lookup_id =
ORGANIZATION.at_busn_partner_type_lkp ) ),( 'UNKNOWN' ))
AT_Dealer_Type_LKP,


NVL(( ORGANIZATION.full_name ),( 'UNKNOWN' )) Full_Name,
NVL(( RAWTOHEX( ORGANIZATION.active_status_lkp )
),( 'UNKNOWN' )) Active_Status_LKP,

NVL(( ORGANIZATION.short_name ),( 'UNKNOWN' )) Short_Name,
NVL(( ORGANIZATION.description ),( 'UNKNOWN' )) Description,
NVL(( ORGANIZATION.locale_id ),( 'UNKNOWN' )) Locale_Id,
NVL(( RAWTOHEX( ORGANIZATION.industry_lkp )


),( 'UNKNOWN' )) Industry_LKP,


NVL(( RAWTOHEX( ORGANIZATION.naics_id )


),( 'UNKNOWN' )) Naics_Id,


NVL(( ORGANIZATION.business_db_num ),( 'UNKNOWN' )) Business_DB_Num,
1 Num_Employees_Band,


1 Revenue_Band,


1 Market_Capital_Band,


NVL(( TRUNC( ORGANIZATION.multi_national_flag ) ),( 0 ))
Multi_National_Flag,


NVL(( ORGANIZATION.fiscal_year_begin ),( 'UNKNOWN' ))
Fiscal_Year_Begin,


NVL(( TRUNC( ORGANIZATION.strategic_account_flag ) ),( 0 ))
Strategic_Account_Flag,


'UNKOWN' Fortune_Class_LKP,

'UNKNOWN' Web_Address,

'UNKNOWN' Num_Sales_Reps_Band,



1 Num_Yrs_In_Business_Band,


'UNKNOWN' Avg_Cost_Of_Sales_Band,

'UNKNOWN' Annual_Sales_Budget_Band,

'UNKNOWN' Brand_Recognition_LKP,



NVL(( ( select full_telephone_number from telephone where
telephone.organization_id = ORGANIZATION.organization_id and
telephone.primary_flag = 1 and telephone.obsolete_flag = 0 ) ),(


'UNKNOWN' )) Telephone_Number,



NVL(( ( select area_code from telephone where telephone.organization_id
= ORGANIZATION.organization_id and primary_flag = 1 and
telephone.obsolete_flag = 0 ) ),( 'UNKNOWN' )) Telephone_Area_Code,
NVL(( ( select address_1 from address where address.organization_id =
ORGANIZATION.organization_id and address.primary_flag = 1 and
address.obsolete_flag = 0 ) ),( 'UNKNOWN' )) Address_Line1,
NVL(( ( select address_2 from address where address.organization_id =
ORGANIZATION.organization_id and address.primary_flag = 1 and
address.obsolete_flag = 0 ) ),( 'UNKNOWN' )) Address_Line2,
NVL(( ( select city from address where address.organization_id =
ORGANIZATION.organization_id and address.primary_flag = 1 and
address.obsolete_flag = 0 ) ),( 'UNKNOWN' )) City,
NVL(( ( select postal_code from address where address.organization_id =
ORGANIZATION.organization_id and address.primary_flag = 1 and
address.obsolete_flag = 0 ) ),( 'UNKNOWN' )) Postal_Code,
NVL(( ( select RAWTOHEX( state_lkp )


from address where address.organization_id =
ORGANIZATION.organization_id and address.primary_flag = 1 and
address.obsolete_flag = 0 ) ),( 'UNKNOWN' )) State_LKP,
NVL(( ( select lookup.text_string from address, lookup where
address.organization_id = ORGANIZATION.organization_id and
lookup.lookup_id = address.state_lkp and primary_flag = 1 and
address.obsolete_flag = 0 ) ),( 'UNKNOWN' )) State,
NVL(( ( select RAWTOHEX( country_lkp )


from address where address.organization_id =
ORGANIZATION.organization_id and address.primary_flag = 1 and
address.obsolete_flag = 0 ) ),( 'UNKNOWN' )) Country_LKP,
NVL(( ( select lookup.text_string from address, lookup where
address.organization_id = ORGANIZATION.organization_id and
lookup.lookup_id = address.country_lkp and primary_flag = 1 and
address.obsolete_flag = 0 ) ),( 'UNKNOWN' )) Country,
NVL(( ( select audit_data.user_inserted from audit_data where
audit_data.related_tbl_name = 'organization' and audit_data.related_id
= ORGANIZATION.organization_id ) ),( 'UNKNOWN' ))
Organization_Createdby,


NVL(( TO_CHAR(( select audit_data.date_inserted from audit_data where
audit_data.related_tbl_name = 'organization'
and audit_data.related_id = ORGANIZATION.organization_id), 'MM/DD/YYYY'
) ),( 'UNKNOWN' )) Date_Org_Created,


-- 'UNKNOWN' Date_Org_Created,


NVL(( (select lookup.text_string from lookup, at_dealership where
at_dealership.at_lot_type_lkp = lookup.lookup_id
and at_dealership.at_org_id = ORGANIZATION.organization_id and
ORGANIZATION.obsolete_flag = 0 and at_dealership.at_obsolete_flag = 0 )
),( 'UNKNOWN' ))Lot_Type,


NVL(( (select lookup.text_string from lookup, at_dealership where
lookup.lookup_category_name = 'at_lot_size_lkp' and
at_dealership.at_lot_size_new_lkp = lookup.lookup_id
and at_dealership.at_org_id = ORGANIZATION.organization_id and
ORGANIZATION.obsolete_flag = 0 and at_dealership.at_obsolete_flag = 0)
),( 'UNKNOWN' ))New_Lot_Size,



NVL(( (select lookup.text_string from lookup, at_dealership where
lookup.lookup_category_name = 'at_lot_size_lkp' and
at_dealership.at_lot_size_used_lkp = lookup.lookup_id
and at_dealership.at_org_id = ORGANIZATION.organization_id and
ORGANIZATION.obsolete_flag = 0 and at_dealership.at_obsolete_flag = 0)
),( 'UNKNOWN' ))Used_Lot_Size,



NVL(( (select lookup.text_string from lookup, at_dealership where
at_dealership.at_bhph_lkp = lookup.lookup_id
and at_dealership.at_org_id = ORGANIZATION.organization_id and
ORGANIZATION.obsolete_flag = 0 and at_dealership.at_obsolete_flag = 0)
),( 'UNKNOWN' ))Buy_Pay_Here,


NVL(( (select lookup.text_string from lookup, at_dealership where
at_dealership.at_svc_bdyshp_lkp = lookup.lookup_id
and at_dealership.at_org_id = ORGANIZATION.organization_id and
ORGANIZATION.obsolete_flag = 0 and at_dealership.at_obsolete_flag = 0)
),( 'UNKNOWN' ))Service_Body_Shop,


-- NVL(( (select unique text_string from lookup where lookup_id =
ADDRESS.at_latlong_source_lkp and ADDRESS.organization_id =
ORGANIZATION.organization_id and ORGANIZATION.obsolete_flag = 0 ) ),(


'UNKNOWN' ))LatLong_Source,

'UNKNOWN' LatLong_Source,



NVL(( ( select at_org_source from at_dealership where
ORGANIZATION.organization_id = at_dealership.at_org_id) ),( 'UNKNOWN'
))Organization_Source,



'UNKNOWN' Primary_Email_Address,

'UNKNOWN' Address_Verification_DT,

'UNKNOWN' Parent_Corp1,

'UNKNOWN' Parent_Corp2,

'UNKNOWN' Parent_Corp3,

'UNKNOWN' Parent_Corp4,

'UNKNOWN' Parent_Corp5,

'UNKNOWN' Parent_Corp6,

'UNKNOWN' Parent_Corp7,

'UNKNOWN' Parent_Corp8,

'UNKNOWN' Parent_Corp9,

'UNKNOWN' Parent_Corp10,



NVL(( TO_CHAR( ORGANIZATION.date_entered ) ),( 'UNKNOWN' ))
First_Purchase_Date,


'UNKNOWN' Fiscal_Year_End,

'UNKOWN' Fortune_Class,

'UNKNOWN' LIne_Of_Business,

'UNKNOWN' Org_Sub_Segment,



NVL(( ( select text_string from lookup where lookup_id =
ORGANIZATION.active_status_lkp ) ),( 'UNKNOWN' )) Organization_Status,


'UNKNOWN' Organization_Type,

'UNKNOWN' Parent_Corp,

'UKOWN' Stock_Ticker,

'UNKNOWN' Sister_Corp1,

'UNKNOWN' Sister_Corp2,

'UNKNOWN' Sister_Corp3,

'UNKNOWN' Sister_Corp4,

'UNKNOWN' Sister_Corp5,

'UNKNOWN' RT_SSkey,



NVL(( RAWTOHEX( ORGANIZATION.organization_id )
),( 'UNKNOWN' )) Sales_SSkey,


'UNKNOWN' Service_SSkey,



NVL(( RAWTOHEX( ORGANIZATION.organization_id )
),( 'UNKNOWN' )) SSkey_copy



FROM



ORGANIZATION



LEFT OUTER JOIN ADDRESS ON (ADDRESS.Organization_ID =
ORGANIZATION.ORGANIZATION_ID)


WHERE



ORGANIZATION.obsolete_flag = 0






Maybe i have looked at it too long, ut I can't tell why I am getting
that error
Received on Thu Jan 18 2007 - 14:51:02 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US