Sponsored Content
Full Discussion: Combine 3 queries
Top Forums Programming Combine 3 queries Post 302620913 by durden_tyler on Monday 9th of April 2012 01:26:35 PM
Old 04-09-2012
Quote:
Originally Posted by dwr80
can these 3 be combined into 1 query?

Code:
createtablea1as 
selecta.tps_Res_nb,
b.tkt_prod_cd, 
b.tkt_prod_typ_nm,
b.prod_intrnl_ds,
b.tkt_prod_typ_nm AS TKT_ENTL_NM,
casewhen b.tkt_prod_nm isnotnullthen b.tkt_prod_nm
when b.tkt_prod_nm isnulland b.prod_intrnl_ds isnotnullthen b.prod_intrnl_ds
else b.tkt_prod_nm 
endas tkt_prod_nm
FromWDW_PRARVL_GSTA
innerjoin crmmprod.WTRIP_ACM_TKT b
ona.tps_res_nb = b.ord_nb
 
createtableb1as 
selecta.*,
b.*
froma1a
leftjoinTKT_ATTx b
ona.TKT_PROD_CD = b.code
 
createtabletest2nologgingas 
selecta.*
from(select b.*,
ROW_NUMBER ()OVER(PARTITIONBY b.tps_Res_nb ORDERBY b.daycount descnullslast, 
b.adult, b.dayclass, b.tkt_prod_typ_nm, b.tkt_prod_nm, b.prod_intrnl_ds)AS ROW_RN fromb1 b)a


Something like this?

Code:
SELECT a.tps_res_nb,
       b.tkt_prod_cd,
       b.tkt_prod_typ_nm,
       b.prod_intrnl_ds,
       b.tkt_prod_typ_nm AS tkt_entl_nm,
       NVL (b.tkt_prod_nm, b.prod_intrnl_ds) AS tkt_prod_nm,
       c.*,
       ROW_NUMBER () OVER (PARTITION BY a.tps_res_nb
                               ORDER BY c.daycount DESC NULLS LAST,
                                        c.adult,
                                        c.dayclass,
                                        b.tkt_prod_typ_nm,
                                        NVL (b.tkt_prod_nm, b.prod_intrnl_ds),
                                        b.prod_intrnl_ds
                          ) AS row_rn
  FROM wdw_prarvl_gsta
 INNER JOIN crmmprod.wtrip_acm_tkt b
    ON (a.tps_res_nb = b.ord_nb)
 LEFT OUTER JOIN tkt_attx c
    ON (b.tkt_prod_cd = c.code)

tyler_durden
 

9 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

Some queries...

Guys need some advice on how to check some of the questions below? i'm running on an open VMS platform... which i am an idiot to... appreciate if anyone can give some hints or source on how to check on.. a script that is running on cron job... but doesn't run as the login user name.. 1. why... (6 Replies)
Discussion started by: 12yearold
6 Replies

2. UNIX for Advanced & Expert Users

Two small queries

Query 1 : How to check if a directory already exists? If doesn't exist then create a new one. Query 2 : I want to put following text using a single echo statement into a log file and also want to retain the formatting of the text. How it can be... (3 Replies)
Discussion started by: skyineyes
3 Replies

3. Shell Programming and Scripting

my queries

hi guys Well, i need to have a report generation script or any script which will show me all the content/information of a file when i run that script. Please help me on this isssue at the earliest.As i am little bit aware of scripting.Thanks in advance! regards ash (4 Replies)
Discussion started by: whizkidash
4 Replies

4. Homework & Coursework Questions

Queries

Any help on like where to get started on this? I'm just confused. 1. The problem statement, all variables and given/known data: Enter text here.Queries to satisfy these two report requests (use your CCI database): Retrieve all rows of active inventory where current on hands is less than... (0 Replies)
Discussion started by: lakers34kb
0 Replies

5. Solaris

Installation queries

Dear All, Please clarify my queries My 1st doubt is as you know solaris have default 8 slices out of 8 slices in which slice the OS is stored. Like in windows if you select C drive the Program files are stored in C drive like that in solaris in which slice the OS is stored. My 2nd doubt is... (2 Replies)
Discussion started by: suneelieg
2 Replies

6. Shell Programming and Scripting

Few queries regarding awk...

One of the command output is as below. -rw-r--r--+ 1 root root 75G Nov 21 16:43 /var/ovs/mount/86BXXX/running_pool/Machine1/System-sda.img -rw-r--r--+ 1 root root 75G Nov 21 16:36 /var/ovs/mount/86BXXX/running_pool/Machine2/System.img -rw-r--r--+ 1 root root 150G Sep 23 19:13... (2 Replies)
Discussion started by: pinga123
2 Replies

7. AIX

Queries Regarding MigratePV

We have 3PVs, now we want to migrate it to new 2PVs. OLD PVS hdisk1 ---- /u01 hdisk2 ------ /u01 hdisk 3 ----- /u02, /u01 new PVs hdisk4 ---- Free hdisk5 ---- Free I check man, it doesn't say anything about offline/online thing, do i need downtime to migrate pv ? also, what i... (6 Replies)
Discussion started by: Fracker
6 Replies

8. Debian

GRUB Queries ?!

Hello, I am posting the following questions here because I need them answered by people who have actually done a lot of work in GRUB. DO NOT GIVE ME GUESS ANSWERS PLEASE. Feel free to redirect me if this is not the right place to ask these questions. Can I download GRUB separately from... (6 Replies)
Discussion started by: sreyan32
6 Replies

9. UNIX Desktop Questions & Answers

/etc/motd - queries

If I have /etc/motd, he is file or directory? I saw that some call them folders and files others... Which option is better? I knew that being a director, but many told me not. Thank you! (1 Reply)
Discussion started by: mescu
1 Replies
MPI_Alloc_mem(3OpenMPI) 												   MPI_Alloc_mem(3OpenMPI)

NAME
MPI_Alloc_mem - Allocates a specified memory segment. SYNTAX
C Syntax #include <mpi.h> int MPI_Alloc_mem(MPI_Aint size, MPI_Info info, void *baseptr) Fortran Syntax (see FORTRAN NOTES) INCLUDE 'mpif.h' MPI_ALLOC_MEM(SIZE, INFO, BASEPTR, IERROR) INTEGER INFO, IERROR INTEGER(KIND=MPI_ADDRESS_KIND) SIZE, BASEPTR C++ Syntax #include <mpi.h> void* MPI::Alloc_mem(MPI::Aint size, const MPI::Info& info) INPUT PARAMETERS
size Size of memory segment in bytes (nonnegative integer). info Info argument (handle). OUTPUT PARAMETERS
baseptr Pointer to beginning of memory segment allocated. IERROR Fortran only: Error status (integer). DESCRIPTION
MPI_Alloc_mem allocates size bytes of memory. The starting address of this memory is returned in the variable base. FORTRAN NOTES
There is no portable FORTRAN 77 syntax for using MPI_Alloc_mem. There is no portable Fortran syntax for using pointers returned from MPI_Alloc_mem. However, MPI_Alloc_mem can be used with Sun Fortran compilers. From FORTRAN 77, you can use the following non-standard declarations for the SIZE and BASEPTR arguments: INCLUDE "mpif.h" INTEGER*MPI_ADDRESS_KIND SIZE, BASEPTR From either FORTRAN 77 or Fortran 90, you can use "Cray pointers" for the BASEPTR argument. Cray pointers are described further in the For- tran User's Guide and are supported by many Fortran compilers. For example, INCLUDE "mpif.h" REAL*4 A(100,100) POINTER (BASEPTR, A) INTEGER*MPI_ADDRESS_KIND SIZE SIZE = 4 * 100 * 100 CALL MPI_ALLOC_MEM(SIZE,MPI_INFO_NULL,BASEPTR,IERR) ! use A CALL MPI_FREE_MEM(A, IERR) ERRORS
Almost all MPI routines return an error value; C routines as the value of the function and Fortran routines in the last argument. C++ func- tions do not return errors. If the default error handler is set to MPI::ERRORS_THROW_EXCEPTIONS, then on error the C++ exception mechanism will be used to throw an MPI:Exception object. Before the error value is returned, the current MPI error handler is called. By default, this error handler aborts the MPI job, except for I/O function errors. The error handler may be changed with MPI_Comm_set_errhandler; the predefined error handler MPI_ERRORS_RETURN may be used to cause error values to be returned. Note that MPI does not guarantee that an MPI program can continue past an error. SEE ALSO
MPI_Free_mem Open MPI 1.2 September 2006 MPI_Alloc_mem(3OpenMPI)
All times are GMT -4. The time now is 04:50 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy