By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,378 Members | 1,373 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,378 IT Pros & Developers. It's quick & easy.

Returning one field multiple times with different values

P: 5
Table 1 has Merchandise Class and 4 GL account number fields.
Expand|Select|Wrap|Line Numbers
  1. Merch     InventoryAcct      CostAcct      PayableAcct   SalesAcct
  2. Fish          1000             5000             2000          4000
  3. Beef          1100             5100             2100          4100
Table 2 is the GL Master table which hold Account #s and Account Descriptions
Expand|Select|Wrap|Line Numbers
  1. Acct      Description
  2. 1000       Fish Inventory
  3. 1100       Beef Inventory
  4. etc.
I want to return:
Expand|Select|Wrap|Line Numbers
  1. Merch   InvAcct   InvAcctDesc         CostAcct    CostAcctDesc    etc....
  2. Fish      1000      Fish Inventory      5000           Fish Cost
  3. Beef      1100      Beef Inventory      5100           Beef Cost
  4. etc..
I am querying an Informix database (unsure of version) on a Linux (Redhat) server using a variety of tools, including Crystal Reports and MS Query (through and ODBC link) and 'dbaccess', a program on the Linux server.

Also, as a bonus question, what book(s) or websites would you recommend to me to teach myself SQL?
Apr 3 '08 #1
Share this Question
Share on Google+
7 Replies


ck9663
Expert 2.5K+
P: 2,878
Table 1 has Merchandise Class and 4 GL account number fields.
Expand|Select|Wrap|Line Numbers
  1. Merch     InventoryAcct      CostAcct      PayableAcct   SalesAcct
  2. Fish          1000             5000             2000          4000
  3. Beef          1100             5100             2100          4100
Table 2 is the GL Master table which hold Account #s and Account Descriptions
Expand|Select|Wrap|Line Numbers
  1. Acct      Description
  2. 1000       Fish Inventory
  3. 1100       Beef Inventory
  4. etc.
I want to return:
Expand|Select|Wrap|Line Numbers
  1. Merch   InvAcct   InvAcctDesc         CostAcct    CostAcctDesc    etc....
  2. Fish      1000      Fish Inventory      5000           Fish Cost
  3. Beef      1100      Beef Inventory      5100           Beef Cost
  4. etc..
I am querying an Informix database (unsure of version) on a Linux (Redhat) server using a variety of tools, including Crystal Reports and MS Query (through and ODBC link) and 'dbaccess', a program on the Linux server.

Also, as a bonus question, what book(s) or websites would you recommend to me to teach myself SQL?
Try:

select Merch, InventoryAcct , CostAcct, PayableAcct, SalesAcct
from MerchandiseClass
inner join GLMaste ron MerchandiseClass.InventoryAcct = GLMaster.Acct

You did not include the table for CostAcctDesc so I would'nt know where to get it.

Also, INNER join was used assuming all InventoryAcct are in Acct

-- CK
Apr 3 '08 #2

P: 5
Try:

select Merch, InventoryAcct , CostAcct, PayableAcct, SalesAcct
from MerchandiseClass
inner join GLMaste ron MerchandiseClass.InventoryAcct = GLMaster.Acct

You did not include the table for CostAcctDesc so I would'nt know where to get it.

Also, INNER join was used assuming all InventoryAcct are in Acct

-- CK
CostAcctDesc is on the GLMaster table.
Don't I also need to select AcctDesc?

I get a syntax error when I try your query (see below). Oddly, it appears that the error is "join". After I get the syntax error message, when I go back into the editor the cursor goes to the "join" on line three.

select m_mcl, m_cst, m_sal, m_cic, m_ipa
from mglasg
inner join glgmas on mglasg.m_cst = glgmas.glmacn

table mglasg (Merchandise Class Master) contains:
m_mcl = Merch Class Code
m_cst = Inventory Acct
m_sal = Sales Acct
m_cic = Cost Acct
m_ipa = Payable Acct

glgmas is the GL Master table which is a list of GL accounts (glmacn) and their corresponding descriptions (glmdes).

Here is what I originally tried:
select m_mcl, m_cst, m_sal, m_cic, m_ipa, glmdes
from mglasg, glgmas
where m_cst = glmacn

but this returns:
Expand|Select|Wrap|Line Numbers
  1. Merch   InvAcct   InvAcctDesc         CostAcct    CostAcctDesc    etc....
  2. Fish      1000      Fish Inventory      5000           Fish Inventory
  3. Beef      1100      Beef Inventory      5100           Beef Inventory
  4. etc..
In other words, it's matching the inventory account (oddly named m_cst) to the glgmas.glmacn field and returning glmdes which is Fish (or Beef, etc.) Inventory, whereas I want column 3 of my returned data to be the Inventory Account Description and column 5 to be the Cost Account Description (there would also be columns 6-9 for Sales and Payable account # and description).
Apr 3 '08 #3

ck9663
Expert 2.5K+
P: 2,878
CostAcctDesc is on the GLMaster table.
Don't I also need to select AcctDesc?

I get a syntax error when I try your query (see below). Oddly, it appears that the error is "join". After I get the syntax error message, when I go back into the editor the cursor goes to the "join" on line three.

select m_mcl, m_cst, m_sal, m_cic, m_ipa
from mglasg
inner join glgmas on mglasg.m_cst = glgmas.glmacn

table mglasg (Merchandise Class Master) contains:
m_mcl = Merch Class Code
m_cst = Inventory Acct
m_sal = Sales Acct
m_cic = Cost Acct
m_ipa = Payable Acct

glgmas is the GL Master table which is a list of GL accounts (glmacn) and their corresponding descriptions (glmdes).

Here is what I originally tried:
select m_mcl, m_cst, m_sal, m_cic, m_ipa, glmdes
from mglasg, glgmas
where m_cst = glmacn

but this returns:
Expand|Select|Wrap|Line Numbers
  1. Merch   InvAcct   InvAcctDesc         CostAcct    CostAcctDesc    etc....
  2. Fish      1000      Fish Inventory      5000           Fish Inventory
  3. Beef      1100      Beef Inventory      5100           Beef Inventory
  4. etc..
In other words, it's matching the inventory account (oddly named m_cst) to the glgmas.glmacn field and returning glmdes which is Fish (or Beef, etc.) Inventory, whereas I want column 3 of my returned data to be the Inventory Account Description and column 5 to be the Cost Account Description (there would also be columns 6-9 for Sales and Payable account # and description).
Sorry, my bad. the GLMaster and on got stuck together.

Try this:
Expand|Select|Wrap|Line Numbers
  1. select Merch, InventoryAcct , CostAcct, PayableAcct, SalesAcct, inventory.Description as InvAcctDesc, Cost.Description as CostAcctDesc              
  2. from MerchandiseClass
  3. inner join GLMaster inventory on MerchandiseClass.InventoryAcct = inventory.Acct
  4. inner join GLMaster Cost on MerchandiseClass.CostAcct = Cost.Acct
-- CK
Apr 3 '08 #4

P: 5
Sorry, my bad. the GLMaster and on got stuck together.

Try this:
Expand|Select|Wrap|Line Numbers
  1. select Merch, InventoryAcct , CostAcct, PayableAcct, SalesAcct, inventory.Description as InvAcctDesc, Cost.Description as CostAcctDesc              
  2. from MerchandiseClass
  3. inner join GLMaster inventory on MerchandiseClass.InventoryAcct = inventory.Acct
  4. inner join GLMaster Cost on MerchandiseClass.CostAcct = Cost.Acct
-- CK
No, the syntax error wasn't due to the "GLMaste ron" part of your first post. I translated it all into the field names. The query that generated the error is that one in my first reply (2nd post), and it appears not to like the join keyword for some strange reason.

I have a call into our software vendor. Perhaps it has something to do with this particular flavor of Informix. When I've needed to to inner joins before I've just used a WHERE clause.

In the query you just posted, what exactly do you mean by "inventory.description as InvAcctDesc"? Which of those are table names, field names and aliases?
Apr 3 '08 #5

ck9663
Expert 2.5K+
P: 2,878
No, the syntax error wasn't due to the "GLMaste ron" part of your first post. I translated it all into the field names. The query that generated the error is that one in my first reply (2nd post), and it appears not to like the join keyword for some strange reason.

I have a call into our software vendor. Perhaps it has something to do with this particular flavor of Informix. When I've needed to to inner joins before I've just used a WHERE clause.

In the query you just posted, what exactly do you mean by "inventory.description as InvAcctDesc"? Which of those are table names, field names and aliases?

Whenever you see this ""inventory.description as InvAcctDesc", it means inventory.description is a column on the table and InvAcctDesc will be the new name/alias. If you save the query into a new table using the INTO keyword, the new name/alias will be the column name of the new table. So it has to be unique.

-- CK
Apr 4 '08 #6

P: 5
I finally got it to work with this:
Expand|Select|Wrap|Line Numbers
  1. select m_mcl , m_dsc, m_sal ,
  2. (select glmdsc Desc from glgmas where
  3. m_sal=glmacn),
  4.  
  5. m_cst,
  6. (select glmdsc from glgmas where
  7. m_cst=glmacn),
  8.  
  9. m_cic,
  10. (select glmdsc from glgmas where
  11. m_cic=glmacn),
  12.  
  13. m_ipa,
  14. (select glmdsc from glgmas where
  15. m_ipa=glmacn)
  16.  
  17. from mglasg
  18.  
Apr 10 '08 #7

ck9663
Expert 2.5K+
P: 2,878
That'll be slow specially if you have big tables. But if it suit you and it's working, it's your call.

-- CK
Apr 10 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.