Home » SQL & PL/SQL » SQL & PL/SQL » Query :(
icon9.gif  Query :( [message #576775] Thu, 07 February 2013 19:53 Go to next message
ydeonia
Messages: 14
Registered: February 2013
Location: India
Junior Member
Hi

In below data A will come only once and D will always be followed by B. D can be 2 or 3 in number after B.
I need to write a query so that slect query comes like

ABD, ABDDD , ABD and so on . All B,D are different from each other and should be in order.
Here A will always with the B and D will always followed by B. Its vwery complex for me . Can anybody please help on this.

A
B
D
B
D
B
D
B
D
B
D
B
D
D
B
D
B
D
B
D
D
B
D
D
B
D
D
Sad
Re: Query :( [message #576777 is a reply to message #576775] Thu, 07 February 2013 20:48 Go to previous messageGo to next message
BlackSwan
Messages: 22781
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

>I need to write a query so that select query comes like
SELECT what from where?

>Can anybody please help on this.
I do not understand what exactly "this" is supposed to be.

[Updated on: Thu, 07 February 2013 20:59]

Report message to a moderator

Re: Query :( [message #576780 is a reply to message #576775] Thu, 07 February 2013 22:42 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
1. Provide working test case
2. Provide proper rules based on which output gets generated.

LAG/LEAD functions may help you.

regards,
Delna
Re: Query :( [message #576781 is a reply to message #576780] Thu, 07 February 2013 22:50 Go to previous messageGo to next message
ydeonia
Messages: 14
Registered: February 2013
Location: India
Junior Member
Ohk I am sorry guys .. Here is what I need and I tried to make it more simpler in words ....
I have a select query which results
ABDBDDBDBDDDBDBDBDBDDDBD

If you see the above pattern 'A' comes only once . D will always be followed by B

I need a query in which

If A comes concat with next B and then take that concat (AB) with next D until next B comes. A has be always with BD .

The result should look like

ABD ABD ABD ABD


I am not getting any idea to get the desired result .
Re: Query :( [message #576782 is a reply to message #576781] Thu, 07 February 2013 23:03 Go to previous messageGo to next message
BlackSwan
Messages: 22781
Registered: January 2009
Senior Member
>I need a query in which
can you write SQL?
can you write SQL when you do not know table name, column name or data?
What is table name?
what are column names?
what data is in table that SELECT operates against.


>The result should look like

>ABD ABD ABD ABD

SELECT 'ABD ABD ABD ABD' FROM DUAL;
Re: Query :( [message #576783 is a reply to message #576781] Thu, 07 February 2013 23:17 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
I am not sure which version of Oracle you are using and what is your exact requiremrent but this may help you

>WITH TBL AS
  2  (SELECT 'ABDBDDBDBDDDBDBDBDBDDDBD' COL1 FROM DUAL
  3   UNION ALL
  4   SELECT 'ABDDBDDBDBDDDBDBDBDBDDDBD' COL1 FROM DUAL
  5   UNION ALL
  6   SELECT 'ABDDDBDDBDBDDDBDBDBDBDDDBD' COL1 FROM DUAL)
  7  SELECT REGEXP_SUBSTR(COL1,'ABD+') FROM TBL;

REGEXP_SUBSTR(COL1,'ABD+')
-----------------------------------------------------------
ABD
ABDD
ABDDD


regards,
Delna
Re: Query :( [message #576784 is a reply to message #576782] Thu, 07 February 2013 23:18 Go to previous messageGo to next message
ydeonia
Messages: 14
Registered: February 2013
Location: India
Junior Member
Below is the select query

SELECT
TRIM(C1Y56CH01),
TRIM(C1Y56CH02),
TRIM(C1Y56CH03),
TRIM(C1Y56CH04),
TRIM(C1Y56CH08),
TRIM(C1Y56CS09),
TRIM(C1Y56CS03),
TRIM(C1Y56CS04),
TRIM(C1Y56CS05),
TRIM(C1Y56CS06),
TRIM(C1Y56CS07),
TRIM(C1Y56CS08),
TRIM(C1Y56CS10),
TRIM(C1Y56CS11),
TRIM(C1Y56CS12),
TRIM(C1Y56CS13),
TRIM(C1Y56CS15),
TRIM(C1Y56CD05),
TRIM(C1Y56CD13),
TRIM(C1Y56CD04),
TRIM(C1Y56CD14),
TRIM(C1Y56CD06),
TRIM(C1Y56CD11),
TRIM(C1Y56CD07),
TRIM(C1Y56CD10),
TRIM(C1Y56CD12),
C1BCT
FROM CRPDTA.F56055C1 A
WHERE
C1Y55ELM01 = 'REP_ABSO'
AND ( TRIM(C1Y55ELM03) IS NULL or TRIM(C1Y55ELM03) IN ('EDITED') )
AND C1ICU = (SELECT MAX(C1ICU) FROM CRPDTA.F56055C1)
ORDER BY C1BCT


and the result is in attachment



If you see the first column is A,B and D.
I need to repeat A, B and D row as per the previous post logic .I am not sure if it is possible. Sad
Re: Query :( [message #576785 is a reply to message #576784] Thu, 07 February 2013 23:25 Go to previous messageGo to next message
ydeonia
Messages: 14
Registered: February 2013
Location: India
Junior Member
Thanks delna.sexy so much for yoru query .I mean to say the first column is the identifier (A,B,D) to repeat the next 25 value till the other B and D comes

in the screenshot you see only ABD but in real scenario there will be more B's and D's with same select query.
Re: Query :( [message #576786 is a reply to message #576785] Thu, 07 February 2013 23:34 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Still I am not getting the requirement. Can you please post CREATE TABLE, INSERT into that table with sample data and your expected output from that sample table?

regards,
Delna
Re: Query :( [message #576792 is a reply to message #576786] Fri, 08 February 2013 00:16 Go to previous messageGo to next message
ydeonia
Messages: 14
Registered: February 2013
Location: India
Junior Member
Hi

Here is the link to download the insert query

ht_tp://www.sendspace.com/file/it45vr

There were almost 2 lakhs of rows so I have uploaded only 5k rows.

Please create a table F56055C1 and import the sql. If you run the below query

SELECT
TRIM(C1Y56CH01),
TRIM(C1Y56CH02),
TRIM(C1Y56CH03),
TRIM(C1Y56CH04),
TRIM(C1Y56CH08),
TRIM(C1Y56CS09),
TRIM(C1Y56CS03),
TRIM(C1Y56CS04),
TRIM(C1Y56CS05),
TRIM(C1Y56CS06),
TRIM(C1Y56CS07),
TRIM(C1Y56CS08),
TRIM(C1Y56CS10),
TRIM(C1Y56CS11),
TRIM(C1Y56CS12),
TRIM(C1Y56CS13),
TRIM(C1Y56CS15),
TRIM(C1Y56CD05),
TRIM(C1Y56CD13),
TRIM(C1Y56CD04),
TRIM(C1Y56CD14),
TRIM(C1Y56CD06),
TRIM(C1Y56CD11),
TRIM(C1Y56CD07),
TRIM(C1Y56CD10),
TRIM(C1Y56CD12),
C1BCT
FROM CRPDTA.F56055C1 A
WHERE
C1Y55ELM01 = 'REP_ABSO'
AND ( TRIM(C1Y55ELM03) IS NULL or TRIM(C1Y55ELM03) IN ('EDITED') )
AND C1ICU = (SELECT MAX(C1ICU) FROM CRPDTA.F56055C1)
ORDER BY C1BCT

You will get first colum like A, B, D

Here A is the header having 25 values ahead when B indicates teh customer with some details again 25 columns and D are the details of purchase order . So the customer B can have mutiple purchase orders like BDD .
But in output I need A(Header)B(Customer) D(purchase detail) . So if there were 3 customer has their own purchase order . The select query will come like

AB1D1B2D1D2B3D1D2D3BD
Here A is unique but the first B customr has D purchase order and then the second customer has 2 purchase order. but I need to list it seperate like

AB1D1
AB2D1
AB2D2
AB3D1


So this is the requirement .
Re: Query :( [message #576798 is a reply to message #576792] Fri, 08 February 2013 00:34 Go to previous messageGo to next message
Michel Cadot
Messages: 59083
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle version, with 4 decimals?

Regards
Michel
Re: Query :( [message #576802 is a reply to message #576792] Fri, 08 February 2013 00:44 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Please see other posts how they provide test case and expected output.

regards,
Delna
Re: Query :( [message #576803 is a reply to message #576802] Fri, 08 February 2013 00:46 Go to previous messageGo to next message
ydeonia
Messages: 14
Registered: February 2013
Location: India
Junior Member
@ Michel Oracle version is 11g 11.1.0.7.0

@delna Please refer me some post .
Re: Query :( [message #576810 is a reply to message #576803] Fri, 08 February 2013 01:50 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
get it here

regards,
Delna
Re: Query :( [message #577522 is a reply to message #576810] Mon, 18 February 2013 03:12 Go to previous messageGo to next message
ydeonia
Messages: 14
Registered: February 2013
Location: India
Junior Member
Hi delna

Can you please see the attached sheet and help me for the logic

http://img543.imageshack.us/img543/3312/20130218160852833.jpg

Thanks
Re: Query :( [message #577525 is a reply to message #577522] Mon, 18 February 2013 03:36 Go to previous messageGo to next message
Michel Cadot
Messages: 59083
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What are step I, step II?

Regards
Michel
Re: Query :( [message #577530 is a reply to message #577525] Mon, 18 February 2013 04:26 Go to previous messageGo to next message
ydeonia
Messages: 14
Registered: February 2013
Location: India
Junior Member
Step II is if another D is there with that B like B1D1D2 then it will again concat with D2 to make AB1D2 .

Step 1 is if there is no preceding D and B is next like B2 then it will become AB2D1
Re: Query :( [message #577539 is a reply to message #577530] Mon, 18 February 2013 05:17 Go to previous messageGo to next message
Michel Cadot
Messages: 59083
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So I don't understand your graphic.
What does mean "-----> STEP I"? What does mean "Follow STEP II"?

Regards
Michel


Re: Query :( [message #577553 is a reply to message #577539] Mon, 18 February 2013 06:02 Go to previous messageGo to next message
ydeonia
Messages: 14
Registered: February 2013
Location: India
Junior Member
http://i.imgur.com/60hC9tx.jpg

I mean to say Step 1 and step 2 are loops if conditions satisfy

[Updated on: Mon, 18 February 2013 06:07]

Report message to a moderator

Re: Query :( [message #577564 is a reply to message #577553] Mon, 18 February 2013 06:53 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2028
Registered: January 2010
Senior Member
Not sure I fully understand, but:

SQL> select  *
  2    from  tbl
  3  /

        ID V
---------- -
         1 A
         2 B
         3 D
         4 B
         5 D
         6 B
         7 D
         8 B
         9 D
        10 B
        11 D

        ID V
---------- -
        12 B
        13 D
        14 D
        15 B
        16 D
        17 B
        18 D
        19 B
        20 D
        21 D
        22 B

        ID V
---------- -
        23 D
        24 D
        25 B
        26 D
        27 D

27 rows selected.

SQL>  


And:

with t as (
           select  val,
                   last_value(
                              case val
                                when 'A' then 'A'
                              end
                              ignore nulls
                             )
                      over(
                           order by id
                          ) ||
                    last_value(
                              case val
                                when 'B' then 'B'
                              end
                              ignore nulls
                             )
                      over(
                           order by id
                          ) || val triad,
                   last_value(
                              case val
                                when 'A' then id
                              end
                              ignore nulls
                             )
                      over(
                           order by id
                          ) || ' + ' ||
                    last_value(
                              case val
                                when 'B' then id
                              end
                              ignore nulls
                             )
                      over(
                           order by id
                          ) || ' + ' || id seq
             from  tbl
          )
select  triad,
        seq
  from  t
  where val = 'D'
/

TRI SEQ
--- ---------------
ABD 1 + 2 + 3
ABD 1 + 4 + 5
ABD 1 + 6 + 7
ABD 1 + 8 + 9
ABD 1 + 10 + 11
ABD 1 + 12 + 13
ABD 1 + 12 + 14
ABD 1 + 15 + 16
ABD 1 + 17 + 18
ABD 1 + 19 + 20
ABD 1 + 19 + 21

TRI SEQ
--- ---------------
ABD 1 + 22 + 23
ABD 1 + 22 + 24
ABD 1 + 25 + 26
ABD 1 + 25 + 27

15 rows selected.

SQL>  


SY.

[Updated on: Mon, 18 February 2013 06:57]

Report message to a moderator

Re: Query :( [message #577586 is a reply to message #577564] Mon, 18 February 2013 19:41 Go to previous message
ydeonia
Messages: 14
Registered: February 2013
Location: India
Junior Member
Beautiful Solomon . Thank u Smile
I can ask you one more thing . If you see below link.
http://www.orafaq.com/forum/fa/10641/177130/

How will I merge Select query with your query . Thanks a lot .You actually understood my need. Smile

SELECT
TRIM(C1Y56CH01),	
TRIM(C1Y56CH02),	
TRIM(C1Y56CH03),	
TRIM(C1Y56CH04),	
TRIM(C1Y56CH08),	
TRIM(C1Y56CS09),	
TRIM(C1Y56CS03),	
TRIM(C1Y56CS04),	
TRIM(C1Y56CS05),	
TRIM(C1Y56CS06),	
TRIM(C1Y56CS07),	
TRIM(C1Y56CS08),	
TRIM(C1Y56CS10),	
TRIM(C1Y56CS11),	
TRIM(C1Y56CS12),	
TRIM(C1Y56CS13),	
TRIM(C1Y56CS15),	
TRIM(C1Y56CD05),	
TRIM(C1Y56CD13),	
TRIM(C1Y56CD04),	
TRIM(C1Y56CD14),	
TRIM(C1Y56CD06),	
TRIM(C1Y56CD11),	
TRIM(C1Y56CD07),	
TRIM(C1Y56CD10),	
TRIM(C1Y56CD12),	
C1BCT 
FROM CRPDTA.F56055C1 A
WHERE
C1Y55ELM01 = 'REP_ABSO'
AND ( TRIM(C1Y55ELM03) IS NULL or TRIM(C1Y55ELM03) IN ('EDITED') )
AND C1ICU = (SELECT MAX(C1ICU) FROM CRPDTA.F56055C1)
ORDER BY C1BCT
with t as (
           select  val,
                   last_value(
                              case val
                                when 'A' then 'A'
                              end
                              ignore nulls
                             )
                      over(
                           order by id
                          ) ||
                    last_value(
                              case val
                                when 'B' then 'B'
                              end
                              ignore nulls
                             )
                      over(
                           order by id
                          ) || val triad,
                   last_value(
                              case val
                                when 'A' then id
                              end
                              ignore nulls
                             )
                      over(
                           order by id
                          ) || ' + ' ||
                    last_value(
                              case val
                                when 'B' then id
                              end
                              ignore nulls
                             )
                      over(
                           order by id
                          ) || ' + ' || id seq
             from  tbl
          )
select  triad,
        seq
  from  t
  where val = 'D' 


What should I take id in this case because you have made a id column in your case. And if I run the above query it says
Error at Command Line:34 Column:14
Error report:
SQL Error: ORA-00933: SQL command not properly ended

[Updated on: Mon, 18 February 2013 20:51]

Report message to a moderator

Previous Topic: Varchar doesnot print zero front of number
Next Topic: subprogram or cursor 'C1' reference is out of scope
Goto Forum:
  


Current Time: Tue Sep 16 09:04:02 CDT 2014

Total time taken to generate the page: 0.22108 seconds