Showing posts with label eyes. Show all posts
Showing posts with label eyes. Show all posts

Monday, March 19, 2012

Need another pair of eyes sql Order by

my page suddenly stopped working when I wasn't working on it and it seems to be down to the 'ORDER BY' part of my SQL. I'm here alone as usual and I need someone to glance at the sql strings below. (yes, I do need the select *)
If I run this in SQL Manager it works fine:
SELECT * from dest_search WHERE trip_type like 'Trekking' ORDER BY start_date
if I do the same from my asp page it fails but if I leave out 'ORDER BY start_date' it works.

the error I get is:
Microsoft OLE DB Provider for SQL Server error '80040e21'
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

/Newindex/trip_types.asp, line 53

line 53 is the 'desc = oRS...' bizarrely
oRS.Open strSQL, oConn, 2, 3
oRS.moveFirst
Do while not oRS.eof
country = oRS("country")
53--> desc = oRS("description")
url_link = oRS("url_link")
startDate = oRS("start_date")
endDate = oRS("end_date")
trip = oRS("trip_type")
difficulty = oRS("difficulty")
not all the descriptions are filled in (some are null) but that doesn't stop SQL manager from working or unordered results coming up fine in my web page.

any comments gratefully received thanks.Is the SQL Statement hard-coded in the asp page? Or can people change the value of 'Trekking'?

Outside of that, you could try to reference the fields by number (starting at 0, naturally), and see if you are running into the fact that "Description" is a keyword in T-SQL.|||people can change TREKKING and that bit works, but I have simplified the sql string in this thread so people concentrate on what's going wrong.
the examples above work fine on the web page and SQL manager but the web version fails if I add 'ORDER BY start_date'
The fact that it all works fine makes me think there's no problem with spelling.
What's T-SQL and is that relevent to me using MS SQL Server and asp -vvbscript?|||T-SQL = Transact SQL. Microsoft/Sybase's version of the Structured Query Language standard. All queries passed to SQL Server are in T-SQL by definition.

I was wondering if an extra quote may have strayed into the word 'Trekking', which would more likely throw a syntax problem, though.

Have you tried using a different Cursor Type in the line:

oRS.Open strSQL, oConn, 2, 3

Maybe the dynamic cursor is the one having trouble with a sorted recordset. Not being a VB programmer of any note, this is more of an educated guess.|||test your spelling theroy
try - order by 4

also could be that ASP is terminating the string prior to order by.
some sort of ''' problem
Test this by removing the where clause

print / msgbox whatever ASP uses to select the SQL prior to running.

Just some thoughts|||thanks I'll have a go and get back 2u|||I removed the cursors and it works. thanks MCrowley

innitially they were 2,3 (whatever that means)
then I changed them to 0,1 and it worked then I removed them thinking that if I don't know what I'm doing I should go with the defaults... or change jobs.

Thanks for your time folks

Saturday, February 25, 2012

Need 2nd set of eyes on a CASE statement

I am going batty looking at this! I keep getting ORA-00907:Missing Right parenthesis when I have the 2nd case statement in this SQL (in bold). If I remove that line, it works just fine. As I don't see any left ( w/o a right ), I have no idea what the real issue is. (this is against a Oracle DB)

select rownum as IndexNum,
lm.hostlocid "Loc. Code",
pm.hostpartid "Comcode",
pm.partname "Description",
pc.hostplannercodeid "Plnr Code",
pc.codedescription "PC Descr",
sa.ohneworig "On Hand New Orig",
sa.ohfixedorig "On Hand Fixed Orig",
sa.ohbadorig "On Hand Bad Orig",
sa.onorderorig "On Order Orig",
sa.backorderorig "Backorder Orig",
sa.inrepairorig "In Repair Orig",
sa.inreturnorig "In Return Orig",
sa.allocatedorig "Allocated Orig",
sa.amountcustom1 "Amt Cus 1",
sa.amountcustom2 "Amt Cus 2",
sa.amountcustom3 "Amt Cus 3",
sa.amountcustom4 "Amt Cus 4",
sa.amountcustom5 "Amt Cus 5",
upper(ip.isaslitem) "Is ASL Item",
upper(ip.aslstatus) "ASL Status",
ip.stockmax "Stock Max",
ip.rop "Reorder Point",
pm.price "Std Cost",
fd.ForecastAmount,
to_char(fd.ForecastSliceDate,'MM-YYYY') "Current Period",
case when (sa.ohfixedorig + sa.ohneworig)=0 or fd.ForecastAmount=0 then null else round(((sa.ohfixedorig + sa.ohneworig)/(fd.ForecastAmount/30))) end Out_Of_Stock_Days,
pcd.ChainParentID,
pcd.RelationType,
case when pcd.relationtype = 1 then (N'Alt') else (N'Rep') end Chain_Type
from
stock_amount sa,
part_master pm,
loc_master lm,
loc_type lt,
stock_level ip,
planner_codes pc,
Forecasted_data fd,
part_chain_details pcd
where
sa.partid=pm.partid and
sa.locid=lm.locid and
lm.loctypeid=lt.loctypeid and
ip.partid=sa.partid and
pm.plannercodeid=pc.PLANNERCODEID and
ip.locid=sa.locid and
fd.partid = sa.partid and
fd.locid = sa.locid and
lm.hostlocid = 'B014' and
pcd.partid (+) = pm.partid and
(pc.hostplannercodeid = 'C50' or pc.hostplannercodeid = 'C51' or pc.hostplannercodeid = 'C52' or pc.hostplannercodeid = 'ZZ5') and
fd.ForecastSliceDate >ADD_MONTHS(SYSDATE,-1) and
fd.ForecastSliceDate<ADD_MONTHS(SYSDATE,0)

Any help showing the evil of my ways would be greatly appreciated!case when pcd.relationtype = 1 then (N'Alt') else (N'Rep') end Chain_Type

Your problem, is that both (N'Alt') and ('N'Rep') are invalid. The syntax to append text to a column is, (column||' text');|||try it without the Ns

Edit: there's no concatenation; ChainType is a column alias|||Thanks R123456 and R937.

For some reason I had in my head that when using a case statement to output a text value I had to preced the 'text' with (N'my text').

No I was not trying to append to a column! Big OOPS!

I decided to try a DECODE statement that works. (I also was not taking into account nulls!)

With decode( pcd.relationtype,1,'Alt',0,'Rep','') as Chain_Type, the output is what I expect.

Hopefully I can be back to the dynamic SQL issue I posted earler this week!

Thanks for the slap in the face to wake me up! :-)