[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index] [Date Index] [Thread Index]
[SQR-USERS Info] [SQRUG Home Page]

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

Subject: RE: [sqr-users] Problem with UNION Clause in SQR( with positionalparameters)
From: vaibhav_jindal@non.agilent.com
Date: Tue, 22 Jun 2004 19:08:04 +0800
Delivery-date: Tue, 22 Jun 2004 06:09:19 -0500
List-id: "This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions." <sqr-users.sqrug.org>

Ya ray !! 

U are right ... I got that .. Its running fine the way u told ..
Hey darshil ... The problem was not what u told... but still thanks very much 
for ur interest.
Thanks ryo as well....

Bye and keep in touch .. 
Thanks & Regards

Vaibhav Gupta
Business Intelligence and Enterprise Reporting
TCS @ Agilent Technologies

 

-----Original Message-----
From: Ray Ontko [mailto:rayo@ontko.com]
Sent: Monday, June 21, 2004 5:37 PM
To: This list is for discussion about the SQR database reporting
languagefrom Hyperion Solutions.
Subject: Re: [sqr-users] Problem with UNION Clause in SQR( with
positionalparameters)


Vaibhav, Darshil,

The "union" portion of your sql statement should look like
a normal SQL statement.  It should not use the modified 
SQR syntax.

  UNION SELECT DISTINCT
  TO_CHAR(ooha.order_number) &ORACLE_ORDER (15,1),
  ooha.orig_sys_document_ref &LEGACY_ORDER (15,150),
  ...

Should be:
  UNION SELECT DISTINCT
    TO_CHAR(ooha.order_number)
  , ooha.orig_sys_document_ref
  ...

Ray

On Mon, Jun 21, 2004 at 03:05:06AM -0700, Darshil Mehta wrote:
> 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
----------------------------------------------------------------------
Ray Ontko   rayo@ontko.com   Phone 1.765.935.4283   Fax 1.765.962.9788
Ray Ontko & Co.   Software Consulting Services   http://www.ontko.com/



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

Prev by Date: RE: [Potential SPAM] RE: [sqr-users] RE: Creating Menus in SQR 6.1
Next by Date: [sqr-users] Re: Export data into Excel....... (Srinivas Amaragani)
Previous by thread: Re: [sqr-users] Problem with UNION Clause in SQR( with positionalparameters)
Next by thread: Re: [sqr-users] Problem with UNION Clause in SQR( withpositionalparameters)
Indexes: [ Thread] [ Author] [ Date]
SQR-USERS List and Archive Info
SQRUG Home Page