Re: Getting two fields from the one table.

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 Thu Nov 20 2003 - 05:44:44 CST