meniyk
asked on
How do i Aliases sql query in vba excel to query oracle database
Hi
i'm currently using the excel vba to query data from oracle database, how can i use the Aliases in sql query with inner join statement.
1. How do i query using aliase?
2. if i use ADODB.Connection and ADODB.Recordset object to establish the connection with oracle database, does it means i need to use oracle syntax query to query data or i can use Ms Access sql syntax to do the query?
Thanks
i'm currently using the excel vba to query data from oracle database, how can i use the Aliases in sql query with inner join statement.
1. How do i query using aliase?
2. if i use ADODB.Connection and ADODB.Recordset object to establish the connection with oracle database, does it means i need to use oracle syntax query to query data or i can use Ms Access sql syntax to do the query?
select workorder.custpo, shipinfo.lotno shipDetails.shipdate, from (workorder INNER JOIN shipinfo ON workorder.workOrderNo = shipinfo.lotno) inner join shipDetails on shipDetails.shipID = shipInfo.shipID"
Thanks
there is a syntax error in that sql, remove the comma just prior to the word FROM
here are some stages that I hope help show how to apply aliases in the query
-- as is
SELECT
workorder.custpo
, shipinfo.lotno shipDetails.shipdate
,
FROM (
workorder INNER JOIN shipinfo ON workorder.workOrderNo = shipinfo.lotno
)
INNER JOIN shipDetails ON shipDetails.shipID = shipInfo.shipID
-- remove bad comma, fix a missing comma
SELECT
workorder.custpo
, shipinfo.lotno
, shipDetails.shipdate
FROM (
workorder INNER JOIN shipinfo ON workorder.workOrderNo = shipinfo.lotno
)
INNER JOIN shipDetails ON shipDetails.shipID = shipInfo.shipID
-- remove unwanted parenthese, introduce aliases (wo, si, sd )
SELECT
workorder.custpo
, shipinfo.lotno
, shipDetails.shipdate
FROM workorder AS wo
INNER JOIN shipinfo AS si ON workorder.workOrderNo = shipinfo.lotno
INNER JOIN shipDetails AS sd ON shipDetails.shipID = shipInfo.shipID
-- apply aliases (wo, si, sd ) to all other references
SELECT
wo.custpo
, si.lotno
, sd.shipdate
FROM workorder AS wo
INNER JOIN shipinfo AS si ON wo.workOrderNo = si.lotno
INNER JOIN shipDetails AS sd ON sd.shipID = si.shipID
-- make ON conditions consistent in sequence left/right
-- prior table first in sequence
SELECT
wo.custpo
, si.lotno
, sd.shipdate
FROM workorder AS wo
INNER JOIN shipinfo AS si ON wo.workOrderNo = si.lotno
INNER JOIN shipDetails AS sd ON si.shipID = sd.shipID -- changed here
-- finished query with aliases
SELECT wo.custpo, si.lotno, shipDetails.shipdate FROM workorder AS wo INNER JOIN shipinfo AS si ON wo.workOrderNo = si.lotno INNER JOIN shipDetails AS sd ON si.shipID = sd.shipID
useful website to "see" sql code in formatted fashion www.poorsql.com
2. does it means i need to use oracle syntax query
YES, if sending sql to Oracle it must be in Oracle compatible syntax
YES, if sending sql to Oracle it must be in Oracle compatible syntax
darn error in last line (still has shipdetail in it) the corrected finished query:
SELECT wo.custpo, si.lotno, sd.shipdate FROM workorder AS wo INNER JOIN shipinfo AS si ON wo.workOrderNo = si.lotno INNER JOIN shipDetails AS sd ON si.shipID = sd.shipID
ASKER
Hi PortletPaul,
Again thank you for your response.
It's does not work if the query with the alias. could it be the ADODB.Recordset object I used? Or the oracle sql query does not support alias syntax
even i have tried the simplest queries, it does not work if with alias syntax.. if without alias, it work...
sqlStr = "SELECT WO.WORKORDERNO FROM WORKORDER AS WO"
I'm not sure is there anything to do with the add-in reference in excel VBA? it is kind of strange that the simplest database queries with alias on it does not work..
thanks
regards
YK
Again thank you for your response.
It's does not work if the query with the alias. could it be the ADODB.Recordset object I used? Or the oracle sql query does not support alias syntax
even i have tried the simplest queries, it does not work if with alias syntax.. if without alias, it work...
sqlStr = "SELECT WO.WORKORDERNO FROM WORKORDER AS WO"
I'm not sure is there anything to do with the add-in reference in excel VBA? it is kind of strange that the simplest database queries with alias on it does not work..
thanks
regards
YK
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.