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
>
>
> The error is quite obvious, and the statement shouldn't be looked at at
> all, because it hurts everyones eyes due to unreadibility.
I'm impressed Sybrand ... you looked. I couldn't read down that far due
to a splitting headache. ;-)
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Fri Jan 19 2007 - 09:06:13 CST
