Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Query on Oracle DB via ODBC
16 views
Skip to first unread message
Mahen
Jun 8, 2009, 8:43:17 AM6/8/09
to
Why does the following query :
SELECT
st.KST_NAME AS Store,
dp.KST_NAME AS Dept,
wg.WGR_NAME AS ItemGrp,
vp.VPK_NAME AS Unit,
ar.ART_NAME AS Item,
hp.LBP_MENGEGE AS Qty,
hp.LBP_EKP AS UPrice,
hp.LBP_MENGEGE*hp.LBP_EKP as Cost,
wg.WGR_KONR2 AS AC,
dp.KST_KONR as CC,
EXTRACT(month FROM hl.LBW_DATUM) AS Mon,
EXTRACT(year FROM hl.LBW_DATUM) AS Yr,
us.BST_NAME as User,
hl.LBW_NAME AS Ref
FROM
ARTIKEL ar,
HIS_LAGERBEW hl,
HIS_LAGBEWPOS hp,
VPCKEINH vp,
WARENGRUPPE wg,
KOSTST st,
KOSTST dp ,
BESTELLER us
WHERE (
(hl.LBW_ID = hp.LBW_ID) AND
(hp.ART_NR = ar.ART_ID) AND
(hp.KST_ID = st.KST_ID) AND
(hp.KST_ID1 = dp.KST_ID) AND
(ar.WGR_ID = wg.WGR_ID) AND
(hp.VPK_ID = vp.VPK_ID) AND
(us.BST_ID = hp.NEW_USER) AND
EXTRACT(year FROM hl.LBW_DATUM) >=2007
)
ORDER BY
hl.LBW_DATUM
SELECT
st.KST_NAME AS Store,
dp.KST_NAME AS Dept,
wg.WGR_NAME AS ItemGrp,
vp.VPK_NAME AS Unit,
ar.ART_NAME AS Item,
hp.LBP_MENGEGE AS Qty,
hp.LBP_EKP AS UPrice,
hp.LBP_MENGEGE*hp.LBP_EKP as Cost,
wg.WGR_KONR2 AS AC,
dp.KST_KONR as CC,
EXTRACT(month FROM hl.LBW_DATUM) AS Mon,
EXTRACT(year FROM hl.LBW_DATUM) AS Yr,
us.BST_NAME as User,
hl.LBW_NAME AS Ref
FROM
ARTIKEL ar,
HIS_LAGERBEW hl,
HIS_LAGBEWPOS hp,
VPCKEINH vp,
WARENGRUPPE wg,
KOSTST st,
KOSTST dp ,
BESTELLER us
WHERE (
(hl.LBW_ID = hp.LBW_ID) AND
(hp.ART_NR = ar.ART_ID) AND
(hp.KST_ID = st.KST_ID) AND
(hp.KST_ID1 = dp.KST_ID) AND
(ar.WGR_ID = wg.WGR_ID) AND
(hp.VPK_ID = vp.VPK_ID) AND
(us.BST_ID = hp.NEW_USER) AND
EXTRACT(year FROM hl.LBW_DATUM) >=2007
)
ORDER BY
hl.LBW_DATUM
result in the following error:
[Microsoft][ODBC driver for Oracle][Oracle]ORA-00923: FROM keyword not
found where expected
?
Plamen Ratchev
Jun 8, 2009, 9:00:17 AM6/8/09
to
It may be the use of the FROM keyword in the EXTRACT function. This is
valid Oracle syntax, but I have seen some ODBC drivers that do not
support EXTRACT. You can try to replace the use of EXTRACT with TO_CHAR:
valid Oracle syntax, but I have seen some ODBC drivers that do not
support EXTRACT. You can try to replace the use of EXTRACT with TO_CHAR:
EXTRACT(month FROM hl.LBW_DATUM)
with
TO_CHAR(hl.LBW_DATUM, 'mm')
--
Plamen Ratchev
http://www.SQLStudio.com
Mahen
Jun 8, 2009, 9:11:02 AM6/8/09
to
Tried this, but same message results. The funny thing is the above
code worked the day before... any clues?
Plamen Ratchev
Jun 8, 2009, 10:18:08 AM6/8/09
to
You can change your old style join to the ANSI-92 join style and try that:
SELECT ...
FROM HIS_LAGERBEW AS hl
JOIN HIS_LAGBEWPOS AS hp
ON hl.LBW_ID = hp.LBW_ID
JOIN ARTIKEL AS ar
ON hp.ART_NR = ar.ART_ID
JOIN KOSTST AS st
ON hp.KST_ID = st.KST_ID
JOIN KOSTST AS dp
ON hp.KST_ID1 = dp.KST_ID
JOIN WARENGRUPPE AS wg
ON ar.WGR_ID = wg.WGR_ID
JOIN VPCKEINH AS vp
ON hp.VPK_ID = vp.VPK_ID
JOIN BESTELLER AS us
ON us.BST_ID = hp.NEW_USER
WHERE EXTRACT(year FROM hl.LBW_DATUM) >= 2007
...
Mahen
Jun 9, 2009, 12:24:02 PM6/9/09
to
Thanks for taking the trouble. It looks like the registry on my
machine got corrupted after some software installation. . . leading to
this problem. Currently re-installing Windows.
machine got corrupted after some software installation. . . leading to
this problem. Currently re-installing Windows.
0 new messages