• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 441
  • Last Modified:

union query with unique identifier

I have the following stored procedure that I am attempting either to put
into a temp table or do a union query.  I believe the problem may be that
I do not understand the unique identifier guid. Each of the queries work but they are evidently producing different output.  I would appreciate any help. The joins in the last table are on unique identifiers.

CREATE PROCEDURE udp_Lot_CDV_Select_Maker_Prod_Order
  AS
select
right(L.siemens_locn,charindex('-',reverse(L.Siemens_Locn) ) -1 ) as Maker,Code='0',Type='-',Description='None'

FROM udt_Lot_Locations L inner JOIN udt_LOT_Maker_by_Blend M
ON L.Record_ID = M.Maker_Location_ID

where  M.Maker_Blend_Id='{FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF}'
order by cast(  right(L.siemens_locn,charindex('-',reverse(L.Siemens_Locn)  ) -1  ) as integer)

union

SELECT  
right(L.siemens_locn,charindex('-',reverse(L.Siemens_Locn)  ) -1  ) as Maker,I.Blend_Code as code,

     case
          when  I.Blend_Code='100' then 'M'
          when  I.Blend_Code='102' then 'R'
          when  I.Blend_Code='0' then '-'
                       
     end--case
 as Type, I.Description

FROM udt_Lot_Locations L inner JOIN (udt_Lot_Items I inner JOIN udt_LOT_Maker_by_Blend M
ON I.Record_ID = M.Maker_Blend_ID) ON L.Record_ID = M.Maker_Location_ID
order by cast(  right(L.siemens_locn,charindex('-',reverse(L.Siemens_Locn) ) -1 ) as integer)

GO
0
ulu21e
Asked:
ulu21e
2 Solutions
 
malekamCommented:
Is blend_code integer or character data?

It looks as though you may need to change
Code='0'
to
Code=0

Any luck?
0
 
Brendt HessSenior DBACommented:
The string representation of a uniqueidentifier (GUID) should not have the braces around it, as I understand this.  So, the query should have the line:

where  M.Maker_Blend_Id='FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF'
0
 
walterecookCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Split: malekam {http:#8160788} & bhess1 {http:#8161764}

Please leave any comments here within the next four days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

walterecook
EE Cleanup Volunteer
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now