[sqr-users] Problem with UNION Clause in SQR( with positionalparameters)

[vaibhav_jindal@non.agilent.com]

Hi 
I want to use UNION in two queries but its not working .. Can someone please 
guide me why is this not working ? 
Heres the code that is giving me the prob!!!
 
BEGIN-PROCEDURE QUERY_TOP
 Print-Direct printer=html '%%ResetBorder'
BEGIN-SELECT DISTINCT
 Alter-Printer Font=903 Point-Size=10
TO_CHAR(ooha.order_number) &ORACLE_ORDER (15,1)
ooha.orig_sys_document_ref &LEGACY_ORDER (15,150)
CONCAT(CONCAT(CONCAT(CONCAT(oola.line_number,'.'),CONCAT(oola.shipment_number,'.')),CONCAT(CONCAT(oola.option_number,'.'),CONCAT(oola.component_number,'.'))),oola.service_number)
 &ORACLE_LINE (15,350)
TRANSLATE(oos.name,',',' ') &ORDER_SOURCE (15,550)
TRANSLATE(ottt.name,',',' ') &ORDER_TYPE (15,750)
 NEXT-LISTING
FROM
oe_order_headers_all ooha,
oe_order_lines_all oola,
oe_order_sources oos,
oe_transaction_types_tl ottt,
hr_all_organization_units_tl haou
 
WHERE
ooha.header_id = oola.header_id
AND oola.ship_from_org_id = 128
AND ooha.org_id = haou.organization_id
 
 
UNION SELECT DISTINCT
TO_CHAR(ooha.order_number) &ORACLE_ORDER (15,1),
ooha.orig_sys_document_ref &LEGACY_ORDER (15,150),
CONCAT(CONCAT(CONCAT(CONCAT(oola.line_number,'.'),CONCAT(oola.shipment_number,'.')),CONCAT(CONCAT(oola.option_number,'.'),CONCAT(oola.component_number,'.'))),oola.service_number)
 &ORACLE_LINE (15,350),
TRANSLATE(oos.name,',',' ') &ORDER_SOURCE (15,550),
TRANSLATE(ottt.name,',',' ') &ORDER_TYPE (15,750)
 NEXT-LISTING
FROM
oe_order_headers_all ooha,
oe_order_lines_all oola,
oe_order_sources oos,
oe_transaction_types_tl ottt,
hr_all_organization_units_tl haou,
hr_all_organization_units_tl haou2
 
WHERE
ooha.header_id = oola.header_id
AND oola.ship_from_org_id = 128
AND ooha.org_id = haou.organization_id
AND haou.LANGUAGE = 'US' 
AND oola.INTMED_SHIP_TO_ORG_ID = haou2.organization_id (+)
 
End-Select
 Print-Direct printer=html '%%End-Select'
 Next-Listing
   Print-Direct printer=html '%%ResetBorder'
End-Procedure
 
 
 
 
The error coming is : 
 
 
(SQR 5528) ORACLE OCIStmtExecute error 923 in cursor 5:
   ORA-00923: FROM keyword not found where expected
SQL:  SELECT DISTINCT TO_CHAR(ooha.order_number), ooha.orig_sys_document_ref,
      CONCAT(CONCAT(CONCAT(CONCAT(oola.line_number,'.'),CONCAT(oola.shipment_nu
      mber,'.')),CONCAT(CONCAT(oola.option_number,'.'),CONCAT(oola.component_nu
      mber,'.'))),oola.service_number), TRANSLATE(oos.name,',',' '),
      TRANSLATE(ottt.name,',',' ')  FROM oe_order_headers_all ooha,
      oe_order_lines_all oola, oe_order_sources oos, oe_transaction_types_tl
      ottt, hr_all_organization_units_tl haou WHERE ooha.header_id =
      oola.header_id AND oola.ship_from_org_id = 128 AND ooha.org_id =
      haou.organization_id UNION SELECT DISTINCT TO_CHAR(ooha.order_number) :1
      (15,1), ooha.orig_sys_document_ref :2 (15,150),
      CONCAT(CONCAT(CONCAT(CONCAT(oola.line_number,'.'),CONCAT(oola.shipment_nu
      mber,'.')),CONCAT(CONCAT(oola.option_number,'.'),CONCAT(oola.component_nu
      mber,'.'))),oola.service_number) :3 (15,350), TRANSLATE(oos.name,',',' ')
      :4 (15,550), TRANSLATE(ottt.name,',',' ') :5 (15,750) NEXT-LISTING FROM
      oe_order_headers_all ooha, oe_order_lines_all oola, oe_order_sources
      oos, oe_transaction_types_tl ottt, hr_all_organization_units_tl haou,
      hr_all_organization_units_tl haou2 WHERE ooha.header_id = oola.header_id
      AND oola.ship_from_org_id = 128 AND ooha.org_id = haou.organization_id
      AND haou.LANGUAGE = 'US' AND oola.INTMED_SHIP_TO_ORG_ID =
      haou2.organization_id (+)

Thanks & Regards

Vaibhav Gupta
Business Intelligence and Enterprise Reporting
TCS @ Agilent Technologies

  

 

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users