Hi,
I need help with a SQL SELECT statement. I am using a UNION to merge four
tables together. For the tables that did not include a particular field, I
have used a placeholder of 'N/A' in the SELECT statement as follows:
SELECT organizationid, organization_name, businessunitid,
business_unit_name, subdivision_id, subdivision_name, lot_no, plan_number,
plan_name, current_retail_price, current_expiration_date,
plan_availability_pricing_id, plan_id
FROM hb_view_lot_plan
UNION
SELECT organizationid, organization_name, businessunitid,
business_unit_name, subdivision_id, subdivision_name, 'N/A', plan_number,
plan_name, current_retail_price, current_expiration_date,
plan_availability_pricing_id, plan_id
FROM hb_view_subdivision_plan
UNION
SELECT organizationid, organization_name, businessunitid,
business_unit_name, 'N/A', 'N/A', 'N/A', plan_number, plan_name,
current_retail_price, current_expiration_date, plan_availability_pricing_id,
plan_id
FROM hb_view_business_unit_plan
UNION
SELECT organizationid, organization_name, businessunitid, 'N/A', 'N/A',
'N/A', 'N/A', plan_number, plan_name, current_retail_price,
current_expiration_date, plan_availability_pricing_id, plan_id
FROM hb_view_organization_plan
I am having a problem with using the 'N/A' placeholder in lieu of a
uniqueidentifier. It works for varchar field types but apparently not
uniqueidentifier type fields. I receive the error message "error converting
from character string into uniqueidentifier". Thanks for your help.
DonYou could cast the top uniqueidentifier to a varchar(36) and it will work.
It seems a bit wierd though, because are you going to display the guid to
the user? Pretty hideous if you are.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
<dbj> wrote in message news:uwbfXluPFHA.2136@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I need help with a SQL SELECT statement. I am using a UNION to merge four
> tables together. For the tables that did not include a particular field,
> I have used a placeholder of 'N/A' in the SELECT statement as follows:
>
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, lot_no, plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_lot_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, 'N/A', plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_subdivision_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, 'N/A', 'N/A', 'N/A', plan_number, plan_name,
> current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_business_unit_plan
> UNION
> SELECT organizationid, organization_name, businessunitid, 'N/A',
> 'N/A', 'N/A', 'N/A', plan_number, plan_name, current_retail_price,
> current_expiration_date, plan_availability_pricing_id, plan_id
> FROM hb_view_organization_plan
>
> I am having a problem with using the 'N/A' placeholder in lieu of a
> uniqueidentifier. It works for varchar field types but apparently not
> uniqueidentifier type fields. I receive the error message "error
> converting from character string into uniqueidentifier". Thanks for your
> help.
>
> Don
>
>|||When you use union, first query defines column data types for all other
queries.
So, the problem is (probably, you did not provide DDl, so I cannot tell for
sure) in 3th and/or 4th query, where you set 'N/A' for subdivision_id which
can be of GUID data type.
Solution can be to replace 'N/A' with "empty" guid
'{00000000-0000-0000-0000-000000000000}' or with null.
Regards,
Marko Simic
"dbj" wrote:
> Hi,
> I need help with a SQL SELECT statement. I am using a UNION to merge four
> tables together. For the tables that did not include a particular field,
I
> have used a placeholder of 'N/A' in the SELECT statement as follows:
>
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, lot_no, plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_lot_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, 'N/A', plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_subdivision_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, 'N/A', 'N/A', 'N/A', plan_number, plan_name,
> current_retail_price, current_expiration_date, plan_availability_pricing_i
d,
> plan_id
> FROM hb_view_business_unit_plan
> UNION
> SELECT organizationid, organization_name, businessunitid, 'N/A', 'N/A'
,
> 'N/A', 'N/A', plan_number, plan_name, current_retail_price,
> current_expiration_date, plan_availability_pricing_id, plan_id
> FROM hb_view_organization_plan
>
> I am having a problem with using the 'N/A' placeholder in lieu of a
> uniqueidentifier. It works for varchar field types but apparently not
> uniqueidentifier type fields. I receive the error message "error converti
ng
> from character string into uniqueidentifier". Thanks for your help.
>
> Don
>
>
>|||Sorry I forgot to write the most important part :)
SQL try to convert 'N/A' to uniqueidentifier data type which is not
possible. you may try this to see what will happen:
select newid()
union
select 'N/A'
Regards,
Marko Simic
"dbj" wrote:
> Hi,
> I need help with a SQL SELECT statement. I am using a UNION to merge four
> tables together. For the tables that did not include a particular field,
I
> have used a placeholder of 'N/A' in the SELECT statement as follows:
>
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, lot_no, plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_lot_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, 'N/A', plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_subdivision_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, 'N/A', 'N/A', 'N/A', plan_number, plan_name,
> current_retail_price, current_expiration_date, plan_availability_pricing_i
d,
> plan_id
> FROM hb_view_business_unit_plan
> UNION
> SELECT organizationid, organization_name, businessunitid, 'N/A', 'N/A'
,
> 'N/A', 'N/A', plan_number, plan_name, current_retail_price,
> current_expiration_date, plan_availability_pricing_id, plan_id
> FROM hb_view_organization_plan
>
> I am having a problem with using the 'N/A' placeholder in lieu of a
> uniqueidentifier. It works for varchar field types but apparently not
> uniqueidentifier type fields. I receive the error message "error converti
ng
> from character string into uniqueidentifier". Thanks for your help.
>
> Don
>
>
>|||Oops, no it won't (d'oh!) you will have to cast them all to char:
select cast( 'na' as varchar(36))
union
select cast(newId() as varchar(36))
Sorry,
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:u%238on1uPFHA.532@.TK2MSFTNGP09.phx.gbl...
> You could cast the top uniqueidentifier to a varchar(36) and it will work.
> It seems a bit wierd though, because are you going to display the guid to
> the user? Pretty hideous if you are.
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> <dbj> wrote in message news:uwbfXluPFHA.2136@.TK2MSFTNGP14.phx.gbl...
>|||Marko,
Thank you very much. Your answer worked on the first try.
Don
"Simic Marko" <SimicMarko@.discussions.microsoft.com> wrote in message
news:CA618990-5DFD-4753-B3CC-13FA859F136C@.microsoft.com...
> When you use union, first query defines column data types for all other
> queries.
> So, the problem is (probably, you did not provide DDl, so I cannot tell
> for
> sure) in 3th and/or 4th query, where you set 'N/A' for subdivision_id
> which
> can be of GUID data type.
> Solution can be to replace 'N/A' with "empty" guid
> '{00000000-0000-0000-0000-000000000000}' or with null.
> Regards,
> Marko Simic
> "dbj" wrote:
>
with this...why do u use 'as' word ?