| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL issues - sub query returns multiple results
Thanks.. by the way..
It hurts my eyes as well , but this is just a snippet of one
extraction statement from a data warehouse, so that is why it is so
convoluted.
It puls from the data warehouse into a CRM system.
sybrandb wrote:
> On Jan 18, 9:51 pm, "BookerT" <c..._at_ascac.org> wrote:
> > 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
> >
>
 |
 |