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

[Darshil Mehta <darshilm@yahoo.com>]

Vaibhav,

I guess you're missing one , in your first query. The
line in 1st query " TRANSLATE(oos.name,',',' ')
&ORDER_SOURCE (15,550)" should be 

" TRANSLATE(oos.name,',',' ') &ORDER_SOURCE (15,550),"

Please see below :
----------------------------------------------
 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
----------------------------------------------

I hope it works.

Darshil

--- vaibhav_jindal@non.agilent.com wrote:
> 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
> 



        
                
__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail 

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