Thanks McStock & VC.
I can't get it to work. What should the resulting SQL look like?
--
Julian Milano
"mcstock" <mcstockspamplug_at_spamdamenquery.com> wrote in message
news:FsGdnRDN6towpyOiRVn-vw_at_comcast.com...
> 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 Tue Nov 25 2003 - 22:41:30 CST
