Home » SQL & PL/SQL » SQL & PL/SQL » differentiate item units (merged)
differentiate item units (merged) [message #294363] Thu, 17 January 2008 05:57 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
Hi All,
I have the following issue.

I have an Item with no 123445.

This item has a commodity pack size of 1 unit and a commodity pack size of 10 units but the user cannot see which is which.
so the user cannot distinguish between the single commodity pack size and a multipack commodity pack size.

I need to write a query for this.
can you please give me idea to distinguish these items.

Thank you,
Re: distingishing items [message #294375 is a reply to message #294363] Thu, 17 January 2008 06:43 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
What is Item?
From which table?
What are the other columns?
Does the user run from SQL*Plus or Oracle Forms or what else?

By
Vamsi
Re: distingishing items [message #294380 is a reply to message #294363] Thu, 17 January 2008 07:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
41 posts and this how you ask your question. Gee!

You are a newbie, stop posting in experts forum, all your questions are newbie ones, post in newbies forum.

In addition to previous post, what is the question your query is supposed to answer?

Regards
Michel
Re: distingishing items [message #294391 is a reply to message #294363] Thu, 17 January 2008 07:45 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
There is not one useful piece of information in this post. If you didn't mentioned the word "query" I wouldn't even know this has anything to do with a database.
differentiate item units [message #295104 is a reply to message #294363] Mon, 21 January 2008 06:15 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,
I have the table like as follows.


Item     Unit    material

12345     1       systems
12345     6       systems
2342      1       book
3245      1       cells
3245      8       cells
3289      1       shirt
3200      1       pants
3200      9       pants

from the above table for Eg. Item No 12345.

This item has a commodity pack size of 1 unit and a commodity pack size of 6 units but the user cannot see which is which.
so the user cannot distinguish between the single commodity pack size and a multipack commodity pack size.
Now the requirement is :::::
So need to update it as

if it has one unit then : systems_1
If it has multiple units Then : systems_6 [for item 12345]
Like this I need to update all the items.

Please write a query for this..
Please let me know if u need more Info...
Thank you.



Re: differentiate item units [message #295105 is a reply to message #295104] Mon, 21 January 2008 06:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Don't start a new topic, continue on the previous one.

Regards
Michel
Re: differentiate item units [message #295106 is a reply to message #295104] Mon, 21 January 2008 06:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Please write a query for this..

What about YOU writing the query?

What is the problem with concatenation?

Regards
Michel
Re: differentiate item units [message #295107 is a reply to message #295105] Mon, 21 January 2008 06:22 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
Ok. It's previous one.
The table name is "item_prods"
Remaining Cols : location ordered trans
123 100 1
132 100 6

Thank you,
Re: differentiate item units [message #295108 is a reply to message #295106] Mon, 21 January 2008 06:24 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
Hi Micheal ,

How to give concatenation for this ?

Really I don't know to add that particular one. Please help me to write this query..

Thank you.
Re: differentiate item units [message #295111 is a reply to message #295108] Mon, 21 January 2008 06:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Is not your result the concatenation of "material" and "unit"?

Regards
Michel
Re: differentiate item units [message #295115 is a reply to message #295111] Mon, 21 January 2008 06:35 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
hi,
if i concatenate like there will be another column right?
But i need to update in Material column itself.
How it is?
so i need o/p as
Item     Unit    material

12345     1       systems_1
12345     6       systems_6
2342      1       book_1
3245      1       cells_1
3245      8       cells_8
3289      1       shirt_1
3200      1       pants_1
3200      9       pants_9



Thank u

[Updated on: Mon, 21 January 2008 06:36]

Report message to a moderator

Re: differentiate item units [message #295117 is a reply to message #295115] Mon, 21 January 2008 06:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Use UPDATE statement.
At least try to do it.

Regards
Michel
Re: differentiate item units [message #295121 is a reply to message #295117] Mon, 21 January 2008 06:39 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
I have already tried it .. But I am not getting in that Material column. i am getting new_column named it as " Orig_material".

But this is not my req...
So I am requesting to write a query for this...

Thank u.
Re: differentiate item units (merged) [message #295124 is a reply to message #294363] Mon, 21 January 2008 06:41 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Show us how did you try .

Clue : You need to update the material field itself
No need to query a new field like "Orig_material".


Thumbs Up
Rajuvan
Re: differentiate item units (merged) [message #295127 is a reply to message #295124] Mon, 21 January 2008 06:47 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
i wrote the query like this...
select item,unit,material,concat(material||'_'||unit) orig_material
 from item_products;
Re: differentiate item units (merged) [message #295131 is a reply to message #294363] Mon, 21 January 2008 06:52 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Good ,

But you were asked to do a UPDATE statement . right ?
Did you check the link by Michel ?

Thumbs Up
Rajuvan.
Re: differentiate item units (merged) [message #295144 is a reply to message #294363] Mon, 21 January 2008 08:12 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Here is the demostration

SQL> desc  item_desc;
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------------
 ITEM                                               NUMBER
 UNIT                                               NUMBER
 MATERIAL                                           VARCHAR2(20)

before updating the values 


SQL> select * from item_desc;

      ITEM       UNIT MATERIAL
---------- ---------- --------------------
     12345          1 systems
     12345          6 systems
      2342          1 book
      3245          1 cells
      2345          8 cells
      3289          1 shirt
      3200          1 pants
      3200          1 pants

8 rows selected.

SQL> update item_desc set material = material||'_'||unit;

8 rows updated.

SQL> select * from item_desc;

      ITEM       UNIT MATERIAL
---------- ---------- --------------------
     12345          1 systems_1
     12345          6 systems_6
      2342          1 book_1
      3245          1 cells_1
      2345          8 cells_8
      3289          1 shirt_1
      3200          1 pants_1
      3200          1 pants_1

8 rows selected.
Re: differentiate item units (merged) [message #295145 is a reply to message #295144] Mon, 21 January 2008 08:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Do you think you are unable to give the answer?
Don't you think if you didn't give it there is a reason?
Think!

Regards
Michel
Re: differentiate item units (merged) [message #295150 is a reply to message #294363] Mon, 21 January 2008 08:26 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
@michel

sorry i didnot get you.
but my intension is to close this thread by giving the solution.
As i said early i am looking to help some one with the information i know and would like to leran from my mistakes.
so,looking for the opertunity to help help and help to enhance my self. Smile

[Updated on: Mon, 21 January 2008 09:33] by Moderator

Report message to a moderator

Re: differentiate item units (merged) [message #295161 is a reply to message #295150] Mon, 21 January 2008 09:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
But you don't help anyone giving obvious solution.
He must learn to find it by himself.
You improve yourself by finding the solution, very good!
Don't you think he should have the same chance?

Regards
Michel
Re: differentiate item units (merged) [message #295362 is a reply to message #295144] Tue, 22 January 2008 03:41 Go to previous message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
Hi ,

Thank youvery much..
Previous Topic: How To Create Dynamic Query
Next Topic: narration
Goto Forum:
  


Current Time: Sat Nov 09 21:14:55 CST 2024