RE: SQL problem
create view my_view(invoice#, line#, unit_price, cost_of_sales) asselect t1.invoice#, t1.line#, t1.cost, t2.cost
from my_table t1, my_table t2
where t1.invoice# = t2.invoice# and t1.line# = t2.line#
and t1.cost_type = 'unit price' and t2.cost_type = 'cost of sales'
When you have more cost types extend the view in a similar way
------------------------------------------------------------------------
-
Frido van Orden
FAA Partners BV
Planetenbaan 117
3606 AK Maarssen
The Netherlands
Phone: +31-346-587076
Fax: +31-346-587086
Email: fridoo@faapartners.com
------------------------------------------------------------------------
-
} -----Original Message-----
} From: peter.marley@acco-uk-co.uk [SMTP:peter.marley@acco-uk-co.uk]
} Sent: Wednesday, November 19, 1997 5:45 PM
} To: informix-list@rmy.emory.edu
} Subject: SQL problem
}
} I have to create a view that extracts its information from one table
} and generates one row in the view per invoice in the table. Is this
} possible in SQL?
}
} Example:
} Table has the following columns:
}
} invoice# line# cost type cost
} 00001 001 unit price 100
} 00001 001 cost of sales 200
}
}
} Can I create the following view:
}
} invoice# line# unit price cost of sales
} 00001 001 100 200
}
}
} Any help would be appreciiated,
}
} Thanks
}
} Peter
}
} __________________________________
} Peter Marley
} DataBase Manager, Acco UK Ltd
} Tel: +44 (0)1296 397444 ext. 4098
} Fax: +44 (0)1296 311019
} email: peter.marley@acco-uk.co.uk
}