Helpful Information
 
 
Category: Access SQL help
Access SQL help >> pgsql...

Hello,

I have this access sql:

"SELECT Count(Ports.PortID) AS CountOfPortID
FROM ((Ports
RIGHT JOIN Switches ON Ports.SwitchID = Switches.SwitchID)
INNER JOIN SwitchTypes ON Switches.SwitchTypeID = SwitchTypes.SwitchTypeID)
WHERE (((Switches.SwitchID)=" & Me!SwitchID & ")
AND ( IIf(nz(InStr(1,[BBLabels],'.' & (Ports.PortLabel) & '.',1),0)>0,1,0)=1 )
AND (Ports.StatusCodeID)=" & InUseValue & ");"

which I need to use in php and postgres7.

The line:

AND ( IIf(nz(InStr(1,[BBLabels],'.' & (Ports.PortLabel) & '.',1),0)>0,1,0)=1 )

is throwing me for a loop as to how to get the same using php for postgres7.

From what I understand of it, it if saying that if the BBlabel is in the port label (or is this vice versa)

I wrote a php fn to check this (as i understand it):

$pos = stripos(port.p_label, switch_type.swt_backbone_label);
if $pos === false
return false
else
return true

how is this supposed to fit into the sql?

and if $pos === true ?

any help here?

Iif is non standard, look at the PostgreSQL manual for CASE (http://www.postgresql.org/docs/8.1/interactive/functions-conditional.html) and note that string concatenation (http://www.postgresql.org/docs/8.1/interactive/functions-string.html) is performed with ||

yes, iif is non-std...

my question is more what is the purpose and how can achieve thiseffect IFF it is needed...

sorta lost...

been staring @ it for hours...

Please, review the docs, there you can find the purpose of it and of the functions I suggested.
I can only add that NZ should be equivalent to COALESCE

Thank you again,

Upon review I see the importance of those references...

I am still up in the air about the line of code though...

I did not write this, neither is the author available any more.

could you shed some light on this line of code:

AND ( IIf(nz(InStr(1,[BBLabels],'.' & (Ports.PortLabel) & '.',1),0)>0,1,0)=1 )

thank you!

ok, so I believe the string is saying:

that

if the port label is in the bblabel return the value, else 1

then if instr returns > 0 nz returns 1 (true) else 0 (false)

iff nz returns one THEN it's ok...?

so is this like:


"SELECT count(port.p_id) AS cpid
FROM ((port
RIGHT JOIN switch ON port.p_switch_id = switch.sw_id)
INNER JOIN switch_type ON switch.sw_type_id = switch_type.swt_id)
WHERE (((switch.sw_id) = ".$_SESSION['s'].")
AND (CASE COALESCE(stripos(port.p_label, switch_type.swt_backbone_label))
WHEN > 0 THEN 1
WHEN 0 THEN 0
END)
AND (port.p_status_code_id) = ".$port_active

I still don't understand the AND part though and am not sure this will work...

so WHERE ...
AND 1
AND ...

CAn you help clarify a little more...

I believe that this was over complicated and that by using LIKE I can create the same functionality.

as such:


SELECT Count(port.p_id) AS cpid
FROM (port RIGHT JOIN switch ON port.p_switch_id = switch.sw_id) INNER JOIN switch_type ON switch.sw_type_id = switch_type.swt_id
WHERE (((switch.sw_id)=1) AND ((switch_type.swt_backbone_label) Like '%.port.p_label.%'))
GROUP BY port.p_status_code_id
HAVING (((port.p_status_code_id)=10));

that is assuming that :
Like '%.port.p_label.%'

will search against ~= anything .portlabel. anything

plz let me know if I am wrong...

SELECT count(port.p_id) AS cpid FROM (port RIGHT JOIN switch ON port.p_switch_id = switch.sw_id) INNER JOIN switch_type ON switch.sw_type_id = switch_type.swt_id WHERE (((switch.sw_id)=1) AND ((switch_type.swt_backbone_label) Like '%.port.p_label.%') AND ((port.p_status_code_id)=8));

this returns the right # of rows but doesn't return it as a count...