Re: Getting two fields from the one table.

refer to my original post :

"you will need to join to each "price schema" structure separately your where clause is including two PS.CODES in the same record set, you need to retrieve the two codes, and related price data, by joining to the
REFCODE table twice, once for each CODE, then joining from each of these to related tables"

since the original problem involved combining two rows of 10 columns, with 9 being identically, the most straight-forward way seemed the extra join(s)

your example does not reflect the same case as the OP -- it had two known values that identified two different prices -- your example has a varying number of elements

the OP has an OR condition for returning two rows from the same table, -- this is what needs to be changed into an AND condition with two passes (two joins) on the same table, one pass for PS01.code='RRP EX GST' and one for PS02.code='WHL EX GST' (not the new table aliases, implying REFCODE PS in the from clause is replaced with REFCODE PS01, REFCODE PS02 -- and all appropriate joins also added, including two joins to the SPRICE_CLRSKU table to get two prices on the same row
then the SELECT clause is revised to include two prices

take a close look at the OP and see how you would need rewrite that query with your technique

• mcs

"VC" <boston103_at_hotmail.com> wrote in message news:LO1vb.191840$mZ5.1418512_at_attbi_s54...
| I still do not understand what you mean by 'very directly with additional
| joins'.
|
| In the original SQL, each row in 'sprice_style p1' is joined to two rows
in
| 'sprice_clrsku p2' (p1.spidx=p2.spidx). How would you put two values from
| 'sprice_clrsku p2' on one row without a pivot , just by using additional
| joins ?
|
| Please illustrate with an example.
|
| Rgds.
|
| "mcstock" <mcstockspamplug_at_spamdamenquery.com> wrote in message
| news:E6WdnTsD8aTCriGiRVn-iw_at_comcast.com...
| > i did not suggest doing a pivot with just joins
| >
| > my point was that the original problem can be solved very directly with
| > additional joins
| >
| > "VC" <boston103_at_hotmail.com> wrote in message
| > news:slUub.191787$275.672134_at_attbi_s53...
| > | I apologize for possible double-posting -- my provider is acting up.
| > |
| > | Hello mrstock,
| > |
| > | I am curious as to how you would accomplish the pivot using just
joins.
| > | E.g.:
| > |
| > | given:
| > |
| > | X Y
| > | 1 a
| > | 1 b
| > | 2 y
| > | 3 z
| > | 3 z
| > | 2 x
| > |
| > | a trivial solution using decode, courtesy of Mr. Kyte, would be:
| > |
| > | select x, max(decode(rn, 1, y, null)) val1, max(decode(rn, 2, y,
null))
| > val2
| > | from
| > | (select x, y, row_number() over (partition by x order by y) rn from
t1)
| > | group by x
| > |
| > | X VAL1 VAL2
| > | 1 a b
| > | 2 x y
| > | 3 z z
| > |
| > | How would you do it with joins ? Genuinely curious ...
| > |
| > | Rgds.
| > |
| > |
| > | "mcstock" <mcstockspamplug_at_spamdamenquery.com> wrote in message
| > | news:Osidnc9TE-vt8iaiRVn-hg_at_comcast.com...
| > | > you will need to join to each "price schema" structure separately
| > | >
| > | > your where clause is including two PS.CODES in the same record set,
| you
| > | need
| > | > to retrieve the two codes, and related price data, by joining to the
| > | REFCODE
| > | > table twice, once for each CODE, then joining from each of these to
| > | related
| > | > tables
| > | >
| > | > the DECODE, CASE and pivot suggestions are related but more
| appropriate
| > | for
| > | > when working with aggregate data in a matrix output.
| > | >
| > | > -- mcs
| > | >
| > | >
| > | > "Prince Of Thieves" <thief_NOSPAM_at_hotmail.com> wrote in message
| > | > news:T7zub.58$rG.1893_at_nnrp1.ozemail.com.au...
| > | > | I use Golden to send SQLs to our Oracle database. Today's
objective
| is
| > | to
| > | > | retrieve product data from the database. The product can have many
| EX
| > | GST
| > | > or
| > | > | INC GST prices, and the prices are per colour. The following SQL
| will
| > | give
| > | > | me the EX GST per colour and the INC GST per colour. So if an EX &
| INC
| > | > price
| > | > | is available for a particular colour, I get two records. What i'd
| like
| > | to
| > | > | get is one record showing BOTH the EX & INC prices for that
colour.
| > | > |
| > | > | SQL:
| > | > |
| > | > | /* Place the Division code here */
| > | > | var DivCode string
| > | > | var DivCode = "CHARACTER"
| > | > |
| > | > | select ps.code "Price Schema", s.stylecode "Style", s.name
| > | "Description",
| > | > | clr.clrcode "Col Code",
| > | > | clr.clrname "Col Desc", sku.sizeval "Sizes",sz.sizeseq,
kapn.apnnum
| > | "APN",
| > | > | p2.price "Price", seas.code "Season"
| > | > | from sprice_style p1, sprice_clrsku p2, style s, clr, sku, kapn,
| sizes
| > | sz,
| > | > | refcode ps, refcode seas, refcode divn
| > | > | where p1.styleidx=s.styleidx
| > | > | and p1.spidx=p2.spidx
| > | > | and p2.clridx=clr.clridx
| > | > | and clr.clridx=sku.clridx
| > | > | and sku.skuidx=kapn.skuidx(+)
| > | > | and sku.sizeidx=sz.sizeidx
| > | > | and ps.rcidx=p1.rcidx
| > | > | and seas.rcidx(+)=clr.ref1
| > | > | and divn.rcidx=s.ref1
| > | > | and sku.active=1
| > | > | and sku.sizeval is not null
| > | > | and (ps.code='RRP EX GST' or ps.code='WHL EX GST')
| > | > | and divn.code= :DivCode
| > | > | order by s.stylecode;
| > | > |
| > | > | Output of THIS SQL:
| > | > | WHL EX GST | 0019B | BANANAS IN PYJAMAS DAISY WOMEN | 290 | NAVY |
L
| |
| > | 50
| > | > |
| > | > | 9317326271078 | 17.3 | WI97
| > | > | RRP EX GST | 0019B | BANANAS IN PYJAMAS DAISY WOMEN | 290 | NAVY |
| L
| > |
| > | 50
| > | > |
| > | > | 9317326271078 | 29.95 | WI97
| > | > |
| > | > | The SQL-output Goal:
| > | > | 0019B | BANANAS IN PYJAMAS DAISY WOMEN | 290 | NAVY | L | 50 |
| > | > 9317326271078
| > | > | | 17.3 | 29.95 | WI97
| > | > |
| > | > | Having both prices in the same record. How can I change my SQL to
do
| > | this?
| > | > |
| > | > | ---------------------------------
| > | > | If you need additional info, let me know.
| > | > |
| > | > |
| > | > | --
| > | > |
| > | > | Julian Milano
| > | > |
| > | > |
| > | > |
| > | >
| > | >
| > |
| > |
| >
| >
|
|
Received on Fri Nov 21 2003 - 10:17:30 CST