[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 816
  • Last Modified:

Select Query

How do I correct this query using Pervasive SQL?

Original Query:
SELECT "APIBH".* FROM "GLPOST","APIBH" WHERE ("APIBH"."CNTBTCH"=1171) AND ("GLPOST". "DRILLDWNLK" = "APIBH"."CNTBTCH")

Modified Query:
SELECT "APIBH".* FROM "GLPOST","APIBH" WHERE ("APIBH"."CNTBTCH"=1171) AND ("GLPOST". + substring("DRILLDWNLK",7,4) + "= "APIBH"."CNTBTCH")"

I also need to cast "DRILLDWNLK" from a numeric value to a string value first.


0
jnsimex
Asked:
jnsimex
  • 3
  • 2
  • 2
  • +2
1 Solution
 
rushShahCommented:
hi try this..

SELECT	APIBH.* 
FROM	GLPOST,APIBH
WHERE	(APIBH.CNTBTCH=1171) AND (substring(Convert(VARCHAR(Max),GLPOST.DRILLDWNLK),7,4) = APIBH.CNTBTCH)

Open in new window

0
 
jnsimexAuthor Commented:
hi rushShah,

I get an error when i try to execute the query

SELECT  APIBH.*  
FROM    GLPOST,APIBH
WHERE   (APIBH.CNTBTCH=1171) AND (substring(Convert(VARCHA(Max),GLPOST.DRILLDWNLK),7,4) = APIBH.CNTBTCH)
Some error(s) encountered while executing SQL statement(script).
0
 
SharathData EngineerCommented:
what about this?
SELECT apibh.* 
FROM   glpost, 
       apibh 
WHERE  (apibh.cntbtch = 1171) 
       AND (Substring(Convert(VARCHAR(10),glpost.drilldwnlk),7, 
                      4) = apibh.cntbtch)

Open in new window

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
jnsimexAuthor Commented:
Pervasive doesnt like the second parameter for Convert

ODBC Error: SQLSTATE = 37000, Native error code = 0
The second parameter glpost for CONVERT is invalid.
0
 
LowfatspreadCommented:
try

SELECT "APIBH".* FROM "GLPOST","APIBH"
WHERE ("APIBH"."CNTBTCH"=1171)
AND  substring(Cast("GLPOST"."DRILLDWNLK" as char(30)),7,4) = "APIBH"."CNTBTCH"
0
 
SharathData EngineerCommented:
try with CAST as Lowfatspread suggested.
0
 
Anthony PerkinsCommented:
Please refrain from including the SQL Server 2005 Zone for Pervasive questions.  They do not apply.
0
 
jnsimexAuthor Commented:
I still get an syntax using lowfatspread has suggested:

ODBC Error: SQLSTATE = 37000, Native error code = 0
Syntax Error: SELECT "APIBH".* FROM "GLPOST","APIBH"
 WHERE ("APIBH"."CNTBTCH"=1171)
 AND  substring(Cast("GLPOST"."DRILLDWNLK" as<< ??? >> char(30)),7,4) = "APIBH"."CNTBTCH"

I tried to isolate the "DRILLDWNLK" value using substring and concactenating it with the "GLPOST" but i still get the same syntax error.

ODBC Error: SQLSTATE = 37000, Native error code = 0
Syntax Error: SELECT "APIBH".* FROM "GLPOST","APIBH"
 WHERE ("APIBH"."CNTBTCH"=1171)
 AND  "GLPOST." + substring(Cast(DRILLDWNLK as<< ??? >> char(30)),7,4) +" = "APIBH"."CNTBTCH""
0
 
LowfatspreadCommented:
you cant "isolate" the tablename/alias from the column name expression that doesn't make any sense to the syntax of sql!

why do you have the as<< ??? >>  in the middle of the code???
that isn't what i posted


try
AND  substring(Cast("GLPOST"."DRILLDWNLK" as char20),7,4) = "APIBH"."CNTBTCH"
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now