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 LINUX

ppmtosixel

ppmtosixel(1)						      General Commands Manual						     ppmtosixel(1)

NAME

       ppmtosixel - convert a portable pixmap into DEC sixel format

SYNOPSIS

       ppmtosixel [-raw] [-margin] [ppmfile]

DESCRIPTION

       Reads  a  portable  pixmap  as input.  Produces sixel commands (SIX) as output.	The output is formatted for color printing, e.g. for a DEC
       LJ250 color inkjet printer.

       If RGB values from the PPM file do not have maxval=100, the RGB values are rescaled.  A printer control header and a color assignment table
       begin the SIX file.  Image data is written in a compressed format by default.  A printer control footer ends the image file.

OPTIONS

       -raw   If  specified,  each  pixel  will  be explicitly described in the image file.  If -raw is not specified, output will default to com-
	      pressed format in which identical adjacent pixels are replaced by "repeat pixel" commands.  A raw file is often an order	of  magni-
	      tude larger than a compressed file and prints much slower.

       -margin
	      If  -margin  is not specified, the image will be start at the left margin (of the window, paper, or whatever).  If -margin is speci-
	      fied, a 1.5 inch left margin will offset the image.

PRINTING

       Generally, sixel files must reach the printer unfiltered.  Use the lpr -x option or cat filename > /dev/tty0?.

BUGS

       Upon rescaling, truncation of the least significant bits of RGB values may result in poor color conversion.  If the original PPM maxval was
       greater	than  100,  rescaling  also reduces the image depth.  While the actual RGB values from the ppm file are more or less retained, the
       color palette of the LJ250 may not match the colors on your screen.  This seems to be a printer limitation.

SEE ALSO

       ppm(5)

AUTHOR

       Copyright (C) 1991 by Rick Vinci.

								   26 April 1991						     ppmtosixel(1)