Combine 3 queries Post: 302620913

Sponsored Content

Top Forums Programming Combine 3 queries Post 302620913 by durden_tyler on Monday 9th of April 2012 01:26:35 PM

04-09-2012

Registered User

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

durden_tyler

View Public Profile for durden_tyler

Find all posts by durden_tyler

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...

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. 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. 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...

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...

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...

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...

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...

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!

LEARN ABOUT OSX

size

SIZE(1) 						      General Commands Manual							   SIZE(1)

NAME

       size - print the size of the sections in an object file

SYNOPSIS

       size [ option ... ] [ object ... ]

DESCRIPTION

       Size  (without  the -m option) prints the (decimal) number of bytes required by the __TEXT, __DATA and __OBJC segments.	All other segments
       are totaled and that size is listed in the `others' column.  The final two columns is the sum in decimal and hexadecimal.  If  no  file	is
       specified, a.out is used.

       The options to size(1) are:

       -      Treat the remaining arguments as name of object files not options to size(1).

       -m     Print the sizes of the Mach-O segments and sections as well as the total sizes of the sections in each segment and the total size of
	      the segments in the file.

       -l     When used with the -m option, also print the addresses and offsets of the sections and segments.

       -x     When used with the -m option, print the values in hexadecimal (with leading 0x's) rather than decimal.

       -arch arch_type
	      Specifies the architecture, arch_type, of the file for size(1) to operate on when the file is a universal file.	(See  arch(3)  for
	      the  currently know arch_types.)	The arch_type can be "all" to operate on all architectures in the file.  The default is to display
	      only the host architecture, if the file contains it; otherwise, all architectures in the file are shown.

SEE ALSO

       otool(1)

BUGS

       The size of common symbols can't be reflected in any of the numbers for relocatable object files.

Apple Computer, Inc.						   July 28, 2005							   SIZE(1)